Description:
The command adds a password history entry even when the user already exists and the password is not altered. A subsequent ALTER USER fails due to the password history policy.
How to repeat:
mysql [localhost:40019] {msandbox} ((none)) > select @@version ;
+-----------+
| @@version |
+-----------+
| 8.0.31 |
+-----------+
1 row in set (0.00 sec)
mysql [localhost:40019] {msandbox} ((none)) > select @@password_history ;
+--------------------+
| @@password_history |
+--------------------+
| 5 |
+--------------------+
1 row in set (0.00 sec)
mysql [localhost:40019] {msandbox} ((none)) > CREATE USER IF NOT EXISTS 'test'@'%' IDENTIFIED BY 'password';
Query OK, 0 rows affected (0.01 sec)
mysql [localhost:40019] {msandbox} ((none)) > select * from mysql.password_history where user = 'test' ;
+------+------+----------------------------+------------------------------------------------------------------------+
| Host | User | Password_timestamp | Password |
+------+------+----------------------------+------------------------------------------------------------------------+
| % | test | 2022-12-16 20:00:19.841037 | $A$005${"I@'~lw<a{C3M_E!9oPkPR8pZWxeEIkeigZ3ihpSRhoQDVu21Jr2BkKav48 |
+------+------+----------------------------+------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql [localhost:40019] {msandbox} ((none)) > CREATE USER IF NOT EXISTS 'test'@'%' IDENTIFIED BY 'newpassword';
Query OK, 0 rows affected, 1 warning (0.01 sec)
mysql [localhost:40019] {msandbox} ((none)) > show warnings ;
+-------+------+---------------------------------------------+
| Level | Code | Message |
+-------+------+---------------------------------------------+
| Note | 3163 | Authorization ID 'test'@'%' already exists. |
+-------+------+---------------------------------------------+
1 row in set (0.00 sec)
mysql [localhost:40019] {msandbox} ((none)) > select * from mysql.password_history where user = 'test' ;
+------+------+----------------------------+------------------------------------------------------------------------+
| Host | User | Password_timestamp | Password |
+------+------+----------------------------+------------------------------------------------------------------------+
| % | test | 2022-12-16 20:00:39.792705 | $A$005$(3ccMc
?nVN?M^j6DN/olTTYinBbh/xEsA0Nh5/czVuSsT7GlRboJe3./ |
| % | test | 2022-12-16 20:00:19.841037 | $A$005${"I@'~lw<a{C3M_E!9oPkPR8pZWxeEIkeigZ3ihpSRhoQDVu21Jr2BkKav48 |
+------+------+----------------------------+------------------------------------------------------------------------+
2 rows in set (0.00 sec)
mysql [localhost:40019] {msandbox} ((none)) > ALTER USER 'test'@'%' IDENTIFIED BY 'newpassword';
ERROR 3638 (HY000): Cannot use these credentials for 'test@%' because they contradict the password history policy
Suggested fix:
Prevent adding a password entry when the password is not altered.
Description: The command adds a password history entry even when the user already exists and the password is not altered. A subsequent ALTER USER fails due to the password history policy. How to repeat: mysql [localhost:40019] {msandbox} ((none)) > select @@version ; +-----------+ | @@version | +-----------+ | 8.0.31 | +-----------+ 1 row in set (0.00 sec) mysql [localhost:40019] {msandbox} ((none)) > select @@password_history ; +--------------------+ | @@password_history | +--------------------+ | 5 | +--------------------+ 1 row in set (0.00 sec) mysql [localhost:40019] {msandbox} ((none)) > CREATE USER IF NOT EXISTS 'test'@'%' IDENTIFIED BY 'password'; Query OK, 0 rows affected (0.01 sec) mysql [localhost:40019] {msandbox} ((none)) > select * from mysql.password_history where user = 'test' ; +------+------+----------------------------+------------------------------------------------------------------------+ | Host | User | Password_timestamp | Password | +------+------+----------------------------+------------------------------------------------------------------------+ | % | test | 2022-12-16 20:00:19.841037 | $A$005${"I@'~lw<a{C3M_E!9oPkPR8pZWxeEIkeigZ3ihpSRhoQDVu21Jr2BkKav48 | +------+------+----------------------------+------------------------------------------------------------------------+ 1 row in set (0.00 sec) mysql [localhost:40019] {msandbox} ((none)) > CREATE USER IF NOT EXISTS 'test'@'%' IDENTIFIED BY 'newpassword'; Query OK, 0 rows affected, 1 warning (0.01 sec) mysql [localhost:40019] {msandbox} ((none)) > show warnings ; +-------+------+---------------------------------------------+ | Level | Code | Message | +-------+------+---------------------------------------------+ | Note | 3163 | Authorization ID 'test'@'%' already exists. | +-------+------+---------------------------------------------+ 1 row in set (0.00 sec) mysql [localhost:40019] {msandbox} ((none)) > select * from mysql.password_history where user = 'test' ; +------+------+----------------------------+------------------------------------------------------------------------+ | Host | User | Password_timestamp | Password | +------+------+----------------------------+------------------------------------------------------------------------+ | % | test | 2022-12-16 20:00:39.792705 | $A$005$(3ccMc ?nVN?M^j6DN/olTTYinBbh/xEsA0Nh5/czVuSsT7GlRboJe3./ | | % | test | 2022-12-16 20:00:19.841037 | $A$005${"I@'~lw<a{C3M_E!9oPkPR8pZWxeEIkeigZ3ihpSRhoQDVu21Jr2BkKav48 | +------+------+----------------------------+------------------------------------------------------------------------+ 2 rows in set (0.00 sec) mysql [localhost:40019] {msandbox} ((none)) > ALTER USER 'test'@'%' IDENTIFIED BY 'newpassword'; ERROR 3638 (HY000): Cannot use these credentials for 'test@%' because they contradict the password history policy Suggested fix: Prevent adding a password entry when the password is not altered.