第 8 章 · 实验二 · 在第三节之后

权限管理

给用户授予库级、表级和字段级权限，换成这些用户登录检验权限的范围，再练习转授和回收权限。

环　境

MySQL 8.0 和 MySQL Workbench，机房电脑已装好。所有语句在 Workbench 的 SQL 编辑区中执行。

执　行

Ctrl+Enter 执行光标所在的一句，Ctrl+Shift+Enter 执行全部。Mac 上把 Ctrl 换成 ⌘。

注　意

输入法切到英文。表名、字段名不加引号，字符串值才用单引号。

本次实验的顺序　后一个实验用到前一个的结果

实验 2-1授予库级权限
实验 2-2检验库级权限
实验 2-3表级和字段级权限
实验 2-4转授权限
实验 2-5回收权限

第一步

数据准备（必做）

与实验一相同的脚本。执行后本章的用户都被删除，teaching 库恢复教材数据。

整段复制，全部执行下载 .sql

-- 第 8 章实验数据：删除本章用到的用户和角色，并重建教材 teaching 库的五张表
-- 用 root 连接整段执行。三次实验共用，想从头再做时重新执行即可。
DROP USER IF EXISTS 'student'@'localhost', 'teacher1'@'%', 'teacher2'@'localhost',
  'teacher3'@'localhost', 'teacher4'@'localhost', 'teacher10'@'localhost';
DROP ROLE IF EXISTS 'teacher', 'administrator', 'developer'@'localhost';

CREATE DATABASE IF NOT EXISTS teaching DEFAULT CHARACTER SET utf8mb4;
USE teaching;

-- 删除第 7 章实验建的视图
DROP VIEW IF EXISTS s_view, s_sc_c_view, s_maj_view, s_update_view, c_count_view, maj_view, s_sc_view, s_lxy_view;

-- 删除旧的表。先暂时关闭外键检查，库里有别的表引用它们时也能删除
SET FOREIGN_KEY_CHECKS = 0;
DROP TABLE IF EXISTS sc, tc, avgsal, classroom, s, c, t;
SET FOREIGN_KEY_CHECKS = 1;

-- 课程表
CREATE TABLE c (
  cno CHAR(4) NOT NULL,
  cn VARCHAR(30) NOT NULL,
  ct INT,
  PRIMARY KEY (cno)
);
-- 学生表
CREATE TABLE s (
  sno CHAR(4) NOT NULL,
  sn VARCHAR(20) NOT NULL,
  sex CHAR(2),
  age INT,
  maj VARCHAR(30),
  dept VARCHAR(30),
  PRIMARY KEY (sno)
);
-- 教师表
CREATE TABLE t (
  tno CHAR(4) NOT NULL,
  tn VARCHAR(20) NOT NULL,
  sex CHAR(2),
  age INT,
  prof VARCHAR(20),
  maj VARCHAR(30),
  dept VARCHAR(30),
  sal DECIMAL(8,2),
  PRIMARY KEY (tno)
);
-- 选课表
CREATE TABLE sc (
  sno CHAR(4) NOT NULL,
  cno CHAR(4) NOT NULL,
  score DECIMAL(5,2),
  PRIMARY KEY (sno, cno),
  FOREIGN KEY (sno) REFERENCES s (sno),
  FOREIGN KEY (cno) REFERENCES c (cno)
);
-- 授课表
CREATE TABLE tc (
  tno CHAR(4) NOT NULL,
  cno CHAR(4) NOT NULL,
  tcdate DATE,
  PRIMARY KEY (tno, cno),
  FOREIGN KEY (tno) REFERENCES t (tno),
  FOREIGN KEY (cno) REFERENCES c (cno)
);

INSERT INTO c VALUES
('c1','Java程序设计',40), ('c2','程序设计基础',48), ('c3','线性代数',48),
('c4','数据结构',64), ('c5','数据库系统',56), ('c6','数据挖掘',32),
('c7','高等数学',60), ('c8','控制理论',32);

INSERT INTO s VALUES
('s1','王彤','女',18,'计算机','信息学院'),
('s2','苏乐','男',20,'信息','信息学院'),
('s3','林欣怡','女',19,'信息','信息学院'),
('s4','陶然','男',19,'自动化','工学院'),
('s5','魏立','男',17,'数学','理学院'),
('s6','何欣荣','女',21,'计算机','信息学院'),
('s7','赵琳琳','女',19,'数学','理学院'),
('s8','李轩','男',18,'自动化','工学院');

