MySQL Bugs: #79622: Create user ... identified by --- is placing password hash into Plugin column

Bug #79622	Create user ... identified by --- is placing password hash into Plugin column
Submitted:	13 Dec 2015 19:57	Modified:	24 Dec 2015 10:13
Reporter:	Seth Willits	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Security: Privileges	Severity:	S2 (Serious)
Version:	5.7.9	OS:	MacOS
Assigned to:		CPU Architecture:	Any

Description:
When creating a user using the IDENTIFIED BY syntax, MySQL is placing the password hash into the Plugin column and leaving the Password column empty. This is completely wrong and means it's impossible for the user to login.

This is 100% reproducible for me.

What I expect is that the password hash should be placed into the Password column, and the Plugin column should contain "mysql_native_password"

How to repeat:
mysql> create user 'bob'@'%' identified by 'password';
Query OK, 0 rows affected (0.01 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)

mysql> select user, password, plugin from mysql.user where user = 'bob';
+------+----------+-------------------------------------------+
| user | password | plugin                                    |
+------+----------+-------------------------------------------+
| bob  |          | *2470C0C06DEE42FD1618BB99005ADCA2EC9D1E19 |
+------+----------+-------------------------------------------+
1 row in set (0.00 sec)

mysql> select password('password');
+-------------------------------------------+
| password('password')                      |
+-------------------------------------------+
| *2470C0C06DEE42FD1618BB99005ADCA2EC9D1E19 |
+-------------------------------------------+
1 row in set, 1 warning (0.00 sec)

Note that the Plugin column value is the hash of "password".

You can see that MySQL thinks this hash really is the Plugin value not only when trying to log in, but also when attempting to change the password:

mysql> set password for 'bob'@'%' = password('different');
ERROR 1524 (HY000): Plugin '*2470C0C06DEE42FD1618BB99005ADCA2EC9D1E19' is not loaded
mysql> select user, password, plugin from mysql.user where user = 'bob';
+------+----------+-------------------------------------------+
| user | password | plugin                                    |
+------+----------+-------------------------------------------+
| bob  |          | *2470C0C06DEE42FD1618BB99005ADCA2EC9D1E19 |
+------+----------+-------------------------------------------+
1 row in set (0.00 sec)

This happens even when using the long form explicitly specifying the plugin:

mysql> create user 'bob'@'%' identified with 'mysql_native_password' by 'password';
Query OK, 0 rows affected (0.02 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)

mysql> select user, password, plugin from mysql.user where user = 'bob';
+------+----------+-------------------------------------------+
| user | password | plugin                                    |
+------+----------+-------------------------------------------+
| bob  |          | *2470C0C06DEE42FD1618BB99005ADCA2EC9D1E19 |
+------+----------+-------------------------------------------+
1 row in set (0.00 sec)

mysql>

Some additional information which may be helpful:

If I move the Plugin column value (the password hash) into Password, and set Plugin to mysql_native_password, the user can log in correctly.

However, even if logged in as root, if I try to grant any privileges to that user, MySQL says it can't find it:

mysql> grant select on `test`.* TO 'bob'@'%';
ERROR 1133 (42000): Can't find any matching row in the user table

mysql> select user, password, plugin from mysql.user where user = 'bob';
+------+-------------------------------------------+-----------------------+
| user | password                                  | plugin                |
+------+-------------------------------------------+-----------------------+
| bob  | *2470C0C06DEE42FD1618BB99005ADCA2EC9D1E19 | mysql_native_password |
+------+-------------------------------------------+-----------------------+
1 row in set (0.00 sec)

mysql>

Finally, I should also note that modifying the grants of users which were created in versions prior to 5.7.9 (probably in either 5.5 or maybe 5.6), work fine.

Looks like you're using an old mysql.user table.
There is no "password" column on 5.7.   So,  please run mysql_upgrade to correct schema.

Hmm. I'm absolutely 100% certain I did do mysql_upgrade when I upgraded to 5.7.9 from 5.6.23, and to the best of my recollection there were no errors at all, but it was a massive struggle for some reason to get to that point. (This was months ago.)

Running mysql_upgrade again now shows that every single table, including mysql.user is "OK", but it did decide to upgrade the schema:

$ sudo mysql_upgrade -u root
Checking if update is needed.
Checking server version.
Running queries to upgrade MySQL server.
Checking system database.
mysql.columns_priv                                 OK
...
mysql.user                                         OK
mysql.user_info                                    OK
The sys schema is already up to date (version 1.5.0).
Found 0 sys functions, but expected 21. Re-installing the sys schema.
Upgrading the sys schema.
Checking databases.
allofmydbs.andtables                               OK
...
Upgrade process completed successfully.
Checking if update is needed.
$ 

After doing that, mysql.user has no password column and creating a user works as expected. So something must have gone wrong the first time I did the upgrade and went unnoticed. My mistake? It seems MySQL could step up and spew out a useful error that the schema is out of date though. 

Thanks.

Thank you for the report.
Please note that the authentication_string column in the mysql.user table now stores credential information for all accounts. The Password column, previously used to store password hash values for accounts authenticated with the mysql_native_password and mysql_old_password plugins, is removed.

If you upgrade to 5.7 MySQL release from an earlier version, you must run mysql_upgrade (and restart the server) to incorporate the changes to the mysql database. mysql_upgrade moves Password column values to the authentication_string column and removes the Password column.

Bug #82465 marked as duplicate of this