数据库原理与应用
1 / 7
0

准备数据

情景　教务处要给几位老师开数据库账号。动手之前，先把练习用的 teaching 库恢复成标准数据，再清掉以前建过的同名账号。

  1. root 在 Workbench 首页双击 root 的连接，打开 SQL 编辑区。这个标签页整节课都开着。

  2. root 复制下面整段脚本，粘贴进编辑区，按 Ctrl+Shift+Enter 全部执行。

    root 中整段执行下载 .sql
    -- 第 8 章上机练习：准备数据（用 root 整段执行，可以反复执行）

-- 1. 删除本练习会创建的账号和角色
DROP USER IF EXISTS 'jiaowu'@'localhost', 'fudao'@'localhost',
  'zhuli'@'localhost', 't_liu'@'localhost';
DROP ROLE IF EXISTS 'r_teacher';

-- 2. 重建 teaching 库
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');

-- 3. 检查数据
SELECT COUNT(*) AS 学生数 FROM s;
SELECT COUNT(*) AS 教师数 FROM t;
SELECT COUNT(*) AS 选课数 FROM sc;

    应看到最后三个结果依次是 8、6、20。

1

创建用户

情景　教务处新来三位同事：教务员、辅导员、学生助理。先给每人开一个只能在本机登录的账号，就像先办三张工卡。

  1. root 用一条语句创建三个用户，都只能从本机登录：jiaowu 口令 jw123，fudao 口令 fd123，zhuli 口令 zl123。

    格式CREATE USER '用户名'@'localhost' IDENTIFIED BY '口令', '用户名'@'localhost' IDENTIFIED BY '口令', …;

    看答案
    root 中执行
    CREATE USER 'jiaowu'@'localhost' IDENTIFIED BY 'jw123',
            'fudao'@'localhost'  IDENTIFIED BY 'fd123',
            'zhuli'@'localhost'  IDENTIFIED BY 'zl123';

    应看到执行成功，没有报错。

  2. root 查看这三个用户在不在。

    格式SELECT User, Host FROM mysql.user WHERE User IN (…);

    看答案
    root 中执行
    SELECT User, Host FROM mysql.user
WHERE User IN ('jiaowu', 'fudao', 'zhuli');

    应看到查到 3 行，Host 都是 localhost。

  3. root 学生助理觉得口令太简单，把 zhuli 的口令改成 zl456。

    格式ALTER USER '用户名'@'主机' IDENTIFIED BY '新口令';

    看答案
    root 中执行
    ALTER USER 'zhuli'@'localhost' IDENTIFIED BY 'zl456';

    应看到执行成功。以后 zhuli 要用 zl456 登录。

2

新用户能做什么

情景　学生助理拿到账号，兴冲冲地登录，却发现什么都查不了。我们换成他的账号看看为什么。

  1. zhuli 用 zhuli（口令 zl456）新建一个连接，并打开它。

    做法首页 MySQL Connections 右边点 ⊕ → Connection Name、Username 都填 zhuli → Test Connection，输入 zl456 → OK → 双击打开。

    应看到多了一个标签页，标题是 zhuli。

  2. zhuli 看看 zhuli 能看到哪些数据库。

    zhuli 中执行
    SHOW DATABASES;

    应看到只有 information_schema 和 performance_schema，看不到 teaching。

  3. zhuli 试着进入 teaching 库。

    zhuli 中执行
    USE teaching;

    应看到报错 1044。新用户只能登录，还没有任何权限——这正是下一项要解决的。

3

授权

情景　三个人要的权限不一样：教务员管整个库；辅导员只看学生；学生助理只能看老师的工号和姓名，不能看工资。

  1. root 教务员 jiaowu 管整个 teaching 库：能查询、插入、修改、删除。

    格式GRANT 权限1, 权限2, … ON 库名.* TO '用户名'@'主机';

    看答案
    root 中执行
    GRANT SELECT, INSERT, UPDATE, DELETE ON teaching.* TO 'jiaowu'@'localhost';

    应看到执行成功。

  2. root 辅导员 fudao 只能查询学生表 s。

    格式GRANT 权限 ON 库名.表名 TO '用户名'@'主机';

    看答案
    root 中执行
    GRANT SELECT ON teaching.s TO 'fudao'@'localhost';

    应看到执行成功。

  3. root 学生助理 zhuli 只能看教师表 t 的 tno、tn 两列。

    格式GRANT SELECT(字段1, 字段2) ON 库名.表名 TO '用户名'@'主机';

    看答案
    root 中执行
    GRANT SELECT(tno, tn) ON teaching.t TO 'zhuli'@'localhost';

    应看到执行成功。

  4. root 查看三个人的权限。

    格式SHOW GRANTS FOR '用户名'@'主机';

    看答案
    root 中执行
    SHOW GRANTS FOR 'jiaowu'@'localhost';
