Internal Security: Preventing Unauthorized Filesystem Access

This section shows how to lock down your MySQL installation to keep it from being tampered with by unauthorized users on the server host. The section applies only to Unix systems; I assume that if you're running a server on Windows, you have complete control of the machine and that there are no other local users. (In other words, it's best to keep a Windows-based server out of the reach of other users and restricted from general access.)

The MySQL installation procedure creates several directories, some of which need to be protected differently than others. For example, there is no need for the server binary to be accessible to anyone other than the MySQL administrative login account. By contrast, the client programs normally should be publicly accessible so that other users can run thembut not so accessible that other users can modify or replace them.

Other files to be protected are created after the initial installation, either by yourself as part of your post-installation configuration procedure, or by the server as it runs. Files created by you include option files or SSL-related files. Directories and files that the server creates for itself as it runs include database directories, the files under those directories that correspond to tables in the databases, log files, and the Unix socket file.

Clearly you want to maintain the privacy of the databases maintained by the server. Database owners usually, and rightly, consider database contents private. Even if they don't, it should be their choice to make the contents of a database public rather than having its contents exposed due to insufficient protection of the database directory.

The log files must be kept secure because they contain the text of statements sent by clients to the server. This is a general concern in that anyone with log file access can monitor changes to the contents of databases. A more specific security issue relating to log files is that statements such as GRANT and SET PASSWORD are logged. The log files thus contain the text of sensitive statements, including passwords. MySQL uses password encryption, but this applies to connection establishment after passwords already have been set up. The process of setting up a password involves a statement such as GRANT, SET PASSWORD, or INSERT, and these statements are logged in plain text form in some of the logs. An attacker who has read access to the logs may be able to discover sensitive information through an act as simple as running grep on the log files to look for words such as GRANT or PASSWORD.

Certain other files must be accessible to client programs, such as the Unix socket file. But normally you'll want to allow only access to the file, not control of it. For example, users should be able to connect to the server through the socket file. But they should not be able to delete the file; that would cripple the ability of other users to connect to the server.

How to Steal Data

The following description provides a brief example that illustrates why security is important. It underscores the fact that you don't want other users to have direct access to the MySQL data directory.

The MySQL server provides a flexible privilege system implemented by means of the grant tables in the mysql database. You can set up the contents of these tables to allow or deny database access to clients. This provides you with security against unauthorized network access to your data. However, setting up good security for network access to your databases is an exercise in futility if other users on the server host have direct access to the contents of the data directory. Unless you know you are the only person who ever logs in on the machine where the MySQL server is running, you need to be concerned about the possibility of other people on that machine gaining access to the data directory.

Obviously you don't want other users on the server host to have direct write access to data directory files, because then they can stomp all over your status files or database tables. But direct read access is just as dangerous. If a table's files can be read, it is trivial to steal the files and to get MySQL itself to show you the contents of the table. How? Like this:

Think about this scenario for a moment, and then reverse the perspective. Do you want someone to do that to you? Of course not. So protect yourself using the instructions in the following discussion.

Securing Your MySQL Installation

The procedure described here shows how to set up ownerships and access modes for the directories and files that make up your MySQL installation. The instructions here use mysql for both the user and group names that are to be given ownership of the installation. The instructions also assume initially a standard layout such that all parts of your MySQL installation are located under a single base directory, rather than scattered in various places throughout your filesystem. The installation base directory in the examples is /usr/local/mysql and the data directory is located under that with a pathname of /usr/local/mysql/data.

After going through the procedure, I'll describe how to handle some non-standard types of installation layouts. Your system layout may vary from any of those described here, but you should be able to adapt the general principles appropriately. Change the names and pathnames as necessary for your own system. If you run multiple servers, you should perform the procedure for each one.

You can determine whether your data directory contains insecure files or directories by executing ls -la. Look for files or directories that have the "group" or "other" permissions turned on. Here's a listing of a data directory that is insecure, as are some of the database directories within it:

