Configuring MySQL Securely

Learning how to secure your database and servers has never been more important. Read on for tips on how to secure your MySQL server.

In some cases, we might have the MySQL database server setup on a dedicated machine independent from the web server. Therefore, a remote connection would be required. Using an unencrypted connection between our web application and the MySQL Server means that all traffic is sent in cleartext (unencrypted). An attacker within the network could sniff all our traffic and exfiltrate sensitive information.

The following is a sample of sniffed network packets containing unencrypted traffic containing sensitive data to MySQL.
"........SELECT * FROM wp_users....,....def.wp.wp_users.wp_users.ID.ID.?......#B...<....def.wp.wp_users.wp_users
user_loginuser_login.!...... @...:....def.wp.wp_users.wp_users user_pass ser_pass.!...........B....def.wp.wp_users.wp_users user_nicename user_nicename.!...... @...<....def.wp……....?...........@....def.wp.wp_users.wp_users.display_name.display_name.!...........|....1.admin"$P$Bvsl4MV4/JFJp89aplP8wBHsIQSmbl0.admin.admin@admin.com.

In order to avoid this, we must enable TLS on the database server. We need a valid TLS certificate prior enabling the secure connection. For the purposes of this article, we will create a self-signed certificate (we’ll use OpenSSL for this). For production use, it’s recommended to use a certificate issued by a certificate authority (CA) you trust.

We first need to create a directory where the TLS certificates will be stored.

secuser@secureserver:/# mkdir sqlcert && cd sqlcert

Now we will create the CA certificate. The CA certificate will be used later on to create-sign the Server-Client certificates. We use the genrsa option to create a 2048-bit RSA private key.

secuser@secureserver:~/sqlcert# openssl genrsa 2048 > ca-key.pem

 

--> Generating RSA private key, 2048 bit long modulus
.................................................+++...+++
e is 65537 (0x10001)

Instead of creating a CSR (Certificate Signing Request) which would then be needed to be sent to a third party Certificate Authority for signing, we use the -x509 option to self-sign our root certificate (or Certificate of Authority) using the private key we generated.

Note — You need to make sure that COMMON NAME is different between CA and Server/Client Certificate.

secuser@secureserver:~/sqlcert# openssl req -new -x509 -nodes -days 3600 -key ca-key.pem -out ca.pem

 

You are about to be asked to enter information that will be incorporated
into your certificate request.

 

# You will be asked some questions

Server Certificate

We are now ready to create the Server certificate. First, we initiate a private key request.

secuser@secureserver:~/sqlcert# openssl req -newkey rsa:2048 -days 3600 -nodes -keyout server-key.pem -out server-req.pem

 

Generating a 2048 bit RSA private key
..........+++.......................................................+++
writing new private key to 'server-key.pem'
You are about to be asked to enter information that will be incorporated
into your certificate request.

 

# You will be asked some questions

The private key is protected by a passphrase which will be needed every time the server starts. To avoid having to enter the passphrase, we can remove it using the following command.

secuser@secureserver:~/sqlcert# openssl rsa -in server-key.pem -out server-key.pem

 

--> writing RSA key

The root certificate we initially created (ca.pem) as well as its private key (ca-key.pem) will now be used to sign the server’s certificate. The result will be saved in the server-cert.pem file.

secuser@secureserver:~/sqlcert# openssl x509 -req -in server-req.pem -days 3600 -CA ca.pem -CAkey ca-key.pem -set_serial 01 -out server-cert.pem
Signature ok
subject=/C=AU/ST=Some-State/O=Internet Widgits Pty Ltd/OU=Security/CN=example.com/emailAddress=ssl@example.com
Getting CA Private Key

Client Certificate

The user-client who will be connecting to the MySQL server must have a certificate as well. We will follow the same procedure that we followed for the server certificate.

secuser@secureserver:~/sqlcert# openssl req -newkey rsa:2048 -days 3600 -nodes -keyout client-key.pem -out client-req.pem

 

--> Generating a 2048 bit RSA private key
....................................................+++...+++
writing new private key to 'client-key.pem'
You are about to be asked to enter information that will be incorporated into your certificate request.

 

# You will be asked some questions

We sign the certificate using our root certificate (CA).

secuser@secureserver:~/sqlcert# openssl x509 -req -in client-req.pem -days 3600 -CA ca.pem -CAkey ca-key.pem -set_serial 01 -out client-cert.pem

 

--> Signature ok
subject=/C=AU/ST=Some-State/O=Internet Widgits Pty Ltd/CN=example.com/emailAddress=ssl@example.com
Getting CA Private Key

After generating the server-client certificates, we need to verify that they are not corrupted and that they were created using our root certificate (CA).

secuser@secureserver:~/sqlcert# openssl verify -CAfile ca.pem server-cert.pem client-cert.pem

 

--> server-cert.pem: OK
client-cert.pem: OK