INSERT INTO t VALUES
('t1','刘杨','男',38,'教授','计算机','信息学院',3800.00),
('t2','石丽','女',28,'讲师','计算机','信息学院',2800.00),
('t3','顾伟','男',35,'副教授','计算机','信息学院',3200.00),
('t4','赵礼','男',45,'教授','自动化','工学院',4267.90),
('t5','赵希希','女',33,'副教授','数学','理学院',3332.67),
('t6','张刚','男',30,'讲师','计算机','工学院',2600.00);

INSERT INTO sc VALUES
('s1','c1',90.50), ('s1','c2',88.00),
('s2','c4',70.00), ('s2','c5',57.00), ('s2','c6',81.50), ('s2','c7',NULL),
('s3','c1',75.00), ('s3','c2',76.50), ('s3','c4',85.00),
('s4','c1',93.00), ('s4','c2',82.00), ('s4','c6',NULL), ('s4','c7',68.00),
('s5','c2',79.00), ('s5','c7',84.00),
('s7','c3',77.00), ('s7','c5',62.00), ('s7','c7',100.00),
('s8','c2',66.00), ('s8','c3',96.00);

INSERT INTO tc VALUES
('t1','c1','2021-09-03'), ('t1','c2','2021-09-04'),
('t2','c5','2021-03-06'), ('t2','c6','2021-09-06'),
('t3','c2','2021-03-08'), ('t3','c4','2021-09-08'),
('t4','c3','2021-03-10'),
('t5','c7','2021-09-12'), ('t5','c8','2021-03-15');

2-1

授予库级权限

讲义 · GRANT 讲义 · 授权的例子讲义 · SHOW GRANTS

目的: 创建三个用户，给其中一个授予 teaching 库上的权限，并查看它的权限。
前提: 已用 root 连接执行数据准备。

创建 teacher2、teacher3、teacher4 三个用户，都只能从本机登录，口令分别为 t2123、t3123、t4123
给 teacher4 授予 teaching 库上的 SELECT、CREATE、INSERT 权限
查看 teacher4 的权限，以及 mysql.db 中的记录

参考答案

可以直接复制到 Workbench 执行

CREATE USER 'teacher2'@'localhost' IDENTIFIED BY 't2123',
            'teacher3'@'localhost' IDENTIFIED BY 't3123',
            'teacher4'@'localhost' IDENTIFIED BY 't4123';
GRANT SELECT, CREATE, INSERT ON teaching.* TO 'teacher4'@'localhost';
SHOW GRANTS FOR 'teacher4'@'localhost';
SELECT User, Db, Select_priv, Insert_priv, Create_priv, Delete_priv
FROM mysql.db WHERE User = 'teacher4';

语句解释

GRANT SELECT, CREATE, INSERT	授予的三项权限，用逗号分隔
ON teaching.*	数据库级：teaching 库中的全部表
SHOW GRANTS FOR	查看用户的权限，一行是一条授权
mysql.db	库级权限记在这张表中，Y 表示有这项权限

执行后看：SHOW GRANTS 有两行：GRANT USAGE 和 teaching.* 上的三项权限。mysql.db 中 Delete_priv 为 N。

2-2

检验库级权限

讲义 · 授权的例子

目的: 用 teacher4 登录，检验它能做什么、不能做什么。
前提: 已完成实验 2-1。

按实验 1-3 的方法，用 teacher4（口令 t4123）新建一个连接并打开。

在 teacher4 的连接中，统计学生人数
插入一门课程 c9：数据库原理，48 课时，再查出来
删除 c9
回到 root 的连接，删除 c9

参考答案

在 teacher4 的连接中执行

SELECT COUNT(*) FROM teaching.s;
INSERT INTO teaching.c VALUES ('c9', '数据库原理', 48);
SELECT * FROM teaching.c WHERE cno = 'c9';
-- 没有 DELETE 权限，这一句会报错 1142
DELETE FROM teaching.c WHERE cno = 'c9';

在 root 的连接中执行

DELETE FROM teaching.c WHERE cno = 'c9';

语句解释

teaching.s	表名前面写库名，不用先 USE
INSERT INTO teaching.c …	teacher4 有 INSERT 权限，可以插入
报错 1142	DELETE command denied，teacher4 没有 DELETE 权限

执行后看：查询和插入都成功，删除报错 1142。c9 由 root 删除。

2-3

表级和字段级权限

讲义 · 授权的例子

目的: 授予表级和字段级权限，检验字段级权限只能访问指定的字段。
前提: 已完成实验 2-1。

给 teacher3 授予教师表 t 上的 SELECT、INSERT、UPDATE、DELETE 权限
给 teacher2 授予 t 表 tno、tn 两个字段的 SELECT 权限
查看 teacher2 的权限和 mysql.columns_priv 中的记录
用 teacher2 登录，分别查询 tno、tn 和 tn、sal

