General Information
MySQL is one of the most popular databases on the Internet. Besides its undoubted advantages, such as easy of use and relatively high performance, MySQL offers simple, but very effective security mechanisms. Unfortunately, the default installation of MySQL, and in particular the empty root password and the potential vulnerability to buffer overflow attacks, makes the database an easy target for attacks.
This howto Covers:
- Installing MySQL
- Chrooting MySQL
- MySQL Permissions
- Configuring MySQL
Installation
Lets start ou/t by installing MySQL from the ports tree.
# cd /usr/ports/databases/mysql323-server # make install clean
This should work fine with the other versions of MySQL inside the ports tree. You may also want to go over what options you would like to include, such as building MySQL:
# make BUILD_OPTIMIZED=yes WITH_LINUXTHREADS=yes
Whatever options you may pass don’t forget to put inside your pkgtools.conf file.
Now we need to make sure any portupgrades of MySQL does not overwrite our MySQL database.
# vi /usr/local/etc/pkgtools.conf
Search for the MAKE_ARGS section. You should make it look like this below:
MAKE_ARGS = {
'databases/mysql323-server' => 'SKIP_INSTALL_DB=yes'
}
Save and exit. Now we need to configure the MySQL client.
# cp /usr/local/share/mysql/my-medium.cnf /etc/my.cnf
The following step is optional. Most of the time there is no real need to have an open port for MySQL unless you have other machines on the network that need quick access to the database server. So, instead we are going to use a unix socket file.
# vi /usr/local/etc/rc.d/mysql-server.sh
Change this line:
safe_mysqld --user=mysql --datadir=${DB_DIR} ..etc..etc..
to look like:
safe_mysqld --user=mysql --skip-networking --datadir=${DB_DIR} ..etc..etc..
Chrooting MySQL
Now we chroot our server.
# mkdir -p /chroot/mysql/dev # mkdir -p /chroot/mysql/bin # mkdir -p /chroot/mysql/sbin # mkdir -p /chroot/mysql/etc # mkdir -p /chroot/mysql/tmp # mkdir -p /chroot/mysql/var/tmp # mkdir -p /chroot/mysql/var/db # mkdir -p /chroot/mysql/var/log # mkdir -p /chroot/mysql/var/run # mkdir -p /chroot/mysql/usr/local/bin # mkdir -p /chroot/mysql/usr/local/libexec # mkdir -p /chroot/mysql/usr/local/share/mysql # mkdir -p /chroot/mysql/usr/libexec # mkdir -p /chroot/mysql/usr/bin # mkdir -p /chroot/mysql/usr/sbin # mkdir -p /chroot/mysql/usr/lib
Next, the following files have to be copied into the new directory structure:
# cp /usr/local/libexec/mysqld /chroot/mysql/usr/local/libexec/ # cp -Rv /usr/local/share/mysql /chroot/mysql/usr/local/share/ # cp /etc/hosts /chroot/mysql/etc/ # cp /etc/resolv.conf /chroot/mysql/etc/ # cp /etc/group /chroot/mysql/etc/ # cp /etc/master.passwd /chroot/mysql/etc/passwords # cp /etc/my.cnf /chroot/mysql/etc/
MySQL Permissions
From the files: /chroot/mysql/etc/passwords and /chroot/mysql/etc/group we must remove all of the lines except the mysql account and group. Next, we have to build the password database as follows (this applies only to FreeBSD):
# cd /chroot/mysql/etc # vi group
Remove every entry except for the sys and mysql group
# vi passwords
Same again, remove every entry except for root and mysql. Then change the root shell to /sbin/nologin. You also want to change your root password to something other than your system’s password.
# pwd_mkdb -d /chroot/mysql/etc passwords
You will receive an error of:
pwd_mkdb: warning, unknown root shell
That is fine.
# rm -rf /chroot/mysql/etc/master.passwd
Special Considerations
As in case of the Apache web server, we have to create a special device file /dev/null:
# ls -al /dev/null
crw-rw-rw- 1 root sys 2, 2 Jun 21 18:31 /dev/null
# mknod /chroot/mysql/dev/null c 2 2 # chown root:sys /chroot/mysql/dev/null # chmod 666 /chroot/mysql/dev/null
We must also copy the mysql database, which contains grant tables created during MySQL installation:
# cp -Rv /var/db/mysql /chroot/mysql/var/db/
Configuration
Now we need to copy needed files for MySQL to run inside the chrooted enviroment.
# install -C /bin/cat /chroot/mysql/bin/ # install -C /bin/date /chroot/mysql/bin/ # install -C /bin/hostname /chroot/mysql/bin/ # install -C /bin/ls /chroot/mysql/bin/ # install -C /bin/rm /chroot/mysql/bin/ # install -C /bin/sh /chroot/mysql/bin/ # install -C /sbin/nologin /chroot/mysql/sbin/ # install -C /usr/bin/limits /chroot/mysql/bin/ # install -C /usr/bin/nohup /chroot/mysql/bin/ # install -C /usr/bin/sed /chroot/mysql/bin/ # install -C /usr/bin/tee /chroot/mysql/bin/ # install -C /usr/bin/touch /chroot/mysql/bin/ # install -C /usr/bin/umask /chroot/mysql/bin/ # install -C /usr/lib/libc.so.5 /chroot/mysql/usr/lib/
# install -C /usr/lib/libc_r.so.5 /chroot/mysql/usr/lib/ # install -C /usr/lib/libcrypt.so.2 /chroot/mysql/usr/lib/ # install -C /usr/lib/libm.so.2 /chroot/mysql/usr/lib/ # install -C /usr/lib/libreadline.so.4 /chroot/mysql/usr/lib/ # install -C /usr/lib/libstdc++.so.4 /chroot/mysql/usr/lib/ # install -C /usr/lib/libutil.so.3 /chroot/mysql/usr/lib/ # install -C /usr/lib/libwrap.so.3 /chroot/mysql/usr/lib/ # install -C /usr/lib/libz.so.2 /chroot/mysql/usr/lib/ # install -C /usr/libexec/ld-elf.so.1 /chroot/mysql/usr/libexec/ # cp /usr/local/bin/my* /chroot/mysql/usr/local/bin/ # cp /usr/local/bin/safe_mysqld /chroot/mysql/usr/local/bin/ # install -C /usr/sbin/chown /chroot/mysql/usr/sbin/ # install -C /var/run/ld.so.hints /chroot/mysql/var/run/
The access rights to the above directories should be set as follows:
# chown -R root:sys /chroot/mysql # chmod -R 755 /chroot/mysql # chmod 1777 /chroot/mysql/tmp # chown -R mysql:mysql /chroot/mysql/var/db/mysql
Now let’s startup our database for the first time and test out the enviroment.
# chroot /chroot/mysql /bin/sh # /usr/local/bin/safe_mysqld & # exit
Now, make sure the server is running.
# ps -ax | grep mysql 60586 p0 S 0:00.01 /bin/sh /usr/local/bin/safe_mysqld 60601 p0 S 0:00.03 /usr/local/libexec/mysqld --basedir=/usr/local --datadir=/var/db/mysql --user=mysql --pid-file=/var 60603 p0 S+ 0:00.00 grep mysql
Now let’s tell our MySQL client how to access the server so we don’t need to chroot ourselves every time we wish to run a query.
# vi /etc/my.cnf
Inside the [client] section, modify the socket option.
socket = /chroot/mysql/tmp/mysql.sock
MySQL User Accounts
# mysql
mysql> drop database test;
mysql> use mysql;
mysql> DELETE FROM user WHERE NOT (host="localhost" and user="root");
mysql> SET PASSWORD FOR root@localhost=PASSWORD('new_password');
Now let’s create a different name for our super user and then remove the root user.
mysql> INSERT INTO user VALUES ('localhost','db_admin',PASSWORD('password'),'Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y');
mysql> DELETE FROM user WHERE (user="root");
mysql> flush privileges;
mysql> commit;
mysql> quit
Now let’s be sure this worked.
# mysql ERROR 1045: Access denied for user: 'root@localhost' (Using password: NO) # mysql -u db_admin -p Enter password: Welcome to the MySQL monitor. Commands end with ; or g. Your MySQL connection id is 4 to server version: 3.23.58-log Type 'help;' or 'h' for help. Type 'c' to clear the buffer. mysql> u mysql mysql> SELECT * FROM user; mysql> quit
Startup Script
Now we need to make our startup file.
# cd /usr/local/etc/rc.d # rm mysql-server.sh # vi mysql-server.sh
#!/bin/sh
DB_DIR=/var/db/mysql
PIDFILE=${DB_DIR}/`/bin/hostname -s`.pid
case "$1" in
start)
if [ -x /usr/local/bin/safe_mysqld ]; then
/usr/bin/limits -U mysql
nohup /sbin/chroot /chroot/mysql /usr/local/bin/safe_mysqld --user=mysql --skip-networking \
--datadir=${DB_DIR} --pid-file=${PIDFILE} > /dev/null &
echo -n ' mysqld'
fi
;;
stop)
if [ -f /chroot/mysql/${PIDFILE} ]; then
/bin/kill `cat /chroot/mysql/${PIDFILE}` > /dev/null 2>&1 && echo -n ' mysqld'
else
echo "mysql-server isn't running"
fi
;;
*)
echo ""
echo "Usage: `basename $0` { start | stop }"
echo ""
exit 64
;;
esac
Congratulations. Save, exit, kill your current running server, and start it up with the startup script. Now, whenever there is a new portupgrade or buildworld, it’s important to keep the binaries inside the chroot updated. So here’s a handy little script that will accomplish this for us:
#!/bin/sh
echo "Updating Mysql Libs"
cd /chroot/mysql
for file in `find usr/ -type f `; do
if [ /$file -nt $file ]; then
cp -v /$file $file
fi
done
References Used:
- Security Infocus
- Screaming Electron (For the script help)