mysql-5.7 Password Expiration detailed

First, Origin:

As soon as I got to work today, I heard that Error-log recorded a lot of

ERROR 1820 (HY000): Must reset your password using ALTER USER statement before executing this statement.

　　

Second, the MySQL user password expires processing:

1, from a simple create user to see the internal logic of MySQL password expiration

Mysql> Create User[Email protected]'%'Identified by '[email protected]'; Query OK,0Rows Affected (0.01sec) MySQL>ShowCreate User[Email protected]'%';+------------------------------------------------------------------------------------------------------------- --------------------------------------------------------+| CREATE USER  for[Email protected]%                                                                                                                                            |+------------------------------------------------------------------------------------------------------------ ---------------------------------------------------------+| CREATE USER 'Exuser'@'%'Identified with 'Mysql_native_password'  as '*cd089516e876a47febf3bb6a9add45f02f4bf73b'REQUIRE NONE PASSWORD EXPIREDEFAULTAccount UNLOCK|+------------------------------------------------------------------------------------------------------------- --------------------------------------------------------+1Rowinch Set(0.00Sec

　　

2, from the above show create user can see that MySQL will silently add a password expire DEFAUTL clause after the CREATE USER statement;

The password expire default clause is used to set the password expiration time (in days) for the MySQL account.

3. How does MySQL deal with the expiration time of a dead MySQL account password?

Mysql> Create User[Email protected]'%'Identified by '123456'Password expire interval -  Day; Query OK,0Rows Affected (0.00sec) MySQL>ShowCreate User[Email protected]'%'; +------------------------------------------------------------------------------------------------------------- ------------------------------------------------------------+| CREATE USER  for[Email protected]%                                                                                                                                                    |+------------------------------------------------------------------------------------------------------------- ------------------------------------------------------------+| CREATE USER 'TT'@'%'Identified with 'Mysql_native_password'  as '*6bb4837eb74329105ee4568dda7dc67ed2ca2ad9'REQUIRE NONE PASSWORD EXPIRE INTERVAL -  DayAccount UNLOCK|+------------------------------------------------------------------------------------------------------------- ------------------------------------------------------------+

4, password expire default here the value of the default is referred to default_password_lifetime this variable, that is, you can pass

Change the value of the Default_password_lifetime to flexibly control the expiration time of the account.

third, default_password_lifetime default value :

Currently default_password_lifetime the default value for this parameter is 0, which means create user by default [email protected] identified by ' xxx '

The created account password is never expired.

Iv. answer the questions of origin:

The password mentioned in "origin" expires because the default value of Default_password_lifetime in mysql-5.7.4 ~ mysql-5.7.10 versions is 360,

This is embarrassing, such a setting makes MySQL run for 360 days by default will change the password, this is not scientific! And then after the mysql-5.7.11.

MySQL set the default value of the Default_password_lifetime parameter to 0.

Five, give you an environment how to see that the user's password is not expired

　　For the reference to the password expiration of the decision can not simply see user.password_expired This column to distinguish the password is not expired, but to the MySQL account expires

Inside logic

　　

1, the first step: Check the user.assword_last_changed column to see the corresponding account password last modified time

Mysql> Select User, Host,password_expired,password_last_changed,password_lifetime from User; +---------------+-----------+------------------+-----------------------+-------------------+| User          |Host|Password_expired|Password_last_changed|Password_lifetime|+---------------+-----------+------------------+-----------------------+-------------------+|Root|localhost|N|  .-Ten- the Ten: -: +   |              NULL ||Mysql.session|localhost|N|  .-Ten-Geneva  A: -:Ten   |              NULL ||Mysql.sys|localhost|N|  .-Ten-Geneva  A: -:Ten   |              NULL ||Jianglexing|localhost|N|  .-Ten- -  the: -: -   |              NULL ||Repl| %         |N|  .-Ten- -  +: -: -   |              NULL ||Tstuser|localhost|N|  .-Ten- A Ten:Geneva: -   |              NULL |+---------------+-----------+------------------+-----------------------+-------------------+

2, the second step: see Default_password_lifetime The value of this variable is how much

Mysql>Show global variables like 'Default_password_lifetime'; +---------------------------+-------+|Variable_name|Value|+---------------------------+-------+|Default_password_lifetime| 1     |+---------------------------+-------+1Rowinch Set(0.00Sec

3. Determine if the user is using the MySQL global password expiration policy

Mysql>ShowCreate User[Email protected]'localhost'; +------------------------------------------------------------------------------------------------------------- ------------------+| CREATE USER  forTstuser@localhost                                                                                             |+------------------------------------------------------------------------------------------------------------ -------------------+| CREATE USER 'Tstuser'@'localhost'Identified with 'Mysql_native_password'REQUIRE NONE PASSWORD EXPIREDEFAULTAccount UNLOCK|+------------------------------------------------------------------------------------------------------------- ------------------+1Rowinch Set(0.00Sec

4, determine the current time

Mysql> SelectNow ();+---------------------+|Now ()|+---------------------+|  .-Ten- the  One: Geneva: $ |+---------------------+1Rowinch Set(0.00Sec

5. Conclusion:

For Tstuser, it uses a global password expiration policy, which is one day after the password expires, but it was the last time the password was updated in 2017-10-12, and the current time is

2017-10-15 so it's OK to say that Tstuser's password has expired.

mysql-5.7 Password Expiration detailed