Bug #112132 The configuration of password_history does not take effect

• Submitted: 22 Aug 2023 8:58
• Modified: 25 Aug 2023 5:22
• Reporter: zhenxing yu
• Status: Not a Bug
• Category: MySQL Server
• Severity: S3 (Non-critical)
• Version: 8.0.33
• OS: Linux (CentOS Linux release 7.5.1804 (Core))
• CPU Architecture: x86 (Intel(R) Xeon(R) CPU E5-4620 v2 @ 2.60GHz)
• Tags: password_history, password_reuse_interval

[22 Aug 2023 8:58] zhenxing yu

Description:
There is a description in the MySQL 8.0 documentation: [To prohibit reusing any of the last 6 passwords or passwords newer than 365 days, put these lines in the server my.cnf file]

[mysqld]
password_history=6
password_reuse_interval=365

In fact, when I configure password_reuse_interval, password_history does not take effect for the reuse configuration of historical passwords. Instead of recording 6 historical passwords in the mysql.history table, it records all historical passwords within 365 days controlled by password_reuse_interval

- When password_reuse_interval is set to 0 or the default value, the password_history parameter can control the number of records of historical passwords
- When password_reuse_interval is set to a non-zero value, the parameter of password_history cannot control the number of historical password records

How to repeat:
1. set password_history and password_reuse_interval
set global password_history=5;
set global password_reuse_interval=1;

2. Create user and change password multiple times
create user aaa identified by 'aaBB11__';
alter user aaa identified by 'aaBB22__';
alter user aaa identified by 'aaBB33__';
alter user aaa identified by 'aaBB44__';
alter user aaa identified by 'aaBB55__';
alter user aaa identified by 'aaBB66__';
alter user aaa identified by 'aaBB77__';
alter user aaa identified by 'aaBB88__';

3. Set the password to the value of the first time
alter user aaa identified by 'aaBB11__';
ERROR: 3638 (HY000): Cannot use these credentials for 'aaa@%' because they contradict the password history policy

4. check mysql.history table record
select * from mysql.password_history;
+------+------+----------------------------+-------------------------------------------+
| Host | User | Password_timestamp         | Password                                  |
+------+------+----------------------------+-------------------------------------------+
| %    | aaa  | 2023-08-22 16:50:28.546622 | *F274665AC4F019C8CACA05BFB8193A062A948539 |
| %    | aaa  | 2023-08-22 16:50:27.868119 | *3FDDCE34BEF577CF9410EA359F5DF68FB6892EB7 |
| %    | aaa  | 2023-08-22 16:50:27.865984 | *7F44BC2B35540D640C269F749B7B67BD7526E84B |
| %    | aaa  | 2023-08-22 16:50:27.863383 | *94222908FF69A738944B368F97BB4614AC74506F |
| %    | aaa  | 2023-08-22 16:50:27.861216 | *E989FE025646F4C4FCD4779D5D511FACF58157BF |
| %    | aaa  | 2023-08-22 16:50:27.858255 | *974E9BFE163AB829FEE09FE840F509241EFC7A3D |
| %    | aaa  | 2023-08-22 16:50:27.853766 | *CB61FC5EF4494250F7E67BB900D3A47BFFFEB13B |
| %    | aaa  | 2023-08-22 16:50:23.947832 | *3FB54DE55B8C1AC738C1352139BCAE08BCC52F77 |
+------+------+----------------------------+-------------------------------------------+
8 rows in set (0.0014 sec)

Suggested fix:
Need help to judge whether there is a problem with the current logic

[22 Aug 2023 9:32] zhenxing yu

test_case SQL file

Attachment: test_case.sql (application/octet-stream, text), 11.32 KiB.

[24 Aug 2023 12:46] MySQL Verification Team

Hello Zhenxing yu,

Thank you for the bug report.
Imho this is not a bug. 

Password_history - This variable defines the global policy for controlling reuse of previous passwords based on required minimum number of password changes. It doesn't mean it should store only defined number of passwords. In your case 6, it means password can be changed only after 6th attempt.

Regards,
Ashwini Patil

[25 Aug 2023 5:22] zhenxing yu

Thanks for the answer. From the point of view of actual verification, the password_reuse_interval parameter and the password_history parameter have effective priority. If the password_reuse_interval is set to a value greater than 0, only the configuration specified by the password_reuse_interval parameter will be used. Only when the password_reuse_interval is set to 0, it will It takes effect according to the parameter configuration of password_history. Is this logic in design?