As we all know, data breaches occur all too often. After a data breach occurs, we are used to seeing security experts, company associates and other people advising us to change our passwords and safeguard our data by securing our infrastructure.

This is what the majority of developers do – we secure our infrastructure, maybe hire some information security consultants for advice on how to prevent data breaches in the future and forget about the data breach as time passes.

But have we ever thought how would we secure MySQL in case such an event occurs? This is what we will try to do in this post.

MySQL Security 101

As far as security in MySQL is concerned, we have a wide range of options to look at:

1. The security of our MySQL installation – did we think about securing our MySQL nodes when we first installed MySQL on our server? If not, why?
2. MySQL access control – how many users were entrusted with certain privileges? How many users had access to running administrative statements in the database? When was access granted? Was access revoked at some point in time?
3. The usage of security plugins – did our MySQL installation use any security plugins available? What were they? Were they updated?
4. General database security – did we enforce general database security practices? Did our users have the choice to use strong and secure passwords? Did we make an effort to secure our application against SQL injection? What storage engine did our database use? Why?
5. Backups – did we have backups? Were they up to date? Did we test them?

These are the basic questions we need to answer if we want to understand how to secure MySQL – we will now look further into them.

The Security of a MySQL Installation

If our information systems are suspected to have suffered a data breach, it’s a good idea to re-install MySQL on our servers – it’s also a good idea to start from securing the MySQL installation itself meaning that when we have re-installed MySQL on our server we should look at the following things:

1. Assign a password to the root MySQL user – this should seem obvious, but sometimes we forget the simplest things. Make sure that the password consists of uppercase, lowercase letters, numbers and special symbols.
2. Consider changing the location of the top-level directory of your MySQL installation – to do this, you can copy the existing database directory to a new location:
   sudo rsync -a /var/lib/mysql /mount/volume-01, then include the following lines in your /var/lib/mysql/my.cnf file:
   socket=/mount/volume_01/mysql/mysql.sock
datadir=/mount/volume_01/mysql. Change the directory according to your needs.
3. The secure_file_priv system variable sets the directory from which import and export operations are allowed to execute – if the operations will not originate from this directory, the file(s) will not be imported into the server.
   In order to set the variable to your desired directory, navigate to the /var/lib/mysql/my.cnf file and change the value of the secure_file_priv variable to a different directory.

The basic MySQL security steps depicted above should help you to secure your MySQL installation, but that’s only one part of MySQL’s security. Now we will the other side of it – MySQL access control.

MySQL Access Control

In order to ensure proper MySQL access control, restrict the usage of certain statements only to certain users – weigh your options and only allow users access to statements that are necessary for them to perform their duties.

For example, if a user should only read from the database, he should be only allowed the usage of SELECT statements – the choice of using multiple statements like ALTER, UPDATE and others might not be necessary.

Also consider revoking user access to certain statements – if you see that a certain user no longer needs to, for example, add indexes to the table, consider revoking his access to ALTER statements, etc.

MySQL Security Plugins

MySQL includes several plugins that are able to strengthen its security capabilities. We will not be discussing all of them in this chapter, but some of them are:

• Authentication plugins – MySQL has more than one such plugin – one of them is the SHA-256 pluggable authentication plugin which is built-in to the server and can be used by creating a user and specifying that it authenticates using the sha256_password plugin:
  CREATE USER 'demo_user'@'localhost' IDENTIFIED WITH sha256_password;
  Next, set the old_passwords variable to 2 (this will cause the PASSWORD() function to use the SHA-256 hashing algorithm):
  SET old_passwords = 2;
SET PASSWORD FOR 'demo_user'@localhost = PASSWORD('demo_password');
• The password validation plugin – the validate_password plugin aims to improve security by enabling password strength testing.
  In order to use the plugin, install validate_password, and, if needed, modify the value of validate_password_policy (the variable can be set to numeric or string values – 0, 1 and 2 or the values of LOW, MEDIUM and STRONG respectively) – the policy setting LOW only tests password strength – by default, passwords must be at least 8 characters long. This length can be changed by modifying the variable validate_password_length, a MEDIUM policy setting adds certain conditions – passwords must contrain at least 1 numeric, lowercase, uppercase and special character. These values can also be changed by modifying the variables validate_password_number_count, validate_password_mixed_case_count and validate_password_special_char_count.
  A STRONG password policy setting adds the condition that passwords that match or exceed the length of 4 characters must not match words in a dictionary file which can be specified by modifying the validate_password_dictionary_file variable.
• The MySQL enterprise firewall – as of MySQL Enterprise edition 5.6.24, MySQL also includes a MySQL Enterprise Firewall with a purpose of permitting or denying SQL statement execution according to a whitelist input validation. Each account registered with the firewall has its own whitelist of allowed statements – all other statements are blocked.
• The MySQL enterprise audit – the enterprise edition of MySQL also includes MySQL Enterprise Audit which is implemented using a plugin called audit_log. Enterprise audit uses the audit API in order to enable the monitoring and logging of queries executed on MySQL servers. The plugin produces a log file containing content that was derived looking at the activity of the server.

General Database Security

General database security, in the majority of cases, boils down to the usage of basic security measures for databases – we should use strong passwords for all of the accounts in use, developers should only allow people they trust to access data, prevent the database from being overloaded, avoid physical damage to the servers, avoid design flaws in software that deals with databases (SQL injection, for example, can be avoided by using prepared statements), also avoid data corruption or loss, etc.

Backups

Data is an essential part of your website – as such, backing up your files and database should be a no-brainer. Data corrupts. Hard drives fail. There are all kinds of scenarios where you can lose data and if you do not back it up, you can be in deep trouble.

Backing up your files and database is an extremely important part of any security routine. There are multiple tools and services that are designed to automate this task (you can even make one yourself by creating a bash script), you can store data on cloud storage, drives etc. – storage space nowadays is so cheap that it makes sense to back up pretty much everything.

And, in case a disaster does occur and your data gets wiped from the servers in production, it’s extremely important to also test your backups and see if you can restore your data quickly and efficiently – performing backup testing should be an essential part of any backup routine.

Summary

To summarize, in order to protect your MySQL database after a data breach it is a good idea to re-install MySQL on your server and restrict user access shortly after. Keep in mind that your user accounts should use strong passwords (use uppercase, lowercase letters, numbers, and, if possible, symbols), consider using MySQL security plugins, be sure to enforce database security practices both in MySQL and in associated software configurations and take care of your backups. Back up all of your critical data, make sure your backups are up to date and always test them.

If you have performed the steps outlined above, you should be well on your way to a better future for your MySQL database, however, securing your MySQL infrastructure is only one piece of the database puzzle: to keep your infrastructure safe, you should also consider performing a scan through a list of various known data breaches that have been already leaked on the web and then performing steps to secure yourself judging from that. Consider changing your passwords, enabling two-factor authentication, and keeping an eye out on various security-related blogs to learn more about the newest developments in the database space.