Protecting MySQL Beyond mysql_secure_installation: A Guide

MySQL is a complex beast to tame. Part of that is because the RDBMS has a lot of settings and parameters that can be configured to make it able to perform at the very best of its ability, but another side of the reason is that its protection and security are more than clicking a couple of buttons on the screen.

One of the most popular ways to secure MySQL (and MariaDB, for that matter) is by using mysql_secure_installation or mariadb_secure_installation. Both commands are shell scripts unique to either MySQL or MariaDB and as of MariaDB 10.4.6, both of them are symlinks as well – those shell scripts allow us to improve the security of our MySQL-based databases by letting us set a strong password for initial (“root”) accounts, letting us make root accounts accessible only locally, letting us remove anonymous accounts, and letting us remove the initial test database.

Setting a password for initial accounts is important because a strong password protects us from data breaches both now and in the future, anonymous accounts are dangerous because users can use those accounts to connect to our databases without specifying a password, and removing the test database is important because it can, by default, be accessed by anonymous users too. Both mysql_secure_installation and mariadb_secure_installation can be run by simply executing the commands in the terminal with the syntax mysql_secure_installation or mariadb_secure_installation respectively.

However, protecting MySQL is so much more than just using mysql_secure_installation. Bear with us and we will explain what you need to do to be safe.

mysql_secure_installtion: Best Practices

As with most things related to the web, the security of MySQL begins with a couple of best practices. These include running mysql_secure_installation as per the advice above, setting up strong passwords for all user accounts, keeping an eye on the applications running on the server and making sure they’re safe, keeping the server itself locked up and safe, and regularly assessing the security of our database instances. However, best practices alone won’t get us very far – we also need to secure access to our databases and take proper care of the privileges within them.

Access Control and User Privileges

The way mysql_secure_installation begins to secure our infrastructure is by letting us specify a safe password for the root account in our database. That’s a good start, but we should also keep in mind that all accounts – no matter if they’re initial or not – need to have privileges. To ensure the security of your MySQL instance and the security of the applications behind it, consider granting only necessary privileges to all users; evaluate the needs and capabilities of your project and choose accordingly. A good practice is to use a set of privileges ranging from the lowest to the highest levels of security. Everything then could be arranged as follows:

• The set of privileges concerning the lowest level of security should contain pretty much all of the privileges that can be specified – either specifying ALL or CREATE, DELETE, DROP, EXECUTE, INSERT, SELECT, SHOW DATABASES, and UPDATE will do. If needed, specify the GRANT OPTION privilege. Such a security level is intended to be allocated to the accounts that we trust the most and that manage our databases at a day-to-day level. The people in charge of such accounts could include experienced database administrators, database-minded software engineers, or even security engineers dealing with databases – it all depends on the company. However, bear in mind that these accounts can cause as much destruction as they can cause peace of mind.
• The set of privileges concerning the medium level of security could include the CREATE, DELETE, DROP, INSERT, SELECT, UPDATE, and SHOW DATABASES privileges – such a set of privileges would allow people to create databases and tables, delete data, drop databases and tables, and also insert, select, and update data within tables. That’s more than enough for any unsophisticated maintenance operation that pertains to updates, data insertion or deletion, or other operations – however, for the highest level of security, we’d still have to move up a notch.
• The set of privileges with the highest level of security should contain only the privileges that allow users to administrate MySQL at the simplest level possible. These might include only CRUD (Create, Read, Update, Delete) privileges: in most cases, simply granting INSERT, SELECT, UPDATE, and DELETE will be enough. In some cases, granting even fewer privileges (e.g. only SELECT) would be feasible (always think what the account is intended for: if the account only runs SELECT queries, only the SELECT privilege will do.)

These kinds of privileges will help us when completing all kinds of operations – from basic maintenance and essential functions to preventing high-profile security breaches. Employ a firewall and you should be good to go!

Before you hit the ground running, though, it may be a good idea to be mindful of the security plugins offered by MySQL.

Security Plugins

As far as security plugins are concerned, they fall in one of the following categories:

• Plugins that help us authenticate with MySQL;
• Plugins that validate passwords and ensure their security;
• Plugins that help keep our connections safe (e.g. protect MySQL from bruteforce attempts by delaying server responses after many failed logins);
• Keyring plugins that enable MySQL to store potentially sensitive information so that it can be accessed at a later date;
• Enterprise plugins – MySQL also offers two enterprise-grade plugins and those usually either enable users to pursue audit-related activities or secure them from attacks targeting their MySQL infrastructure. Two of such plugins that come to mind include the MySQL Enterprise Audit and MySQL Enterprise Firewall plugins.

Authentication plugins let us implement multiple different authentication methods into MySQL, password validation plugins require the passwords of accounts to adhere to certain security policies, connection-control plugins help us fend off bruteforce attacks, keyring plugins can communicate with multiple different cloud providers (AWS, Hashicord, Oracle Cloud Infrastructure) to store keyring data.  Keyring data can also be stored in a local encrypted file (keyrings store sensitive data in a file stored locally or in a cloud for later retrieval.)

Enterprise plugins, however, come at a steep price (licenses can cost upwards of $5000), but they can be immensely useful for database-minded companies that need to either:

• Monitor and block malicious activity targeting their databases (MySQL Enterprise Firewall)
• Meet regulatory necessities and demands and achieve security compliance as a result (think ISO 27001 and the like) by regularly auditing their databases and securely storing audit files (MySQL Enterprise Audit)
• Provide multiple sophisticated and advanced ways to back up our data and keep it safe from prying eyes (MySQL Enterprise Backup.)

Securing Data with Data Breach Search Engines

Some of the advice above will help you secure your MySQL instances, some of it will keep your applications safe from nefarious parties as well; However, your infrastructure won’t be fully secure unless you take care of the security of your organization and employees as well. The BreachDirectory data breach search engine and BreachDirectory API can keep your organization safe by providing reliable and quick access to leaked data found in data breaches and providing up-to-date information on how to protect yourself, your loved ones, and your employees from the growing threat of data breaches.

Summary

mysql_secure_installation is a decent first step if we want to secure our MySQL infrastructure and the data behind it; however, if we are serious about the security of our systems, we need to employ a couple of key additional measures including taking care of secure access, assigning only those privileges that are necessary, and using security plugins to further the security posture of our databases.

For those who are even more into security, data breach search engines like the one provided by BreachDirectory will help in protecting their key online assets from identity theft and data breaches – the BreachDirectory API will ensure that their organization doesn’t become the next victim of identity theft. We hope that you’ve enjoyed reading this article, come back to the BreachDirectory blog to learn more about the newest developments in the cybersecurity space, and until next time.