Security is a crucial part of any database system and MySQL is no different! There are very specific techniques a database administrator can use to protect their MySQL installation. Below is a list of the top security tips for MySQL database administrators.
In this article...
Keep your software up-to-date.
One of the best ways to keep your data secure is to make sure you are using up-to-date software. When software is updated, it often includes security updates that protect against hackers and other vulnerabilities. If your company does not update its software regularly, it will be more vulnerable to attacks.
Always check release notes.
In any version of MySQL, there is a detailed list of changes that range from performance to security fixes. You can check them out on the MySQL Community
Server download page; just look for the Change History link or you can go right to the change history here.
It is important to know the changes in your release so you can keep track of possible bugs that your application could exploit.
Avoid LOCAL INFILE.
Using LOAD DATA LOCAL INFILE is a great feature to have in QA, DEV or TEST; however, it is not a good feature to enable in production. Basically, if you connect to a remote MySQL server you could load a file from the client to the MySQL host server. This is a great feature to have when you don’t want to mess with copying the file out of a remote server you don’t have access to. On the other hand, this can be dangerous given that the client can load any file to the MySQL server host to which it has read access.
Monitor as much as you can.
Monitoring the MySQL database and schema in general is a good idea. In MySQL, you can utilize the information schema database to access a lot of great information. You can keep a running count of tables and columns within any and all databases and monitor those numbers to see if they have changed. You can also implement a similar mechanism to monitor the USER_PRIVILEGES table.
If you have MySQL Enterprise Monitor there is a security section you can enable to check if something has changed in your schema or user accounts.
Don’t run MySQL as root.
You should not be running your MySQL server as the root user. To do so is extremely dangerous given that any user with FILE privilege within MySQL is able to create a file as root.
I find it very easy to create an unprivileged Unix user called mysql and use this account to start and stop mysqld.
When preloading with init-file, you take the good with the bad.
The init-file option in MySQL can be very useful; however, it is VERY dangerous as well. A good use of init-file is to preload your data into memory during the initial startup. The problem with this is that in the event someone had access to modify this file they could put in ANYTHING they want in the preload and it will be executed upon startup.
Don’t use default passwords.
Another important best practice for database security is to avoid using default passwords. If you’re the person in charge of maintaining a database, it’s important to change the default password.
If you don’t change the default password, anyone who knows that it exists will be able to access your database without any problems whatsoever. It not only increases your risk of being hacked but also makes it easier for someone to do it.
Review top password management systems to take the guesswork out of security credentials.| TechRepublic
Use strong passwords for the server and its endpoints.
Make sure that the server and any endpoint or employee that connects to it have a strong and secure password. There are many methods hackers can use to guess or brute force your password. So, it is important to use a strong password that is at least 12 characters in length and contains a mix of letters, numbers, and symbols.
In most enterprise settings, IT and security teams insist on the use of a secure password management system. Whether it hosts login credentials on local servers or uses a cloud-stored SaaS model, password managers are critical to keeping databases safe from unauthorized access.
Admins can set policies as to both username and password age and complexity, along with mandating use of multifactor authentication. Too, because databases typically have complex URIs whose levels of access may vary based on user, a centrally administered password management system will make life far easier for security and DBAs. By mandating complex passwords, time-based password rests, and controlled access based on job responsibility, the system eliminates much of the lost time and security risk created by handling constant password reset requests.
Avoid plain text passwords in scripts.
It is very easy to be lazy and place a plain text password in your scripts. I think that it is safe to say that we’ve all done this at least once or twice… or still do. In any case, you should try the technique below to help hide passwords from the ordinary user.
- Start by making a file in the /root directory called,
- Insert a key value pair with username and password for a
- Make sure that root owns the file
- Make sure the you chmod 700 .mycreds
- In roots .bashrc put in the following
export MYUSR=`grep myusername /root/.mycred | cut -d "=" -f 2` export MYPWD=`grep mypassword /root/.mycred | cut -d "=" -f 2`
You can now login to MySQL with the following:
[root@sandbox ~]# mysql -u$MYUSR -p$MYPWD somedb Welcome to the MySQL monitor. Commands end with ; or g. Your MySQL connection id is 34015 Server version: 5.5.1-m2-community-log MySQL Community Server (GPL) Type 'help;' or 'h' for help. Type 'c' to clear the current input statement. (somedb@localhost) [(none)]>
In your daily scripts that are run out of root CRONs, you can now replace plain text usernames and passwords. Keep in mind that only the root user has
access to this login method, so, if you are worried about the “what if” on the root user account you probably have bigger security issues. In that case, write everything in C and compile your code.
Store your data on a secure server.
Your data should be stored on a secure server and only accessed on an individual basis by authorized employees or clients who have access to the authorized IP addresses or locations. This will make it harder for hackers to easily compromise your system or obtain sensitive data.
If you store your data with a third-party vendor, make sure the company has strong security measures. Check if they have firewalls to protect against potential intruders and viruses, and find out how long they’ve been storing customer data securely.
Back up your data regularly.
One of the best things you can do to keep your database secure is to regularly perform backups. Your database contains valuable information, so it’s crucial to make sure it stays safe.
If you have any worries about your data being compromised, make sure you back up your data and store it in a safe location. This way, if anything goes wrong with your database, you’ll have your data saved, so you can start over again.
Encrypt your data.
Encrypt your data to protect it from hackers. Encryption is a process that takes plain text and scrambles it into unreadable characters. The only way to decrypt the data is with the correct key, which prevents unauthorized people from viewing the information.
The more sensitive your data is, the stronger encryption you should use on it. For example, if you store passwords in your database, you should use a strong algorithm like Blowfish or AES. But, if you store Social Security numbers, you should use 128-bit encryption or higher.
You should also encrypt data at rest when storing it on a hard drive, as this protects against physical theft of the device or computer holding your database. Encryption is especially important for laptop computers because they can easily be stolen while on the go or compromised when using public Wi-Fi connections.
You can’t have ALL PRIVILEGES.
Give specific permissions on an as-needed basis and use different logins for different purposes. Below is a shortlist of some users that are used for different aspects of managing MySQL:
- Backupuser = local user that runs backups
- Root = local user for total administration
- Replication = User for replication
- Someappuser = local/remote user with SELECT,INSERT,UPDATE,DELETE
NOTE: You don’t have to give column-level permissions because they hurt performance so try and stick to table, at the lowest, and database level permissions.
FQDN is a NO NO.
Avoid using FQDN or hostnames when granting access. Try to keep it to a subnet
(Example: 10.1.2.%). On one side, this is a security issue because it is very
easy to spoof a FQDN, on the other side this is performance related.
If you don’t disable reverse DNS lookups and DNS is miss-configured or goes down, all of your connections will be in an “unauthenticated” state for the duration of the reverse DNS lookup, around 2 minutes. Needless to say, this is something to avoid on at least two levels.
Beware of %.
Avoid having the MySQL server open to connections from everywhere by using ‘%’ for any user. Although remote exploits are few and far between, it is just better not to risk it. Limit a user’s access to a specific VLAN or even better a specific IP address.
Know the basics of SQL injection and RFI hacking.
SQL injection and RFI hacking are two ways your data can be compromised. What these methods do is allow a third party to access information from your database. To avoid this, you need to know how these hacks work.
A SQL injection hack happens when an attacker inserts SQL commands into a form field in order to retrieve data from the vulnerable database. This is usually done by submitting a string that contains the command along with some user input.
For example, if you type “SELECT * FROM Users” into a username textbox on a login form, they could enter “; DROP TABLE Users” as the username and something like “1′ or 1=1–” as the password and then submit it to the website. The resulting query will return all of the data from the table called ‘Users.’
An RFI hack is when someone sends an FTP request containing an arbitrary FTP command to read or write files in an FTP server directory via HTTP request headers.
This can happen when someone makes an attempt to use FTP to remotely access their own file from a computer. Rather than typing “ftp://” to access their own file, they can mistakenly type “file://,” giving them full remote access to any other files on that person’s computer via FTP.
Use MySQL Secure Installation.
The number one security tip, and the most basic, is the use of mysql_secure_installation. Below is an example of mysql_secure_installation usage:
[chris@sandbox chris]# /usr/bin/mysql_secure_installation NOTE: RUNNING ALL PARTS OF THIS SCRIPT IS RECOMMENDED FOR ALL MySQL SERVERS IN PRODUCTION USE! PLEASE READ EACH STEP CAREFULLY! In order to log into MySQL to secure it, we'll need the current password for the root user. If you've just installed MySQL, and you haven't set the root password yet, the password will be blank, so you should just press enter here. Enter current password for root (enter for none): OK, successfully used password, moving on... Setting the root password ensures that nobody can log into the MySQL root user without the proper authorization. Set root password? [Y/n] Y New password: Re-enter new password: Password updated successfully! Reloading privilege tables.. ... Success! By default, a MySQL installation has an anonymous user, allowing anyone to log into MySQL without having to have a user account created for them. This is intended only for testing, and to make the installation go a bit smoother. You should remove them before moving into a production environment. Remove anonymous users? [Y/n] Y ... Success! Normally, root should only be allowed to connect from 'localhost'. This ensures that someone cannot guess at the root password from the network. Disallow root login remotely? [Y/n] Y ... Success! By default, MySQL comes with a database named 'test' that anyone can access. This is also intended only for testing, and should be removed before moving into a production environment. Remove test database and access to it? [Y/n] Y - Dropping test database... ... Success! - Removing privileges on test database... ... Success! Reloading the privilege tables will ensure that all changes made so far will take effect immediately. Reload privilege tables now? [Y/n] Y ... Success! Cleaning up... All done! If you've completed all of the above steps, your MySQL installation should now be secure. Thanks for using MySQL!
Checking the secure installation at the end is a good practice to adopt.
Below are some quick ways you can use to test and see if the installation is
[chris@sandbox chris]# mysql -uroot ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO) [chris@sandbox chris]# mysql -uroot -p Enter password: Welcome to the MySQL monitor. Commands end with ; or g. Your MySQL connection id is 11 Server version: 5.5.5-m3-log MySQL Community Server (GPL) Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved. This software comes with ABSOLUTELY NO WARRANTY. This is free software, and you are welcome to modify and redistribute it under the GPL v2 license Type 'help;' or 'h' for help. Type 'c' to clear the current input statement. (root@localhost) [(none)]> use mysql Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed (root@localhost) [mysql]> select user,host,password from user where user = '' OR password = ''; Empty set (0.00 sec) NOTE: no blank users and no users without a password. (root@localhost) [mysql]> show databases like 'test'; Empty set (0.00 sec) NOTE: the test database is gone.
Some Closing Thoughts
Securing production MySQL installations is very necessary and should not be considered an afterthought by anyone in the organization. There are many techniques you can implement to aid in the security of a MySQL installation but keep in mind that security on the database level alone is not where security ends. Make sure to collaborate with your system administrators, network engineers, and development teams to ensure security everywhere!