Posted on Sunday March 13, 2016 by Eric Potvin
Hardening MySQL does not only apply to back-end configuration. Before, we proceed to the hardening process, here's some important principles you need to know first.
- Sanitise user/form data
- Parameterize queries
- Like any software keeping up to date is important
- Understand password encryption. Salt passwords. Don't use md5 nor sha1
- Having a policy to change passwords periodically is a good idea.
- Always start with no privileges and add only the ones they need to preform their role.
Application Specific Users
It is recommended to isolated your MySQL users to their own databases. Each application that uses MySQL should have its own users with their limited privileges.
For example, you have a database called
We need to create a new that only belongs to this database. We can create the user using the
CREATE USER command:
CREATE USER 'myuser'@'localhost' IDENTIFIED BY 'mypassword';
Now, you need to setup the permissions (grant privileges) for this user on the
GRANT SELECT,UPDATE,DELETE ON mydb.* TO 'myuser'@'localhost';
If you need to remove privileges (revoke), you can use this command:
REVOKE UPDATE ON mydb.* FROM 'myuser'@'localhost';
If you need to grant all privileges, you can use this command:
GRANT ALLUPDATE ON mydb.* FROM 'myuser'@'localhost';
When done, you must run the
flush privileges command to make sure all privileges are applied. This command is used everytime you grant or revoke privileges.
Change Root Username
The default user when installing MySQL is
root. This user has all privileges on everything. It is the easiest way for a hacker to login with this username and do some damage and steal information from your database.
To make this a little harder for them, you can rename the
root user to something else using a complex password.
To rename the
root user, use the
RENAME USER command.
USE mysql UPDATE mysql.user set user = 'myuser' where user = 'root';
Then, change his password:
SET PASSWORD FOR 'my_new_user'@'%hostname' = PASSWORD('new_pass');
Don't forget to flush privileges:
Make sure you erase these changes in your
~/.mysql_history. This can be very easy from anyone who has access to figure out the new username and password.
Disable LOCAL INFILE
LOCAL INFILE needs to be disable for security reason. It allow MySQL to read files form the local file system. This
technique can be used by hackers in application to get local information like username and passwords.
Example, let's read the
LOCAL INFILE, edit the
sudo vi /etc/mysql/my.cnf
And change the value of the
Disable Remote Access
Remote access needs to be disable if your don't need to access your MySQL database remotely. This will reduce the risk of an attack by forcing all MySQL connections to be allowed locally.
To restrict MySQL from opening a network socket, you need to add the following parameter in the
[mysqld] section of the
Lower system privileges
To protect your MySQL database, make sure that the MySQL file directory is owned by the
mysql user and the
sudo ls -l /var/lib/mysql
Also, make sure only the
mysql and/or root users have access to the directory
The mysql binaries files should be only owned by
mysql or root
sudo ls -l /usr/bin/my*
Lower database privileges
A privileges you need to be aware of is
SHOW DATABASES. By default, this command can be used by everyone. It will show them all databases in your server.
To disable this command, you need to enter the following parameter in your
/etc/my.cnf under the
Secure MySQL installation
mysql_secure_installation will allow you secure the MySQL installation.
It will cover the follwing topic:
- Change the root password
- Remove anonymous users
- Disallow root login remotely
- Remove test database and access to it
- Reload privilege tables now
You should answer "Y" (for yes) to all of the remaining questions
Modify the permission of the config file
The configuration files should always only be owned by root. To change the permissions you can use the following commands:
sudo chown -R root:root /etc/mysql/ sudo chmod 0644 /etc/mysql/my.cnf