SHOW GRANTS FOR 'fudao'@'localhost';
SHOW GRANTS FOR 'zhuli'@'localhost';

    应看到每人两行：第一行是 GRANT USAGE …，第二行是刚才授的权限。

4

检验权限

情景　权限给出去了，到底管得对不对？换上他们三个人的账号，一条条试。

  1. 把 zhuli 的标签页关掉，再双击重新打开（权限改了要重新连接）；再给 fudao（fd123）和 jiaowu（jw123）各建一个连接。

    应看到现在开着 root、zhuli、fudao、jiaowu 四个标签页。

  2. zhuli 查教师的工号和姓名。

    zhuli 中执行
    SELECT tno, tn FROM teaching.t;

    应看到查到 6 行。

  3. zhuli 再试着查工资。

    zhuli 中执行
    SELECT tn, sal FROM teaching.t;

    应看到报错 1143：sal 这一列没有授权，整句都不执行。

  4. fudao 查学生表。

    fudao 中执行
    SELECT * FROM teaching.s;

    应看到查到 8 行。

  5. fudao 再试着查课程表。

    fudao 中执行
    SELECT * FROM teaching.c;

    应看到报错 1142：课程表 c 没有授权给 fudao。

  6. jiaowu 往课程表加一门课。

    jiaowu 中执行
    INSERT INTO teaching.c VALUES ('c9', '数据库原理', 48);

    应看到插入成功。库级权限对 teaching 中的每张表都有效。

5

收回权限

情景　教务员误删过一次数据，教务处决定：以后他只能查、加、改，不能删。账号保留，只收回删除权限。

  1. root 收回 jiaowu 在 teaching 库上的 DELETE 权限，再查看他的权限。

    格式REVOKE 权限 ON 库名.* FROM '用户名'@'主机';　和 GRANT 一样，只是 TO 换成 FROM

    看答案
    root 中执行
    REVOKE DELETE ON teaching.* FROM 'jiaowu'@'localhost';
SHOW GRANTS FOR 'jiaowu'@'localhost';

    应看到teaching.* 那一行只剩 SELECT, INSERT, UPDATE。

  2. jiaowu 关掉 jiaowu 的标签页再重新打开，试着删掉刚加的 c9。

    jiaowu 中执行
    DELETE FROM teaching.c WHERE cno = 'c9';

    应看到报错 1142：DELETE command denied。权限已经收回了。

  3. root 由 root 删掉 c9，把数据恢复原样。

    看答案
    root 中执行
    DELETE FROM teaching.c WHERE cno = 'c9';

    应看到删除 1 行。

6

角色

情景　任课老师有几十位，权限都一样。教务处决定做一个「任课教师」角色，先拿刘老师试一试——注意看「分配了」和「激活了」有什么不同。

  1. root 创建角色 r_teacher，给它选课表 sc 的查询权限。

    格式CREATE ROLE '角色名';　GRANT 权限 ON 库名.表名 TO '角色名';

    看答案
    root 中执行
    CREATE ROLE 'r_teacher';
GRANT SELECT ON teaching.sc TO 'r_teacher';

    应看到两句都执行成功。

  2. root 创建刘老师的账号 t_liu（只能本机登录，口令 liu123），把角色分给他。

    格式GRANT '角色名' TO '用户名'@'主机';　分角色时没有 ON

    看答案
    root 中执行
    CREATE USER 't_liu'@'localhost' IDENTIFIED BY 'liu123';
GRANT 'r_teacher' TO 't_liu'@'localhost';

    应看到两句都执行成功。

  3. t_liu 给 t_liu 建连接并打开，看看当前角色，再查选课表。

    t_liu 中执行
    SELECT CURRENT_ROLE();
SELECT COUNT(*) FROM teaching.sc;

    应看到第一句是 NONE，第二句报错 1142：角色分给他了，但还没激活。

  4. root 激活：把 r_teacher 设为 t_liu 的默认角色。

    格式SET DEFAULT ROLE '角色名' TO '用户名'@'主机';

    看答案
    root 中执行
    SET DEFAULT ROLE 'r_teacher' TO 't_liu'@'localhost';

    应看到执行成功。

  5. t_liu 关掉 t_liu 的标签页再重新打开，把第 3 步的两句再执行一次。

    t_liu 中执行
    SELECT CURRENT_ROLE();
SELECT COUNT(*) FROM teaching.sc;

    应看到第一句是 `r_teacher`@`%`，第二句查到 20。激活以后，权限才生效。

练习结束

需要的话，用下面的语句删掉本次练习建的账号和角色。

root 中执行（可选）
DROP USER IF EXISTS 'jiaowu'@'localhost', 'fudao'@'localhost',
  'zhuli'@'localhost', 't_liu'@'localhost';
DROP ROLE IF EXISTS 'r_teacher';
回看理论讲义返回第 8 章