Bug #71475 Server fails to disable accounts with conflicting password/plugin
Submitted: 24 Jan 2014 21:46 Modified: 27 Jan 2014 7:55
Reporter: Paul Dubois Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Pluggable Authentication Severity:S3 (Non-critical)
Version:5.7 OS:Any
Assigned to: CPU Architecture:Any

[24 Jan 2014 21:46] Paul Dubois
Description:
Harin tells me that in 5.7, when the server reloads the privilege
tables, it should refuse to load an account for which the password
hash format and the authentication plugin are inconsistent.

However, the server does not do so, at least not in the cases
described here.

1) Create an account that has mysql_native_password plugin and
old-format password hash.
2) Issue SELECT to verify that plugin and password hash are inconsistent.
3) Issue FLUSH PRIVILEGES.  This should result in the server refusing to
load the 'x'@'localhost' account and write a warning to the error log.

Then do the same thing, but for an account that has mysql_old_password
plugin and new-format password hash.

Examination of the error log after running the test script shows that the
server writes no warning for either account when privileges are reloaded.

How to repeat:
Test script:

-- Create an account that mysql_native_password and old-format password hash
CREATE USER 'x'@'localhost' IDENTIFIED WITH 'mysql_native_password';
SET old_passwords = 1;
UPDATE mysql.user SET Password = PASSWORD('x')
WHERE (User, Host) = ('x', 'localhost');
SELECT User, Host, plugin, Password
FROM mysql.user WHERE (User, Host) = ('x', 'localhost')\G
FLUSH PRIVILEGES;
DROP USER 'x'@'localhost';

-- Create an account that mysql_old_password and new-format password hash
CREATE USER 'x'@'localhost' IDENTIFIED WITH 'mysql_old_password';
SET old_passwords = 0;
UPDATE mysql.user SET Password = PASSWORD('x')
WHERE (User, Host) = ('x', 'localhost');
SELECT User, Host, plugin, Password
FROM mysql.user WHERE (User, Host) = ('x', 'localhost')\G
FLUSH PRIVILEGES;
DROP USER 'x'@'localhost';

Result of running script:

mysql> -- Create an account that mysql_native_password and old-format password hash
mysql> CREATE USER 'x'@'localhost' IDENTIFIED WITH 'mysql_native_password';
Query OK, 0 rows affected (0.00 sec)

mysql> SET old_passwords = 1;
Query OK, 0 rows affected (0.00 sec)

mysql> UPDATE mysql.user SET Password = PASSWORD('x')
    -> WHERE (User, Host) = ('x', 'localhost');
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> SELECT User, Host, plugin, Password
    -> FROM mysql.user WHERE (User, Host) = ('x', 'localhost')\G
*************************** 1. row ***************************
    User: x
    Host: localhost
  plugin: mysql_native_password
Password: 606706156665cd86
1 row in set (0.00 sec)

mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)

mysql> DROP USER 'x'@'localhost';
Query OK, 0 rows affected (0.00 sec)

mysql> -- Create an account that mysql_old_password and new-format password hash 
mysql> CREATE USER 'x'@'localhost' IDENTIFIED WITH 'mysql_old_password';
Query OK, 0 rows affected (0.00 sec)

mysql> SET old_passwords = 0;
Query OK, 0 rows affected (0.01 sec)

mysql> UPDATE mysql.user SET Password = PASSWORD('x')
    -> WHERE (User, Host) = ('x', 'localhost');
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> SELECT User, Host, plugin, Password
    -> FROM mysql.user WHERE (User, Host) = ('x', 'localhost')\G
*************************** 1. row ***************************
    User: x
    Host: localhost
  plugin: mysql_old_password
Password: *B69027D44F6E5EDC07F1AEAD1477967B16F28227
1 row in set (0.01 sec)

mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)

mysql> DROP USER 'x'@'localhost';
Query OK, 0 rows affected (0.00 sec)
[27 Jan 2014 7:55] Umesh Shastry
Hello Paul,

Thank you for the bug report and test case.
Verified as described on 5.7.3.

Thanks,
Umesh