mysql⼝令更换周期_MySQL密码过期策略介绍
上⼀篇⽂章我们介绍了如何设置Mysql的密码复杂度，其实除了设置密码复杂度策略外，我们还可以设置密码⾃动过期，⽐如说隔 90 天密码会过期必须修改密码后才能继续使⽤，这样我们的数据库账号就更加安全了。下⾯来看下怎样设置密码⾃动过期。
单独设置某个账号密码过期时间
使单个账号密码过期我们可以使⽤ ALTER USER 语句，也可以更改账号过期时间。# 通过 mysql.ur 系统表查看数据库账号状态
mysql> lect ur,host,password_expired,password_lifetime,password_last_changed,account_locked from mysql.ur;
+------------------+-----------+------------------+-------------------+-----------------------+----------------
+| ur | host | password_expired | password_lifetime | password_last_changed | account_locked |
+------------------+-----------+------------------+-------------------+-----------------------+----------------
+| expur | % | N | NULL | 2021-01-05 14:30:30 | N |
| root | % | N | NULL | 2020-10-30 14:45:43 | N |
| testur | % | N | NULL | 2021-01-04 17:22:37 | N |
| mysql.infoschema | localhost | N | NULL | 2020-10-30 14:37:09 | Y |
kill them
| mysql.ssion | localhost | N | NULL | 2020-10-30 14:37:09 | Y |
danny| mysql.sys | localhost | N | NULL | 2020-10-30 14:37:09 | Y |
| root | localhost | N | NULL | 2020-10-30 14:38:55 | N |
+------------------+-----------+------------------+-------------------+-----------------------+----------------+7 rows in t (0.01 c)
# 使 expur 账号密码⽴即过期
mysql> ALTER USER 'expur'@'%' PASSWORD EXPIRE;
Query OK, 0 rows affected (0.00 c)
mysql> lect ur,host,password_expired,password_lifetime,password_last_changed,account_locked from mysql.ur;
+------------------+-----------+------------------+-------------------+-----------------------+----------------
+| ur | host | password_expired | password_lifetime | password_last_changed | account_locked |
+------------------+-----------+------------------+-------------------+-----------------------+----------------
+| expur | % | Y | NULL | 2021-01-05 14:30:30 | N |
| root | % | N | NULL | 2020-10-30 14:45:43 | N |
| testur | % | N | NULL | 2021-01-04 17:22:37 | N |
i e you是什么意思
| mysql.infoschema | localhost | N | NULL | 2020-10-30 14:37:09 | Y |
| mysql.ssion | localhost | N | NULL | 2020-10-30 14:37:09 | Y |
| mysql.sys | localhost | N | NULL | 2020-10-30 14:37:09 | Y |
| root | localhost | N | NULL | 2020-10-30 14:38:55 | N |
+------------------+-----------+------------------+-------------------+-----------------------+----------------+7 rows in t (0.00 c)
# 修改账号密码永不过期手续费英语
mysql> ALTER USER 'expur'@'%' PASSWORD EXPIRE NEVER;
Query OK, 0 rows affected (0.01 c)
nrm# 单独设置该账号密码90天过期
mysql> ALTER USER 'expur'@'%' PASSWORD EXPIRE INTERVAL 90 DAY;
Query OK, 0 rows affected (0.00 c)
mysql> lect ur,host,password_expired,password_lifetime,password_last_changed,account_locked from mysql.
ur;
+------------------+-----------+------------------+-------------------+-----------------------+----------------
b+| ur | host | password_expired | password_lifetime | password_last_changed | account_locked |
+------------------+-----------+------------------+-------------------+-----------------------+----------------
dancing girl
+| expur | % | N | 90 | 2021-01-05 14:41:28 | N |
| root | % | N | NULL | 2020-10-30 14:45:43 | N |
| testur | % | N | NULL | 2021-01-04 17:22:37 | N |
| mysql.infoschema | localhost | N | NULL | 2020-10-30 14:37:09 | Y |
| mysql.ssion | localhost | N | NULL | 2020-10-30 14:37:09 | Y |
| mysql.sys | localhost | N | NULL | 2020-10-30 14:37:09 | Y |
| root | localhost | N | NULL | 2020-10-30 14:38:55 | N |
+------------------+-----------+------------------+-------------------+-----------------------+----------------+7 rows in t (0.00 c)
# 让此账号使⽤默认的密码过期全局策略
mysql> ALTER USER 'expur'@'%' PASSWORD EXPIRE DEFAULT;
justcarQuery OK, 0 rows affected (0.01 c)
每个账号的相关信息在mysql.ur 系统表都有记录，当 password_expired 字段值为 Y 时，代表此密码已过期，使⽤过期密码仍可以登录，但不能进⾏任何操作，进⾏操作会提⽰：ERROR 1820 (HY000): You must ret your password using ALTER USER statement before executing this statement. 必须更改密码后才能进⾏正常操作。
对给定过期时间的账号来说，⽐如说设置 90 天过期，数据库系统会将当前时间与上次修改密码的时间差值作⽐较，如果距上次修改密码时间超过 90 天，则将此账号密码标记为过期，就必须要更改密码后才能操作。
设置全局过期策略
构建全局密码⾃动过期策略，我们要使⽤ default_password_lifetime 系统变量。在 5.7.11 版本之前，默认的
default_password_lifetime 值为 360，之后的版本默认值为 0，表⽰密码不会过期。此参数的单位是天，⽐如可以将此参数设置为 90，则表⽰全局密码⾃动过期策略是 90 天。# 设置全局过期策略 先⼿动更改再加⼊配置⽂件
mysql> SET GLOBAL default_password_lifetime = 90;
Query OK, 0 rows affected (0.01 c)
mysql> show variables like 'default_password_lifetime';
+---------------------------+-------+| Variable_name | Value |
+---------------------------+-------+| default_password_lifetime | 90 |
albinism+---------------------------+-------+1 row in t (0.00 c)
# 写⼊配置⽂件使得重启⽣效
[mysqld]
default_password_lifetime = 90
虽然可以通过将过期的密码设置为当前值来“重置”它，但出于良好的 Policy 考虑，我们最好选择其他密码。
总结：
本篇⽂章主要介绍了关于数据库密码的安全策略，上⼀篇是密码复杂度，本篇为密码过期策略，多⼀份策略多⼀份安⼼。要记住：安全⽆⼩事。weight是什么意思
