In the ever-changing technological landscape, it is important for companies and individuals to know a list of best practices they can use on any technology their databases support. As you already know, most of the websites in the world 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 some time, you already know some of the security basics that come with MySQL. MySQL DBAs recommend that you run mysql_secure_installation (or mariadb_secure_installation) on your installation of MySQL or MariaDB to improve the security of your database instances upon installation, allowing you to set a password for root accounts, which allows root accounts can remove. Remove localhost, anonymous user accounts, and remove the test database, which can be accessed by anonymous users by default, but that’s just the basics.
Setting one password for all your accounts is, of course, a good security practice, but if you want to ensure that your MySQL databases continue to perform and, most importantly, secure, against all the threats of modern times, So there are a couple of additional tips and tricks that you need to follow.
MySQL Security Checklist
A proper security checklist includes much more than just setting a password for your accounts. Depending on the version of MySQL you are running, the MySQL checklist for your database may include the following:
- access control.
- Protecting all users working with MySQL.
- Granting and revoking privileges to and from users.
- Understanding the concepts of account categories, reserve accounts and roles.
- Understanding how password management and account locking work.
- Properly backing up and securing MySQL backups.
- If necessary, using enterprise plugins offered by MySQL.
First, proper access controls are necessary to allow only people with the appropriate knowledge and requirements to access, read, or modify the data in the database. Protecting users goes hand in hand with the prior statement – strong passwords will help, but privileges protect your database from complete destruction if at least some of your users are compromised. A user who is only able to read from a database but not be able to write to it will not be able to do much harm. Privileges also go along with roles – a role is essentially a collection of privileges and roles can be granted and revoked to users, which means that if you grant certain privileges to a role And then assign a 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), be aware that MySQL also supports account locking and unlocking when using the Account Lock and Account Unlock details – a specific one when your employee is on Locking a user account may be required to leave, 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 closed.
Employing enterprise plugins will protect your database from sophisticated attackers as MySQL is able to provide an enterprise firewall that is capable of blocking all kinds of attacks, and using the backup features provided by MySQL will ensure that Once your database goes down. Corrupt, or something happens, your data is always safe.
Security Checklist in Real Life Scenario
“In theory, it all sounds great”, we hear you say, however, how does the security checklist compete against threats in the real world? Allow us to walk you through each phase of the defense one by one:
- Start with consolidating access control – to do this, first find out which account is able to access which part of the application in question. Once you’re done, start looking through all your accounts one by one and make sure only the necessary privileges exist for that account.
- Make sure all users who work with MySQL in any scenario (not just the root account) have strong passwords – passwords must contain at least 16 characters, upper and lower case letters, at least some special characters and numbers Should be Of course, we understand that remembering passwords that are unique to each service you use is incredibly time-consuming and tedious: for this, use a password manager – combine all your passwords into one ” Master” password, put random passwords in a vault (that’s securely encrypted even when you’re not using a password manager), and forget about them!
- Create some roles that go something like this: one role is for basic users, another role is for mid-tier users (think normal users using your web application), and a third role is for all those users who have some kind of administrative privileges in your web application. Keep a note of the accounts reserved in MySQL (the root, mysql.sys, mysql.session, and mysql.infoschema accounts), and secure them appropriately. Also note that as of MySQL 8.0.16, MySQL has included the concept of SYSTEM_USER numbering user account categories based on privilege: a user with the above privileges is a system user, and a user without one is a regular user. Make the user a system user if you want it to affect 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 MySQL’s documentation.
- It is also important to properly understand account management and how account locking works – an account that is locked 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: ‘your_password’ CREATE USER ‘demo_user’ identified by the account lock;
And to lock an already created user, use the ALTER USER query like so:
Replace demo_user identified by ‘your_password’ account lock;
- To properly take care of backups in your MySQL infrastructure, first think about what kind of backups are necessary for your use case (MySQL offers several types of backups to choose from: physical and logical, where physical backup in MySQL takes care of files) infrastructure and logical backups back up queries that are later used to rebuild data in MySQL), then choose the form of your backup (full, incremental, partial), think Whether you need a hot backup (a backup that is performed when users are still logged into a system) or a cold one (done with all offline users), and finally, an automatic backup of your data. Use the capabilities of a software like cPanel or any other to schedule a cron-job to back up from. After making sure you can recover your data on another server, you can store them in a secure location.
- Enterprise plugins may be a necessity if you find yourself in need of an enterprise-grade firewall or security that is not typically provided by MySQL: in this case, you may find yourself looking at the Enterprise edition of MySQL, which supports MySQL. As per, “includes the most comprehensive set of advanced features, management tools, and technical support to achieve the highest level of MySQL security, scalability, reliability, and uptime.” The most popular tool in this scenario would probably be an enterprise firewall capable of protecting your web application from SQL injection and similar database-specific attacks.
Going from top to bottom, securing your users, password protection, taking care of privileges, roles and backups will certainly take your database security to the next level of security highway, but if you find yourself in the security of an organization If you do, there are some additional things you need to consider.
Enterprise-level security in a real-world scenario
When securing an organization, taking care of passwords, privileges, and certain 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’s explain:
- MySQL Enterprise Firewall can allow, block, or detect malicious SQL statements by anyone in your organization (there are three statement operation modes), Enterprise Firewall can also create an aggregated list of allowed queries for a group of users Can block SQL injection attacks and detects database intrusions by default, monitors threats in real time and blocks suspicious traffic hitting your database, lets you use pre-approved SQL statements Allows users to create user-specific permission-lists, and much more. Of course, this comes with a fairly hefty price tag, but the price for a data breach can be so high!
Services like BreachDirectory and their API offering can help you become more secure by searching through up-to-date lists of data breaches and your organization being secure by easily implementing API offerings into your infrastructure. can do.
Securing a relational database management system such as MySQL or its flavors such as MariaDB or Percona Server is never an easy task – however, with the right amount of knowledge required, you can make your database sing!
We hope this blog post has given your team the knowledge your team needs to secure your database – be sure to run a search through BreachDirectory to see if you or someone you know has been exposed to identity theft. Now, implement the API offering in your infrastructure, and until next time!