"Grant All" to a Web DB User?
By Duleepa Wijayawardhana on Jan 23, 2009
Please don't. Please, please don't. I don't know the number of times I have seen this, heck I know that certain software installations recommend you allowing a "grant all" to the user which will connect to the db from that software (see WordPress installation guide).
So... please don't.
One of the quotes I live by is one I remember from my university days and uttered by the German playwright Bertolt Brecht (I've never found the actual quote, if someone knows let me know) where he basically says, if you put a telephone on stage, use it.
MySQL grants for a user should only include what that user needs, never more, never less. If a piece of software, or if you as a programmer/dba decide to use "grant all", make sure the software or you actually needs that and remove it once it is not necessary. If not you are setting yourself up for disaster.
So here's what happens typically: You have a web server with a database and the user from the web site connects locally. You are fairly smart so you disable access to the database from the outside world and the user can only access from localhost. So far, so good. Now, there are very few ways you can hack and gain shell access onto a linux server that keeps its packages updated, so you feel fairly confident (you should never assume this btw). Your misplaced confidence leads you to believe you can give "all" access to a db user used by a web site. You have basically said that web user can do whatever they want. They can "drop" your database even.
How can someone do that without shell access or direct access to the DB? Well one way is that they can do it by means of SQL injection.
Wait! You say, "Wait! I know my code, I know my skill, I have taken care of SQL injection." Well, unless you are smarter than almost every coder I know and you do not use any third-party packages or software (like WordPress, or Gallery or whatever) then you are mistaken.
Why am I writing this? Well this week I witnessed the "trifecta": A directory opened for global public read with the SQL password in a plain text file, the grant statement for that user being "all" and a SQL injection possibility (even if minor and unlikely to do any damage) on a single site.
So please, even if accidentally, even if temporarily, be mindful of what grants you give what user.
Paul DuBois has an older but still relevant article on securing a MySQL installation. There is also notes on securing a MySQL Installation in the MySQL manual.