Password expiry dates

from the Artful Common Queries page


MySQL manages password lifetime with the command 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;


Return to the Artful Common Queries page