I seem to have painted myself into a corner with my efforts to run a secure PHP / MySQL site. I created a MySQL user that has minimal powers to run the site, so no DELETE privilege, amongst others, and it all works fine. Recently, I have added extensions that look for excessive users taking up too many resources and I ban them for 24 / 48 hours based on IP address. Not perfect, but it deters the average user from too much abuse.
That works fine too. The issue is that my list of 24 hour bans is in a table that grows at an alarming rate. Ideally I’d have a script that deletes everything in the table whose timestamp is older than the ban period as they are no longer needed. Then I could run that script from a cron job.
The problem is that that script fails because the user account credentials don’t include DELETE privileges. If I create an extra user who does have DELETE, aren’t I undermining the security of the whole site? Is there a middle way or other solution, or just some precautions that I can take. What’s the best way to resolve this conflict?
The whole point of having separate accounts is so that you can give each account the privileges appropriate to its duties. If you need to delete things, you obviously have to create an account with DELETE privileges. But that doesn’t undermine everything, because all the other accounts are still restricted.
Furthermore, this account doesn’t have to have widespread
privileges. You can just grant it the privilege to
in the table that lists bans. Although if you try to get too fine-grained with permissions, you’re likely to drive yourself crazy.
GRANT DELETE on dbname.bans TO [email protected];
Finally, if you have an account that has
privileges, it’s not so bad to allow it
privileges as well. Because even if it can’t delete rows, it could still totally mess up your data by replacing rows with garbage.