November 28th, 2003, 12:59
MySQL is one of the most popular databases on the Internet and it is often used in conjunction with PHP. 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 Coveres:
# Installing MySQL
# Chrooting MySQL
# MySQL Permissions
# Configuring MySQL

Lets start out by installing MySQL from the ports tree.

[code:1:5d5b2c8bb3]($:~)=> cd /usr/ports/databases/mysql323-server
($:~)=> make install clean[/code:1:5d5b2c8bb3]

Now we need to make sure any portupgrades of MySQL does not overwrite our mysql database.

[code:1:5d5b2c8bb3]($:~)=> vi /usr/local/etc/pkgtools.conf[/code:1:5d5b2c8bb3]

Search for the MAKE_ARGS section. You should make it look like this below:

'databases/mysql323-server' => 'SKIP_INSTALL_DB=yes'

Save and exit. Now we need to configure the mysql client.

[code:1:5d5b2c8bb3]($:~)=> cp /usr/local/share/mysql/my-medium.cnf /etc/my.cnf[/code:1:5d5b2c8bb3]
(Replace my-medium.cnf with whatever suites your serverís enviroment.)

The following step is optional. Most of the time there is no real need to have a 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.

[code:1:5d5b2c8bb3]($:~)=> vi /usr/local/etc/rc.d/[/code:1:5d5b2c8bb3]

Change This Line:

[code:1:5d5b2c8bb3]safe_mysqld --user=mysql --datadir=${DB_DIR} ..etc..etc..[/code:1:5d5b2c8bb3]

to look like:

[code:1:5d5b2c8bb3]safe_mysqld --user=mysql --skip-networking --datadir=${DB_DIR} ..etc..etc..[/code:1:5d5b2c8bb3]

Now we chroot our server.

[code:1:5d5b2c8bb3]($:~)=> 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[/code:1:5d5b2c8bb3]

Next, the following files have to be copied into the new directory structure:

[code:1:5d5b2c8bb3]($:~)=> 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/[/code:1:5d5b2c8bb3]

Tighten passwords and groups
From the files: /chroot/mysql/etc/passwords and /chroot/mysql/etc/group we must remove all the lines except the mysql account and group. Next, we have to build the password database as follows (this applies only to FreeBSD):

[code:1:5d5b2c8bb3]($:~)=> cd /chroot/mysql/etc
($:~)=> vi group[/code:1:5d5b2c8bb3]

Remove every entry except for the sys and mysql group

[code:1:5d5b2c8bb3]($:~)=> vi passwords[/code:1:5d5b2c8bb3]

Same again remove every entry except for root and mysql. Then change the root shell to /sbin/nologin you probbibly also want to change the root password also.

[code:1:5d5b2c8bb3]($:~)=> pwd_mkdb -d /chroot/mysql/etc passwords[/code:1:5d5b2c8bb3]

You will receive an error of:
pwd_mkdb: warning, unknown root shell
That is fine.

[code:1:5d5b2c8bb3]($:~)=> rm -rf /chroot/mysql/etc/master.passwd[/code:1:5d5b2c8bb3]

Special considerations
As in case of the Apache web server, we have to create a special device file /dev/null:

[code:1:5d5b2c8bb3]($:~)=> 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[/code:1:5d5b2c8bb3]

We must also copy the mysql database, which contains grant tables created during MySQL installation:

[code:1:5d5b2c8bb3]($:~)=> cp -Rv /var/db/mysql /chroot/mysql/var/db/[/code:1:5d5b2c8bb3]

Now we need to copy needed files for mysql to run inside the chrooted enviroment

[code:1:5d5b2c8bb3]($:~)=> 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/ /chroot/mysql/usr/lib/[/code:1:5d5b2c8bb3]
(Use if you are running FreeBSD 5x, If you are running 4x use
[code:1:5d5b2c8bb3]($:~)=> install -C /usr/lib/ /chroot/mysql/usr/lib/
($:~)=> install -C /usr/lib/ /chroot/mysql/usr/lib/
($:~)=> install -C /usr/lib/ /chroot/mysql/usr/lib/
($:~)=> install -C /usr/lib/ /chroot/mysql/usr/lib/
($:~)=> install -C /usr/lib/ /chroot/mysql/usr/lib/
($:~)=> install -C /usr/lib/ /chroot/mysql/usr/lib/
($:~)=> install -C /usr/lib/ /chroot/mysql/usr/lib/
($:~)=> install -C /usr/lib/ /chroot/mysql/usr/lib/
($:~)=> install -C /usr/libexec/ /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/ /chroot/mysql/var/run/[/code:1:5d5b2c8bb3]

The access rights to the above directories should be set as follows:

[code:1:5d5b2c8bb3]($:~)=> 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[/code:1:5d5b2c8bb3]

Now lets startup our database for the first time and test out the enviroment.

[code:1:5d5b2c8bb3]($:~)=> chroot /chroot/mysql /bin/sh
($:~)=> /usr/local/bin/safe_mysqld &
($:~)=> exit[/code:1:5d5b2c8bb3]

Now make sure the server is running

[code:1:5d5b2c8bb3]($:~)=> 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[/code:1:5d5b2c8bb3]

Now lets tell our mysql client how to access the server so we dont need to chroot ourselves every time we wish to run a query.

[code:1:5d5b2c8bb3]($:~)=> vi /etc/my.cnf[/code:1:5d5b2c8bb3]

Inside the [client] section modify the socket option.

[code:1:5d5b2c8bb3]socket = /chrot/mysql/tmp/mysql.sock[/code:1:5d5b2c8bb3]

Mysql User accounts

[code:1:5d5b2c8bb3]($:~)=> 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');[/code:1:5d5b2c8bb3]

Now lets create a diffrent name for our super user then remove the root user.

[code:1:5d5b2c8bb3]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[/code:1:5d5b2c8bb3]

Now lets be sure this worked.

[code:1:5d5b2c8bb3]($:~)=> 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[/code:1:5d5b2c8bb3]

Now we need to make our startup file

[code:1:5d5b2c8bb3]($:~)=> cd /usr/local/etc/rc.d
($:~)=> rm
($:~)=> vi[/code:1:5d5b2c8bb3]


PIDFILE=${DB_DIR}/`/bin/hostname -s`.pid

case "$1" in
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'
if [ -f /chroot/mysql/${PIDFILE} ]; then
/bin/kill `cat /chroot/mysql/${PIDFILE}` > /dev/null 2>&1 && echo -n ' mysqld'
echo "mysql-server isn't running"
echo ""
echo "Usage: `basename $0` { start | stop }"
echo ""
exit 64

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 acomplish this for us:

echo "Updating Mysql Libs"
cd /chroot/mysql
for file in `find usr/ -type f `; do
if [ /$file -nt $file ]; then
cp -v /$file $file

References Used:
Security Infocus
Screaming Electron (For the script help)

The origional artice may be found over here: