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)
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)