'.' represents the directory being listed, that is, /usr/local/mysql/data. '..' represents the parent directory, /usr/local/mysql. Some of the database directories have the proper permissions: drwx------ allows read, write, and execute access to the owner, but no access to anyone else. But other directories have an overly permissive access mode: drwxrwxr-x allows read and execute access to all other users, even those outside of the mysql group. The situation shown in this example is one that resulted over time, starting with an older MySQL installation that was progressively upgraded to successive newer versions. The less-restrictive permissions were created by older MySQL servers that were less stringent than more recent servers about setting permissions. (You can see that the more restrictive database directories, menagerie, sampdb, and tmp, all have more recent dates.) The behavior of a MySQL server now is to set the permissions on database directories that it creates to be accessible only to the account it runs as.

You can also use ls -la to check the base directory of the MySQL installation, /usr/local/mysql. For example, you might get a result something like this:

The data directory permissions and ownership need to be changed, as already indicated. One other change you might make is to restrict access to the libexec directory, which is where the mysqld server lives. Nobody but the MySQL administrator needs access to the server, so you can make that directory private to the mysql login account.

To correct the problems just described, use the following procedure. The general idea is to lock down everything to be accessible only to the mysql account, except for those parts of the installation that other users have a legitimate need to access.

Note that some parts of this procedure do not apply if your installation is such that the MySQL server and client programs are installed in general system directories along with other non-MySQL programs. (For example, the server may be in /usr/sbin and the clients in /usr/bin.) In that case, the ownership and mode of the MySQL programs should be set the same as other binaries in those directories.

If the MySQL server is running, shut it down:
```
% mysqladmin -p -u root shutdown
```
Set the owner and group name assignments of the entire MySQL installation to those of the MySQL administrative account using the following commands, which you must execute as root:
```
# chown -R mysql /usr/local/mysql
# chgrp -R mysql /usr/local/mysql
```
Another popular approach is to make everything owned by root except the data directory, which you can accomplish like this:
```
# chown -R root /usr/local/mysql
# chgrp -R mysql /usr/local/mysql
# chown -R mysql /usr/local/mysql/data
# chgrp -R mysql /usr/local/mysql/data
```
If you set the general ownership to root, you'll need to perform most of the following steps as root. Otherwise, you can perform them as mysql.
For the base directory and any of its subdirectories that clients should be able to access, change their mode so that mysql has full access and everyone else has only read and execute permission. That may be how they are set already, but if not, change them. For example, the base directory can be set using either of the following commands:
```
% chmod 755 /usr/local/mysql
% chmod u=rwx,go=rx /usr/local/mysql
```
Similarly, the bin directory that contains the client programs can be set with either of these commands:
```
% chmod 755 /usr/local/mysql/bin
% chmod u=rwx,go=rx /usr/local/mysql/bin
```
Directories that clients need not have access to can be made private to mysql. The libexec directory that contains the server is an example. Either of the following commands will set its mode appropriately:
```
% chmod 700 /usr/local/mysql/libexec
% chmod u=rwx,go-rwx /usr/local/mysql/libexec
```
Change the mode of your data directory and all files and directories under it so that they are private to mysql. This prevents login accounts other than the one used for running the server from directly accessing the contents of your data directory. You can do this with the following command:
```
% chmod -R go-rwx /usr/local/mysql/data
```

After following the preceding instructions, your MySQL installation base directory has ownerships and permissions that look something like this:

As shown, everything now is owned by mysql, with a group ownership of mysql. The exception is for '..', which refers to the parent directory of /usr/local/mysql. That directory is owned by and modifiable only by root, which is good. You don't want unprivileged users to be able to mess with the directory containing your installation.

The data directory under the base directory has even more restrictive permissions:

Here, the '..' line refers to the parent of the data directory, that is, the MySQL base directory.

