MySQL - Secure MySQL

Run the script called “mysql_secure_installation”. This will guide us through some procedures that will remove some defaults that are dangerous to use in a production environment.

sudo mysql_secure_installation

It will first prompt you for the root password you set up during installation. Immediately following, you will be asked a series of questions, beginning with if you'd like to change the root password.

This is another opportunity to change your password to something secure if you have not done so already.

You should answer “Y” (for yes) to all of the remaining questions.

This will remove the ability for anyone to log into MySQL by default, disable logging in remotely with the administrator account, remove some test databases that are insecure, and update the running MySQL instance to reflect these changes.

Unless you have reason to connect to MySQL from an external system, lock it down to only accepts connections from the local machine.

/etc/mysql/my.cnf

bind-address = 127.0.0.1

MySQL is able to access the underlying filesystem. To prevent this, modify the configuration file by adding in this directive.

/etc/mysql/my.cnf

local-infile=0

Set the log variable within the “[mysqld]” section of the configuration file.

/etc/mysql/my.cnf

log=/var/log/mysql-logfile

To ensure that they are not world readable:

sudo ls -l /var/log/mysql*
-rw-r----- 1 mysql adm    0 Jul 23 18:06 /var/log/mysql.err
-rw-r----- 1 mysql adm    0 Jul 23 18:06 /var/log/mysql.log
 
/var/log/mysql:
total 28
-rw-rw---- 1 mysql adm 20694 Jul 23 19:17 error.log

Ensure there are no users without a password:

SELECT User,Host,Password FROM mysql.user;
+------------------+-----------+-------------------------------------------+
| user             | host      | password                                  |
+------------------+-----------+-------------------------------------------+
| root             | localhost | *DE06E242B88EFB1FE4B5083587C260BACB2A6158 |
| demo-user        | %         |                                           |
| root             | 127.0.0.1 | *DE06E242B88EFB1FE4B5083587C260BACB2A6158 |
| root             | ::1       | *DE06E242B88EFB1FE4B5083587C260BACB2A6158 |
| debian-sys-maint | localhost | *ECE81E38F064E50419F3074004A8352B6A683390 |
+------------------+-----------+-------------------------------------------+
5 rows in set (0.00 sec)

As can be seen, in our example set up, the user “demo-user” has no password. This is very insecure.

Set a password for the user with this command:

UPDATE mysql.user SET Password=PASSWORD('newPassWord') WHERE User="demo-user";
FLUSH PRIVILEGES;

Ensure there are no users without a host association:

SELECT User,Host,Password FROM mysql.user;
+------------------+-----------+-------------------------------------------+
| user             | host      | password                                  |
+------------------+-----------+-------------------------------------------+
| root             | localhost | *DE06E242B88EFB1FE4B5083587C260BACB2A6158 |
| demo-user        | %         |                                           |
| root             | 127.0.0.1 | *DE06E242B88EFB1FE4B5083587C260BACB2A6158 |
| root             | ::1       | *DE06E242B88EFB1FE4B5083587C260BACB2A6158 |
| debian-sys-maint | localhost | *ECE81E38F064E50419F3074004A8352B6A683390 |
+------------------+-----------+-------------------------------------------+
5 rows in set (0.00 sec)

As can be seen, in our example set up, the user “demo-user” is valid regardless of what host they are on. The “%” for the host is a wildcard that means any host. This is very insecure.

Change the host to be “localhost”:

UPDATE mysql.user SET Host='localhost' WHERE User="demo-user";
FLUSH PRIVILEGES;

The “mysql_secure_installation” script usually removes blank users but best to check:

SELECT User,Host,Password FROM mysql.user;

If our table contains any blank users remove them.

DELETE FROM mysql.user WHERE User="";
FLUSH PRIVILEGES;

Each application that uses MySQL should have its own user that only has limited privileges and only has access to the databases it needs to run.

To create a new user, use the following command:

CREATE USER 'demo-user'@'localhost' IDENTIFIED BY 'password';

Grant the new user privileges on the table they can access:

GRANT SELECT,UPDATE,DELETE ON testDB.* TO 'demo-user'@'localhost';

Revoke privileges is required:

REVOKE UPDATE ON testDB.* FROM 'demo-user'@'localhost';

If we need all privileges on a certain database, we can specify that with the following:

GRANT ALL ON testDB.* TO 'demo-user'@'localhost';

Flush and check:

FLUSH PRIVILEGES;
show grants for 'demo-user'@'localhost';

Displays

+------------------------------------------------------------------------------------------------------------------+
| Grants for demo-user@localhost                                                                                   |
+------------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'demo-user'@'localhost' IDENTIFIED BY PASSWORD '*2470C0C06DEE42FD1618BB99005ADCA2EC9D1E19' |
| GRANT SELECT, UPDATE, DELETE ON `testDB`.* TO 'demo-user'@'localhost'                                            |
+------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

Change the root login name might prevent an attacker trying to access the root MySQL login.

The root login can be changed with the following command:

rename user 'root'@'localhost' to 'newAdminUser'@'localhost';
FLUSH PRIVILEGES;

We can see the change by using the same query we've been using for the User database:

<code mysql> select user,host,password from mysql.user; </mysql>