MySQL Bugs: #71359: Validate and SHA256 plugins do not work together

Bug #71359	Validate and SHA256 plugins do not work together
Submitted:	13 Jan 2014 9:58	Modified:	24 Feb 2015 22:38
Reporter:	Daniël van Eeden (OCA)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Pluggable Authentication	Severity:	S2 (Serious)
Version:	5.6.14, 5.6.17	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	password, plugin, SHA256, Validation

Description:
When a minimum password length of 8 characters is set it is still possible to create an account without password.

How to repeat:
mysql> SHOW GLOBAL VARIABLES LIKE 'validate\_password\_%';
+--------------------------------------+--------+
| Variable_name                        | Value  |
+--------------------------------------+--------+
| validate_password_dictionary_file    |        |
| validate_password_length             | 8      |
| validate_password_mixed_case_count   | 1      |
| validate_password_number_count       | 1      |
| validate_password_policy             | MEDIUM |
| validate_password_special_char_count | 1      |
+--------------------------------------+--------+
6 rows in set (0.00 sec)

mysql> SET old_passwords=2;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE USER 'sha256val'@'%' IDENTIFIED WITH sha256_password;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT VERSION();
+-------------------------------------------+
| VERSION()                                 |
+-------------------------------------------+
| 5.6.14-enterprise-commercial-advanced-log |
+-------------------------------------------+
1 row in set (0.00 sec)

Now "mysql -u sha256val" gives a connection.

Some more details:
mysql> CREATE USER 'sha256val2'@'%' IDENTIFIED WITH sha256_password AS 'foo';
Query OK, 0 rows affected (0.01 sec)

mysql> CREATE USER 'normval'@'%';
ERROR 1819 (HY000): Your password does not satisfy the current policy requirements
mysql> CREATE USER 'normval'@'%' IDENTIFIED BY 'foo';
ERROR 1819 (HY000): Your password does not satisfy the current policy requirements

Suggested fix:
Make sure the password validation plugin works with the sha256_password plugin.

This is only for CREATE USER...IDENTIFIED WITH
The SET PASSWORD does work correctly.
mysql> SET PASSWORD FOR 'sha256val2'@'%'=PASSWORD('fooo');
ERROR 1819 (HY000): Your password does not satisfy the current policy requirements

Thank you for the report.

Verified as described.

Added tags

Posted by developer:
 
The fundamental problem is that there is no support in 5.5 or 5.6 for a single SQL statement which does all three of the following:

1.  Creates a new user account
2.  Specifies a non-default authentication plugin
3.  Specifies a password

Example:

mysql> CREATE USER 'sha256val2'@'%' IDENTIFIED WITH sha256_password BY 'foo';
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near 'BY 'foo'' at line 1
mysql> 

(Note that the example ending with ... AS 'foo'; provided earlier simply sets the authentication_string, not password)

If committed to using sha256_password as the default authentication plugin, one can start mysqld with --default_authentication_plugin=sha256_password.  This eliminates the need to explicitly define the authentication plugin in the CREATE USER statement, allowing the following:

mysql> CREATE USER 'sha256val3'@'%' IDENTIFIED BY 'foo';
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT user, host, plugin, authentication_string, password
    -> FROM mysql.user
    -> WHERE user = 'sha256val3'\G
*************************** 1. row ***************************
                 user: sha256val3
                 host: %
               plugin: sha256_password
>B$iUhLBhP25cP9czoOjuT69kS.w2e/YFUIJvO7jWj4ylC
             password: 

Of course, this only works when the authentication plugin is defined as the default, so changing --default_authentication_plugin simply transfers the problem to a different plugin:

mysql> CREATE USER 'normal'@'%' IDENTIFIED WITH 'mysql_native_password' BY 'foo';
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near 'BY 'foo'' at line 1

mysql> CREATE USER 'normal'@'%' IDENTIFIED WITH 'mysql_native_password';
Query OK, 0 rows affected (0.00 sec) 

To properly fix this, SQL syntax support needs to be added allowing the combination of account creation, authentication plugin and password declaration in a single command.  Changing SQL syntax in a maintenance release is not recommended.

Noted in 5.7.6 changelog.

The validate_password plugin did not properly enforce password
constraints for accounts authenticated by the sha256_password
authentication plugin.