Bug #83174 set password with hash_string does not work as described
Submitted: 27 Sep 2016 13:53 Modified: 28 Sep 2016 13:12
Reporter: Oli Sennhauser Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Security: Privileges Severity:S3 (Non-critical)
Version:5.7.12, 5.7.15 OS:Linux (na)
Assigned to: CPU Architecture:Any
Tags: hash_string, set password, Validation

[27 Sep 2016 13:53] Oli Sennhauser
Description:
SET PASSWORD command with hash_string does not work as described and expected.

How to repeat:
https://dev.mysql.com/doc/refman/5.6/en/validate-password-plugin.html

"Passwords specified as hashed values are not checked because the original password value is not available:"

mysql> SET PASSWORD = '*0D3CED9BEC10A777AEC23CCC353A8C08A633045E';

This is still a valid MySQL 5.7 command!

mysql> show plugins ;
+----------------------------+----------+--------------------+----------------------+---------+
| Name                       | Status   | Type               | Library              | License |
+----------------------------+----------+--------------------+----------------------+---------+
...
| validate_password          | ACTIVE   | VALIDATE PASSWORD  | validate_password.so | GPL     |
+----------------------------+----------+--------------------+----------------------+---------+
47 rows in set (0.00 sec)

mysql> show global variables like 'validate_pass%';
+--------------------------------------+--------+
| 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 password for 'dummy'@'%' = '0D3CED9BEC10A777AEC23CCC353A8C08A633045E';
ERROR 1819 (HY000): Your password does not satisfy the current policy requirements root@localhost

mysql> set global validate_password_policy = 0;
Query OK, 0 rows affected (0.00 sec)

mysql> set password for 'dummy'@'%' = '0D3CED9BEC10A777AEC23CCC353A8C08A633045E';
Query OK, 0 rows affected (0.00 sec)

Suggested fix:
set password = hash_value should bypass password validation rules!
[28 Sep 2016 13:12] Umesh Shastry
Hello Oli,

Thank you for the report.

Thanks,
Umesh