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 ...

  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 ...
  user, host, password_last_changed, expiry_policy,
  If( expiry_policy='Never', 
      'Never expires',
      Concat( If(curdate()>=expires,'Expired ','Expires '), expires )
    ) as Status
from (
    user, host, password_last_changed,
    If( lifetime=0, 
        Concat(lifetime, ' days' ) 
      ) as expiry_policy,
    password_last_changed + Interval lifetime Day as expires
  from (
      user, host, password_last_changed, password_lifetime, 
      if( IsNull(password_lifetime), 
        ) as lifetime
    from mysql.user
    where left(user,6)<>'mysql.'
  ) u
) p;

Last updated 16 Nov 2018