Password expiration policy in MySQL Server 5.7.17

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)

This is great feature and enhance security. Therefore important. Enjoy …

One Comment

Leave a Reply

Your email address will not be published. Required fields are marked *