| Bug #78033 | CREATE USER accepts plugin and password hash, but ignores the password hash | ||
|---|---|---|---|
| Submitted: | 12 Aug 2015 2:39 | Modified: | 20 Apr 2016 15:07 |
| Reporter: | Jesper wisborg Krogh | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: Security: Privileges | Severity: | S2 (Serious) |
| Version: | 5.5.8 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
[20 Apr 2016 15:07]
Paul DuBois
Posted by developer: Noted in 5.6.31 changelog. Using CREATE USER to create an account with the mysql_native_password or mysql_old_password authentication plugin and using a clause of the form IDENTIFIED WITH plugin AS 'hash_string' caused the account to be created without a password.
[12 Jun 2017 21:11]
Szymon Komendera
I found the associated commit: https://github.com/mysql/mysql-server/commit/8a2aefcd72d72b9c6a9ecd5d3aa75da7d746e761 The fix is: - If validate_password plugin is active, throw an error on CREATE. - If validate_password plugin is not active, retain old behavior. Note that: - The bug is not actually fixed since the CREATE command still doesn't do what is expected, and the error message isn't helpful because it suggests an issue with the password, while the real issue is different. - The fix depends on plugin availability i.e. most users will still see the incorrect behavior. Fundamentally, this is still a bug because the engine does not behave consistently end-to-end i.e. uses different fields (password vs authentication_string) when creating an user versus when the user is authenticated.
[12 Jun 2017 22:01]
Szymon Komendera
I created a new bug report https://bugs.mysql.com/bug.php?id=86664 as a follow-up to this.
Description: CREATE USER in 5.5 and later supports the syntax: CREATE USER user_specification IDENTIFIED WITH auth_plugin AS 'hash_string' However in MySQL 5.5 and 5.6 the hash_string is ignored and the user created without a password. Issue verified in 5.5.8, 5.5.44, 5.6.10, and 5.6.26. Works in 5.7.8 and 5.7.9. How to repeat: mysql> SELECT PASSWORD('my_n0t_v3ry_secur3_passw0rd'); +-------------------------------------------+ | PASSWORD('my_n0t_v3ry_secur3_passw0rd') | +-------------------------------------------+ | *FBE2E24FCEAC1399CF7E7B60E2E723AF45F25D44 | +-------------------------------------------+ 1 row in set (0.00 sec) -- Notice no warnings when creating the user mysql> CREATE USER 'myuser'@'localhost' IDENTIFIED WITH mysql_native_password AS '*FBE2E24FCEAC1399CF7E7B60E2E723AF45F25D44'; Query OK, 0 rows affected (0.07 sec) mysql> SELECT User, Host, Plugin, Password FROM mysql.user WHERE User = 'myuser' AND host = 'localhost'; +--------+-----------+-----------------------+----------+ | User | Host | Plugin | Password | +--------+-----------+-----------------------+----------+ | myuser | localhost | mysql_native_password | | +--------+-----------+-----------------------+----------+ 1 row in set (0.00 sec) Suggested fix: When the password hash is provided, the password should be set.