Hack 52 Secure MySQL

Basic steps you can take 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 internally 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 are running MySQL in a chrooted 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 may sound. To start with, let's look at how to chroot() MySQL.

First create a user and group for MySQL to run as. Next, you'll need to download the MySQL source distribution. After you've done that, unpack it and go into the directory that it created. Run this command to build MySQL and set up its directory structure for chrooting:

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

This configures MySQL to be installed in /mysql and statically links the mysqld binary. This will make 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 then 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. In addition, it 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. This is because it expected to find MySQL installed beneath /mysql. Many other scripts and programs will expect this, too, so creating the symbolic link will make your life easier.

Now you need to set up the correct directory permissions so that MySQL will be able to function properly. To do this, run these commands:

# 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 chrooted environment:

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

Here 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 may 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.

To do this, open up /mysql/bin/mysqld_safe and locate 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

In addition, you may 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 you do not have to manually specify the socket 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.