MySQL Database User Security

All too often, especially on small setups, database security is ignored. When you’ve got a single machine hosting your entire LAMP application stack it’s easy to use the same username and password for all your applications. The worst example of this sort of habit is to simply use the root user and password for all your applications connecting to the MySQL server. This is often compounded by the fact that many applications aren’t designed with strict security or efficient design in mind so the database username and password show up on every single script that calls the database, or worse, every single time the database is called.

This can lead to a scenario where your root database username and password are spread all over the filesystem, just waiting for someone unscrupulous to find them. While this certainly increases exposure, especially if the username and password are carelessly left in a web readable directory, in most cases an attacker will have to have filesystem access in order to read the authentication pair.

More practically this practice can lead to nightmares if the database information changes, or if the application has to be ported to another server. The developer must hunt all over the place and update each and every instance of the data connection details.

The best practice is to list the connection details only once per application, and to put the script or file that includes this information in a protected directory. Then you can call the page using PHP includes so that if you ever have to update the information it’s located in only one place.

While this alleviates some of the problems of maintenance, if you’re using the same username and password for all of your applications, or if you’re using the root user account then you’re asking for trouble. Doing so exposes all your applications to any weakness in any one of your applications. Thus, the overall security of your data layer becomes as weak as your weakest application. If one application exposes a SQL injection vulnerability then an attacker could potentially compromise or destroy all of the data in your database, because the application uses the same user account as every other and has access to all of them.

The best way to avoid this sort of situation is to create an account for each application. Then you can limit not only the power of the account to specific databases and actions. In this way you can restrict the account only to operations necessary to the application, and only to data that is required by the application. For instance, if an application only needs read access to tables in database X then there is no reason to grant more than select privileges to that account on the specifically needed tables in database X.

Often times when developing an application you might need to create a super user account to actually set up the data structure, test and deploy the application, but this should be created as a separate account.

When setting up an account in MySQL you can limit not only the privileges the account has and the databases and tables the account can exercise these privileges on, but also the host from which the account connects. For instance, if you plan for an account to be used to power a blog application from your web server you could create an account with select, insert, update and delete privileges on each table of the blog database. To create this account all you have to do is log into MySQL as a user with privileges to create users and type in:

mysql> GRANT SELECT,INSERT,UPDATE,DELETE ON blog.* TO ‘blog_web_user’@'webserver’
-> IDENTIFIED BY ’some_pass’;

where ‘blog’ is the name of the database, ‘blog_web_user’ is the name of the account, ‘webserver’ is the name (or IP address) of the web server, and ’some_pass’ is the password you wish to assign to the new account.

You can give all the privileges by using ‘GRANT ALL’ in lieu of a comma delimited list. The available privileges you can assign are:

DELETE
INSERT
SELECT
UPDATE
CREATE
DROP
RELOAD
SHUTDOWN
PROCESS
FILE
GRANT
REFERENCES
INDEX
ALTER
SHOW_DB
SUPER
CREATE_TMP_TABLE
LOCK_TABLES
EXECUTE
REPL_SLAVE
REPL_CLIENT

Be judicious about which privileges you need to grant to your web user. Keep in mind that the account will only be used for the actual application. If the application doesn’t need to create or drop tables there is no reason to grant those privileges to the account.

By following this process you insure that the account can only be used to access specific tables and databases from specific location. You can further lock down privileges by limiting the resources available to accounts. The values you can limit are:

MAX_QUESTIONS_PER_HOUR
MAX_UPDATES_PER_HOUR
MAX_CONNECTIONS_PER_HOUR

on MySQL 4.1.12, but this varies from version to version. The appropriate columns are listed in the user table in the mysql database. You can only limit the resources for an account on a per hour. This allows you to throttle the usage a specific user has with respect to their privileges and can help to fine tune resources.

You can delete users simply by issuing:

mysql> drop user username;

Where ‘username’ is the name of the user. You can also delete the user from the user table using:

mysql> delete from user where user = ‘username’;

Be sure to issue a:

mysql> FLUSH PRIVILEGES;

Once you’re done granting new privileges to let the database know to re-read the privileges table, otherwise your changes won’t take effect until the database is restarted.

While all of this might seem like a hassle, once the user account is set up it probably won’t ever get modified. Taking the extra time to ensure that a secure setup is put in place in the beginning of a project can save time and headaches down the road. Segregating application user accounts allows for easier auditing and maintenance as well.