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:/# 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:~/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:~/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:~/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:~/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:~/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.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:~/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:~/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.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:~/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:~/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:/# sudo cp ca-cert.pem server-cert.pem server-key.pem /etc/mysql-tls
secuser:/# 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:/# 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:/# sudo service mysql restart
We will login to the MySQL server to verify that TLS is now enabled.
secuser:/# 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:~# 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.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.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.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:~$ 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:~$ 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:~$ sudo apt-get update && sudo apt-get upgrade
If we only want to upgrade the MySQL server, we can run the following command.
secuser:~$ 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
Linux Security Blog 