-
10【DCL数据库控制语言】
上一篇：09【多表查询案例】
下一篇：11【事务处理】
10【DCL数据库控制语言】
4.1 用户管理
我们现在默认使用的都是root用户，超级管理员，拥有全部的权限。但是，一个公司里面的数据库服务器上面可能同时运行着很多个项目的数据库。所以，我们应该可以根据不同的项目建立不同的用户，分配不同的权限来管理和维护数据库。
4.1.1 查看用户
- MySQL的所有用户都在mysql数据库中的user表中存储：
select * from mysql.user;- 1
4.1.2 创建用户
- 语法：
CREATE USER '用户名'@'主机名' IDENTIFIED BY '密码';- 1
- 语法说明：
关键字 说明 ‘用户名’ 将创建的用户名 ‘主机名’ 指定该用户在哪个主机上可以登陆，如果是本地用户可用localhost，如果想让该用户可以从任意远程主机登陆，可以使用通配符% ‘密码’ 该用户的登陆密码，密码可以为空，如果为空则该用户可以不需要密码登陆服务器 Tips：用户名、主机名和密码都应该加上单引号
【案例1】：
创建zhangsan用户，只能在localhost这个服务器登录mysql服务器，密码为123456- 创建用户：
create user 'zhangsan'@'localhost' identified by '123456';- 1
- 查看用户：
select * from mysql.user;- 1
- 使用zhangsan账号登录MySQL服务器：
mysql -uzhangsan -p123456- 1
【案例2】：
创建lisi用户可以在任何电脑上登录mysql服务器，密码为admin- 创建用户：
create user 'lisi'@'%' identified by 'admin';- 1
- 查看用户：
select * from mysql.user;- 1
- 使用lisi账号登录MySQL服务器：
mysql -ulisi -padmin- 1
4.1.3 修改密码
1）修改密码
- 语法：
mysqladmin -uroot -p password 新密码- 1
Tips：
- 1）mysqladmin是MySQL服务器提供的cmd命令，和mysql命令一样需要在cmd窗口执行；
- 2）-p后面有个空格，输入password，然后后面输入新密码
【案例1】：
修改root的密码为123：mysqladmin -uroot -p password 123- 1
- 使用新密码登录MySQL服务器：
mysql -uroot -p123- 1
2）破解密码
- 在mysql/bin文件夹下可执行文件，不用登录，但要指定管理员的密码
mysqladmin -uroot -p password 新密码- 1
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-FZYNstQW-1680520492372)(…/…/…/02%25E3%2580%2590MySQL%25E3%2580%2591/03%25E3%2580%2590%25E8%25BF%259E%25E6%258E%25A5%25E6%259F%25A5%25E8%25AF%25A2%25E3%2580%2581%25E5%25AD%2590%25E6%259F%25A5%25E8%25AF%25A2%25E3%2580%2581DCL%25E3%2580%2591/doc/media/77.png#from=url&id=YgLix&originalType=binary&ratio=1&rotation=0&showTitle=false&status=done&style=none&title=)]
Tips：回车后要输出原密码，才能更改成功。如果原密码不正确，则修改失败
在控制台输入：mysql --help- 1
往下滚动，查看MySQL的配置文件存放位置：
MySQL服务启动时，会加载如下几个位置的配置文件（权重从上到下）：- 1）C:\WINDOWS\my.ini
- 2）C:\WINDOWS\my.cnf
- 3）C:\my.ini
- 4）C:\my.cnf
- 5）C:\Program Files\MySQL\MySQL Server 5.7\my.ini
- 6）C:\Program Files\MySQL\MySQL Server 5.7\my.cnf
默认情况下，MySQL的核心配置文件在C:\ProgramData\MySQL\MySQL Server 5.7\my.ini（我们也可以将其复制到上面说的那几个目录）
需要注意的是，默认情况下MySQL会在提供一个文件：C:\Program Files\MySQL\MySQL Server 5.7\my-default.ini，MySQL并不会加载这个文件，这个文件只是MySQL提供给我们复制用的配置文件；
Tips：MySQL只会加载我们前面说到的那几个目录中的指定名称的配置文件
打开C:\ProgramData\MySQL\MySQL Server 5.7\my.ini文件，该配置里面配置很多MySQL系统方面的配置，我们以后会详细讲到
在[mysqld]组下面添加如下配置：skip-grant-tables- 1
以管理员身份运行cmd窗口：
重启MySQL服务：net stop mysql57 net start mysql57- 1
- 2
- 3
使用MySQL客户端登录MySQL（此时不需要输入密码）：
修改root用户密码：-- 切换到mysql数据库 use mysql; -- 修改root用户密码 update user set authentication_string=password('123456') where user='root'; -- 刷新权限 flush privileges; exit;- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
去掉mysql配置文件中的skip-grant-tables配置；
重启MySQL服务：net stop mysql57 net start mysql57- 1
- 2
- 3
使用新密码登录：
4.1.4 删除用户
- 语法：
DROP USER '用户名'@'主机名';- 1
【案例1】：
删除zhangsan、lisi用户：drop user 'zhangsan'@'localhost'; drop user 'lisi'@'%'; select * from mysql.user; -- 查询用户发现已经没有了zhangsan、lisi用户- 1
- 2
- 3
- 4
- 再次使用zhangsan/lisi账号登录MySQL服务器，发现登录失败：
mysql -uzhangsan -padmin- 1
4.2 权限管理
- 创建两张测试表：
drop database if exists test01; use test01; drop table if exists user; create table user( id int, username varchar(30), password int ); insert into user values(1,'root','123'); insert into user values(2,'admin','456'); insert into user values(3,'guest','000'); drop table if exists student; create table student( id int, name varchar(30), age int ); INSERT INTO student VALUES (1, 'zhangsan', 20); INSERT INTO student VALUES (2, 'lisi', 18); INSERT INTO student VALUES (3, 'wangwu', 23);- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 创建4个测试用户：
create user 'zhangsan'@'localhost' identified by 'aaa'; create user 'lisi'@'localhost' identified by 'bbb'; create user 'wangwu'@'localhost' identified by 'ccc'; create user 'zhaoliu'@'localhost' identified by 'ddd';- 1
- 2
- 3
- 4
用户创建之后，没什么任何权限，需要给用户授权
mysql> use test01 ERROR 1044 (42000): Access denied for user 'zhangsan'@'localhost' to database 'test01' mysql>- 1
- 2
- 3
Tips：连切换数据库的权限都没有；
4.2.1 授予权限
- 授权语法说明：
关键字 说明 REVOKE…ON…FROM 撤销授权的关键字 权限 用户的权限，如CREATE、ALTER、SELECT、INSERT、UPDATE、SELECT等，所有的权限则使用ALL 数据库名.表名 对哪些数据库的哪些表，如果要取消该用户对所有数据库和表的操作权限则可用表示，如.* ‘用户名’@‘主机名’ 给哪个用户撤销，要加上单引号。与创建用户时的用户名和主机名要相同。 在MySQL中，权限范围分为4种，分别是：列权限、表权限、数据库权限、全局权限，不同的权限作用范围不一样；
需要注意的是：如果一个用户连select权限都没有，那么对应的update和delete权限也将失效，但insert权限不受影响；1）【案例-1-列权限】
给zhangsan用户分配对test01数据库的student表权限：insert(id,name)、update(name)、delete
Tips：delete属于表权限，不能指定列- 使用root账号给zhangsan账号分配权限：
-- 分配权限 grant insert(id,name),update(name),delete on test01.student to 'zhangsan'@'localhost'; -- 查看权限 show grants for 'zhangsan'@'localhost';- 1
- 2
- 3
- 4
- 5
- 1）登录zhangsan用户：
# 登录zhangsan用户 mysql -uzhangsan -paaa- 1
- 2
- 2）测试权限：
mysql> use test01; # 切换到test01数据库 Database changed mysql> select * from student; # 没有select权限 ERROR 1142 (42000): SELECT command denied to user 'zhangsan'@'localhost' for table 'student' mysql> insert into student values(100,'t1',20); # 不能插入age列 ERROR 1142 (42000): INSERT command denied to user 'zhangsan'@'localhost' for table 'student' mysql> insert into student(id,name) values(100,'t1'); # 可以插入id,name列 Query OK, 1 row affected (0.00 sec) mysql> update student set age=100 where id=1; # 不可以修改age列 ERROR 1143 (42000): SELECT command denied to user 'zhangsan'@'localhost' for column 'id' in table 'student' mysql> update student set name='zs' where id=1; # 连name列也不可以修改(因为zhangsan用户没有select权限) ERROR 1143 (42000): SELECT command denied to user 'zhangsan'@'localhost' for column 'id' in table 'student' mysql> delete from student where id=1; ERROR 1143 (42000): SELECT command denied to user 'zhangsan'@'localhost' for column 'id' in table 'student' mysql>- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 3）使用root账号给zhangsan用户分配select权限：
-- 分配权限 grant select(id,name),update(age) on test01.student to 'zhangsan'@'localhost'; -- 查看权限 show grants for 'zhangsan'@'localhost';- 1
- 2
- 3
- 4
- 5
- 4）不用重新登录，再次测试zhangsan用户的权限：
mysql> select * from student; # 查询整表权限不足 ERROR 1142 (42000): SELECT command denied to user 'zhangsan'@'localhost' for table 'student' mysql> select id,name from student; # 查询id,name字段可以 +------+----------+ | id | name | +------+----------+ | 1 | zhangsan | | 2 | lisi | | 3 | wangwu | | 4 | t1 | | 100 | t1 | +------+----------+ 5 rows in set (0.00 sec) mysql> update student set age=100 where id=1; # 修改age字段,发现权限不足(因为age字段没有select权限) ERROR 1143 (42000): UPDATE command denied to user 'zhangsan'@'localhost' for column 'age' in table 'student' mysql> update student set name='zs' where id=1; # 修改zhangsan字段成功 Query OK, 1 row affected (0.01 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> delete from user where id=1; # delete权限依旧不行 ERROR 1142 (42000): DELETE command denied to user 'zhangsan'@'localhost' for table 'user' mysql>- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 测试完毕删除用户：
drop user 'zhangsan'@'localhost';- 1
2）【案例-2-表权限】
- 使用root账号清空表，插入测试数据：
truncate student; insert into student values(1,'zhangsan',20); insert into student values(2,'lisi',18); insert into student values(3,'wangwu',23);- 1
- 2
- 3
- 4
- 5
给lisi用户分配delete、insert、update权限，该权限针对于student表；
注意：如果一个用户连select权限都没有，那么update、delete权限也将执行不了，但insert权限可以。- 使用root账号给lisi账号分配权限：
-- 分配权限 grant delete,insert,update on test01.student to 'lisi'@'localhost'; -- 查看权限 show grants for 'zhangsan'@'localhost';- 1
- 2
- 3
- 4
- 5
- 1）登录lisi用户：
mysql -ulisi -pbbb- 1
- 2）测试权限：
mysql> use test01; # 切换数据库 Database changed mysql> delete from student where id=1; # 删除权限(权限不足,因为lisi没有select权限) ERROR 1143 (42000): SELECT command denied to user 'lisi'@'localhost' for column 'id' in table 'student' mysql> update student set age=200 where id=1; # 修改权限(权限不足,因为lisi没有select权限) ERROR 1143 (42000): SELECT command denied to user 'lisi'@'localhost' for column 'id' in table 'student' mysql> insert into student values(4,'t1',100); # 插入权限 Query OK, 1 row affected (0.00 sec) mysql> select * from student; # 查询权限(权限不足) ERROR 1142 (42000): SELECT command denied to user 'lisi'@'localhost' for table 'student' mysql>- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 3）使用root账号给lisi用户分配select权限：
grant select on test01.student to 'lisi'@'localhost'; show grants for 'lisi'@'localhost';- 1
- 2
- 3
- 4）不用重新登录，再次测试权限：
mysql> select * from student; +------+----------+------+ | id | name | age | +------+----------+------+ | 1 | zhangsan | 20 | | 2 | lisi | 18 | | 3 | wangwu | 23 | | 4 | t1 | 100 | +------+----------+------+ 4 rows in set (0.00 sec) mysql> update student set age=200 where id=1; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> delete from student where id=1; Query OK, 1 row affected (0.00 sec) mysql> insert into student values(5,'t2',200); Query OK, 1 row affected (0.00 sec) mysql> select * from user; # lisi没有对user表的权限 ERROR 1142 (42000): SELECT command denied to user 'lisi'@'localhost' for table 'user' mysql> use db01; # 切换到db01数据库,发现权限不足 ERROR 1044 (42000): Access denied for user 'lisi'@'localhost' to database 'db01' mysql>- 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
- 测试完毕删除用户：
drop user 'lisi'@'localhost';- 1
3）【案例-3-数据库权限】
- 使用root账号清空表，插入测试数据：
truncate student; insert into student values(1,'zhangsan',20); insert into student values(2,'lisi',18); insert into student values(3,'wangwu',23);- 1
- 2
- 3
- 4
- 5
首先给wangwu用户分配test01数据库中的student所有的权限
- 使用root账号给wangwu账号分配权限：
-- 分配权限 grant all on test01.student to 'wangwu'@'localhost'; -- 查看权限 show grants for 'wangwu'@'localhost';- 1
- 2
- 3
- 4
- 5
- 1）登录wangwu账号：
mysql -uwangwu -pccc- 1
- 2）测试wangwu账号的权限：
mysql> use test01; # 切换到test01数据库 Database changed mysql> select * from student; +------+----------+------+ | id | name | age | +------+----------+------+ | 1 | zhangsan | 20 | | 2 | lisi | 18 | | 3 | wangwu | 23 | +------+----------+------+ 3 rows in set (0.00 sec) mysql> insert into student values(4,'t1',100); Query OK, 1 row affected (0.00 sec) mysql> delete from student where id=1; Query OK, 1 row affected (0.00 sec) mysql> update student set name='ls' where id=2; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> select * from student; +------+--------+------+ | id | name | age | +------+--------+------+ | 2 | ls | 18 | | 3 | wangwu | 23 | | 4 | t1 | 100 | +------+--------+------+ 3 rows in set (0.00 sec) mysql> select * from user; # wangwu只能操作student表,不能操作其他表 ERROR 1142 (42000): SELECT command denied to user 'wangwu'@'localhost' for table 'user' mysql>- 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
- 3）使用root账号扩大wangwu账号的权限：
-- 分配权限 grant all on test01.* to 'wangwu'@'localhost'; -- 查看权限 show grants for 'wangwu'@'localhsot';- 1
- 2
- 3
- 4
- 5
- 4）需要重新切换数据库，才能刷新数据库权限；
更新数据库权限时，需要重新切换数据库权限才能刷新；（当然重新登录也是可以刷新权限的）
如果会话在持有某个数据库的权限时进入了该数据库，那么会话在执行use dbName时拿到的权限就会保存在会话变量中；此后更改了用户的数据库权限（赋予新权限、回收权限等）将不会被刷新；但如果用户在use dbName之前就更改好了数据的权限，那么此时并不需要退出账号来刷新权限；mysql> select * from user; # 没有切换数据库登录权限是无法刷新的 ERROR 1142 (42000): SELECT command denied to user 'wangwu'@'localhost' for table 'user' mysql> exit # 退出登录 Bye mysql> use test01; # 重新切换一下数据库,数据库权限将会刷新 Database changed mysql> select * from user; +------+----------+----------+ | id | username | password | +------+----------+----------+ | 1 | root | 123 | | 2 | admin | 456 | | 3 | guest | 0 | +------+----------+----------+ 3 rows in set (0.00 sec) mysql>- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 测试完毕删除用户：
drop user 'wangwu'@'localhost';- 1
4）【案例-4-全局权限】
- 使用root账号清空表，插入测试数据：
truncate student; insert into student values(1,'zhangsan',20); insert into student values(2,'lisi',18); insert into student values(3,'wangwu',23);- 1
- 2
- 3
- 4
- 5
首先给zhaoliu用户分配test01数据库中的所有表的所有的权限
- 使用root账号给zhaoliu账号分配权限：
-- 分配权限 grant all on test01.* to 'zhaoliu'@'localhost'; -- 查看权限 show grants for 'wangwu'@'localhost'- 1
- 2
- 3
- 4
- 5
- 1）登录zhaoliu账号：
mysql -uzhaoliu -pddd- 1
- 2）测试zhaoliu账号的权限：
mysql> use test01; Database changed mysql> use db01; # 不能操作其他数据库 ERROR 1044 (42000): Access denied for user 'zhaoliu'@'localhost' to database 'db01' mysql>- 1
- 2
- 3
- 4
- 5
Tips：此时zhaoliu账号可以对test01数据库里面的任意表执行任意操作；
- 3）使用root账号扩大zhaoliu账号权限：
-- 分配权限 grant all on *.* to 'zhaoliu'@'localhost'; -- 查看权限 show grants for 'wangwu'@'localhost'- 1
- 2
- 3
- 4
- 5
- 4）需要重新登录，才能刷新全局权限；
mysql> use db01; # 不重新登录权限不会刷新 ERROR 1044 (42000): Access denied for user 'zhaoliu'@'localhost' to database 'db01' mysql> exit # 退出登录 Bye C:\Users\Horizon>mysql -uzhaoliu -pddd # 重新登录 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 16 Server version: 5.7.13-log MySQL Community Server (GPL) Copyright (c) 2000, 2016, 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> use test01; Database changed mysql> use db01; # 可以正常使用其他数据库 Database changed mysql>- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 测试完毕删除用户：
drop user 'zhaoliu'@'localhost';- 1
5）小结
- 权限小结：
- 1）数据库权限范围分为列权限、表权限、数据库权限、全局权限
- 2）如果一个用户没有select权限，那么update和delete权限也会失效
- 3）关于权限刷新：
- 1）用户被分配列权限、表权限时不需要重新登录权限即可刷新；
- 2）分配数据库权限时需要重新切换数据库才能刷新权限；
- 3）分配全局权限时需要重新登录才能刷新权限；
- 权限语法：
# 创建用户 mysql> create user 'test'@'localhost' identified by 'aaa'; Query OK, 0 rows affected (0.00 sec) mysql> show grants for 'test'@'localhost'; +------------------------------------------+ | Grants for test@localhost | +------------------------------------------+ | GRANT USAGE ON *.* TO 'test'@'localhost' | +------------------------------------------+ 1 row in set (0.00 sec) # 列权限 mysql> grant select(id,name) on test01.student to 'test'@'localhost'; Query OK, 0 rows affected (0.00 sec) mysql> show grants for 'test'@'localhost'; +---------------------------------------------------------------------+ | Grants for test@localhost | +---------------------------------------------------------------------+ | GRANT USAGE ON *.* TO 'test'@'localhost' | | GRANT SELECT (name, id) ON `test01`.`student` TO 'test'@'localhost' | +---------------------------------------------------------------------+ 2 rows in set (0.00 sec) # 表权限 mysql> grant select on test01.student to 'test'@'localhost'; Query OK, 0 rows affected (0.00 sec) mysql> show grants for 'test'@'localhost'; +-----------------------------------------------------------------------------+ | Grants for test@localhost | +-----------------------------------------------------------------------------+ | GRANT USAGE ON *.* TO 'test'@'localhost' | | GRANT SELECT, SELECT (name, id) ON `test01`.`student` TO 'test'@'localhost' | +-----------------------------------------------------------------------------+ 2 rows in set (0.00 sec) # 数据库权限 mysql> grant select on test01.* to 'test'@'localhost'; Query OK, 0 rows affected (0.00 sec) mysql> show grants for 'test'@'localhost'; +-----------------------------------------------------------------------------+ | Grants for test@localhost | +-----------------------------------------------------------------------------+ | GRANT USAGE ON *.* TO 'test'@'localhost' | | GRANT SELECT ON `test01`.* TO 'test'@'localhost' | | GRANT SELECT, SELECT (name, id) ON `test01`.`student` TO 'test'@'localhost' | +-----------------------------------------------------------------------------+ 3 rows in set (0.00 sec) # 全局权限 mysql> grant select on *.*to 'test'@'localhost'; Query OK, 0 rows affected (0.00 sec) mysql> show grants for 'test'@'localhost'; +-----------------------------------------------------------------------------+ | Grants for test@localhost | +-----------------------------------------------------------------------------+ | GRANT SELECT ON *.* TO 'test'@'localhost' | | GRANT SELECT ON `test01`.* TO 'test'@'localhost' | | GRANT SELECT, SELECT (name, id) ON `test01`.`student` TO 'test'@'localhost' | +-----------------------------------------------------------------------------+ 3 rows in set (0.00 sec) # 删除用户 mysql> drop user 'test'@'localhost'; Query OK, 0 rows affected (0.00 sec) mysql>- 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
- 61
- 62
- 63
- 64
- 65
- 66
- 67
- 68
- 69
- 70
- 71
4.2.2 撤销权限
- 撤销权限语法：
REVOKE 权限1, 权限2... ON 数据库.表名 FROM '用户名'@'主机名';- 1
- 关键字说明：
关键字 说明 REVOKE…ON…FROM 撤销授权的关键字 权限 用户的权限，如CREATE、ALTER、SELECT、INSERT、UPDATE、SELECT等，所有的权限则使用ALL 数据库名.表名 对哪些数据库的哪些表，如果要取消该用户对所有数据库和表的操作权限则可用表示，如.* ‘用户名’@‘主机名’ 给哪个用户撤销，要加上单引号。与创建用户时的用户名和主机名要相同。
- 1）创建一个用户赋予如下权限：
create user 'xiaohui'@'localhost' identified by 'admin'; -- 列权限 grant select(id,name) on test01.student to 'xiaohui'@'localhost'; -- 表权限 grant select on test01.student to 'xiaohui'@'localhost'; -- 数据库权限 grant select on test01.* to 'xiaohui'@'localhost'; -- 全局权限 grant select on *.* to 'xiaohui'@'localhost';- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 2）查看用户的权限：
show grants for 'xiaohui'@'localhost';- 1
- 3）回收权限：
mysql> revoke select(id,name) on test01.student from 'xiaohui'@'localhost'; # 回收列权限 Query OK, 0 rows affected (0.00 sec) mysql> show grants for 'xiaohui'@'localhost'; +-------------------------------------------------------------+ | Grants for xiaohui@localhost | +-------------------------------------------------------------+ | GRANT SELECT ON *.* TO 'xiaohui'@'localhost' | | GRANT SELECT ON `test01`.* TO 'xiaohui'@'localhost' | | GRANT SELECT ON `test01`.`student` TO 'xiaohui'@'localhost' | +-------------------------------------------------------------+ 3 rows in set (0.00 sec) mysql> revoke select on test01.student from 'xiaohui'@'localhost'; # 回收表权限 Query OK, 0 rows affected (0.00 sec) mysql> show grants for 'xiaohui'@'localhost'; +-----------------------------------------------------+ | Grants for xiaohui@localhost | +-----------------------------------------------------+ | GRANT SELECT ON *.* TO 'xiaohui'@'localhost' | | GRANT SELECT ON `test01`.* TO 'xiaohui'@'localhost' | +-----------------------------------------------------+ 2 rows in set (0.00 sec) mysql> revoke select on test01.* from 'xiaohui'@'localhost'; # 回收数据库权限 Query OK, 0 rows affected (0.00 sec) mysql> show grants for 'xiaohui'@'localhost'; +----------------------------------------------+ | Grants for xiaohui@localhost | +----------------------------------------------+ | GRANT SELECT ON *.* TO 'xiaohui'@'localhost' | +----------------------------------------------+ 1 row in set (0.00 sec) mysql> revoke select on *.* from 'xiaohui'@'localhost'; # 回收全局权限 Query OK, 0 rows affected (0.00 sec) mysql> show grants for 'xiaohui'@'localhost'; +---------------------------------------------+ | Grants for xiaohui@localhost | +---------------------------------------------+ | GRANT USAGE ON *.* TO 'xiaohui'@'localhost' | +---------------------------------------------+ 1 row in set (0.00 sec) mysql>- 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
Tips：在回收数据库权限和全局权限时，被回收权限的账号需要退出重新登录才能刷新最新的权限；
- 测试完毕删除用户：
drop user 'xiaohui'@'localhost';- 1
4.2.3 权限原理
1）权限磁盘表
在MySQL中，每种权限的信息都会在磁盘和内存中存储，具体的存储位置为：
- 列权限：
- 磁盘：mysql.columns_priv表
- 内存：和表权限组成的hash结构column_priv_hash
- 表权限：
- 磁盘：mysql.tables_priv表
- 内存：和列权限组成的hash结构column_priv_hash
- 数据库权限：
- 磁盘：mysql.db表
- 内存：数组acl_dbs
- 全局权限：
- 磁盘：mysql.user表
- 内存：数组acl_user
Tips：只有列权限和表权限粗壮
重新创建一个用户，分别赋予列、表、数据库、全局权限：create user 'xiaohui'@'localhost' identified by 'admin'; -- 列权限 grant select(id,name) on test01.student to 'xiaohui'@'localhost'; -- 表权限 grant select on test01.student to 'xiaohui'@'localhost'; -- 数据库权限 grant select on test01.* to 'xiaohui'@'localhost'; -- 全局权限 grant select on *.* to 'xiaohui'@'localhost';- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 查看磁盘表：
select * from mysql.columns_priv; select * from mysql.tables_priv; select * from mysql.db; select * from mysql.user;- 1
- 2
- 3
- 4
列权限：
mysql> select * from mysql.columns_priv; +-----------+--------+---------+------------+-------------+---------------------+-------------+ | Host | Db | User | Table_name | Column_name | Timestamp | Column_priv | +-----------+--------+---------+------------+-------------+---------------------+-------------+ | localhost | test01 | xiaohui | student | name | 0000-00-00 00:00:00 | Select | | localhost | test01 | xiaohui | student | id | 0000-00-00 00:00:00 | Select | +-----------+--------+---------+------------+-------------+---------------------+-------------+ 2 rows in set (0.00 sec) mysql>- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
表权限：
mysql> select * from mysql.tables_priv; +-----------+--------+-----------+------------+----------------+---------------------+------------+-------------+ | Host | Db | User | Table_name | Grantor | Timestamp | Table_priv | Column_priv | +-----------+--------+-----------+------------+----------------+---------------------+------------+-------------+ | localhost | sys | mysql.sys | sys_config | root@localhost | 2021-06-10 20:29:01 | Select | | | localhost | test01 | xiaohui | student | root@localhost | 0000-00-00 00:00:00 | Select | Select | +-----------+--------+-----------+------------+----------------+---------------------+------------+-------------+ 2 rows in set (0.00 sec) mysql>- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
数据库权限：
mysql> select * from mysql.db\G; *************************** 2. row *************************** Host: localhost Db: test01 # 数据库名 User: xiaohui # 用户名 Select_priv: Y # 查询 Insert_priv: N # 插入 Update_priv: N # 修改 Delete_priv: N # 删除 Create_priv: N # 创建表 Drop_priv: N # 删除表 Grant_priv: N # 赋予表的其他权限 References_priv: N # 创建外键 Index_priv: N # 创建索引 Alter_priv: N # 修改表结构 Create_tmp_table_priv: N # 创建临时表 Lock_tables_priv: N # 锁表 Create_view_priv: N # 创建视图 Show_view_priv: N # 查看视图 Create_routine_priv: N # 创建存储过程和存储函数 Alter_routine_priv: N # 修改存储过程和存储函数 Execute_priv: N # 执行存储过程和存储函数 Event_priv: N # 创建事件 Trigger_priv: N # 创建触发器 2 rows in set (0.00 sec)- 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
全局权限：
mysql> select * from mysql.user\G; *************************** 3. row *************************** Host: localhost User: xiaohui Select_priv: Y Insert_priv: N Update_priv: N Delete_priv: N Create_priv: N Drop_priv: N Reload_priv: N Shutdown_priv: N Process_priv: N File_priv: N Grant_priv: N References_priv: N Index_priv: N Alter_priv: N Show_db_priv: N Super_priv: N Create_tmp_table_priv: N Lock_tables_priv: N Execute_priv: N Repl_slave_priv: N Repl_client_priv: N Create_view_priv: N Show_view_priv: N Create_routine_priv: N Alter_routine_priv: N Create_user_priv: N Event_priv: N Trigger_priv: N Create_tablespace_priv: N ssl_type: ssl_cipher: x509_issuer: x509_subject: max_questions: 0 max_updates: 0 max_connections: 0 max_user_connections: 0 plugin: mysql_native_password authentication_string: *4ACFE3202A5FF5CF467898FC58AAB1D615029441 password_expired: N password_last_changed: 2023-04-03 16:37:59 password_lifetime: NULL account_locked: N 3 rows in set (0.00 sec)- 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
- 测试完毕删除用户：
drop user 'xiaohui'@'localhost';- 1
2）flush privileges
flush privileges命令用于刷新权限；
flush privileges操作会清空权限内存数组，然后从对应的权限磁盘表中读取数据重新构造一个内存数组，也就是以数据表中数据为准，将内存数组重新加载一遍；所以说如果内存中的权限数据和磁盘表中的数据一致的话，flush privileges其实是可以不用做的。
而对于正常的grant/revoke/create user等操作，内存和磁盘中的数据都是同步更新的，所以正常的grant/revoke操作后是不需要flush privileges的。
但是，更改权限、回收权限、创建用户等操作不仅可以使用grant/revoke/create user等命令来完成；同样可以通过修改磁盘权限表来完成；
【创建账号】- 1）使用磁盘表方式创建一个测试账号：
INSERT INTO mysql.user(Host, User, authentication_string, ssl_cipher, x509_issuer, x509_subject) VALUES ('localhost', 'xiaohong', PASSWORD('admin'), '', '', ''); flush privileges; -- 此时一定要刷新权限- 1
- 2
- 3
- 4
上述操作就一定要执行flush privileges;来刷新权限，将磁盘权限表中的数据加载到MySQL服务器内存；
- 2）使用xiaohong账号登录：
mysql -uxiaohong -padmin- 1
【赋权限】
- 使用root账号直接通过修改mysql.user表来赋予权限
-- 注意: 此时的权限是全局权限(因为改的是mysql.user表) update mysql.user set select_priv='Y' where user='xiaohong'; -- 一定要刷新权限,将磁盘权限表中的数据加载到MySQL服务器内存 flush privileges;- 1
- 2
- 3
- 4
- 5
- 重新使用xiaohong账号登录MySQL服务器（因为刚刚修改的是全局权限）：
mysql -uxiaohong -padmin- 1
- 执行查询：
select * from student;- 1
发现权限正常；
-
相关阅读:
Vue项目中ESLint配置（VScode）
可视化 | （一）数据基础及基本数据可视化方法
二聚乳酸-羟基乙酸共聚物聚乙二醇 PLGA-PEG-PLGA
21.Redis系列之缓存穿透、击穿、雪崩
Stream学习2
从Linux的安装到HDFS上传再到MapReduce的词频统计
异步 PHP — 多进程、多线程和协程
点云从入门到精通技术详解100篇-基于补全点云与图像像素级融合的障碍物识别（中）
SpringBoot整合dubbo（一）
Compose 编译器版本和Kotlin版本对应关系
- 原文地址：https://blog.csdn.net/Bb15070047748/article/details/126564635