Mysql

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.

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 mydb.

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 mydb database.

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.

FLUSH 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:

FLUSH PRIVILEGES;
IMPORTANT:

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

The 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 /etc/passwd:

SELECT load_file("/etc/passwd");

To disable LOCAL INFILE, edit the my.cnf:p>

sudo vi /etc/mysql/my.cnf

And change the value of the local-infile to 0.

[mysqld]
local-infile=0

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 /etc/mysql/my.cnf.

bind-address=127.0.0.1

Lower Privileges

Lower system privileges

To protect your MySQL database, make sure that the MySQL file directory is owned by the mysql user and the mysql group.

sudo ls -l /var/lib/mysql

Also, make sure only the mysql and/or root users have access to the directory /var/lib/mysql.

The mysql binaries files should be only owned by mysql or root users.

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 [mysqld].

[mysqld]
skip-show-database

Secure MySQL installation

The mysql_secure_installation will allow you secure the MySQL installation.

It will cover the follwing topic:

mysql_secure_installation

You should answer "Y" (for yes) to all of the remaining questions

Secure my.cnf

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