MySQL Security Best Practices
27 Oct 2013 Devops for Dummies 18
If you install a MySQL Database Server with the default options, your data are insecure and your server is in risk of invasion and some performance issues will appear shortly. With some best practices, your MySQL database becomes secure and the performance goes well.
1 – Set root password and change its login name
As in Linux, root user has full access to all databases with permission to do anything. From default install on CentOS, it comes without password (Ubuntu asks to define during package install but everybody puts an empty password). Think as an attacker: if you are accessing a console to login in a MySQL, the first access you will likely try to use user root with password empty.
In the post about install MySQL on Ubuntu and CentOS, showed how to change root’s password:
Access MySQL console:
$ mysql -u root -p
In MySQL console:
> SET PASSWORD FOR 'root'@'localhost' = PASSWORD('new_password');
Another way to do it from Linux console is using the mysqladmin tool:
$ mysqladmin -u root password new_password
Remember to use a strong password to avoid a brute force attack. If you don’t have any idea about a secure password, I suggest you see this article from Microsoft.
In order to improve even more the security around the root user, another good practice is rename it. In order to change root’s username, you have to update in mysql database in table user, so in MySQL Console:
> USE mysql; > UPDATE user SET user="hard_username_to_find" WHERE user="root"; > FLUSH PRIVILEGES;
Now, The access MySQL console via Linux is just by the new username:
$ mysql -u hard_username_to_find -p
2 – Avoid MySQL be accessible from Internet / Different Access privileges regarding the host
Changing directly databases in production environment from a computer outside the network is tremendous risky. Even thought you got the Practice #1, anyone can access your database if your root’s password has been gotten.
Sometimes you want to open access to your database in the hosting and you do:
> GRANT ALL ON *.* TO 'root'@'%';
You are opening FULL ACCESS to root from any server! It’s important restrict operations to specific host:
> GRANT ALL ON *.* TO 'root'@'localhost'; > GRANT ALL ON *.* TO 'root'@'myip.athome' > FLUSH PRIVILEGES
You still have full access, but only from your IP whether it is static.
3 – Backup your database periodically
Sometimes, shit happens. Server crashes, MySQL crashes, you can be invaded and all data are deleted… the world is hostile. When you are prepared for the worst cases, you will be able to recover from the disaster quickly.
Make the process of backup as job in you server. In article about Exporting and Importing databases in MySQL, it was explained about the process about backup and restore databases.
4 – Remove “test” database
In the default install, MySQL comes with “test” database that can be accessed by anonymous user. Any useless database can be removed to avoid unexpected open accesses to your database. Therefore, in MySQL console:
> DROP DATABASE test;
5 – Remove “anonymous” user
Another item in default install is the anonymous user. Its objective is to have a user and a database to test how MySQL works. As root user, if you are using a default install, an attacker can user anonymous user to gain access to your system. To remove it, go to MySQL Console:
> DROP USER ''; > FLUSH PRIVILEGES;
Yes, anonymous user doesn’t have a username. Don’t forget the important rule: give access to users to do only what they are going to do!
6 – Lower database privileges for users
This is a completion of Good Practicie #1 and #2. Besides removing anonymous user and renaming root user, you must not give full access to any user! Some applications connect to MySQL by a user/password in a specific database table, so why are you giving full access to this user?
If an attacker gets this user with full access, he will have all your database!
How can I see the permissions of a user? Use the command SHOW GRANT in MySQL Console:
> SHOW GRANTS FOR 'user'@'localhost';
To define which access a user must have, use GRANT command. In the example below, user1 only can select from table billing in ecommerce database:
> GRANT SELECT ON billing.ecommerce TO 'user1'@'localhost'; > FLUSH PRIVILEGES;
Now user1 only can select in billing.ecommerce, avoid this user change any data in this table and other tables in the database. For more details see GRANT syntax.
On the other side, when your must remove access from a user, use the command REVOKE that is analogous to GRANT:
> REVOKE SELECT ON billing.ecommerce FROM 'user1'@'localhost'; > FLUSH PRIVILEGES;
7 – Remove and disable .mysql_history history file
When your user access MySQL Console, all commands history are logged in ~/.mysql_history . If an attacker has access to this file, he can understand how you database is structured. Do you doubt it?
$ cat ~/.mysql_history
In order to remove and definitely disable it, first the log generated have to be sent to /dev/null. The configuration of this log in in the environment variable MYSQL_HISTFILE:
$ export MYSQL_HISTFILE=/dev/null
Now, all log file must be directed to /dev/null. Remove the .mysql_history from your home folder:
$ rm ~/.mysql_history
And create a symbolic link do /dev/null. If you don’t know how Symbolic links work, I recommend you see the article about Hard Links and Symbolic Links
$ ln -s /dev/null ~/.mysql_history
Now anything you type in MySQL Console will be logged againd.
8 – Security Patching
When somebody discovers a vulnerability in software, a range of versions are affected. When this happens, the community or the company who maintains the software analyses and distributes a patch with the correction and keep it safe again.
Always keep your database updated to latest version. An attacker could use the known vulnerabilities in last version to gain access to your database.
9 – Disable LOCAL INFILE command
This command LOAD DATA LOCAL INFILE allows to read a file from file system and displays in the screen or save in a database. This is a quite dangerous command if the attacker could find a SQL Injection from a vulnerable application, he can access any file from your file system. Let’s reproduce from MySQL Console:
> SELECT LOAD_FILE("/etc/passwd");
The list of all your users is displayed? Creepy, isn’t it? The best solution is disable it in MySQL configurations.
Go to “/etc/my.cnf” in CentOS or “/etc/mysql/my.cnf” in Ubuntu and add the line in [mysqld] section:
10 – Protect your server and lower system privileges
If you are using a Windows system, make sure that it is protected with a antivirus software, install firewall. Even in Linux, keep the system safe by secure password for users and limiting user access to only what he needs to see.
Bonus – MySQL Secure Installation:
Most for the tips presented here are applied using MySQL Secure Installation, it comes with MySQL default packages and provides a secure configuration in your MySQL database.
$ sudo /usr/bin/mysql_secure_installation
The script options:
- Sets Root password (Pratice #1)
- Removes Anonymous user (Pratice #5)
- Disallows remote root login (Pratice #2)
- Drops test database (Pratice #4)
Setting these options you may have a secure MySQL Instance and you are protected against 90% of simple attacks.
NewsletterReceive our posts via e-mail:
Devops – Facebook