-- ---------------------- case1: password_history > 0 and password_reuse_interval = 0 MySQL> set global password_history=default; Query OK, 0 rows affected (0.0019 sec) MySQL> set global password_reuse_interval=default; Query OK, 0 rows affected (0.0002 sec) MySQL> show variables like 'password_%'; +--------------------------+-------+ | Variable_name | Value | +--------------------------+-------+ | password_history | 0 | | password_require_current | ON | | password_reuse_interval | 0 | +--------------------------+-------+ 3 rows in set (0.0077 sec) MySQL> set global password_history=5; Query OK, 0 rows affected (0.0012 sec) -- Set 10 consecutive different passwords for the aaa user MySQL> create user a identified by 'aaBB11__'; Query OK, 0 rows affected (0.0078 sec) MySQL> alter user a identified by 'aaBB22__'; Query OK, 0 rows affected (0.0049 sec) MySQL> alter user a identified by 'aaBB33__'; Query OK, 0 rows affected (0.0044 sec) MySQL> alter user a identified by 'aaBB44__'; Query OK, 0 rows affected (0.0031 sec) MySQL> alter user a identified by 'aaBB55__'; Query OK, 0 rows affected (0.0034 sec) MySQL> alter user a identified by 'aaBB66__'; Query OK, 0 rows affected (0.0045 sec) MySQL> alter user a identified by 'aaBB77__'; Query OK, 0 rows affected (0.0027 sec) MySQL> alter user a identified by 'aaBB88__'; Query OK, 0 rows affected (0.0030 sec) MySQL> alter user a identified by 'aaBB99__'; Query OK, 0 rows affected (0.0034 sec) MySQL> alter user a identified by 'aaBB00__'; Query OK, 0 rows affected (0.0049 sec) -- Re-use the previous password 5 times, it can be set successfully MySQL> alter user a identified by 'aaBB11__'; Query OK, 0 rows affected (0.0040 sec) -- Re-use the password within 5 times, the setting fails MySQL> alter user a identified by 'aaBB99__'; ERROR: 3638 (HY000): Cannot use these credentials for 'a@%' because they contradict the password history policy MySQL> select * from mysql.password_history; +------+------+----------------------------+-------------------------------------------+ | Host | User | Password_timestamp | Password | +------+------+----------------------------+-------------------------------------------+ | % | a | 2023-08-22 17:05:02.586796 | *3FB54DE55B8C1AC738C1352139BCAE08BCC52F77 | | % | a | 2023-08-22 17:04:27.967228 | *9CBE39C9B07031B1426463E2031282013EFD6B7E | | % | a | 2023-08-22 17:04:24.966836 | *A52A9A2904644B62A98164BE26DF333A54DBF5C6 | | % | a | 2023-08-22 17:04:22.106561 | *F274665AC4F019C8CACA05BFB8193A062A948539 | | % | a | 2023-08-22 17:04:19.066598 | *3FDDCE34BEF577CF9410EA359F5DF68FB6892EB7 | +------+------+----------------------------+-------------------------------------------+ 5 rows in set (0.0014 sec) -- Conclusion: The parameters of password_history are as expected -- ---------------------- case2: password_history = 0 and password_reuse_interval > 0 MySQL> set global password_history=default; Query OK, 0 rows affected (0.0011 sec) MySQL> set global password_reuse_interval=default; Query OK, 0 rows affected (0.0008 sec) MySQL> show variables like 'password_%'; +--------------------------+-------+ | Variable_name | Value | +--------------------------+-------+ | password_history | 0 | | password_require_current | ON | | password_reuse_interval | 0 | +--------------------------+-------+ 3 rows in set (0.0079 sec) 10.186.64.136:3306 mysql SQL > set global password_reuse_interval=1; Query OK, 0 rows affected (0.0015 sec) -- Set 10 consecutive different passwords for the aaa user drop user a; 10.186.64.136:3306 mysql SQL > create user a identified by 'aaBB11__'; Query OK, 0 rows affected (0.0077 sec) 10.186.64.136:3306 mysql SQL > alter user a identified by 'aaBB22__'; Query OK, 0 rows affected (0.0024 sec) 10.186.64.136:3306 mysql SQL > alter user a identified by 'aaBB33__'; Query OK, 0 rows affected (0.0026 sec) 10.186.64.136:3306 mysql SQL > alter user a identified by 'aaBB44__'; Query OK, 0 rows affected (0.0023 sec) 10.186.64.136:3306 mysql SQL > alter user a identified by 'aaBB55__'; Query OK, 0 rows affected (0.0020 sec) 10.186.64.136:3306 mysql SQL > alter user a identified by 'aaBB66__'; Query OK, 0 rows affected (0.0030 sec) 10.186.64.136:3306 mysql SQL > alter user a identified by 'aaBB77__'; Query OK, 0 rows affected (0.0021 sec) 10.186.64.136:3306 mysql SQL > alter user a identified by 'aaBB88__'; Query OK, 0 rows affected (0.0021 sec) 10.186.64.136:3306 mysql SQL > alter user a identified by 'aaBB99__'; Query OK, 0 rows affected (0.0022 sec) 10.186.64.136:3306 mysql SQL > alter user a identified by 'aaBB00__'; Query OK, 0 rows affected (0.0028 sec) -- Reuse the password for the previous 10 times, all settings failed 10.186.64.136:3306 mysql SQL > alter user a identified by 'aaBB22__'; ERROR: 3638 (HY000): Cannot use these credentials for 'a@%' because they contradict the password history policy 10.186.64.136:3306 mysql SQL > alter user a identified by 'aaBB33__'; ERROR: 3638 (HY000): Cannot use these credentials for 'a@%' because they contradict the password history policy 10.186.64.136:3306 mysql SQL > alter user a identified by 'aaBB44__'; ERROR: 3638 (HY000): Cannot use these credentials for 'a@%' because they contradict the password history policy 10.186.64.136:3306 mysql SQL > alter user a identified by 'aaBB00__'; ERROR: 3638 (HY000): Cannot use these credentials for 'a@%' because they contradict the password history policy -- The mysql.password_history table records the previous 10 passwords select * from mysql.password_history; +------+------+----------------------------+-------------------------------------------+ | Host | User | Password_timestamp | Password | +------+------+----------------------------+-------------------------------------------+ | % | a | 2023-08-22 17:13:24.753756 | *9CBE39C9B07031B1426463E2031282013EFD6B7E | | % | a | 2023-08-22 17:13:23.540651 | *A52A9A2904644B62A98164BE26DF333A54DBF5C6 | | % | a | 2023-08-22 17:13:23.538272 | *F274665AC4F019C8CACA05BFB8193A062A948539 | | % | a | 2023-08-22 17:13:23.535785 | *3FDDCE34BEF577CF9410EA359F5DF68FB6892EB7 | | % | a | 2023-08-22 17:13:23.532512 | *7F44BC2B35540D640C269F749B7B67BD7526E84B | | % | a | 2023-08-22 17:13:23.530174 | *94222908FF69A738944B368F97BB4614AC74506F | | % | a | 2023-08-22 17:13:23.527521 | *E989FE025646F4C4FCD4779D5D511FACF58157BF | | % | a | 2023-08-22 17:13:23.524578 | *974E9BFE163AB829FEE09FE840F509241EFC7A3D | | % | a | 2023-08-22 17:13:23.521923 | *CB61FC5EF4494250F7E67BB900D3A47BFFFEB13B | | % | a | 2023-08-22 17:13:23.513886 | *3FB54DE55B8C1AC738C1352139BCAE08BCC52F77 | +------+------+----------------------------+-------------------------------------------+ 10 rows in set (0.0011 sec) -- Manually update the records in the mysql.password_history table to exceed the 1-day limit period configured by password_reuse_interval 10.186.64.136:3306 mysql SQL > update mysql.password_history set Password_timestamp='2023-08-21 17:13:24.753756' where Password_timestamp='2023-08-22 17:13:24.753756'; Query OK, 1 row affected (0.0059 sec) -- Set a password that has exceeded the password_reuse_interval setting period again, and it can be set successfully 10.186.64.136:3306 mysql SQL > alter user a identified by 'aaBB00__'; Query OK, 0 rows affected (0.0038 sec) -- Conclusion: The parameters of password_reuse_interval are as expected -- ---------------------- case3: password_history > 0 and password_reuse_interval > 0 10.186.64.136:3306 mysql SQL > set global password_history=default; Query OK, 0 rows affected (0.0021 sec) 10.186.64.136:3306 mysql SQL > set global password_reuse_interval=default; Query OK, 0 rows affected (0.0007 sec) 10.186.64.136:3306 mysql SQL > show variables like 'password_%'; +--------------------------+-------+ | Variable_name | Value | +--------------------------+-------+ | password_history | 0 | | password_require_current | ON | | password_reuse_interval | 0 | +--------------------------+-------+ 3 rows in set (0.0094 sec) 10.186.64.136:3306 mysql SQL > set global password_history=5; Query OK, 0 rows affected (0.0005 sec) 10.186.64.136:3306 mysql SQL > set global password_reuse_interval=1; Query OK, 0 rows affected (0.0005 sec) 10.186.64.136:3306 mysql SQL > create user a identified by 'aaBB11__'; Query OK, 0 rows affected (0.0044 sec) 10.186.64.136:3306 mysql SQL > alter user a identified by 'aaBB22__'; Query OK, 0 rows affected (0.0032 sec) 10.186.64.136:3306 mysql SQL > alter user a identified by 'aaBB33__'; Query OK, 0 rows affected (0.0044 sec) 10.186.64.136:3306 mysql SQL > alter user a identified by 'aaBB44__'; Query OK, 0 rows affected (0.0037 sec) 10.186.64.136:3306 mysql SQL > alter user a identified by 'aaBB55__'; Query OK, 0 rows affected (0.0035 sec) 10.186.64.136:3306 mysql SQL > alter user a identified by 'aaBB66__'; Query OK, 0 rows affected (0.0030 sec) 10.186.64.136:3306 mysql SQL > alter user a identified by 'aaBB77__'; Query OK, 0 rows affected (0.0037 sec) 10.186.64.136:3306 mysql SQL > alter user a identified by 'aaBB88__'; Query OK, 0 rows affected (0.0038 sec) 10.186.64.136:3306 mysql SQL > alter user a identified by 'aaBB99__'; Query OK, 0 rows affected (0.0033 sec) 10.186.64.136:3306 mysql SQL > alter user a identified by 'aaBB00__'; Query OK, 0 rows affected (0.0032 sec) -- History records the last 10 passwords 10.186.64.136:3306 mysql SQL > select * from mysql.password_history; +------+------+----------------------------+-------------------------------------------+ | Host | User | Password_timestamp | Password | +------+------+----------------------------+-------------------------------------------+ | % | a | 2023-08-22 17:27:01.426642 | *9CBE39C9B07031B1426463E2031282013EFD6B7E | | % | a | 2023-08-22 17:26:58.289159 | *A52A9A2904644B62A98164BE26DF333A54DBF5C6 | | % | a | 2023-08-22 17:26:55.206765 | *F274665AC4F019C8CACA05BFB8193A062A948539 | | % | a | 2023-08-22 17:26:52.414111 | *3FDDCE34BEF577CF9410EA359F5DF68FB6892EB7 | | % | a | 2023-08-22 17:26:49.486649 | *7F44BC2B35540D640C269F749B7B67BD7526E84B | | % | a | 2023-08-22 17:26:46.506791 | *94222908FF69A738944B368F97BB4614AC74506F | | % | a | 2023-08-22 17:26:42.466716 | *E989FE025646F4C4FCD4779D5D511FACF58157BF | | % | a | 2023-08-22 17:26:39.706944 | *974E9BFE163AB829FEE09FE840F509241EFC7A3D | | % | a | 2023-08-22 17:26:36.066952 | *CB61FC5EF4494250F7E67BB900D3A47BFFFEB13B | | % | a | 2023-08-22 17:26:30.266825 | *3FB54DE55B8C1AC738C1352139BCAE08BCC52F77 | +------+------+----------------------------+-------------------------------------------+ 10 rows in set (0.0014 sec) -- The last 10 passwords cannot be reused 10.186.64.136:3306 mysql SQL > alter user a identified by 'aaBB22__'; ERROR: 3638 (HY000): Cannot use these credentials for 'a@%' because they contradict the password history policy -- Conclusion: When the password_reuse_interval value is set greater than 0, the value specified by the password_history value has no actual meaning, and the number of historical password records depends on the time interval configured by password_reuse_interval, and all passwords modified within this interval are configured repeatedly