Secured Installation with MySQL (1)

I am writing this blog to provide a basic introduction on MySQL Security Settings where it is useful and easy to kick start a secured environment within MySQL.

This blog page has no intent to cover "all" security concerns but it is a very common step in securing the MySQL server.

The article (1) provides general steps in the following topics to secure MySQL database
- Database Hardening using mysql_secure_installation
- Secure Channel with ssl certificate(s) between mysqld and mysql

There are more and more .... (Audit Plugin, MySQL 5.6 Enterprise Encryption, Password Encryptionm Password Policy, etc...)  which they are not covered in this blog!

Database Hardening using "mysql_secure_installation"
By default, when a MySQL database is installed (for example using "mysql_install_db" without --random-passwords), the "root" account has no passwrod.   There are couples of steps which we can improve the security of the installed database.
1.Remove root access from external access
2. Limit the effect of the LOAD_FILE() function and the LOAD DATA and SELECT ... INTO OUTFILE statements to work only in the folder of @@secure_file_priv
3. Install and enable SSL channel for
4. Remove the 'test' database which by default is installed

The following script provides a brief validation on a mysql database.

echo "Multiple root account"
mysql -uroot -h127.0.0.1 -e "SELECT user,host,plugin,password,password_expired FROM mysql.user;"
echo "Press <Enter> to continue"
read

echo "No restriction on where to write files on the host OS!"
mysql -uroot -h127.0.0.1 -e "SELECT @@secure_file_priv;"
echo "Press <Enter> to continue"
read

echo "No SSL cert for the server"
mysql -uroot -h127.0.0.1 -e "SELECT @@ssl_key, @@ssl_cert, @@ssl_ca, @@ssl_capath;"
echo "Press <Enter> to continue"
read

echo "test database present"
mysql -uroot -h127.0.0.1 -e "show databases;"
echo "Press <Enter> to continue"
read

echo "Everybody has full priv on test database"
mysql -uroot -h127.0.0.1 -e "SELECT * FROM INFORMATION_SCHEMA.SCHEMA_PRIVILEGES;"
echo "Press <Enter> to continue"
read

echo "NO AUDIT plugin"
mysql -uroot -h127.0.0.1 -e "SELECT * FROM INFORMATION_SCHEMA.PLUGINS WHERE plugin_type LIKE '%AUDIT%';"
echo "Press <Enter> to continue"
read

mysql -uroot -h127.0.0.1 -e "select @@general_log;"
echo "Test-only plugins present"
ls -l /usr/local/mysql/lib/plugin/*test*

Running the 'mysql_secure_installation' -

• You can set a password for root accounts.
• You can remove root accounts that are accessible from outside the local host.
• You can remove anonymous-user accounts.
• You can remove the test database (which by default can be accessed by all users, even anonymous users), and privileges that permit anyone to access databases with names that start with test_.

 The following script executes the mysql_secure_installation on the database hosting at $MYSQL_HOST:$MYSQL_PORT

# 03-secure.sh

export MYSQL_HOST=127.0.0.1
export MYSQL_TCP_PORT=3306

mysql_secure_installation

mysql_config_editor set --login-path=root --user=root --host=127.0.0.1 --port=3306 --password

Install SSL certificates to enable SSL channel with mysqld

The following scripts provides a general steps to create the SSL certificates for CA, SERVER and CLIENT.

# 04-cert.sh
# http://dev.mysql.com/doc/refman/5.6/en/creating-ssl-certs.html
# Steps 1 : Creating SSL files
rm -rf newcerts
mkdir newcerts && cd newcerts
# create ca cert
openssl genrsa 2048 > ca-key.pem
openssl req -new -x509 -nodes -days 3600 -key ca-key.pem -out ca-cert.pem

# create server cert
openssl req -newkey rsa:2048 -days 3600 -nodes -keyout server-key.pem -out server-req.pem
openssl rsa -in server-key.pem -out server-key.pem
openssl x509 -req -in server-req.pem -days 3600 -CA ca-cert.pem -CAkey ca-key.pem -set_serial 01 -out server-cert.pem

# create client cert
openssl req -newkey rsa:2048 -days 3600 -nodes -keyout client-key.pem -out client-req.pem
openssl rsa -in client-key.pem -out client-key.pem
openssl x509 -req -in client-req.pem -days 3600 -CA ca-cert.pem -CAkey ca-key.pem -set_serial 01 -out client-cert.pem

# verify certificates
openssl verify -CAfile ca-cert.pem server-cert.pem client-cert.pem

Once the SSL certificates are created, we can modify the my.cnf (MySQL configuration) to install the Server SSL certificate.e.g.

my.cnf
[client]
ssl-ca=/var/lib/mysql/demo/secure/newcerts/ca-cert.pem
ssl-cert=/var/lib/mysql/demo/secure/newcerts/client-cert.pem
ssl-key=/var/lib/mysql/demo/secure/newcerts/client-key.pem

[mysqld]
...
ssl-ca=/var/lib/mysql/demo/secure/newcerts/ca-cert.pem
ssl-cert=/var/lib/mysql/demo/secure/newcerts/server-cert.pem
ssl-key=/var/lib/mysql/demo/secure/newcerts/server-key.pem
 ...

The mysql database should be restarted with the changed SSL configuration.   The SSL channel is tested via the "mysql" client command - With the configuration (my.cnf) having [client] section.  The SSL configuration for 'mysql' can be loaded by executing -
mysql --defaults-file=my.cnf -uroot -e "status;"|grep SSL



The "Ciper in use is DHE-RSA-AES256-SHA' on SSL attribute indicating the SSL channel is established.