Hack #60. Secure MySQL

Take some basic steps to harden your MySQL installation.

MySQL (http://www.mysql.com), one of the most popular open source database systems available today, is often used in conjunction with both the Apache web server and the PHP scripting language to drive dynamic content on the Web. However, MySQL is a complex piece of software and, given the fact that it often has to interact both locally and remotely with a broad range of other programs, special care should be taken to secure it as much as possible.

Some steps you can take include running MySQL in a chroot()-ed environment [Hack #10], running it as a nonroot user, and disabling MySQL’s ability to load data from local files. Luckily, none of these are as hard to do as they might sound. To start, let’s look at how to chroot() MySQL.

First, create a user and group for MySQL to run as and download the MySQL source distribution. After you’ve done that, unpack the source and go into the directory that it creates. Run this command to build MySQL and set up its directory structure for chroot()-ing:

$ ./configure --prefix=/mysql --with-mysqld-ldflags=-all-static && make
         

This configures MySQL to be installed in /mysql and statically links the mysqld binary; this makes setting up the chroot() environment much easier, since you won’t need to copy any additional libraries to the environment.

After the compilation finishes, become root and run these commands to install MySQL:

# make install DESTDIR=/mysql_chroot && ln -s /mysql_chroot/mysql /mysql
# scripts/mysql_install_db
         

The first command installs MySQL, but instead of placing the files in /mysql, it places them in /mysql_chroot/mysql. It also creates a symbolic link from that directory to /mysql, which makes administering MySQL much easier after installation.

The second command creates MySQL’s default databases. If you hadn’t created the symbolic link prior to running this command, the mysql_install_db script would have failed, because it expects to find MySQL installed beneath /mysql. Many other scripts and programs will expect the same, so creating the symbolic link will make your life easier.

Next, you need to set up the correct directory permissions so that MySQL will be able to function properly:

# chown -R root:mysql /mysql
# chown -R mysql /mysql/var
         

Now, try running MySQL:

# /mysql/bin/mysqld_safe&
Starting mysqld daemon with databases from /mysql/var
# ps -aux | grep mysql | grep -v grep
root     10137  0.6  0.5  4156  744 pts/2    S    23:01   0:00 /bin/sh /mysql/bin/
mysqld_safe
mysql    10150  7.0  9.3 46224 11756 pts/2   S    23:01   0:00 [mysqld]
mysql    10151  0.0  9.3 46224 11756 pts/2   S    23:01   0:00 [mysqld]
mysql    10152  0.0  9.3 46224 11756 pts/2   S    23:01   0:00 [mysqld]
mysql    10153  0.0  9.3 46224 11756 pts/2   S    23:01   0:00 [mysqld]
mysql    10154  0.0  9.3 46224 11756 pts/2   S    23:01   0:00 [mysqld]
mysql    10155  0.3  9.3 46224 11756 pts/2   S    23:01   0:00 [mysqld]
mysql    10156  0.0  9.3 46224 11756 pts/2   S    23:01   0:00 [mysqld]
mysql    10157  0.0  9.3 46224 11756 pts/2   S    23:01   0:00 [mysqld]
mysql    10158  0.0  9.3 46224 11756 pts/2   S    23:01   0:00 [mysqld]
mysql    10159  0.0  9.3 46224 11756 pts/2   S    23:01   0:00 [mysqld]
# /mysql/bin/mysqladmin shutdown
040103 23:02:45  mysqld ended

[1]+  Done                    /mysql/bin/mysqld_safe

Now that you know MySQL is working outside of its chroot() environment, you can create the additional files and directories it will need to work inside the chroot() environment:

# mkdir /mysql_chroot/tmp /mysql_chroot/dev
# chmod 1777 /mysql_chroot/tmp
# ls -l /dev/null
crw-rw-rw-    1 root     root       1,   3 Jan 30  2003 /dev/null
# mknod /mysql_chroot/dev/null c 1 3
         

Now, try running mysqld in the chroot()-ed environment:

# /usr/sbin/chroot /mysql_chroot /mysql/libexec/mysqld -u 100
         

In this example, the UID of the user you want mysqld to run as is specified with the -u option. This should correspond to the UID of the user created earlier.

To ease management, you might want to modify the mysqld_safe shell script to chroot() mysqld for you. You can accomplish this by finding the lines where mysqld is called and modifying them to use the chroot program.

Open up /mysql/bin/mysqld_safe and locate the block of lines that looks like this:

if test -z "$args"
  then
    $NOHUP_NICENESS $ledir/$MYSQLD $defaults \
     --basedir=$MY_BASEDIR_VERSION \
     --datadir=$DATADIR $USER_OPTION \
     --pid-file=$pid_file --skip-locking >> $err_log 2>&1
  else
    eval "$NOHUP_NICENESS $ledir/$MYSQLD $defaults \
     --basedir=$MY_BASEDIR_VERSION \
     --datadir=$DATADIR $USER_OPTION \
     --pid-file=$pid_file --skip-locking $args >> $err_log 2>&1"
  fi

Change them to look like this:

if test -z "$args"
  then
    $NOHUP_NICENESS /usr/sbin/chroot /mysql_chroot \
     $ledir/$MYSQLD $defaults \ 
      --basedir=$MY_BASEDIR_VERSION \
      --datadir=$DATADIR $USER_OPTION \
      --pid-file=$pid_file  --skip-locking >> $err_log 2>&1
  else
    eval "$NOHUP_NICENESS /usr/sbin/chroot /mysql_chroot \
     $ledir/$MYSQLD $defaults \ 
      --basedir=$MY_BASEDIR_VERSION \
      --datadir=$DATADIR $USER_OPTION \ 
      --pid-file=$pid_file --skip-locking $args >> $err_log 2>&1"
  fi

Now, you can start MySQL by using the mysqld_safe wrapper script, like this:

# /mysql/bin/mysqld_safe --user=100
         

You might also want to create a separate my.conf file for the MySQL utilities and server. For instance, in /etc/my.cnf, you could specify socket = /mysql_chroot/tmp/mysql.sock in the [client] section so that you don’t have to specify the socket manually every time you run a MySQL-related program.

You’ll also probably want to disable MySQL’s ability to load data from local files. To do this, you can add set-variable=local-infile=0 to the [mysqld] section of your /mysql_chroot/etc/my.cnf. This disables MySQL’s LOAD DATA LOCAL INFILE command. Alternatively, you can disable it from the command line by using the --local-infile=0 option.