Team LiB
Previous Section Next Section

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:

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

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

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

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

5.
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 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:

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

'.' 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:

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

  1. If the MySQL server is running, shut it down:

    % mysqladmin -p -u root shutdown
    

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

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

  4. 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:

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

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:

% ls -la /usr/local/mysql/data
total 10148
drwx------   11 mysql  mysql     1024 May  8 12:20 .
drwxr-xr-x   22 mysql  mysql      512 May  8 13:31 ..
drwx------    2 mysql  mysql      512 Apr 16 15:57 menagerie
drwx------    2 mysql  mysql      512 Jun 25  1998 mysql
drwx------    7 mysql  mysql     1024 May  7 10:45 sampdb
drwx------    2 mysql  mysql     1536 Jun 25  1998 test
drwx------    2 mysql  mysql     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 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:

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

# chown -R mysql /usr/local/mysql
# chgrp -R mysql /usr/local/mysql
# chown -R mysql /var/mysql/data
# chgrp -R mysql /var/mysql/data

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:

# find /usr/local/mysql -follow -print | xargs chown mysql
# find /usr/local/mysql -follow -print | xargs chgrp mysql

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:

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

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:

[mysqld]
user=mysql

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

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

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:

# chmod +t /tmp

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:

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

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:

[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 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:

  • 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 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:

#! /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 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:

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

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.

    Team LiB
    Previous Section Next Section