本文是《数据库系统概论》的第四章的部分课后习题，题目来源：【2019-2020春学期】数据库作业12：第四章：数据库安全性 习题
第6题
对下列两个关系模式：
学生（学号，姓名，年龄，性别，家庭住址，班级号）
班级（班级号，班级名，班主任，班长）
?首先我先在STUDENT数据库中创建两张表“Stu”和“Cla”来分别表示学生和班级这两个关系模式：
CREATE TABLE Cla (
CNO VARCHAR(5) PRIMARY KEY,
CNAME VARCHAR(10) NOT NULL,
TEACHER VARCHAR(8) NOT NULL, /*我这里就以TEACHER代指班主任了哈*/
MONITOR VARCHAR(8) NOT NULL
)
CREATE TABLE Stu (
SNO VARCHAR(10) PRIMARY KEY,
SNAME VARCHAR(10) NOT NULL,
AGE INT NOT NULL,
ADDRESS VARCHAR(10) NOT NULL,
CNO VARCHAR(5) REFERENCES Cla(CNO)
)
因为Stu表中的CNO引用了Cla表的CNO，所以要先创建Cla表
使用GRANT语句完成下列授权功能：
(1)授予用户U1对两个表的所有权限，并可给其他用户授权。
（咳咳，注意PRIVILEGES不要写错了哈，我一开始就写错了hhh）
-- 标准SQL
GRANT ALL PRIVILEGES
ON Stu, Cla
TO U1
WITH GRANT OPTION;
-- T-SQL
GRANT ALL PRIVILEGES
ON Stu
TO U1
WITH GRANT OPTION;
GRANT ALL PRIVILEGES
ON Cla
TO U1
WITH GRANT OPTION;
(2)授予用户U2对学生表具有查看权限，对家庭住址具有更新权限。
-- 标准SQL
GRANT SELECT, UPDATE(ADDRESS)
ON TABLE Stu
TO U2;
-- T-SQL
GRANT SELECT, UPDATE(ADDRESS)
ON Stu
TO U2;
?我说明一下：因为这些授权的讲解在我的上一篇博客中写的很详细【吐血整理】数据库的安全性，这里就不再每个题都截图啦~
(3)将对班级表查看权限授予所有用户。
-- 标准SQL
GRANT SELECT
ON TABLE Cla
TO PUBLIC;
-- T-SQL
GRANT SELECT
ON Cla
TO PUBLIC;
(4)将对学生表的查询、更新权限授于角色R1。
-- 标准SQL
GRANT SELECT, UPDATE
ON TABLE Stu
TO R1;
-- T-SQL
GRANT SELECT, UPDATE
ON Stu
TO R1;
(5)将角色R1授予用户U1,并且U1可继续授权给其他角色。
（在SQL Server（T-SQL）中把角色R1授权给U1就是把用户U1添加到角色R1中）
-- 标准SQL
GRANT R1
TO U1
WITH ADMIN OPTION;
-- T-SQL
ALTER ROLE R1 ADD MEMBER U1; /*不过我在使用U1登录之后发现U1是不能继续传播权限的，所以该语句没有继续授予的功能*/
第7题
今有以下两个关系模式:
职工（职工号，姓名，年龄，职务，工资，部门号）
部门（部门号，名称，经理名，地址，电话号）
?同样的，首先我先创建一个数据库COMPANY数据库，然后在其中创建两张表“Staff”和“Dept”来分别表示职工和部门这两个关系模式：
CREATE DATABASE COMPANY;
USE COMPANY;
CREATE TABLE Dept (
DNO VARCHAR(10) PRIMARY KEY,
DNAME VARCHAR(10) NOT NULL,
MNAME VARCHAR(10) NOT NULL, /*经理名*/
ADDRESS VARCHAR(10) NOT NULL,
PHONE INT NOT NULL
)
CREATE TABLE Staff (
SNO VARCHAR(10) PRIMARY KEY,
SNAME VARCHAR(10) NOT NULL,
AGE INT NOT NULL,
DUTY VARCHAR(8) NOT NULL, /*职务*/
SALARY INT NOT NULL,
DNO VARCHAR(10) REFERENCES Dept(DNO)
)
请用SQL的GRANT和REVOKE语句(加上视图机制)完成以下授权定义或存取控制功能：
(1)用户夏雨对两个表有SELECT权限。
-- 标准SQL
GRANT SELECT
ON Staff, Dept
TO 夏雨;
-- T-SQL
GRANT SELECT
ON Staff
TO 夏雨;
GRANT SELECT
ON Dept
TO 夏雨;
我之前创建的夏雨是在STUDENT数据库里的，而现在是在COMPANY数据库中，所以需要新建用户，新建用户就不做介绍啦，我专门有写过一篇博客：SQL Server 创建登录名和用户名【详细介绍】
CREATE LOGIN 夏雨 WITH PASSWORD = 'henry626626', DEFAULT_DATABASE = COMPANY;
CREATE USER 夏雨 FOR LOGIN 夏雨;
新建完成，下面用到的用户也是如此（不过有的登录名已经存在了（U5、U6、U7），所以就只需映射成用户就可以啦~）
(2)用户夏雨对两个表有INSERT和DELETE权限。
-- 标准SQL
GRANT INSERT DELETE
ON Staff, Dept
TO 夏雨;
-- T-SQL
GRANT INSERT, DELETE
ON Staff
TO 夏雨;
GRANT INSERT, DELETE
ON Dept
TO 夏雨;
(3)每个职工只对自己的记录有SELECT权限。
GRANT SELECT
ON TABLE Staff
WHEN USER() = NAME
TO ALL;
这是网上普遍的写法（标准SQL），在SQL Server里尝试，结果果然不行，T-SQL是不支持这么写的，T-SQL中具体如何写我会继续研究，弄清楚后会在第一时间到这里补充上~
(4)用户夏冰雹对职工表有SELECT权限，对工资字段具有更新权限。
-- 标准SQL
GRANT SELECT, UPDATE(SALARY)
ON TABLE Staff
TO 夏冰雹;
-- T-SQL
GRANT SELECT, UPDATE(SALARY)
ON Staff
TO 夏冰雹;
(5)用户U5具有修改这两个表的结构的权限。
（注意是修改表，不是数据，所以用的是ALTER）
-- 标准SQL
GRANT ALTER
ON TABLE Staff, Dept
TO U5;
-- T-SQL
GRANT ALTER
ON Staff
TO U5;
GRANT ALTER
ON Dept
TO U5;
(6)用户U6具有对两个表的所有权限(读、插、改、删数据)，并具有给其他用户授权的权限。
-- 标准SQL
GRANT ALL PRIVILEGES
ON TABLE Staff, Dept
TO U6
WITH GRANT OPTION;
-- T-SQL
GRANT ALL PRIVILEGES
ON Staff
TO U6
WITH GRANT OPTION;
GRANT ALL PRIVILEGES
ON Dept
TO U6
WITH GRANT OPTION;
(7)用户U7具有从每个部门职工中SELECT最高工资、最低工资、平均工资的权限，他不能查看每个人的工资。
（通过视图来进行限制）
CREATE VIEW DeptSalary(DNO, MAXSALARY, MINSALARY, AVGSALARY)
AS
SELECT Dept.DNO, MAX(SALARY), MIN(SALARY), AVG(SALARY)
FROM Staff, Dept
WHERE Staff.DNO = Dept.DNO
GROUP BY Dept.DNO;
-- 标准SQL
GRANT SELECT
ON VIEW DeptSalary
TO U7;
-- T-SQL
GRANT SELECT
ON DeptSalary
TO U7;
第8题
针对习题7中（1）~（7）的每一种情况，撤销各用户所授予的权限：
（1）
-- 标准SQL
REVOKE SELECT
ON Staff, Dept
TO 夏雨;
-- T-SQL
REVOKE SELECT
ON Staff
TO 夏雨;
REVOKE SELECT
ON Dept
TO 夏雨;
（2）
REVOKE INSERT DELETE
ON Staff, Dept
TO 夏雨;
-- T-SQL
REVOKE INSERT, DELETE
ON Staff
TO 夏雨;
REVOKE INSERT, DELETE
ON Dept
TO 夏雨;
可见，现在的夏雨拥有的权限空空如也，啥都没了……
（3）
（同样的，这个题和上面的原题在搞清楚T-SQL怎么写之后会及时补充上~）
REVOKE SELECT
ON TABLE Staff
WHEN USER() = NAME
TO ALL;
（4）
-- 标准SQL
REVOKE SELECT, UPDATE(SALARY)
ON TABLE Staff
TO 夏冰雹;
-- T-SQL
REVOKE SELECT, UPDATE(SALARY)
ON Staff
TO 夏冰雹;
（5）
-- 标准SQL
REVOKE ALTER
ON TABLE Staff, Dept
TO U5;
-- T-SQL
REVOKE ALTER
ON Staff
TO U5;
REVOKE ALTER
ON Dept
TO U5;
（6）
-- 标准SQL
REVOKE ALL PRIVILEGES
ON TABLE Staff, Dept
TO U6 CASCADE;
-- T-SQL
REVOKE ALL PRIVILEGES
ON Staff
TO U6 CASCADE;
REVOKE ALL PRIVILEGES
ON Dept
TO U6 CASCADE;
注意！！！ 加上CASCADE，如果不加的话会被拒绝执行，有如下提示：
（7）
-- 标准SQL
REVOKE SELECT
ON VIEW DeptSalary
TO U7;
-- T-SQL
REVOKE SELECT
ON DeptSalary
TO U7;
-- 删除创建的DeptSalary视图
DROP VIEW DeptSalary;
这章的习题总的来说难度不大，就是标准SQL和T-SQL有很多不一样的地方?，经过这样的习题训练之后对标准SQL和T-SQL的印象都加深了，还是挺有收获的?
· 本章讲的是数据库的安全性，总结篇在这里：【吐血整理】数据库的安全性