intro
MySQL is a powerful, but complicated database management system - as popular as this RDBMS might be, there are still questions surrounding it - one of the main ones being related to security. In this blog, we will tell you everything you need to know to build a secure foundation for your data using MySQL.
As developers, we already know how important it is to ensure that the systems we build perform at the very best of their ability. However, an often neglected side of application and especially database development is security. How do we build databases that are resilient to the attacks that target our infrastructure? We know you‘re already familiar with basic attack vectors like SQL injection, but in the database world, there‘s so much more to that. Let us walk you through everything you need to know.
Architecting for Security – the Basics
Our databases won’t be safe if our applications are flawed, so to start with, familiarizing yourself with 10 of the most prevalent threats for web applications – the so-called OWASP Top 10 – is a good starting point.
The OWASP Top 10 is a frequent starting point for developers that want to secure their software, and it‘s a good one at that – the OWASP Top 10 provides ten of the most dangerous security flaws targeting our software and databases, and it‘s frequently referred to as the industry-standard practice for securing web applications. Some developers also familiarize themselves with GDPR, ISO 27001, and HIPAA if they‘re working for a company that requires those, but for most, OWASP provides a really good starting point.
Architecting for Security – Attacks and Data
Before we protect our databases, we must ensure that our applications are safe and we can do so if we employ secure coding principles and follow the advice contained in the OWASP Top 10. Doing so is very important because the attacks outlined by OWASP target not only applications but databases as well. Here’s why everything is so interconnected:
You get the point – life after a data breach isn‘t exactly the sweetest one. There are some good news, though – most attacks threatening web applications can be easily fended off by following basic security advice: ensure that your application is not passing user input straight into a database without verification, cleanse it where you make user input visible to the user to protect your web application from Cross-Site Scripting, use PDO when interacting with your databases to fend off SQL injection attacks, and consider using a CDN like the one provided by CloudFlare to protect your web applications from DDoS attacks.
If you are looking for an easy and powerful SQL client and database manager, then you've got to try DbVisualizer. It connects to nearly any database.
All that is a good starting point – securing database management systems, however, is a different beast altogether. Read on to understand why.
Architecting for Security – Securing MySQL
As far as MySQL security is concerned, there are multiple things we should keep in mind. The things that we should familiarize ourselves with include, but are not limited to:
We will now start from the top and move towards the bottom.
User Security & Access Control
Perhaps one of the most obvious – and most important – things to secure are the user accounts since most of the time when users install MySQL they proceed with queries that look something similar to those outlined below:
These two queries are a good starting point – one of them creates a user, and the other grants the user all privileges on a specific database. Choose a strong password, issue a FLUSH PRIVILEGES statement to save your changes, and you will be on your way.
Once you have taken care of “basic user necessities” so to speak, your user security journey shouldn’t end here – keep an eye on the following aspects as well:
1
RENAME USER ‘root’@’localhost’ TO ‘username’@’localhost’;
1
CREATE ROLE ‘demo_role’;
Then, assign a privilege to a role:
1
GRANT INSERT, UPDATE, DELETE ON demo_db.* TO ‘demo_role’;
And finally, assign a role to a user by following the query below (replace dbvisualizer with the name of your user):
1
GRANT ‘demo_role’ TO ‘dbvisualizer’@’localhost’;
Security Plugins
As far as security plugins are concerned, they fall into one or more of the following categories:
1
2
plugin-load-add=validate_password.so
Once that’s done, you would then need to set a password strength policy by utilizing the validate_password_policy variable. The password policy you choose will have a direct impact on the length of passwords that can be used – more information about password policies can be found here:
1
validate_password_policy=[LOW|MEDIUM|STRONG]
1
plugin-load-add=connection_control.so
Completing each of the steps covered above will cost you some time, however, each of them rewards your database in their own way – read the documentation surrounding these security measures if you’re not too sure what they do, and only implement those measures you thoroughly understand.
The steps above should help you secure your MySQL infrastructure and put it on the security highway – make sure to back up your data frequently and feel free to stop reading here. After completing the steps outlined above, the security level of your database infrastructure should be high enough to turn intruders away. However, true security fanatics might want to continue reading since now we’re going to jump into the waters of backup security.
Securing Backups
Securing backups isn’t anything revolutionary – on that front, we just need to make sure backups are stored securely, or in other words, in an encrypted fashion.
To encrypt backups, first take and test them, then if you find yourself using Linux, use the following command:
$
openssl enc -aes-256-cbc -salt -in backup.tar.gz -out backup.tar.gz.encrypted -k password
where:
To decrypt the backup, use the following command:
$
openssl aes-256-cbc -d -in backup.tar.gz.encrypted -out backup.tar.gz -k password
All the same here, just note the “-d” part of the command: it specifies that the password should be decrypted rather than encrypted. First specify a path to the encrypted backup, then specify the name of the decrypted backup file as an output (after the -out part of the command.)
That’s perhaps the easiest way to deal with security regarding backups – however, if you don’t want to encrypt your backups and you would still prefer to backup in the traditional fashion, make sure to specify your user and password underneath mysqldump in my.cnf (see example below), then back up your data as usual.
Doing so will enable you to run the mysqldump command without providing a username and a password – that’s a good security practice since usernames and passwords can be observed by looking at the last issued commands via the terminal – however, once you’ve specified the username and password in my.cnf, you can run mysqldump without providing a username nor the password like so:
$
mysqldump database_name table_name > backup.sql
For all available options relevant to mysqldump, please refer to the docs of MySQL.
Summary
Securing the architecture of MySQL isn’t the easiest thing to do – however, aside from protecting our applications from prominent threats like those outlined in OWASP Top 10, it’s an absolute necessity.
After you’ve familiarized yourself with the methods of protection, consider utilizing the power of data breach search engines to be informed once your account is in danger of identity theft, and you should be good to go!
We hope you’ve enjoyed reading this article – come back to the blog of DbVisualizer to learn more about databases and their interaction with applications after you’ve secured your infrastructure, and until next time.