The sslcert directory should now resemble the following:

secuser@secureserver:~/sqlcert# ls -la

 

-->
-rw-r--r-- 1 secuser secuser 1679 May 29 19:49 ca-key.pem
-rw-r--r-- 1 mysql mysql 1367 May 29 19:49 ca.pem
-rw-r--r-- 1 mysql mysql 1241 May 29 19:49 client-cert.pem
-rw-r--r-- 1 secuser secuser 1679 May 29 19:49 client-key.pem
-rw-r--r-- 1 secuser secuser 1062 May 29 19:49 client-req.pem
-rw-r--r-- 1 mysql mysql 1241 May 29 19:49 server-cert.pem
-rw-r--r-- 1 secuser secuser 1679 May 29 19:49 server-key.pem
-rw-r--r-- 1 secuser secuser 1062 May 29 19:49 server-req.pem

We should now copy the following certificate files into a new directory and assign the correct permissions.

secuser@secureserver:/# sudo cp ca-cert.pem server-cert.pem server-key.pem /etc/mysql-tls

 

secuser@secureserver:/# sudo chown mysql:mysql /etc/mysql-tls/*

Now it’s time to configure our MySQL server to use our TLS certificates. To do so, we’ll need to modify the mysqld.cnf file using a text editor.

secuser@secureserver:/# sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf

Under [mysqld] we find the following entries and uncomment them. We then specify the correct path.

ssl-ca=/etc/mysql-tls/ca.pem
ssl-cert=/etc/mysql-tls/server-cert.pem
ssl-key=/etc/mysql-tls/server-key.pem

Finally, we must restart the MySQL Server for our changes to take effect.

secuser@secureserver:/# sudo service mysql restart

We will login to the MySQL server to verify that TLS is now enabled.

secuser@secureserver:/# mysql -u root -p

 

mysql> SHOW GLOBAL VARIABLES LIKE 'have_%ssl';

 

-->
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| have_openssl | YES |
| have_ssl | YES |
+---------------+-------+
2 rows in set (0.00 sec)

In order to test it, we must copy ca.pem, client-cert.pem and client-key.pem to our client. After copying the files, we are now ready to securely connect to our MySQL Server.

secuser@secureserver2:~# mysql -h 192.168.2.114 -u jason -p --ssl-ca=ca.pem --ssl-cert=client-cert.pem --ssl-key=client-key.pem

 

Enter password:
# MySQL Server message

 

mysql> status;

 

-->
mysql Ver 14.14 Distrib 5.7.12, for Linux (x86_64) using EditLine wrapper
Connection id: 20
Current user: jason@192.168.2.108
SSL: Cipher in use is DHE-RSA-AES256-SHA
Server version: 5.7.12-0ubuntu1 (Ubuntu)
Protocol version: 10
Connection: 192.168.2.114 via TCP/IP
TCP port: 3306
Uptime: 4 min 57 sec

The connection to our MySQL server is now encrypted. The following is a sample of sniffed network packets containing encrypted traffic after the TLS setup.

..........!...........................K...G......#.3..u+.&..j..[..hx...f.W.i... .9.3.}.|.y.x.w.5...... ...../.~......J...F....E:..0..OC.Kt.F..G.....lU...... ......s......j.......E...j..We..9.....v...r..o..l0..h0..P...0. *.H.......0y1.0 ..U....AU1.0...U...Some-State1!0...U...Internet Widgits Pty Ltd1.0...U...cacert.com1.0.. *.H... ...ssl@cacert.com0..160529184557Z.2604071 84557Z0{1.0 ..U....AU1.0...U...Some-State1 !0...U...Internet Widgits Pty Ltd1.0...U....example.com1.0.. *.H... ...ssl@example.com0.."0. *.H............0...........P.....t.q..rw...@..+N3....e...y..0...,..sb.D.wv .x....~...l...q...w...)R.!..kl.aU.u.L Qv...{.T.. .4 ...[.!;.=.G .T..b.9V.k.K*.r./}>.../...I..b^<D...xg!.i..l.R .rGO....8.*..Q.. ...w.[.....|.{...fz..'. ..2ZI...WW..0.....V....>.'...l.q.6.....kuiWL....a........0. *.H............ ...d.5..o..'..]/)...?.~X.

Note – It is recommended that the passphrase of the TLS key is not omitted in a production environment.

mysql_secure_installation

MySQL servers have an out-of-the-box security configuration script which helps us improve the security of our MySQL installation by doing the following:

  • Setting a password for root accounts.
  • Disable remote access.
  • Remove anonymous user accounts.
  • Remove the test database and revoke access to users with privileges that permit anyone to access databases with names that start with test_

As of MySQL 5.7.2, there is the option to install a validate_password plugin which can be used to test passwords against a password policy, and reject a password if it is weak.

After the installation of MySQL Server finishes, we can run the following command:

