Securing MySQL Server on Ubuntu 16.04 LTS – Configuring MySQL Securely, Part 3

In part 2 of this series, we looked at configuring MySQL securely. In this final part we shall continue looking at ways in which to ensure a secure MySQL Configuration.

Secure Communications

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 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.

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 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 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.

mysql_secure_installation

MySQL server has 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 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!

Logs

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

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_time to 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 MySQL server more secure. Sometimes, all an attacker needs is one vulnerability or one misconfiguration to cause havoc, we need to ensure MySQL is hardened at all levels to make it as resistant to attack as possible.

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

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

If we only want to upgrade 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 restoring it from a backup. Not only we need to keep backups, but backups need it 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.

SQL Injection + Server misconfiguration = Recipe for disaster

Below we will demonstrate how a typical SQL Injection vulnerability can lead to potential complete server compromise. Below we have a script written in PHP which accepts an id parameter via HTTP GET method. It queries the database and if the given id is valid, it displays the email of that user.

A single quote (‘) next to the id (1) is enough to verify that the script is vulnerable to SQL Injection.

After counting the table columns, we know that the “injectable” column is the number 5

http://www.example.com/sql.php?id=1+union+select+1,2,3,4,5,6,7,8,9,10--

We can now test the server’s defenses by using the load_file() function to load a file on the remote server.

http://www.example.com/sql.php?id=1+union+select+1,2,3,4,load_file('/etc/passwd'),6,7,8,9,10--

It seems that the MySQL user that the script uses to access the database has FILE privileges and is able to read files.

Let’s try to go beyond the usual SQL Injection route. Instead of enumerating databases and table names, we will access files using the load_file() function.

http://www.example.com/sql.php?id=1+union+select+1,2,3,4,load_file('/etc/hosts'),6,7,8,9,10--

It seems that secret.example.com is hosted on this server. Navigating to that URL does not reveal anything useful.

Let’s go back and try to discover more information via the SQL injection vulnerability we identified.

We know that this webserver is running Apache HTTP Server on Ubuntu Server, therefore, unless the administrator uses a different setup, by default, the Apache HTTP Server configuration file of example.com should be found under /etc/apache2/sites-available/DOMAIN_NAME.conf

http://www.example.com/sql.php?id=1+union+select+1,2,3,4,load_file('/etc/apache2/sites-available/example.com.conf'),6,7,8,9,10--

By analyzing the configuration file, we can spot the VirtualHost setup for secret.example.com, and that the home directory of that subdomain is /var/www/secret/.

Since navigating to the URL yielded nothing, let’s see if we can find anything from the inside.

http://www.example.com/sql.php?id=-1+union+select+1,2,3,4,load_file('/var/www/secret/index.html'),6,7,8,9,10--

We get the same message. That means index.html contains only that text.

Assuming we are confident that there is more to secret.example.com other than a simple HTML index file, let’s check for the existence of an .htaccess in the directory.

http://www.example.com/sql.php?id=-1+union+select+1,2,3,4,load_file('/var/www/secret/.htaccess'),6,7,8,9,10--

Sure enough, we get some data back, but the rule seems to be missing. Since we are viewing this in a browser, and the server is returning a content-type of text/html, because the .htaccess rule is wrapped in <>, the browser parses it as tag thus not displaying it. Viewing the page source reveals the code.

It seems that there is a file named latest_customer_db.csv which is accessible only from a specific IP. We confirm that by trying to access the file.

Using the SQL injection and the load_file() function, we can access the file internally.

http://www.example.com/sql.php?id=-1+union+select+1,2,3,4,load_file('/var/www/secret/latest_customer_db.csv'),6,7,8,9,10--

It seems that the file contains Names, Email Addresses and Credit Card Numbers with their CCV code. That is definitely something we shouldn’t be able to access.

After some additional very tests, we have discovered that the main site, www.example.com has phpMyAdmin installed and it is publicly accessible, however, we need valid credentials to log in.

We know that www.example.com/sql.php connects to the database since it’s the script on which we exploited the SQL injection. Maybe its source file can reveal something.

http://www.example.com/sql.php?id=-1+union+select+1,2,3,4,load_file('/var/www/html/sql.php'),6,7,8,9,10--

The file indeed contains the MySQL user credentials. Let’s use them to login to phpMyAdmin.

Once authentication was successful. We can now do anything on the MySQL databases since the user has been granted all privileges.

Instead of querying the database and using normally, let’s use phpMyAdmin for something different. We will use INTO OUTFILE to upload a small PHP shell script on the remote server so that we can easily execute commands using the following SQL statement.

SELECT "" INTO OUTFILE '/var/www/secret/shell.php’;

Once our web shell has been successfully created, we can run arbitrary commands on the server.

If we wanted to, we could escalate the attack even further by downloading other, more sophisticated web shells or local exploits for privilege escalation attacks. However, that would be outside the scope of this article.

To read more about web-shells, how they are created and best practices to defend against them, take a look at our web-shell series.

Throughout this series, we have analyzed how various configuration options can affect our MySQL installation security, and how, as demonstrated in the sample attack scenario, a simple SQL injection vulnerability in combination with a misconfigured server can have devastating results–everything from database enumeration, unauthorized access, secret files to potential server compromise.

Had the MySQL server been hardened, the SQL injection attack would have been limited only to basic database enumeration. The misconfiguration however gave the attacker the option to evolve into something completely different and much more dangerous.

THE AUTHOR

Agathoklis Prodromou
Web Systems Administrator/Developer

Akis has worked in the IT sphere for more than 13 years, developing his skills from a defensive perspective as a System Administrator and Web Developer but also from an offensive perspective as a penetration tester. He holds various professional certifications related to ethical hacking, digital forensics and incident response.