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:Mac OS X
Assigned to: CPU Architecture:Any

[13 Dec 2015 19:57] Seth Willits
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>
[13 Dec 2015 20:07] Seth Willits
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>
[13 Dec 2015 20:10] Seth Willits
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.
[14 Dec 2015 7:16] Shane Bester
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.
[14 Dec 2015 18:33] Seth Willits
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.
[24 Dec 2015 10:13] Umesh Shastry
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.
[5 Aug 2016 5:20] Umesh Shastry
Bug #82465 marked as duplicate of this