Password expiration enables database administrators to expire account passwords manually, and to establish a policy for automatic password expiration. It is one of the great feature. The table mysql.user has password_expired column. Its default value is ‘N’, but can be set to ‘Y’ with the new ALTER USER statement. There are two reason for enforcing this.
1. User shouldn’t be allowed to login without password.
2. User is forced to set a new password.
Normal User
mysql> select user, host, authentication_string, password_expired from mysql.user where user='nilis'; +-------+-----------+-------------------------------------------+------------------+ | user | host | authentication_string | password_expired | +-------+-----------+-------------------------------------------+------------------+ | nilis | localhost | *46257311B81FB99775C4F6B2B57BB2539E64BBDB | N | +-------+-----------+-------------------------------------------+------------------+ 1 row in set (0.00 sec)
[root@eltra ~]# mysql -unilis -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 4 Server version: 5.7.17 MySQL Community Server (GPL) Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> select user(); +-----------------+ | user() | +-----------------+ | nilis@localhost | +-----------------+ 1 row in set (0.01 sec)
Let’s expire the password for user nilis
mysql> ALTER USER 'nilis'@'localhost' PASSWORD EXPIRE; Query OK, 0 rows affected (0.01 sec) mysql> SELECT user, host, authentication_string, password_expired FROM mysql.user WHERE user='nilis'; +-------+-----------+-------------------------------------------+------------------+ | user | host | authentication_string | password_expired | +-------+-----------+-------------------------------------------+------------------+ | nilis | localhost | *46257311B81FB99775C4F6B2B57BB2539E64BBDB | Y | +-------+-----------+-------------------------------------------+------------------+ 1 row in set (0.00 sec)
The above command mark the password expired in mysql.user table. Still, user ‘nilis’ can connect to mysql with the existing password. Let’s enforce the password expire using ‘flush privileges’.
mysql> flush privileges; Query OK, 0 rows affected (0.00 sec) [root@eltra ~]# mysql -unilis -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 11 Server version: 5.7.17 Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> show processlist; ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement. mysql> mysql> ALTER USER 'nilis'@'localhost' identified by 'P@55w0RD'; Query OK, 0 rows affected (0.00 sec
Now, mysql is asking to change the password.
Automatic password expiration is available in MySQL 5.7.4 and later.
Password Expire examples:
mysql> ALTER USER 'nilis'@'localhost' PASSWORD EXPIRE NEVER; -- PASSWORD NEVER EXPIRE Query OK, 0 rows affected (0.00 sec) mysql> ALTER USER 'nilis'@'localhost' PASSWORD EXPIRE DEFAULT; -- GLOBAL EXPIRATION POLICY Query OK, 0 rows affected (0.00 sec) mysql> ALTER USER 'nilis'@'localhost' PASSWORD EXPIRE INTERVAL 60 DAY; -- PASSWORD EXPIRE AFTER 60 DAYS Query OK, 0 rows affected (0.00 sec) mysql> SET GLOBAL default_password_lifetime = 0; -- PASSWORD NEVER EXPIRE Query OK, 0 rows affected (0.00 sec) mysql> SET GLOBAL default_password_lifetime = 90; -- PASSWORD EXPIRE AFTER 90 DAYS Query OK, 0 rows affected (0.00 sec)
great