If you are an experienced MySQL DBA, you probably already know how important MySQL security is. For you, security comes without any question – you already secured your database instances from the get-go: as soon as you installed them. Did you?
Well, if you did not, no worries: know the basics of securing MySQL and you should be on a good path. You already know about ClusterControl which is developed by known database ninjas from all across the globe and can help solve your MySQL problems in no-time, however, when it comes to security, everything isn’t very simple.
“But wait”, – you say. Why should you secure your MySQL instances in the first place? Well, there are a couple of reasons why you should do that:
Securing your MySQL instances is one of the primary ways you can prevent data breaches targeted towards your application or database: of course, you can check yourself against lists of known data breaches to change your password of those services that have been compromised in the past and that would help a lot, but as far as MySQL instances are concerned, they are an entirely different beast, so they need a different approach too.
By securing your MySQL database instances you can make sure that your company complies with security requirements such as GDPR, etc.
Controlling access to your MySQL instances and properly assigning privileges is a good way to prevent data breaches from the viewpoint that even if an account with less privileges would be breached, the consequences of such a breach would be way less severe than, say, a breach of an account that is a superuser.
However, MySQL security does not end with proper privilege assignment: the basics of MySQL security also include password management and account locking, also securing your backups. In order to secure your MySQL instances, you would first need to examine what attacks target your business the most (it would probably be SQL injection, which can be fended off by using prepared statements and not trusting user input), then decide what kind of security measures you would need to take to fend all of the attacks off, and employ them. You would also need to think about what do you want to achieve when you suspect that your account can already be a target of an attack.
How to Decide What to Do?
Obviously, everything sounds easier than it is, right? Just how do you decide what measures to employ when securing your MySQL database instances? However, don’t worry:
Does your application heavily rely on databases and users are constantly found to be executing SQL queries? Protect against SQL injection attacks – the easiest way to do that is to not trust user input (do not forward all of the user input straight to the database, use prepared statements when querying your database.)
Suspect that the primary way attackers could steal data is by logging in to your MySQL account? Use a strong password, preferably generated by a password manager. The reason why you should do this is simple: if your password is obtained by an attacker (that could be done via hacking into a system, then obtaining all of the data of the users), and if your password manager-generated password is hashed, hash cracking would be a very, very difficult task. The more complex your password is, the more difficult it will be.
Want to ensure that even if your account gets hacked, the attacker will do as little damage as possible? When assigning privileges, make use of the “must-know” scenario: that is, do not assign unnecessary privileges for users. In other words, only assign privileges that are absolutely necessary for users to do their tasks, but not more.
Worried that some of your employees might set weak passwords on their MySQL accounts? Worry not – you can enable strength testing of passwords by making use of the validate_password plugin: when the plugin is in use and, for example, an account is created, accounts must use secure passwords, strength of which is defined by a parameter called validate_password_policy which accepts three values: LOW, MEDIUM, and STRONG. A value of LOW only tests passwords for length, MEDIUM checks that passwords would be comprised of lowercase, uppercase, numeric, and special characters, and a policy of STRONG defines that any password that exceeds four (4) characters in length must not match any word in a specified dictionary file.
Suspected that your MySQL account can be a target of an attack? Lock the account, then investigate the incident. In this case, keep in mind that MySQL supports both ACCOUNT LOCK and ACCOUNT UNLOCK statements which can be used like so:
ALTER USER ‘account_name’ IDENTIFIED BY ‘your_super_safe_password’ ACCOUNT LOCK;
That’s it! Your account is now locked!
Obviously, there is more to it (check one of our older blog posts on MySQL security for more details), but these steps should give you a pretty good idea of what to do.
Now MySQL security is closely related to performance and while the performance of MySQL isn’t really the primary focus of this blog post, it can be taken care of by employing ClusterControl: ClusterControl is a fully-fledged database management system that can take care of everything database-related ranging from backing up your data and monitoring & alerting, deployment & scaling, updates, configuration, performance management, etc. For example, head over to ClusterControl, and click on Performance Advisors: you will see a couple of categories including MySQL. In the MySQL category, you can make sure that the performance of your database never suffers:
In the screenshot above, you can see that ClusterControl provides a couple of advisors (connections, general, and InnoDB in this case) that tell you exactly what to do to make sure that your databases stay in shape and are as performant as never before.
We hope that this blog post has helped you improve the security of your MySQL database instances at least a little – while this blog post only provided you with the basics, keep them in mind and the security of your MySQL instances should be set on a good path.