Alter User Password Expire { Interval N Days | Default | Never }, and the global system variable default_password_lifetime which sets the default number of days from a password being set to when it expires (default 0, the password never expires; maximum 65535).
Alter User Password Expire... offers three options for when a user's password expires ...
- Interval N Day: N days from when it was set; mysql.user.password_lifetime is set to N.
- Default: the number of days set in the global variable default_password_lifetime from when it was last set; mysql.user.password_lifetime is set to Null.
- Never: The password never expires. mysql.user.password_lifetime is set to 0.
So, a simple query to show current user password expiry dates ...
select
user,
password_last_changed,
if( password_lifetime is null or @@default_password_lifetime=0, 'Never',
date_add(password_last_changed, interval password_lifetime day )
) as password_expires
from mysql.user;
and a more elaborate password expiry status query ...
select
user, host, password_last_changed, expiry_policy,
If( expiry_policy='Never',
'Never expires',
Concat( If(curdate()>=expires,'Expired ','Expires '), expires )
) as Status
from (
select
user, host, password_last_changed,
If( lifetime=0,
'Never',
Concat(lifetime, ' days' )
) as expiry_policy,
password_last_changed + Interval lifetime Day as expires
from (
select
user, host, password_last_changed, password_lifetime,
if( IsNull(password_lifetime),
@@default_password_lifetime,
password_lifetime
) as lifetime
from mysql.user
where left(user,6)<>'mysql.'
) u
) p;
Last updated 16 Nov 2024
