Internal Security: Preventing Unauthorized File System Access | High Performance MySQL: Optimization, Backups, Replication, and More

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 account. By contrast, the client programs normally should be publicly accessible so that other users can run them but 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 during its operation 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, not to have its contents be exposed through insufficient protection of the database directory.

The log files must be kept secure because they contain the text of queries. 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 queries such as GRANT and SET PASSWORD are logged. The log files thus contain the text of sensitive queries, 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 query such as GRANT, INSERT, or SET PASSWORD, and such queries 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.

Other files must be accessible to client programs, such as the UNIX socket file. But normally you'll want to allow only use of the file, not control of it. For example, if a user can delete the socket file, that can cripple the ability of clients to connect to the local 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 through the grant tables in the mysql database. You can set up the contents of these tables to allow or deny database access to clients any way you want. 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:

Install your own rogue MySQL server on the server host, but with a port, socket, and data directory that are different from those used by the official server.
Run mysql_install_db to initialize your data directory. This action gives you full access to your server as the MySQL root user and sets up a test database that can serve as a convenient repository for stolen tables.
Access the data directory of the server you want to attack, copying the files corresponding to the table or tables that you want to steal into the test directory under your own server's data directory. This action requires only read access to the targeted data directory.
Start your rogue server. Presto! Its test database now contains copies of the stolen tables, which you can access at will. SHOW TABLES FROM test shows which tables you have a copy of, and SELECT * shows the entire contents of any of them.
If you want to be really nasty, open up the permissions on the anonymous user accounts for your server so that anyone can connect to the server from any host to access your test database. That effectively publishes the stolen tables to the world.

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 mysqladm and mysqlgrp for 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 having different parts of it installed in various places throughout your file system. The installation base directory is /usr/local/mysql and the data directory is 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-l. Look for files or directories that have the "group" or "other" permissions turned on. The following is a listing of a data directory that is insecure, as are some of the database directories within it:

 % ls -la /usr/local/mysql/data  total 10148 drwxrwxr-x   11 mysqladm wheel        1024 May  8 12:20 . drwxr-xr-x   22 root     wheel         512 May  8 13:31 .. drwx------    2 mysqladm mysqlgrp      512 Apr 16 15:57 menagerie drwxrwxr-x    2 mysqladm wheel         512 Jun 25  1998 mysql drwx------    7 mysqladm mysqlgrp     1024 May  7 10:45 sampdb drwxrwxr-x    2 mysqladm wheel        1536 Jun 25  1998 test drwx------    2 mysqladm mysqlgrp     1024 May  8 18:43 tmp

