Securing Your MySQL Installation

Paul DuBois
paul@kitebird.com

Document revision: 1.01
Last update: 2003-01-25

Table of Contents


Introduction


A MySQL installation should be made as secure as possible to protect databases and other information maintained by the MySQL server from unauthorized access. This article describes potential problem areas about which you should be concerned as a MySQL administrator, and provides guidelines for dealing with them. The issues covered here fall into the following broad categories, which include both local and remote exploits:

This article assumes that you have some basic understanding of MySQL's access privilege system. It also assumes an understanding of the distinction between a MySQL account and a login account. (Briefly, a MySQL account refers to an account that is listed in the user table of the mysql database and which has database access privileges that are enforced by the MySQL server. These accounts differ from login accounts, which have file system access privileges that are enforced at the operating system level.)

Unix commands shown with a # prompt should be run as root. Commands shown with a % prompt may be run as an ordinary user. SQL statements shown with a mysql> prompt are displayed as you would enter them using the mysql command-line client program.

General Security Considerations


This section outlines aspects of a suggested MySQL security strategy. Subsequent sections provide specific instructions to help you carry it out. In general, security principles you should consider adopting are as follows:

Another facet of your strategy should be a concern about system security in general. An attacker who gains root login access to a Unix system running a MySQL server can access the data directory no matter what access restrictions you institute. A general security discussion is beyond the scope of this article, though.

Securing the MySQL Server and Data Directory


The instructions in this section apply to Unix systems. If you're running Windows, you can skip ahead to "Securing MySQL Accounts." The procedure described here shows how to run the MySQL server using an unprivileged (non-root) login account and how to protect the server's data directory so that it can be accessed directly only by that account. You'll need to run the commands shown here as root, because they involve login account creation, as well as file ownership and mode changes.

Securing Multiple-Server MySQL Installations


If you run multiple MySQL servers on a single host, you should repeat the procedure just described for each server and its data directory. However, if you want to use different accounts to run different servers, you cannot put a user line in the [mysqld] group of the /etc/my.cnf file--it will be used for all servers. Use one of the following methods instead:

Securing Option Files


Option files are a potential point of compromise because they contain information used to control the server's operation or to store client connection parameters such as MySQL user names and passwords. Option files can be global, server-specific, or user-specific. The precautions you take to secure a given file depend on its scope and purpose.

Global Option Files


/etc/my.cnf is a global option file used both by the MySQL server and by client programs. It should be protected against unauthorized modification (to prevent someone from changing it to tell the server to start up as root, for example), but must also be publicly readable so that client programs can access it. To make this file owned by and modifiable only by root but readable to anyone, use these commands:

   # chown root /etc/my.cnf
   # chmod 644 /etc/my.cnf
Because this file is world-readable, it should not be used to specify password values.

Server-Specific Option Files


A MySQL server can have its own private option file named my.cnf located in the server's data directory. This file should be owned by and accessible only to the account used to run the server. If you've followed the procedure outlined earlier for securing your data directory, these access constraints should be satisfied already. To set the ownership and mode explicitly (assuming the data directory is /usr/local/mysql/data), use these commands:

   # chown mysqlusr /usr/local/mysql/data/my.cnf
   # chmod 600 /usr/local/mysql/data/my.cnf
In a multiple-server installation, you'd need to repeat these commands, substituting the appropriate user name and option file pathname, of course.

User-Specific Option Files


On multiple-user systems, each login account may have its own option file. The file is named .my.cnf and is located in the account's home directory. Personal option files are commonly used to store MySQL user names and passwords, so each one should be accessible only to its owner to prevent other users from reading its contents. A user who has a .my.cnf file can (and should) make it private by executing this command:

   % chmod 600 .my.cnf
Although this precaution can be left up to the discretion of individual MySQL users, an administrator who prefers a more proactive approach can run a program daily from cron that looks for .my.cnf files to make sure that each one has the proper ownership and a restrictive access mode. A sample Perl script that does this may be found in the Appendix at the end of this article.

Securing MySQL Accounts


The MySQL installation procedure creates a database named mysql containing the grant tables that the server uses to determine which MySQL accounts can do what. In particular, the user table in the mysql database lists all valid accounts and indicates which global privileges they have, if any. This section provides some guidelines that you can use to evaluate existing accounts, and that you should keep in mind when creating new accounts. These guidelines apply to servers running on any platform.

A general set of principles for securing the MySQL accounts listed in your grant tables is as follows:

If you have never examined the grant tables that are set up during the MySQL installation procedure, you should do so now, using the instructions in the following sections. The default grant tables created during MySQL installation include accounts for the MySQL root user as well as some "anonymous" accounts that can be used without specifying a user name. These anonymous accounts are convenient for initial testing, but should be removed when you're satisfied that the server is running properly. In addition, none of the default accounts have a password initially--not even the root accounts! This is a security hole that should be fixed by assigning passwords.

Remove Anonymous MySQL Accounts


Anonymous MySQL accounts allow clients to connect to the server without specifying a user name. Under Windows, the default accounts may even have full access to any database managed by the server. To remove anonymous accounts, connect to the server as the MySQL root user to access the mysql database, then issue the following statements:

   mysql> DELETE FROM user WHERE User=";
   mysql> FLUSH PRIVILEGES;
The DELETE statement removes accounts that have an empty value in the User column of the user table that lists MySQL accounts, and FLUSH PRIVILEGES tells the server to reload the grant tables so the changes take effect.

You may also want to check the other grant tables in the mysql database for rows that have empty User values. Those rows can be removed, too.

