【mysql体系结构】用户管理

mysql体系结构–用户管理

用户的组成

用户名@‘白名单’

白名单?

​ 地址列表:

​ %

​ 10.0.0.10

​ 10.0.0.%

​ 10.0.0.5%

​ 10.0.0.0/255.255.254.0

​ db01

​ 127.0.0.1

​ localhost ----> socket

创建用户

create user oldguo@‘10.0.0.%’ identified by ‘123’;

create user oldguo@‘10.0.0.%’ identified with mysql_native_password by ‘123’;

查询用户

mysql> select user,host,plugin from mysql.user;
+------------------+-----------+-----------------------+
| user             | host      | plugin                |
+------------------+-----------+-----------------------+
| oldguo           | 10.0.0.%  | mysql_native_password |
| mysql.infoschema | localhost | caching_sha2_password |
| mysql.session    | localhost | caching_sha2_password |
| mysql.sys        | localhost | caching_sha2_password |
| root             | localhost | caching_sha2_password |
+------------------+-----------+-----------------------+
5 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11

删除用户

mysql> drop user oldguo@'10.0.0.%';
Query OK, 0 rows affected (0.00 sec)
1
2

修改用户

mysql> alter user oldguo@'10.0.0.%' identified with mysql_native_password by '123456';
Query OK, 0 rows affected (0.00 sec)
1
2

密码过期时间

mysql> create user 'oldguo'@'localhost' password expire interval 90 day;
Query OK, 0 rows affected (0.01 sec)

mysql> alter user 'oldguo'@'localhost' password expire interval 180 day;
Query OK, 0 rows affected (0.01 sec)

mysql> create user 'oldguo1'@'localhost' password expire never;
Query OK, 0 rows affected (0.00 sec)

mysql> alter user 'oldguo1'@'localhost' password expire never;
Query OK, 0 rows affected (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11

密码重用

password_history=6
password_reuse_interval=365
1
2

锁定用户

mysql> alter user 'oldguo'@'localhost' account lock;
Query OK, 0 rows affected (0.00 sec)
1
2

连接资源限制

with
max_queries_per_hour count
max_updates_per_hour count
max_connections_per_hour 2000;
max_user_connections count
1
2
3
4
5