How can I find out if a user's password has expired in MySQL?
The MySQL version I'm using is 8.0.16.
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.Sign up to join this community
This information, along with other user properties, is found in the
user table in the
mysql schema (database). The value
'Y' would indicate an expired password
select password_expired from mysql.user where user = 'whatever'
To see all users, and when the password will expire:
select user, password_expired, password_last_changed, password_lifetime, CASE WHEN password_lifetime IS NULL THEN 'Never' ELSE ADDDATE(password_last_changed,INTERVAL password_lifetime DAY) END AS password_expire_date from mysql.user u;
To expire a password now (fur user 'jeffrey'@'localhost'):
ALTER USER 'jeffrey'@'localhost' PASSWORD EXPIRE;
The rest of this info is available here: https://dev.mysql.com/doc/refman/8.0/en/password-management.html