参考答案

在 root 的连接中执行

GRANT SELECT, INSERT, UPDATE, DELETE ON teaching.t TO 'teacher3'@'localhost';
GRANT SELECT(tno, tn) ON teaching.t TO 'teacher2'@'localhost';
SHOW GRANTS FOR 'teacher2'@'localhost';
SELECT User, Table_name, Column_name, Column_priv
FROM mysql.columns_priv WHERE User = 'teacher2';

在 teacher2 的连接中执行

SELECT tno, tn FROM teaching.t;
-- sal 不在授权的字段中，这一句会报错 1143
SELECT tn, sal FROM teaching.t;

语句解释

ON teaching.t	表级：只对 teaching 库的 t 表
SELECT(tno, tn)	字段级：只能查询 tno 和 tn 两个字段
mysql.columns_priv	字段级权限记在这张表中，一个字段一行
报错 1143	查询了没有权限的字段 sal。写 SELECT * 会报错 1142

执行后看：columns_priv 中有 tn、tno 两行。teacher2 查 tno、tn 能看到 6 位教师，查 sal 报错 1143。

2-4

转授权限

讲义 · 转授权限

目的: 用 WITH GRANT OPTION 允许 teacher3 把权限转授给别人。
前提: 已完成实验 2-3。

按实验 1-3 的方法，用 teacher3（口令 t3123）新建一个连接。

在 root 的连接中，给 teacher3 授予 t 表的 SELECT、UPDATE 权限，并允许转授
用 teacher3 登录，把 t 表的 SELECT 权限授予 teacher2
用 teacher3 把 s 表的 SELECT 权限授予 teacher2
回到 root 的连接，查看 teacher2 的权限

参考答案

在 root 的连接中执行

GRANT SELECT, UPDATE ON teaching.t TO 'teacher3'@'localhost'
WITH GRANT OPTION;
SHOW GRANTS FOR 'teacher3'@'localhost';

在 teacher3 的连接中执行

GRANT SELECT ON teaching.t TO 'teacher2'@'localhost';
-- teacher3 在 s 表上没有权限，这一句会报错 1142
GRANT SELECT ON teaching.s TO 'teacher2'@'localhost';

在 root 的连接中执行

SHOW GRANTS FOR 'teacher2'@'localhost';

语句解释

WITH GRANT OPTION	允许 teacher3 把它在 t 表上的权限再授予别人
GRANT SELECT ON teaching.t TO teacher2	teacher3 有这项权限，可以转授
报错 1142	teacher3 在 s 表上没有权限，不能转授

执行后看：teacher3 的授权末尾多了 WITH GRANT OPTION。teacher2 的权限中多了整张 t 表的 SELECT。

2-5

回收权限

讲义 · REVOKE 讲义 · 转授权限

目的: 用 REVOKE 回收部分权限和全部权限，看转授出去的权限会不会一起收回。
前提: 已完成实验 2-4。

收回 teacher4 在 teaching 库上的 INSERT 权限
收回 teacher3 的全部权限和转授权
查看 teacher4、teacher3、teacher2 的权限
再收回一次 teacher3 在 teaching 库上的 SELECT 权限

参考答案

可以直接复制到 Workbench 执行

REVOKE INSERT ON teaching.* FROM 'teacher4'@'localhost';
SHOW GRANTS FOR 'teacher4'@'localhost';
REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'teacher3'@'localhost';
SHOW GRANTS FOR 'teacher3'@'localhost';
SHOW GRANTS FOR 'teacher2'@'localhost';
-- teacher3 在 teaching 库上没有库级权限，这一句会报错 1141
REVOKE SELECT ON teaching.* FROM 'teacher3'@'localhost';

语句解释

REVOKE INSERT ON teaching.*	只收回库级的 INSERT，其他权限不变
REVOKE ALL PRIVILEGES, GRANT OPTION	收回这个用户的全部权限和转授权
报错 1141	There is no such grant，要收回的权限没有授予过

执行后看：teacher4 只剩 SELECT、CREATE。teacher3 只剩 USAGE。teacher2 从 teacher3 得到的 SELECT 仍然保留，MySQL 不会连带收回。

上一节第三节　权限管理下一节第四节　角色管理

保山学院·人工智能教研室·曹鼎鼎

权限管理

环 境

执 行

注 意

本次实验的顺序 后一个实验用到前一个的结果

参考答案

语句解释

参考答案

语句解释

参考答案

语句解释

参考答案

语句解释

参考答案

语句解释

环　境

执　行

注　意

本次实验的顺序　后一个实验用到前一个的结果