Not the answer you need?
Register and ask your own question!

Password expired for all user accounts

Rein vtvRein vtv EntrantInactive User Role Beginner
Hey guys, i've just done an upgrade from (percona) 5.6 to 5.7 and ran into troubles. Not regarding the upgrade, but regarding password policy of mysql 5.7 :-(.

All accounts on my test server have been marked as expired (about 50 accounts) and therefor websites are unable to use the database server. Does anyone know how I can upgrade without having all accounts expired? Funny enough none of the usernames have a Y at the password_expired field in mysql.user ...

Any insights on the matter are greatly appreciated

Comments

  • jriverajrivera Percona Support Engineer Percona Staff Role
    Did you upgrade from 5.6 to any version between 5.7.4 to 5.7.10? Based on the manual:
    From MySQL 5.7.4 to 5.7.10, the default default_password_lifetime value is 360 (passwords must be changed approximately once per year). For those versions, be aware that, if you make no changes to thedefault_password_lifetime variable or to individual user accounts, all user passwords will expire after 360 days, and all user accounts will start running in restricted mode when this happens. Clients (which are effectively users) connecting to the server will then get an error indicating that the password must be changed: ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.

    However, this is easy to miss for clients that automatically connect to the server, such as connections made from scripts. To avoid having such clients suddenly stop working due to a password expiring, make sure to change the password expiration settings for those clients, like this:
    ALTER USER 'script'@'localhost' PASSWORD EXPIRE NEVER
    Alternatively, set the default_password_lifetime variable to 0, thus disabling automatic password expiration for all users.
  • Rein vtvRein vtv Entrant Inactive User Role Beginner
    Well, that's just it. I've ran into this problem when testing some months ago, and reverted back to 5.6. Now I went from 5.6 to the latest 5.7.17-11 and expected not to run into it. I did run mysql_upgrade afterwards, but I don't think that's a bad thing?
  • jriverajrivera Percona Support Engineer Percona Staff Role
    Upgrading major versions like in your case from 5.6 to 5.7, running mysql_upgrade is recommended if not required :)

    Just make sure to check all variables pertaining to password expiry to NOT expire :)
  • Rein vtvRein vtv Entrant Inactive User Role Beginner
    SHOW VARIABLES LIKE "default_pass%";
    +
    +
    +
    | Variable_name | Value |
    +
    +
    +
    | default_password_lifetime | 0 |
    +
    +
    +
    1 row in set (0.00 sec)


    but it passwords are still marked as expired.
    Is there a query I can run to unexpire them?
  • jriverajrivera Percona Support Engineer Percona Staff Role
    Run ALTER USER 'username' PASSWORD EXPIRE NEVER
Sign In or Register to comment.

MySQL, InnoDB, MariaDB and MongoDB are trademarks of their respective owners.
Copyright ©2005 - 2020 Percona LLC. All rights reserved.