MySQL Bugs: #112128: The parameters such as MySQL password

Bug #112128	The parameters such as MySQL password_history does not take effect
Submitted:	22 Aug 2023 6:04	Modified:	22 Aug 2023 12:54
Reporter:	zhenxing yu	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	8.0.33	OS:	CentOS (CentOS Linux release 7.5.1804 (Core))
Assigned to:		CPU Architecture:	x86 (Intel(R) Xeon(R) CPU E5-4620 v2 @ 2.60GHz)
Tags:	password_history, password_reuse_interval, user

Description:
When I set the default values of password-related parameters such as default_password_lifetime, password_history, and password_reuse_interval, creating a new user does not inherit the default values of these parameters, and the configuration observed in the mysql.user table is still NULL

How to repeat:
1. set variables
set global default_password_lifetime=365;
set global password_history=5;
set global password_reuse_interval=1;

2. reconnect and create new user
create user 'aaa' identified by 'aaa';

3. check mysql.user
select user,host,password_lifetime,Password_reuse_history,Password_reuse_time from mysql.user where user='aaa';

Suggested fix:
Is this phenomenon a bug or the way I use it is wrong. If it is not a bug, is it convenient to give a standard usage process? I don’t see any special requirements from the official documents

Reproduced command and output record

Attachment: command_record.txt (text/plain), 4.57 KiB.

update title

Hello!

Thank you for the bug report.
Imho this is not a bug. Quoting Georgi's comment from Bug #89349.

The way it works is as follows:
1. There is the global system variable default_password_lifetime that specifies the policy for all accounts that are set to use the default password lifetime. This is done by ALTER USER PASSWORD EXPIRE DEFAULT. In the system table (not that it matters, but still) this stores a NULL. The NULL value is used as a flag that the account in question does not have a special per user password lifetime. The special per-user password lifetime is set via ALTER USER PASSWORD EXPIRE NEVER (which sets the column to 0) or ALTER USER PASSWORD EXPIRE INTERVAL N DAY (which sets the column to N). 
As a consequence all password lifetimes for all users that do not have a specific password lifetime set will follow the value of the global variable.
And if you store a specific password lifefile for a user account it will "detach" itself from the global variable's value and will be pegged to whatever you've set for it. Until you reset it back to the default of course.

Regards,
Ashwini Patil