MySQL Bugs: #79939: default_password_lifetime > 0 should print warning at startup

Bug #79939	default_password_lifetime > 0 should print warning at startup
Submitted:	12 Jan 2016 17:29	Modified:	13 Jan 2016 4:59
Reporter:	Morgan Tocker	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Security: Privileges	Severity:	S4 (Feature request)
Version:	8.0	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
In BUG #77277 the default for default_password_lifetime was changed to zero to avoid potential upgrade surprises to MySQL 5.7.  This is because there was no feedback when setting a password that it would expire and no feedback when a server started that passwords would expire.  It required reading the manual, which is not a safe assumption :)

It should be possible to run with a non-zero default_password_lifetime in the future *and* receive warning of the full implications of doing so.  I suggest printing a short note to the error log on startup.

How to repeat:
N/A

Suggested fix:
At startup:

2015-12-14T15:14:42.394924Z 0 [Warning] A default_password_lifetime value is set.  User accounts will expire automatically after 360 days without password change.  To disable this set default_password_lifetime to 0.

The suggested warning message is likely to make people just go back to the "insecure setting".  Do you want that?

Perhaps it might be helpful to suggest how to see which passwords are likely to expire soon, so the DBA can then decide what to do about that ahead of time.  If the setting is not 0 then perhaps you could show the first 3 accounts that will expire next and the date that this will happen? (Suggesting the required SQL to do this would be good too, or point to a URL where this is described.)

One such task would be to provide the suggested messages:
2015-12-14T15:14:42.394924Z 0 [Warning] A non-zero default_password_lifetime of 360 is set.  See: http://https://dev.mysql.com/doc/refman/5.7/en/password-expiration-policy.html for how to manage MySQL accounts when password expiry is enabled.
2015-12-14T15:14:42.394924Z 0 [Warning] The first 3 accounts whose password will next expire are:
2015-12-14T15:14:42.394924Z 0 [Warning] '2016-12-01 00:00:00': 'user1'@'192.168.%'
2015-12-14T15:14:42.394924Z 0 [Warning] '2016-12-02 00:00:00': 'user2'@'172.16.%'
2015-12-14T15:14:42.394924Z 0 [Warning] '2016-12-03 00:00:00': 'user3'@'10.%'

That said this sort of task sounds to me like an "init task", a job which should be triggered on startup (like the init_file configuration setup), and not something that's hard-coded into the server.  I'd be tempted in 5.8 to call an init routine in "sys" (if present) and have this task configured to run any other "init scripts". One such task could well be to generate the messages as indicated above but this would be more flexible, and also give the DBA freedom to do other "on startup" initialisation which might be of interest.

Simon, good feedback.  Thank you!
See also: Bug #79959 - SYS should have a view of user accounts expiring

Does that mean that for a development server (only accessible locally) I wouldn't need to have passwords expire (theoretically)?

I update/reinstall the server fairly regularly anyway, so if the default was set to 1 year, it would effectively never need to be changed - each fresh installation has it's own unique password

Posted by developer:
 
Reported version value updated to reflect release name change from 5.8 to 8.0