Bug #94440 some function problem while create an user
Submitted: 22 Feb 2019 9:08 Modified: 14 May 2019 17:25
Reporter: JianJun Shi Email Updates:
Status: Closed Impact on me:
Category:MySQL Server: Security: Privileges Severity:S3 (Non-critical)
Version:5.7.23 OS:Linux
Assigned to: CPU Architecture:Any

[22 Feb 2019 9:08] JianJun Shi
" CREATE USER Syntax" chapter in the "MySQL 5.7 Reference Manual" was describe as following:

auth_option: {
IDENTIFIED BY 'auth_string'
| IDENTIFIED WITH auth_plugin
| IDENTIFIED WITH auth_plugin BY 'auth_string'
| IDENTIFIED WITH auth_plugin AS 'hash_string'

It mains, if we would create an user with 'IDENTIFIED WITH auth_plugin AS' syntax. We need to provide hash_string, instead of auth_string. Otherwise, we will get an error message.

Actually, 5.7.23 version go against this rule. It is in contrast to 8.0.13 version, the latter accord with this rule.

How to repeat:
mysql> select version();
| version() |
| 5.7.23    |
1 row in set (0.00 sec)

mysql> create user 'REVOKE_MANUAL_131'@'%' IDENTIFIED WITH mysql_native_password AS '123456';
Query OK, 0 rows affected (0.00 sec)


mysql> select version();
| version() |
| 8.0.13    |
1 row in set (0.00 sec)

mysql> create user 'REVOKE_MANUAL_131'@'%' IDENTIFIED WITH mysql_native_password AS '123456';
ERROR 1827 (HY000): The password hash doesn't have the expected format. Check if the correct password algorithm is being used with the PASSWORD() function.
[22 Feb 2019 13:40] MySQL Verification Team

Thank you for your bug report.

You are quite correct in your findings. Only, the error is not in the code, but in the documentation, which needs to be corrected.

Thank you for your contribution.
[24 Apr 2019 13:44] MySQL Verification Team
The category of this bug is changed.
[14 May 2019 17:25] Paul DuBois
Posted by developer:
Fixed in 5.7.27.

CREATE USER and ALTER USER did not check the validity of a hashed
authentication string when used with IDENTIFIED WITH auth_plugin AS
'hash_string' syntax.
[15 May 2019 12:46] MySQL Verification Team
Thank you, Paul .....
[29 Jul 2019 6:35] Erlend Dahl
Bug#93395 ALTER USER succeeds on master but fails on slave.

was marked as a duplicate.
[29 Jul 2019 12:14] MySQL Verification Team
Thank you, Erlend ......