MySQL密码过期

最新推荐文章于 2024-05-04 11:25:06 发布

耿进

最新推荐文章于 2024-05-04 11:25:06 发布

阅读量2.1k

点赞数

文章标签： mysql 数据库

本文链接：https://blog.csdn.net/weixin_45925257/article/details/120001898

版权

概述：

我们先来重新认识一下mysql.user表中关于密码过期的字段

mysql> use mysql
Database changed
mysql> desc user;
+------------------------+-----------------------------------+------+-----+-----------------------+-------+
| Field                  | Type                              | Null | Key | Default               | Extra |
+------------------------+-----------------------------------+------+-----+-----------------------+-------+
。。。。
| password_expired       | enum('N','Y')                     | NO   |     | N                     |       |
| password_last_changed  | timestamp                         | YES  |     | NULL                  |       |
| password_lifetime      | smallint(5) unsigned              | YES  |     | NULL                  |       |
+------------------------+-----------------------------------+------+-----+-----------------------+-------+
45 rows in set (0.01 sec)

mysql>

password_expired：从MySQL 5.6.6 版本开始，添加了 password_expired 功能，它允许设置用户的过期时间。
password_last_changed ：密码最后一次修改的时间
password_lifetime：该用户密码的生存时间，默认值为 NULL，除非手动修改此用户密码过期机制，否则都是 NULL。

另外解释一个参数：
default_password_lifetime：从MySQL 5.7.4版本开始，此全局变量可以设置一个全局的自动密码过期策略。

测试：

一、password_expired：手动设置过期

1.设置密码永不过期

mysql> grant all on *.* to test@'localhost' identified by '123';
Query OK, 0 rows affected, 2 warnings (0.03 sec)

mysql> select User,password_last_changed,password_lifetime,password_expired from mysql.user;
+---------------+-----------------------+-------------------+------------------+
| User          | password_last_changed | password_lifetime | password_expired |
+---------------+-----------------------+-------------------+------------------+
| root          | 2021-03-31 14:11:10   |              NULL | N                |
| mysql.session | 2021-03-31 14:11:06   |              NULL | N                |
| mysql.sys     | 2021-03-31 14:11:06   |              NULL | N                |
| root          | 2021-03-31 14:11:10   |              NULL | N                |
| universe_op   | 2021-03-31 14:11:10   |              NULL | N                |
| kobe          | 2021-04-01 16:45:20   |              NULL | N                |              |
| test          | 2021-04-16 17:30:18   |              NULL | N                |
+---------------+-----------------------+-------------------+------------------+
7 rows in set (0.00 sec)

mysql> ALTER USER 'test'@'localhost' PASSWORD EXPIRE NEVER;
Query OK, 0 rows affected (0.00 sec)

mysql> 
mysql> select User,password_last_changed,password_lifetime,password_expired from mysql.user;
+---------------+-----------------------+-------------------+------------------+
| User          | password_last_changed | password_lifetime | password_expired |
+---------------+-----------------------+-------------------+------------------+
| root          | 2021-03-31 14:11:10   |              NULL | N                |
| mysql.session | 2021-03-31 14:11:06   |              NULL | N                |
| mysql.sys     | 2021-03-31 14:11:06   |              NULL | N                |
| root          | 2021-03-31 14:11:10   |              NULL | N                |
| universe_op   | 2021-03-31 14:11:10   |              NULL | N                |
| kobe          | 2021-04-01 16:45:20   |              NULL | N                |                |
| test          | 2021-04-16 17:30:18   |                 0 | N                |
+---------------+-----------------------+-------------------+------------------+
7 rows in set (0.00 sec)

mysql> 
注：如果该参数设置为0，即表示密码永不过期。

2.手动设置该用户密码为30 day（它会自动覆盖密码过期的全局策略）

mysql> ALTER USER 'test'@'localhost' PASSWORD EXPIRE INTERVAL 30 DAY;
Query OK, 0 rows affected (0.01 sec)

mysql> select User,password_last_changed,password_lifetime,password_expired from mysql.user;
+---------------+-----------------------+-------------------+------------------+
| User          | password_last_changed | password_lifetime | password_expired |
+---------------+-----------------------+-------------------+------------------+
| root          | 2021-03-31 14:11:10   |              NULL | N                |
| mysql.session | 2021-03-31 14:11:06   |              NULL | N                |
| mysql.sys     | 2021-03-31 14:11:06   |              NULL | N                |
| root          | 2021-03-31 14:11:10   |              NULL | N                |
| universe_op   | 2021-03-31 14:11:10   |              NULL | N                |
| kobe          | 2021-04-01 16:45:20   |              NULL | N                |                |
| test          | 2021-04-16 17:30:18   |                30 | N                |
+---------------+-----------------------+-------------------+------------------+
7 rows in set (0.00 sec)

