MySQL Security Best Practices: A Checklist

In an ever-changing technology landscape, it is critical for companies and individuals to know a list of best practices that they can use on whatever technology that supports their database. As you already know, the majority of the world’s websites are built on MySQL or their flavors – Percona Server or MariaDB.

MySQL Security – the Basics

If you’ve been running MySQL behind your web infrastructure for a while, you already know some of the security basics that MySQL comes with. MySQL DBAs advise you to run mysql_secure_installation (or mariadb_secure_installation) upon the installation of MySQL or MariaDB in order to improve the security of your database instances upon installation by allowing you to set a password for root accounts, removing the root accounts that are accessible from the local host, remove anonymous user accounts, and removing the test database which by default can be accessed by anonymous users, but that’s only the basics.

Setting a password for all of your accounts is, of course, a good security practice, but if you want to ensure that your MySQL databases stay performant and, most importantly, secure, against all of the modern-day threats, there are a couple of additional tips and tricks you need to follow.

The MySQL Security Checklist

A proper security checklist consists of way more than just setting a password for your accounts. Depending on the version of MySQL you are running, the MySQL checklist for your databases may encompass the following:

• Access control.
• The security of all of the users that work with MySQL.
• Granting and revoking privileges to and from users.
• Grasping the concepts of account categories, reserved accounts, and roles.
• Understanding how password management and account locking works.
• Properly taking and securing MySQL backups.
• If necessary, making use of the enterprise plugins offered by MySQL.

First of all, proper access control is necessary to allow only the people with proper knowledge and requirements to access, read, or modify data in a database. The security of users goes hand in hand with the prior statement – strong passwords will help, but if at least some of your users are compromised, privileges are what saves your database from complete destruction. A user that is able to only read from a database, but not write to it, won’t be able to do much harm. Privileges also go hand in hand with roles – a role essentially is a collection of privileges and users can have roles granted and revoked from them which means that if you assign some privilege to a role and then assign the role to a user, you essentially enable that user to perform a specific set of actions.

If you’re running newer versions of MySQL (version 8 and higher), keep in mind that MySQL also supports account locking and unlocking when using the ACCOUNT LOCK and ACCOUNT UNLOCK statements – locking a specific user account may be necessary when your employee is on vacation, and, when an account is locked and someone tries to access it, MySQL will return an error:

Access denied for user ‘user’@’host’. Account is locked.

Employing enterprise plugins will save your database from sophisticated attackers as MySQL is able to provide an enterprise firewall that is able to fend off all kinds of attacks, and making use of the backup features offered by MySQL will ensure that once your database goes down, gets corrupted, or anything else happens to it, your data is always safe.

The Security Checklist In a Real-Life Scenario

“In theory, it all sounds great”, we hear you saying, however, how does the security checklist compete against threats in the real world? Allow us to walk you through each step of defense one by one:

1. Start with strengthening access control – to do that, first figure out what account is able to access what portion of the application in question. Once you’re done, start going through all of your accounts one-by-one and ensure that only the necessary privileges are in place for that account.
2. Ensure that all of the users that work with MySQL in any scenario (not only the root account) have strong passwords – passwords should consist of at least 16 characters, upper and lower case letters, at least a couple of special characters, and numbers. Of course, we understand that remembering such passwords that are unique for every service you use is incredibly time-consuming and tedious: for that, use a password manager – lock up all of your passwords behind one “master” password, put the random passwords in a vault (that is safely encrypted whenever you’re not using the password manager), and forget about them!
3. Make a couple of roles that go something like this: one role is for basic users, another role is for mid-tier users (think ordinary users using your web application), and the third role is for all users that have some sort of administrative privileges in your web application. Keep a note of the reserved accounts in MySQL (the root, mysql.sys, mysql.session, and mysql.infoschema accounts), and secure them appropriately.Also keep in mind that as of MySQL 8.0.16, MySQL has incorporated the concept of user account categories based on the SYSTEM_USER privilege: a user with the aforementioned privilege is a system user, and the user without one is a regular user. Make user a system user if you want it to have an effect on other regular and system accounts, and make a user a regular user if you want it to modify regular accounts, but not system accounts. For more information, refer to the documentation of MySQL.
4.  Properly understanding how account management and account locking works is also key – a locked account cannot be accessed by anyone in any capacity and can only be unlocked by an account with administrative privileges. To create an account that is locked by default, run a query like the following:CREATE USER ‘demo_user’ IDENTIFIED BY ‘your_password’ ACCOUNT LOCK;

And to lock a user that has already been created, use the ALTER USER query like so:
ALTER USER demo_user IDENTIFIED BY ‘your_password’ ACCOUNT LOCK;

• To properly take care of the backups in your MySQL infrastructure, first think of what kind of backups are necessary for your use case (MySQL offers multiple types of backups to choose from: physical and logical, where physical backups take care of files in the MySQL infrastructure and logical backups backup the queries that are later used to re-create data in MySQL), then choose the form of your backup (full, incremental, partial), think of whether you need a hot backup (a backup that is performed whilst users are still logged in to a system) or a cold one (done with all of the users offline), and finally, use the capabilities of software like cPanel or any other to schedule a cron-job to automatically take backups of your data for you and store them in a safe place after ensuring that you can recover your data on another server.
• Enterprise plugins might be a necessity if you find yourself needing an enterprise-grade firewall or protection that is not usually offered by MySQL: in this case, you might find yourself looking at the Enterprise edition of MySQL, which, according to MySQL, “includes the most comprehensive set of advanced features, management tools, and technical support to achieve the highest levels of MySQL security, scalability, reliability, and uptime.” The most popular tool in this scenario would probably be the Enterprise Firewall that is able to guard your web application against database-specific attacks like SQL injection and the like.

Moving from top to the bottom, securing your users, taking care of password security, privileges, roles, and backups will certainly put your database security towards the next level security highway, but if you find yourself securing an organization, there are a couple of additional things you need to consider.

Enterprise-level Security in a Real-World Scenario

When securing an organization, taking care of passwords, privileges and a couple of roles won’t be enough: for that, you need to either employ enterprise-level plugins or services from outside like BreachDirectory and the like. Let us explain:

• The MySQL Enterprise Firewall can let anyone in your organization to Allow, Block, or Detect malicious SQL statements (the three statements are operation modes), the Enterprise Firewall can also create a composite list of allowed queries for a group of users, blocks SQL injection attacks and detects database intrusion by default, monitors threats in real-time and blocks suspicious traffic hitting your database, allows you to create user-specific allow-lists of pre-approved SQL statements, and so much more. Of course, it comes with a pretty hefty price tag, but price for a data breach can be so much higher!
• Services like BreachDirectory and their API offering can help your organization become more secure by letting you search through up-to-date lists of data breaches and secure your organization by implementing the API offering into your infrastructure at ease.

Summary

Securing a relational database management system like MySQL or its flavors like MariaDB or Percona Server is never an easy task – however, with the right amount of necessary knowledge, you can make your databases sing!

We hope that this blog post has provided your team with the necessary knowledge to secure your databases – make sure to run a search through BreachDirectory to see if you or anybody you know is at risk of identity theft, implement the API offering into your infrastructure, and until next time!