Make Sure MySQL Accounts Have Passwords


MySQL accounts that do not have passwords should either be removed or assigned passwords. To find such accounts, look in the user table of the mysql database using the following query:

   mysql> SELECT * FROM user WHERE Password=";
The accounts corresponding to any rows returned by this query are insecure and should be assigned passwords. To do this, you must specify both the user name and hostname associated with an account. Suppose the values in the User and Host columns for an account having no password are user_name and host_name. The statement to assign a password of new_pass to that account looks like this:
   mysql> UPDATE user SET Password=PASSWORD('new_pass')
       -> WHERE User='user_name' AND Host='host_name';

You can also use a SET PASSWORD statement:
   mysql> SET PASSWORD FOR 'user_name'@'host_name'=PASSWORD('new_pass');
If you assign passwords with UPDATE, issue a FLUSH PRIVILEGES statement afterward to tell the server to reload the grant tables so the changes take effect.

Don't Use Host Wildcards Unnecessarily


A MySQL account is identified by both a user name and a hostname, which are found in the User and Host columns of the user table. The User value is the name that a client must supply when connecting to the server. The Host value indicates the host or hosts from which the user is allowed to connect. If this is a literal hostname, the account is limited to connections only from that host. If the hostname is a pattern such as %.xyz.com or % that contains the '%' wildcard character, the user can connect from any host in the xyz.com domain or from any host at all.

From a security standpoint, literal Host values are best and % is the worst. Accounts that have Host values containing wildcards are more susceptible to attack than accounts with literal Host values, because attackers can attempt to connect from a broader range of machines. For example, if an account has User and Host values of root and %, it means that you can connect as root from anywhere if you know the password. An attacker must guess the password, but may attempt to do so by connecting from any host. By contrast, if the host name is localhost, the attacker can attempt to connect as the root user only after first gaining access to the server host.

To find existing accounts that contain the '%' wildcard character anywhere in the Host column of the user table, use the following query:

   mysql> SELECT * FROM user WHERE Host LIKE '%\%%';
If you discover a hostname value that is overly broad (for example, very few accounts really require a value of % in the Host column), change it with UPDATE to something more restrictive. Then check the other grant tables for rows with the corresponding User and Host values and change the Host columns of any such rows as well. If you do modify any accounts, issue a FLUSH PRIVILEGES statement afterward to tell the server to reload the grant tables so the changes take effect.

When you create new accounts (with the GRANT statement), avoid host values that contain wildcards, or at least constrain them so they are only as broad as necessary. Hostname patterns can be convenient for setting up an account that can be accessed by a given user from a set of machines, but you shouldn't use them unless you really need to. In particular, resist the temptation to simply create all accounts with % as the hostname.

Don't Grant Global Privileges Unnecessarily


Global privileges are granted using *.* in the ON clause of the GRANT statement:

   GRANT privileges ON *.* TO ...
However, in most cases, global privileges should be allowed only for the MySQL root user, and possibly for other accounts used by people that you trust. Global privileges should otherwise not be granted because they allow users to perform operations that may be dangerous. For example, the FILE privilege allows a user to read and write files on the database server (which includes stealing databases), the PROCESS privilege allows currently executing queries to be monitored, and the SHUTDOWN privilege allows a user to shut down the server.

If you discover an existing account with global privileges that it does not need, you can remove them using the REVOKE statement. To take away the FILE privilege from an account with User and Host values of user_name and host_name in the user table, the REVOKE statement looks like this:

   mysql> REVOKE FILE ON *.* FROM 'user_name'@'host_name';
To revoke all global privileges, use this statement instead:
   mysql> REVOKE ALL ON *.* FROM 'user_name'@'host_name';
There is one exception to REVOKE ALL, which is that the GRANT OPTION privilege must be revoked explicitly:
   mysql> REVOKE GRANT OPTION ON *.* FROM 'user_name'@'host_name';

Using Passwords Securely


Provide your MySQL users with the following guidelines to help them avoid unnecessary exposure of their passwords when connecting to the server:

Appendix


The following script, chkmycnf.pl, can be used to look for user-specific .my.cnf option files in the home directory of each account listed in the /etc/passwd file. It makes sure that the file is owned by the proper account and that the mode disallows access to everyone but the file owner. It must be run as root so that it can make any file ownership of access mode changes that are necessary. Typical use is as a daily cron job run from root's crontab file.

   #! /usr/bin/perl -w
   # chkmycnf.pl - check user-specific .my.cnf files and make sure the
   # ownership and modes are correct. Each file should be owned by the
   # user in whose home directory the file is found. The mode should
   # have the group and other permissions turned off. The script must
   # be run as root.

   use strict;

   @ARGV = ("/etc/passwd");
   while (<>)
   {
       my ($uid, $home) = (split (/:/, $_))[2,5];
       my $cnf_file = "$home/.my.cnf";
       next unless -f $cnf_file;   # does account have .my.cnf file?
       if ((stat ($cnf_file))[4] != $uid)
       {
           warn "Changing ownership of $cnf_file to $uid\n";
           chown ($uid, (stat ($cnf_file))[5], $cnf_file);
       }
       my $mode = (stat ($cnf_file))[2];
       if ($mode & 077)    # test "group" and "other" access bits
       {
           warn sprintf ("Changing mode of %s from %o to %o\n",
                           $cnf_file, $mode, $mode & ~077);
           chmod ($mode & ~077, $cnf_file);
       }
   }
   exit (0);

Acknowledgment


The original version of this article was written for NuSphere Corporation. The current version is an updated revision of the original.