How can I find out if a user's password has expired in MySQL?

The MySQL version I'm using is 8.0.16.

  • Did you Google anything and specify what you tried? That's normally considered a minimum before asking a question. I don't wish to be sarcastic, but here it's expected that those who ask questions have tried stuff themselves. There are a few articles about how to ask questions here on my profile - you might want to take a look. Also, visit the help center and take the tour! p.s. welcome to the forum! 8-) – Vérace Nov 15 '19 at 20:16
  • yes i did... there was nothing on it... even in my notes... close to giving up T-T – Anderson Nov 16 '19 at 9:58
  • i mean i did try the ones that popped up but it didnt work for my case n i have no idea – Anderson Nov 16 '19 at 10:03

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'
  • hi this works thanks, however i expired my password earlier but it says that it hasn't expired. do u know what the command for expiring pw is – Anderson Nov 15 '19 at 16:07

To see all users, and when the password will expire:

   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

  • hi @Luuk, this says that there was an error 1396. – Anderson Nov 15 '19 at 18:46
  • @Anderson: You cannot expire your own... stackoverflow.com/questions/5555328/… – Luuk Nov 15 '19 at 21:03
  • is there any way i could? i think i'm logged on as root user though – Anderson Nov 16 '19 at 9:57
  • It would not be wise to expire your own password. It could be that you are the last user who has access.... if you want to expire the root password, you will have to logon as another user (with enough rights) – Luuk Nov 16 '19 at 10:00
  • im intending to expire a separate user's password, logged on as root. its for a school assignment and im supposed to show that i can T-T – Anderson Nov 16 '19 at 10:03

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.