An exception to the mysql-only policy of access to the data directory may be necessary for particular files. For example, if you create a my.cnf option file in the data directory, it will be necessary to open up access to the directory a little if you want to place client options in the file. (Otherwise, client programs won't be able to read the file.) The same applies if you place the Unix socket file in the data directory. To allow client programs to access these files without providing full read access to the data directory, use this command:

As stated earlier, the preceding procedure assumes that all MySQL-related files are located under a single base directory. If that's not true, you'll need to locate each MySQL-related directory and perform the appropriate operations on each of them. For example, if your data directory is located at /var/mysql/data rather than under /usr/local/mysql, you'll need to issue these commands to change the ownership of your installation properly:

Or, suppose that you create an innodb directory under the MySQL installation directory under which to keep all InnoDB-related files. By default, these files are placed in the data directory. If you put them in an innodb directory instead, you'll want to set that directory to have the same access mode as the data directory. This principle also applies if you relocate other files that normally would be placed in the data directory, such as log files.

Another complication occurs if some of the directories under the installation root are really symbolic links that point elsewhere. If your versions of chown and chgrp don't follow symlinks, you'll need to track down the links and apply the ownership changes in the locations to which the links point. One way to do this is to use find:

Similar considerations apply to changing access modes. For example, if there are symbolic links under your data directory and chmod doesn't follow them, use this command instead:

Because the ownership and permissions of the data directory contents at this point are set to allow access only to the mysql login user, you should make sure the server always runs as mysql from now on. An easy way to ensure this is to specify the user in the [mysqld] section of the /etc/my.cnf file or the my.cnf file in the data directory:

That way, the server will run as mysql whether you start it while logged in as root or as mysql. Additional information on running the server using a particular login account is given in Chapter 11, "General MySQL Administration."

Securing the Unix Socket File

The server uses a Unix domain socket file for connections by clients to localhost. The socket file normally is publicly accessible so that client programs can use it. However, it should not be located in a directory where arbitrary clients have delete permission. For example, it's common for the socket file to be created in the /tmp directory, but on some Unix systems, that directory has permissions that allow users to delete files other than their own. That means any user can remove the socket file and as a result prevent client programs from establishing localhost connections to the server until the server is restarted to re-create the socket file. It's better if the /tmp directory has its "sticky bit" set, so that even if anyone can create files in the directory, users can remove only their own files. You can set the sticky bit for the directory by executing the following command as root:

Some installations place the socket file in the data directory, which leads to a problem if you make the data directory private to mysql: No client program can access the socket file, unless it is run by root or mysql. In this case, one option is to open up the data directory slightly so that clients can see the socket file:

Another approach is to change the location in which the server creates the socket file. Either specify a different location in a global option file, or recompile from source to build in a different default location. If you elect to use an option file, be sure to specify the location both for the server and for clients. For example, if the base directory is /usr/local/mysql, you can locate the socket file in that directory like this:

Recompiling is more work, but is a more complete solution because using an option file will not work for client programs that do not check option files. (All the standard MySQL clients do, but third-party programs may not.) By recompiling, the new socket location will become the default known by the client library; any program that uses the client library thus gets the new location as its own default, whether or not it uses option files.

Securing Option Files

Option files represent a potential point of compromise to the extent that they contain options that should not be exposed:

One way to ensure that user-specific option files have the proper mode and ownership is to run a program that looks for a .my.cnf file in each user's home directory and corrects any problems. The following Perl script, chk_mysql_opt_files.pl, will do this:

You can find chk_mysql_opt_files.pl in the admin directory of the sampdb distribution. You must run this script as root because it needs to be able to change mode and ownership of files owned by other users. To execute the script automatically, set it up as a nightly cron job run by root.

One of the standard option files that MySQL programs look for is the my.cnf file in the compiled-in location of the data directory. If you make the data directory private to mysql, client programs cannot read that file. This means that if you want to put client parameters in the file, you'll have to open up access to the data directory a bit. To do this, turn on "execute" permission for the directory, and also make sure that the my.cnf file itself is publicly readable:

However, if you do make the file publicly readable like this, it no longer can be used for listing options that must be private to the server, such as replication passwords.