mysql>

3.设置密码立马过期

mysql> ALTER USER 'hhh'@'%' PASSWORD EXPIRE;
Query OK, 0 rows affected (0.01 sec)

mysql> 
mysql> select User,password_last_changed,password_lifetime,password_expired from mysql.user;
+---------------+-----------------------+-------------------+------------------+
| User          | password_last_changed | password_lifetime | password_expired |
+---------------+-----------------------+-------------------+------------------+
| root          | 2021-03-31 14:11:10   |              NULL | N                |
| mysql.session | 2021-03-31 14:11:06   |              NULL | N                |
| mysql.sys     | 2021-03-31 14:11:06   |              NULL | N                |
| root          | 2021-03-31 14:11:10   |              NULL | N                |
| universe_op   | 2021-03-31 14:11:10   |              NULL | N                |
| kobe          | 2021-04-01 16:45:20   |              NULL | N                |                |
| test          | 2021-04-16 17:30:18   |                30 | Y                |
| gengjin       | 2021-04-16 17:42:33   |              NULL | N                |
| hhh           | 2021-04-16 18:00:32   |              NULL | Y                |
| kkk           | 2021-04-16 18:26:06   |              NULL | N                |
+---------------+-----------------------+-------------------+------------------+
10 rows in set (0.00 sec)

mysql> exit
Bye
[root@manage01 ~]# /opt/mysql/base/5.7.25/bin/mysql -uhhh -p -S /opt/mysql/data/3306/mysqld.sock
Logging to file '/mysqldata/mysql_3306/log/test.log'
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 39469
Server version: 5.7.25-log

Copyright (c) 2000, 2019, 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>

二、default_password_lifetime：自动过期的机制

1.设置全局密码过期时间：

#配置文件
[mysqld]
default_password_lifetime=90
or
#命令行全局修改
mysql> SET GLOBAL default_password_lifetime = 90;
Query OK, 0 rows affected (0.00 sec)

mysql>
mysql> show variables like "default_password_lifetime";
+---------------------------+-------+
| Variable_name             | Value |
+---------------------------+-------+
| default_password_lifetime | 90    |
+---------------------------+-------+
1 row in set (0.00 sec)

mysql>

2.创建用户：

mysql> grant all on *.* to hhh@'%' identified by '123';
Query OK, 0 rows affected, 1 warning (0.04 sec)

mysql> select User,password_last_changed,password_lifetime,password_expired from mysql.user;
+---------------+-----------------------+-------------------+------------------+
| User          | password_last_changed | password_lifetime | password_expired |
+---------------+-----------------------+-------------------+------------------+
| root          | 2021-03-31 14:11:10   |              NULL | N                |
| mysql.session | 2021-03-31 14:11:06   |              NULL | N                |
| mysql.sys     | 2021-03-31 14:11:06   |              NULL | N                |
| root          | 2021-03-31 14:11:10   |              NULL | N                |
| universe_op   | 2021-03-31 14:11:10   |              NULL | N                |
| kobe          | 2021-04-01 16:45:20   |              NULL | N                |                |
| test          | 2021-04-16 17:30:18   |                30 | Y                |
| gengjin       | 2021-04-16 17:42:33   |              NULL | N                |
| hhh           | 2021-04-16 18:00:32   |              NULL | N                |
+---------------+-----------------------+-------------------+------------------+
9 rows in set (0.00 sec)

mysql>

注：很多人一看这个password_lifetime为什么没有变，不应该变成90吗，是不是mysql的bug，其实不然。
顺便贴一个这个“bug”的地址：
https://bugs.mysql.com/bug.php?id=89349

它的工作方式如下:
有一个全局系统变量default_password_lifetime，它为使用默认密码生存期的所有帐户指定策略。在系统表中这将存储一个NULL。NULL值被用作一个标志，表明所涉及的帐户没有每个用户密码的特殊生存期。通过ALTER USER password EXPIRE NEVER(将列设置为0)或ALTER USER password EXPIRE INTERVAL N DAY(将列设置为N)设置每个用户的特殊密码生存期。
因此，没有设置特定密码生存期的所有用户的所有密码生存期都将跟随全局变量的值。

耿进

关注

0
点赞
踩
5

收藏

觉得还不错? 一键收藏
0
评论
MySQL密码过期

概述：我们先来重新认识一下mysql.user表中关于密码过期的字段mysql> use mysqlDatabase changedmysql> desc user;+------------------------+-----------------------------------+------+-----+-----------------------+-------+| Field | Type
复制链接

扫一扫