Securing Your MySQL Installation

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

Introduction
General Security Considerations
Securing the MySQL Server and Data Directory
- Securing Multiple-Server MySQL Installations
Securing Option Files
Securing MySQL Accounts
Appendix
Acknowledgment

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:

Unauthorized use of information by clients that connect to the server and use it to gain access to information in ways that you do not intend. This can occur if you have MySQL accounts that allow clients to connect without a user name or password, or if someone has stolen a legitimate MySQL user name and password. To address this problem, you can remove anonymous accounts, make sure that each account has a password, and instruct legitimate users not to use passwords in ways that expose them to discovery by other users.
Unauthorized access by clients that exploit the MySQL server's file system access privileges. You can address this by running the server using an ordinary unprivileged user account rather than the root account. You can also restrict the number of accounts that have global server privileges such as the FILE privilege that allows a client to read and write files on the server host.
On multiple-user systems such as Unix, it is possible for attacks to be mounted against your MySQL installation by people with login accounts on the server host who bypass the MySQL server entirely to access the data directory directly. You can prevent this by making the data directory accessible only to the account that is used for running the server.

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:

Run the server using an ordinary (unprivileged) login account, not as root
When the MySQL server runs, it executes with the privileges of the login account under which it runs. If the server runs as root, it has the same privileges as the root account, such as the ability to read and write files anywhere in the file system. Clients may attempt to take advantage of the server's root privileges to read privileged information or write files that modify the operation of your system. Running the server using an ordinary login account that has no special privileges minimizes this risk by preventing the server from accessing files it shouldn't be able to access.
Make the MySQL data directory accessible only to the server account
This directory is where the server creates databases and writes its log files. Securing the directory by making it accessible only to the account used for running the server prevents other users from reading or writing database contents directly to steal or modify data. It also prevents them from monitoring the log files to find out what kind of queries are being issued by legitimate MySQL users.
Protect option files properly
Connection parameters such as user names and passwords may be stored in option files. Make sure all option files are protected against illegitimate examination or tampering.
Examine existing MySQL accounts for insecure settings or excessive privileges
The MySQL accounts listed in the user table of the mysql database where the grant tables are located should be checked. If you have anonymous accounts that allow clients to connect without even knowing a user name, remove them. If you have accounts that have no password, remove them or assign passwords to them. If an account is associated with a hostname specifier that is a pattern, make sure that the pattern is not overly broad. (Reducing the number of hosts from which connections may be made also reduces the number of hosts from which break-in attempts may be mounted.) If an account has global privileges that it does not need (such as the FILE, PROCESS, or SHUTDOWN privileges), revoke them.
Take care when you create new MySQL accounts
Follow the same guidelines used to examine existing accounts: Require each account to have a non-empty user name and password, avoid use of wildcards in hostname specifiers, and grant only such privileges as are really needed (especially global privileges).
Educate your users
Help your MySQL users protect their names and passwords from exposure. This includes making personal option files secure and not using passwords in ways that allow them to be discovered.

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.

Determine which login account will be used to run the server and to own the server's data directory. (The account is denoted as mysqlusr in this article.) Create the account if necessary. The procedure for adding login accounts varies among versions of Unix; consult the documentation for your system.
Bring down the server if it's running:
```
   # mysqladmin -p -u root shutdown
```
Omit the -p option if you have not yet set up a password for the MySQL root account.
Set the ownership and access mode of the MySQL data directory and any files and directories under it. You want the data directory to be accessible only to the mysqlusr account, to prevent other people with login accounts on the server host from bypassing the server to gain direct access to the directory's contents. If the data directory is located at /usr/local/mysql/data, the commands look like this:
```
   # chown -R mysqlusr /usr/local/mysql/data
   # chmod -R go-rwx /usr/local/mysql/data
```
If your MySQL installation follows the usual convention of storing individual database directories directly under the data directory, these commands suffice to secure it. However, if your data directory contains symbolic links that point to databases located elsewhere and your versions of chown and chmod don't follow symlinks, you'll need a different approach. Either follow the links yourself and run the appropriate chown and chmod commands in the directories where they lead, or else change the ownership and mode of the data directory contents using the following commands rather than those just shown:
```
   # find /usr/local/mysql/data -follow -print \
       | xargs chown mysqlusr
   # find /usr/local/mysql/data -follow -print \
       | xargs chmod go-rwx
```
Whichever pair of commands you use to lock down the data directory so that it can be accessed only by the mysqlusr account, you may have a problem if the data directory contains the socket file that the server uses to listen for local connections. In that case, other people on the server host won't be able to connect to the server through the socket. To remedy this difficulty after restricting access to the data directory as described above, you can use the following command; it opens up the data directory enough that client programs can access the socket file:
```
   # chmod go+rx /usr/local/mysql/data
```
Unfortunately, making the directory readable to other local login accounts also allows them to see what files and directories it contains. A better solution, if you install MySQL from source, is to recompile the MySQL distribution to relocate the socket file outside the data directory. Use the --with-unix-socket-path option when you run the distribution's configure script. Relocating the socket file by specifying a different pathname for it in an option file is an incomplete solution because it fails to affect clients that do not read option files.
Restart the server, telling it to run as the mysqlusr user. To start the server manually, do this:
```
   # mysqld_safe --user=mysqlusr &
```
However, you can avoid specifying the user name on the command line if you add a user line to the [mysqld] group in the /etc/my.cnf option file:
```
   [mysqld]
   user=mysqlusr
```
This approach has the advantage that it works both for mysqld_safe and for mysql.server, the two scripts most commonly used to start the MySQL server at system boot time. This means that if you put the user value in /etc/my.cnf, no changes will be necessary to your system's startup procedure.

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:

Create a server-specific my.cnf option file in the data directory for each server. Each such file applies only to the server managing a given data directory, so you can specify different user values in the [mysqld] group of different option files.
If you start your servers using the mysqld_multi script, a second approach is to continue using the global /etc/my.cnf option file, but specify user lines in server-specific groups named [mysqld1], [mysqld2], [mysqld3], and so forth. Check the MySQL Reference Manual for more information about mysqld_multi.

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:

Remove anonymous accounts
Make sure that each account has a password
Don't grant global privileges unnecessarily
Avoid using wildcards in the hostname value associated with accounts
Instruct your MySQL users how to keep their passwords secure

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:

Passwords should not be specified on the command line; there is a brief time window during which passwords specified this way can be viewed by other users with process status utilities such as ps. This means that the -ppassval and --password=passval forms of the password options should be avoided. Instead, use -p or --password (with no following passval value) to tell the program to prompt for the password.
MySQL allows a password to be specified using the MYSQL_PWD environment variable, but this practice should be avoided. Other users can use process status utilities to view user environment information and discover the password value.
A password need not be specified on the command line if it is stored in a user's personal option file. However, this file should have its access mode restricted only to its owner so that any passwords in it cannot be read by other users. See "Securing Option Files" for more information.

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.