[toc]

MySQL用户管理

用户作用

  • 登陆数据库
  • 对库表的增删查改

MySQL用户定义

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
# 用户格式
用户@'主机域'

# 示例用户
root@'10.0.0.%'  10.0.0.1-0.0.0.255
root@'10.0.0.5%' 10.0.0.50-10.0.0.59 + 10.0.0.5
root@'10.0.%.%'
root@'10.%.%.%'
root@'%'
root@'10.0.0.0/255.255.255.0'
## 错误写法
root@'10.0.0.0/24'

# 主机域作用
开启白名单，设置允许连接的网段

用户增删改查

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
# 增
## 使用create创建不带密码的用户
create user test_aaa@'%';
## 使用create创建用户并设置密码
create user test_aaa@'%' identified by '111';
## 使用grant创建用户并设置密码
grant all on *.* to test_bbb@'%' identified by '222';
## 在用户表插入数据(物理增加用户)
insert

# 删
## 使用drop删除一个用户
drop user test1@'10.0.0.0/24';
## 使用delete在用户库文件删除指定条件的用户(物理删除)
delete from mysql.user where user='test' and host='10.0.0.0/255.255.255.0';

# 改
## 使用alter修改指定用户密码
alter user root@'localhost' identified by '123456';
## 物理修改数据
insert
delete
update
## 逻辑修改数据
create
drop
alter
## 示例：update可以直接改表数据并生效，但是涉及到修改用户表数据时，无法直接生效
update mysql.user set password=PASSWORD('qqq') where user='root' and host='localhost';
flush privileges; #全局刷新

# 查
## 使用select查看用户列表，以表格的形式
select user,host from mysql.user;
## 使用select查看用户列表，以列的形式
select * from mysql.user\G
## 查看用户权限
show grants for test_ccc@'%';

## grant的功能
grant：
- 创建用户
- 设置密码
- 修改密码
- 设置权限
- 修改权限

MySQL修改用户密码的方式

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
# 使用旧账号密码更改
mysqladmin -uroot -pqqq password '123'

# 使用grant更新用户密码
grant all on *.* to root@'localhost' identified by '222';

# 使用set修改当前登录用户的密码
set password=PASSWORD('456');

# MySQL5.7初次登录需修改初始密码
alter user root@'localhost' identifide by '123';

# 使用undate修改用户信息并在刷新全局后生效
## 5.6.50修改password
update mysql.user set password=PASSWORD('789') where user='root' and host='localhost';
## 5.7.40修改authentication_strin
update mysql.user set authentication_string=PASSWORD('789') where user='root' and host='localhost';		

flush privileges;

用户权限管理

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
# 查看所有权限
grant

INSERT # 插入
SELECT # 查看
UPDATE # 修改
DELETE # 删除
CREATE, DROP, RELOAD, SHUTDOWN,PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER,CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE,REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTERROUTINE, CREATE USER, EVENT, TRIGGER, CREATE TABLESPACE

# 权限分配格式
grant 权限,权限 on 库.表 用户@'主机域' identified by '密码';

# 企业中给开发分配用户
## 一般分配
grant insert,select,update,delete on 库.表 to 用户@'主机域' identified by '密码';
## 规范分配
grant insert,select,update on 库.表 to 用户@'主机域' identified by '密码';

# 脱敏：脱离敏感信息
## 创建库并设定库的字段
create table test(id int,name varchar(10),cardid int,phone bigint);
## 向表中插入数据
insert into test values(1,'heqingwen',123456789,1363658138);
Query OK, 1 row affected (0.00 sec)
## 向表中插入数据
insert into test values(2,'zls',456123789,13012345678);
Query OK, 1 row affected (0.00 sec)
## 创建用户并授予整个表的访问权限
grant insert,select,update on zls.* to zls_test2@'%' identified by '123';
## 创建用户并授予访问权限(id,name,phone)
grant insert,select(id,name,phone),update on zls.test to zls_test3@'%' identified by '123';
## 查看zls_test3的权限
show grants for zls_test3@'%' ;
+-----------------------------------------------------------------------------------+
| Grants for zls_test3@%                                                            |
+-----------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'zls_test3'@'%'                                             |
| GRANT SELECT (phone, id, name), INSERT, UPDATE ON `zls`.`test` TO 'zls_test3'@'%' |
+-----------------------------------------------------------------------------------+

授权的作用域：
*.* 所有库，所有表
库.* 指定库，所有表 // 单库级别
库.表 指定库，指定表 // 单表级别

wordpress.*

企业用户管理问题

企业中给开发分配用户的流程

1
2
3
4
5
6
7
8
9
10
# 常规分配
grant insert,select(id,name,phone),update on zls.test to
用户名@'%' identified by '123';

# 流程
1.操作哪一个库
2.针对哪个表有权限还是该库下的所有表
3.用户名
4.密码
5.申请邮件或工单（企业邮箱，钉钉，企业微信）

实验思考问题：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
# 创建wordpress数据库
create database wordpress;

# 使用wordpress库
use wordpress;

# 创建t1、t2表
create table t1 (id int);
create table t2 (id int);

# 创建blog库
create database blog;

# 使用blog库
use blog;

# 创建t1表
create table tb1 (id int);
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| blog               |
| wordpress          |
+--------------------+
mysql> show tables from blog;
+----------------+
| Tables_in_blog |
+----------------+
| tb1            |
+----------------+
mysql> show tables from wordpress;
+---------------------+
| Tables_in_wordpress |
+---------------------+
| t1                  |
| t2                  |
+---------------------+

# 授权wordpress@'10.0.0.5%'用户针对所有库下的所有表有select权限
grant select on *.* to wordpress@'10.0.0.5%' identified by '123';

# 授权wordpress@'10.0.0.5%'用户针对wordpress库下的所有表有insert,delete,update权限
grant insert,delete,update on wordpress.* to wordpress@'10.0.0.5%' identified by '123';

# 授权wordpress@'10.0.0.5%'用户针对wordpress下的t1表有所有权限
grant all on wordpress.t1 to wordpress@’10.0.0.5%’ identified by '123';

# 问：
一个客户端程序使用wordpress用户登陆到10.0.0.51的MySQL后，
## 对t1表的管理能力？
1 select 2 insert,delete,update 3 all 1 + 2 + 3

## 对t2表的管理能力？
1 select 2 insert,delete,update 1 + 2
## 对tb1表的管理能力？
1 select

# 标准用户分配
grant select,update,insert on 程序库.* to dev@'主机域' identified by '123';

总结：

如果在不同级别都包含某个表的管理能力时，权限是相加关系。
但是我们不推荐在多级别定义重复权限。
最常用的权限设定方式是单库级别授权，即：wordpress.*