mysql 设置临时登录账号密码过期设置

最新推荐文章于 2024-11-01 10:13:28 发布

原创最新推荐文章于 2024-11-01 10:13:28 发布 · 1.1k 阅读

0 ·

CC 4.0 BY-SA版权

mysql 专栏收录该内容

21 篇文章

订阅专栏

本文介绍了如何在MySQL中创建临时登录账号，并进行权限测试。当设置账号过期后，发现账号虽然能登录但无法执行查询操作，通过`ALTER USER`命令可重置密码恢复使用。同时，文章提到了修改配置文件以调整默认密码过期天数的方法，但动态修改在服务重启后无效。

先创建一个测试账号

grant all on *.* to test@'%' identified by '123456';
Query OK, 0 rows affected, 1 warning (0.03 sec)
[root@mysqltest ~]# mysql -u test -p123456
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 5.7.25-log MySQL Community Server (GPL)

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.

test@localhost:(none) 15:13:28>show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| accountdb          |
| addrdb             |
| advertisedb        |
| mysql              |
| performance_schema |
| pointdb            |
| reportdb           |
| sys                |
| test               |
| test1              |
| tmsdb              |
+--------------------+
12 rows in set (0.14 sec)

然后对test账号做权限测试

1.直接使用户过期：

root@localhost:(none) 15:14:12>alter user test@'%' password expire;
Query OK, 0 rows affected (0.01 sec)
root@localhost:(none) 15:14:21>flush privileges;
Query OK, 0 rows affected (0.03 sec)

再使用test账号登录试下

[root@mysqltest ~]# mysql -u test -p123456
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 10
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.

test@localhost:(none) 15:15:48>show databases;
ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.
test@localhost:(none) 15:15:51>use mysql;
ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.

发现账号可以正常登录，但是所有查询功能不能使用了，不过使用alter user 重新设置密码之后又可以继续使用

test@localhost:(none) 15:18:46>alter user test@'%' identified by '123456';
Query OK, 0 rows affected (0.07 sec)

test@localhost:(none) 15:19:28>show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| accountdb          |
| addrdb             |
| advertisedb        |
| mysql              |
| performance_schema |
| pointdb            |
| reportdb           |
| sys                |
| test               |
| test1              |
| tmsdb              |
+--------------------+
12 rows in set (0.00 sec)

test@localhost:(none) 15:19:40>select * from test.t1;
+------+------+
| id   | name |
+------+------+
|    1 | NULL |
|    2 | NULL |
|    3 | NULL |
|    4 | NULL |
|    5 | NULL |
+------+------+
5 rows in set (0.00 sec)

其他操作
修改配置文件默认过期天数：

[mysqld]
default_password_lifetime=180 #如果设置为零标识永不过期

动态修改,服务重启之后设置无效：

SET GLOBAL default_password_lifetime = 180;
SET GLOBAL default_password_lifetime = 0

创建变更用户时候：

create user test@'%' identified by '123456' password expire interval 90 day;
alter user test@'%' identified by '123456' password expire interval 90 day;
# 禁用过期，永久不过期：
create user test@'%' identified by '123456' password expire never;
alter user test@'%' identified by '123456' password expire never;