Monday 2 February 2015

MySQL-5.7.5-m15 - out of the box security improvements

In this article I would understand out of the box security improvement available in MySQL 5.7.5 release (not production ready yet). I am using Centos 6.5 and using RPM packages. 

Installing using RPMs

[root@localhost ~]# rpm -ivh mysql-community-server-5.7.5-0.6.m15.el6.i686.rpm mysql-community-client-5.7.5-0.6.m15.el6.i686.rpm mysql-community-libs-5.7.5-0.6.m15.el6.i686.rpm mysql-community-common-5.7.5-0.6.m15.el6.i686.rpm
Preparing...                ########################################### [100%]
   1:mysql-community-common ########################################### [ 25%]
   2:mysql-community-libs   ########################################### [ 50%]
   3:mysql-community-client ########################################### [ 75%]
   4:mysql-community-server ########################################### [100%]

Starting MySQL Server

[root@localhost ~]# /etc/init.d/mysqld start
Initializing MySQL database:                               [  OK  ]
Starting mysqld:                                           [  OK  ]

Securing the MySQL server deployment.

Connecting to MySQL server using password in '/root/.mysql_secret'

VALIDATE PASSWORD PLUGIN can be used to test passwords
and improve security. It checks the strength of password
and allows the users to set only those passwords which are
secure enough. Would you like to setup VALIDATE PASSWORD plugin?

Press y|Y for Yes, any other key for No:  y

There are three levels of password validation policy:

LOW    Length >= 8
MEDIUM Length >= 8, numeric, mixed case, and special characters
STRONG Length >= 8, numeric, mixed case, special characters and dictionary                  file

Please enter 0 = LOW, 1 = MEDIUM and 2 = STRONG:  2
By default, a MySQL installation has an anonymous user,
allowing anyone to log into MySQL without having to have
a user account created for them. This is intended only for
testing, and to make the installation go a bit smoother.
You should remove them before moving into a production
environment.

Remove anonymous users? (Press y|Y for Yes, any other key for No) :  y
Success.


Normally, root should only be allowed to connect from
'localhost'. This ensures that someone cannot guess at
the root password from the network.

Disallow root login remotely? (Press y|Y for Yes, any other key for No) :  y
Success.

By default, MySQL comes with a database named 'test' that
anyone can access. This is also intended only for testing,
and should be removed before moving into a production
environment.


Remove test database and access to it? (Press y|Y for Yes, any other key for No) :  y
 - Dropping test database...
Success.

 - Removing privileges on test database...
Success.

Reloading the privilege tables will ensure that all changes
made so far will take effect immediately.

Reload privilege tables now? (Press y|Y for Yes, any other key for No) :  y
Success.

All done!

-------------------------------------------------------------------------------------------------------

A very long output but easy to follow, many improvements:

* Password validation plugin is by default loaded and the password validation policy is set to 'STRONG'.
*  Installation removes test database and anonymous user accounts.
*  Installation creates only one 'root' account and automatically generates a password for this account..
* Installation automatically remove permissions on non-existing test% databases.

To  login to the MySQL server, I would need to use random password generated by the installation that is located in /root/.mysql_secret and select a new password using SET PASSWORD() command.


SQL MODE Changes


Strict SQL mode for transactional storage engines (STRICT_TRANS_TABLES) is now enabled by default. An error occurs for invalid or missing values in a data-change statement. The statement is aborted and rolled back. For example, if you wish to insert a value that exceeds the specified column width i.e. VARCHAR(20)

mysql> show create table t\G
*************************** 1. row ***************************
       Table: t
Create Table: CREATE TABLE `t` (
  `a` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `b` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`a`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8
1 row in set (0.08 sec)

mysql> insert into t values (111, repeat('a',21));

ERROR 1406 (22001): Data too long for column 'b' at row 1


Password Expiration Policy

User accounts would automatically expire after 360 days! this is great improvement. As you would want (wish) some of the accounts to definitely expire. At the same time you can disable password expiration for some of the accounts.  You can read MySQL manuals to learn more about this feature here

MySQL OLD Passwords

The mysql_old_password authentication plugin is removed. Accounts that use this plugin are disabled at startup and the server writes an “unknown plugin” message to the error log. For instructions on upgrading accounts that use this plugin, see