MySql User Password Expiration

默认密码过期永不过期:

mysql> select Host,User,account_locked,password_lifetime,password_expired,password_last_changed from mysql.user;
+-----------+------------------+----------------+-------------------+------------------+-----------------------+
| Host      | User             | account_locked | password_lifetime | password_expired | password_last_changed |
+-----------+------------------+----------------+-------------------+------------------+-----------------------+
| %         | dsg              | N              |              NULL | N                | 2018-10-12 05:23:22   |
| localhost | mysql.infoschema | Y              |              NULL | N                | 2018-09-30 20:04:23   |
| localhost | mysql.session    | Y              |              NULL | N                | 2018-09-30 20:04:22   |
| localhost | mysql.sys        | Y              |              NULL | N                | 2018-09-30 20:04:23   |
| localhost | root             | N              |              NULL | N                | 2018-10-12 05:22:52   |
+-----------+------------------+----------------+-------------------+------------------+-----------------------+

使某个用户密码过期

alter user dsg@'%'  PASSWORD EXPIRE;

• 发现用户能登陆但是不能查看数据:

[root@mysql3 ~]# mysql -u dsg -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 12
Server version: 8.0.12-commercial

Copyright (c) 2000, 2018, 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 databases;
ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.
mysql> 

重新设置密码:

alter user dsg@'%' identified by '1234';
show databases

设置全局用户用户过期:

[mysqld]
default_password_lifetime=90

密码永不过期:

[mysqld]
default_password_lifetime=0

设置某个用户间隔多少天密码过期

ALTER USER ‘testuser’@‘localhost' PASSWORD EXPIRE INTERVAL 30 DAY;

禁用某个用户密码过期

ALTER USER 'testuser'@'localhost' PASSWORD EXPIRE NEVER;

让某个用户使用全局的密码过期策略

ALTER USER 'testuser'@'localhost' PASSWORD EXPIRE DEFAULT;