Security improvements in MySQL 5.7

If you have a look on the last mysql 5.7.4 version or later you will probably see that there are several security improvements. The list of added security features and improvements can be seen on the following page: http://dev.mysql.com/doc/refman/5.7/en/mysql-nutshell.html

There are three main improvements that are shortly described in this blog:

1. Nonempty plugin column
2. Password lifetime policy
3. mysql_install_db secured

Nonempty plugin column

As of MySQL 5.7.2, the server requires account rows in the mysql.user table to have a nonempty plugin column value and disables accounts with an empty value. The following error will occor when trying to connect with user having empty plugin colum:

2014-11-30T10:41:04.943384Z 2 [Note] Access denied for user 'sbtest'@'localhost' (using password: YES)ERROR 1045 (28000): Access denied for user 'sbtest'@'localhost' (using password: YES)

If the user is connected when you update the plugin column, MySQL will behave as described below:

1. The user connect to the database:

mysql -u sbtest -p --socket=/u00/app/mysql/admin/mysqld5/socket/mysqld5.sock 
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or g.
Your MySQL connection id is 9
Server version: 5.7.4-m14 MySQL Community Server (GPL)Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.Type 'help;' or 'h' for help. Type 'c' to clear the current input statement.
mysql>

2. Update column with root user:

mysql> update mysql.user set plugin='' where user='sbtest';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

3. With the root user you flush the privileges:

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

4. The following message will appear in the sbtest session:

2014-11-30T22:08:16.652477Z 8 [Warning] User entry 'sbtest'@'localhost' has an empty plugin value. The user will be ignored and no one can login with this user anymore.

Password Lifetime policy

Since mysql 5.7.4 MySQL enables database administrators to expire account passwords manually and to establish a policy for automatic password expiration. How does it work ?

Two new columns have been added to MySQL:

 
| password_last_changed | timestamp            | YES | | NULL |
| password_lifetime     | smallint(5) unsigned | YES | | NULL |

These two columns allow to see when password has been changed and to set a password lifetime.

You can establish a global password policy by setting the variable default_password_lifetime in the option file. By default this variable is set to 360. It means that all users will have to change their password once per year. A value of 0 disables automatic password expiration.

As stated in the documentation, the global password expliration policy can be overridden as desired for individual accounts using the ALTER USER statement.

Example:

mysql> ALTER USER 'sbtest'@'localhost' PASSWORD EXPIRE INTERVAL 90 DAY;
Query OK, 0 rows affected (0.00 sec)

A client session operates in restricted mode if the account password has been expired. In restricted mode, operations performed in the session result in an error until the user issues a SET PASSWORD statement to establish a new account password:

mysql> alter user 'sbtest'@'localhost' password expire interval 1 day;
[mysqld5] mysql -u sbtest -p 
mysql> show databases;
ERROR 1820 (HY000): You must SET PASSWORD before executing this statement
mysql> set password=password('sbtest');
Query OK, 0 rows affected (0.01 sec)
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| sysbench           |
+--------------------+
2 rows in set (0.00 sec)

To remove the password expiration policy simple use “expire never” as presented in the following example:

mysql>alter user 'sbtest'@'localhost' password expire never;

mysql_install_db secured

MySQL deployments installed using mysql_install_db now are secure by default. The following changes have been implemented as the default deployment characteristics:

The installation process creates only a single root account and not anymore anonymous-user accounts.

Example on MySQL 5.7.4:

mysql> select user,password, host from mysql.user;
+-------------+-------------------------------------------+-----------+
| user        | password                                  | host      |
+-------------+-------------------------------------------+-----------+
| root        | *7D2ABFF56C15D67445082FBB4ACD2DCD26C0ED57 | localhost |
+-------------+-------------------------------------------+-----------+
1 rows in set (0.00 sec)

Example on mysql 5.6.20:

mysql> select user,password, host from mysql.user;
+-------------+-------------------------------------------+----------------+
| user        | password                                  | host           |
+-------------+-------------------------------------------+----------------+
| root        | *7D2ABFF56C15D67445082FBB4ACD2DCD26C0ED57 | localhost      |
| root        |                                           | thinkpad-t540p |
| root        |                                           | 127.0.0.1      |
| root        |                                           | ::1            |
|             |                                           | localhost      |
|             |                                           | thinkpad-t540p |
+-------------+-------------------------------------------+----------------+
6 rows in set (0.01 sec)

After the installation you can also note that there is no “test” database anymore.

Example on MySQL 5.7.4:

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
+--------------------+
3 rows in set (0.00 sec)

Example on MySQL 5.6.20:

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| test               |
+--------------------+
4 rows in set (0.00 sec)

Conclusion

Oracle did some significative security improvements on MySQL 5.7 version. This improvements will help database administrators to deploy MySQL with a better security level than with previous versions. Some improvements have also been done on the mysql_secure_installation script. The list of improvement and additional features can be seen on the following URL: http://dev.mysql.com/doc/refman/5.7/en/mysql-secure-installation.html