secuser@secureserver:~$ mysql_secure_installation
--> Securing the MySQL server deployment.

If we have already specified a password for root during the MySQL server setup, we will be asked to enter it.

Enter password for user root:

Otherwise, if there is no password set for root, we’ll connect to MySQL straight away and receive a prompt to set a root password later on.

Connecting to MySQL using a blank password.

We will be asked to install Validate Password Plugin. Entering the letter y to install it.

VALIDATE PASSWORD PLUGIN can be used to test passwords and improve security. It checks the strength of password and allows the users to set only those passwords which are secure enough. Would you like to setup VALIDATE PASSWORD plugin?

 

Press y|Y for Yes, any other key for No: y

We then select the level of validation policy. We will set it to 2 (strong).

There are three levels of password validation policy:
LOW Length >= 8
MEDIUM Length >= 8, numeric, mixed case, and special characters
STRONG Length >= 8, numeric, mixed case, special characters and dictionary file
Please enter 0 = LOW, 1 = MEDIUM and 2 = STRONG: 2

If there is a password set for root, it will show its strength and then ask if we want to change it.

Estimated strength of the password: 100
Change the root password? (Press y|Y for Yes, any other key for No) :

The rest of the process is straightforward. Press y for all prompts.

By default, a MySQL installation has an anonymous user, allowing anyone to log into MySQL without having to have a user account created for them. This is intended only for testing, and to make the installation go a bit smoother. You should remove them before moving into a production
environment.

 

Remove anonymous users? (Press y|Y for Yes, any other key for No) : y
Success.

 

Normally, root should only be allowed to connect from 'localhost'. This ensures that someone cannot guess at the root password from the network.

 

Disallow root login remotely? (Press y|Y for Yes, any other key for No) : y
Success.

 

By default, MySQL comes with a database named 'test' that anyone can access. This is also intended only for testing, and should be removed before moving into a production environment.

 

Remove test database and access to it? (Press y|Y for Yes, any other key for No) : y
- Dropping test database...
Success.
- Removing privileges on test database...
Success.

 

Reloading the privilege tables will ensure that all changes made so far will take effect immediately.

 

Reload privilege tables now? (Press y|Y for Yes, any other key for No) : y
Success.

 

All done!

Note — Understanding how to manually configure MySQL server security controls is crucial; it would be a bad idea to rely solely on the use of mysql_secure_installation to secure your MySQL server as it only exposes limited security configuration tweaks.

Logs

When it comes to troubleshooting or debugging an application, as well as investigating a security incident, one of the most important sources of information we have is log files. A MySQL server has many log files, but we will focus only on the main three.

Note — Any changes made to the MySQL server configuration file requires a restart.

MySQL’s logging can be configured from the mysqld.cnf file using a text editor.

secuser@secureserver:~$ sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf

General Log

The general log contains information regarding client connection/disconnection as well as all SQL queries received from clients. It is useful for debugging and auditing user actions but it may affect the overall performance of the server.

general_log_file = /var/log/mysql/mysql.log
general_log = 1

Error Log

The error log contains information regarding the mysql daemon operation and will contain information such as when the mysql daemon was started and stopped, as well as any critical errors that may have occurred during the daemon’s runtime.

log_error = /var/log/mysql/error.log

Long/Slow Queries Log

It contains information regarding queries that took more time than the value set for long_query_timeto execute. This is mostly useful for monitoring the performance of the server.

slow_query_log = 1
slow_query_log_file = /var/log/mysql/mysql-slow.log
long_query_time = 2

Updates

Apart from all the configuration analyzed in this article, another important area we should focus on is MySQL server updates.

Keeping MySQL server up to date is critical because not only do newer versions include patches to known (or unknown) vulnerabilities, but they can also include security enhancements and features which can make a MySQL server more secure. Sometimes, all an attacker needs is one vulnerability or one misconfiguration to cause havoc, so we need to ensure MySQL is hardened at all levels to make it as resistant to attacks as possible.

We can upgrade MySQL as part of system updates using an APT package manager on Ubuntu.

secuser@secureserver:~$ sudo apt-get update && sudo apt-get upgrade

If we only want to upgrade the MySQL server, we can run the following command.

secuser@secureserver:~$ sudo apt-get update && sudo apt-get install mysql-server

Backups

No system can ever be 100% secure. Whilst we can’t change that fact, we can, and should, be proactive by having the correct mechanisms in place that will make an attacker’s job harder, as well as to be well prepared in the event of a disaster.

If an attacker gains access to our server and drops our database, the only way to restore business continuity is to restore it from a backup. Not only do we need to keep backups, but these backups need to be stored in a secure location. Even if an attacker manages to get access to our secure storage, by encrypting them we make his job really hard, if not impossible.

Source: Securing MySQL Server on Ubuntu 16.04 LTS: Configuring MySQL Securely, Part 3 – DZone Security

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s