Some of those database directories have 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 mysqlgrp 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. (Notice 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-l to check the base directory of the MySQL installation. For example, you might get a result something like this:

 % ls -la /usr/local/mysql  total 44 drwxrwxr-x   13 mysqladm mysqlgrp     1024 May  7 10:45 . drwxr-xr-x   24 root     wheel        1024 May  1 12:54 .. drwxr-xr-x    2 mysqladm mysqlgrp     1024 Jul 16 20:58 bin drwxrwxr-x   12 mysqladm wheel        1024 May  8 12:20 data drwxr-xr-x    3 mysqladm mysqlgrp      512 May  7 10:45 include drwxr-xr-x    2 mysqladm mysqlgrp      512 May  7 10:45 info drwxr-xr-x    3 mysqladm mysqlgrp      512 May  7 10:45 lib drwxr--r-x    2 mysqladm mysqlgrp      512 Jul 16 20:58 libexec drwxr-xr-x    3 mysqladm mysqlgrp      512 May  7 10:45 man drwxr-xr-x    6 mysqladm mysqlgrp     1024 May  7 10:45 mysql-test drwxr-xr-x    3 mysqladm mysqlgrp      512 May  7 10:45 share drwxr-xr-x    7 mysqladm mysqlgrp     1024 May  7 10:45 sql-bench

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 mysqladm.

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

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 command, which you must execute as root:
```
 # chown -R mysqladm.mysqlgrp /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.mysqlgrp /usr/local/mysql  # chown -R mysqladm.mysqlgrp /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 mysqladm.
For the base directory and any of its subdirectories that clients should be able to access, change their mode so that mysqladm 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 the following 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 mysqladm. 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 mysqladm. This prevents 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 will have ownerships and permissions that look something like this:

 % ls -la /usr/local/mysql  total 44 drwxr-xr-x   13 mysqladm mysqlgrp     1024 May  7 10:45 . drwxr-xr-x   24 root     wheel        1024 May  1 12:54 .. drwxr-xr-x    2 mysqladm mysqlgrp     1024 Jul 16 20:58 bin drwx------   12 mysqladm mysqlgrp     1024 May  8 12:20 data drwxr-xr-x    3 mysqladm mysqlgrp      512 May  7 10:45 include drwxr-xr-x    2 mysqladm mysqlgrp      512 May  7 10:45 info drwxr-xr-x    3 mysqladm mysqlgrp      512 May  7 10:45 lib drwx------    2 mysqladm mysqlgrp      512 Jul 16 20:58 libexec drwxr-xr-x    3 mysqladm mysqlgrp      512 May  7 10:45 man drwxr-xr-x    6 mysqladm mysqlgrp     1024 May  7 10:45 mysql-test drwxr-xr-x    3 mysqladm mysqlgrp      512 May  7 10:45 share drwxr-xr-x    7 mysqladm mysqlgrp     1024 May  7 10:45 sql-bench

As shown, everything now is owned by mysqladm, with a group membership of mysqlgrp. (The listing for '..' 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 will have even more restrictive permissions:

 % ls -la /usr/local/mysql/data  total 10148 drwx------   11 mysqladm mysqlgrp     1024 May  8 12:20 . drwxr-xr-x   22 mysqladm mysqlgrp      512 May  8 13:31 .. drwx------    2 mysqladm mysqlgrp      512 Apr 16 15:57 menagerie drwx------    2 mysqladm mysqlgrp      512 Jun 25  1998 mysql drwx------    7 mysqladm mysqlgrp     1024 May  7 10:45 sampdb drwx------    2 mysqladm mysqlgrp     1536 Jun 25  1998 test drwx------    2 mysqladm mysqlgrp     1024 May  8 18:43 tmp

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

An exception to the mysqladm-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 the following command:

 % chmod go+x /usr/local/mysql/data

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 two commands to change the ownership of your installation properly:

 # chown -R mysqladm.mysqlgrp /usr/local/mysql  # chown -R mysqladm.mysqlgrp /var/mysql/data

Or suppose 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 version of chown doesn't follow symlinks, you'll need to track them down and apply the ownership changes where the links point to. One way to do this is to use find:

 # find /usr/local/mysql -follow -print | xargs chown mysqladm.mysqlgrp

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 the following command instead:

 % find /usr/local/mysql/data -follow -print | xargs chmod go-rwx

Note that parts of these instructions 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.

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

 [mysqld]  user=mysqladm

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

After securing your MySQL installation, you can restart the server.

Securing the 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.

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

 % chmod go+x /usr/local/mysql/data

Otherwise, you can 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, you can place the socket file in the base directory as follows:

 [mysqld]  socket=/usr/local/mysql/mysql.sock [client] socket=/usr/local/mysql/mysql.sock

Recompiling is more work, but is a more complete solution because using an option file will not work for clients 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. As a general principle, you shouldn't make an option file publicly readable if it contains sensitive information, such as MySQL account names or passwords. /etc/my.cnf normally is publicly readable because it's a common location in which to specify global client options. (This means you should not use it for server options, such as replication passwords.) Each user-specific .my.cnf option file should be owned by and readable only by the user in whose home directory the file appears. The option file in the server's data directory can go either way, depending on what you use it for.

One way to ensure that user-specific option files have the proper mode and ownership is to run a program that looks for such files and corrects any problems. The following Perl script, chk_mysql_opt_files.pl, will do this:

 #! /usr/bin/perl -w  # chk_mysql_opt_files.pl - check user-specific .my.cnf files and make sure # the ownership and mode is 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. # This script must be run as root.  Execute it with your password file as # input.  If you have an /etc/passwd file, run it like this: #  chk_mysql_opt_file.pl /etc/passwd # For Mac OS X, use the netinfo database: #  nidump passwd . | chk_mysql_opt_file.pl use strict; while (<>) {     my ($uid, $home) = (split (/:/, $_))[2,5];     my $cnf_file = "$home/.my.cnf";     next unless -f $cnf_file;           # is there a .my.cnf file?     if ((stat ($cnf_file))[4] != $uid)  # test ownership     {         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/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);

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 mysqladm, 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:

 % chmod go+x /usr/local/mysql/data  % chmod +r /usr/local/mysql/data/my.cnf

However, if you do this, the file becomes unsuitable for listing options that must be private to the server, such as replication passwords.