第四章作业

一、习题六

对下列两个关系模式：
学生(学号，姓名，年龄，性别，家庭住址，班级号)
班级(班级号，班级名，班主任，班长)
首先需创建表

CREATE TABLE Class     
(Cno INT PRIMARY KEY, 
Cname CHAR(20) UNIQUE,      
Teacher CHAR(20),
Monitor Char(20);

CREATE TABLE Student1       
(Sno CHAR(9) PRIMARY KEY, 
Sname CHAR(20) UNIQUE,      
Sage SMALLINT,
Ssex CHAR(2),
Diraction CHAR(20),
Cno INT,
FOREIGN KEY(Cno) REFERENCES Class(Cno)
);
);

创建结果：

使用GRANT语句完成下列授权功能：
(1)授予用户U1对两个表的所有权限，并可给其他用户授权。
标准SQL语句：
代码：

GRANT ALL PRIVILEGES
ON TABLE Student1,Class
TO U1;

T-SQL语句：
代码：

GRANT ALL PRIVILEGES
ON Student1
TO U1
GRANT ALL PRIVILEGES
ON Class
TO U1;

授权结果：

(2)授予用户U2对学生表具有查看权限，对家庭住址具有更新权限。
标准SQL语句：
代码：

GRANT SELECT,UPDATE(Diraction)
ON TABLE Student1
TO U2;

T-SQL语句：
代码：

GRANT SELECT,UPDATE(Diraction)
ON Student1
TO U2;

授权结果：

(3)将对班级表查看权限授予所有用户。
标准SQL语句：
代码：

GRANT SELECT
ON TABLE Class
TO PUBLIC;

T-SQL语句：
代码：

GRANT SELECT
ON Class
TO PUBLIC;

授权结果：

(4)将对学生表的查询、更新权限授予角色R1。
标准SQL语句：
代码：

CREATE ROLE R1;

GRANT SELECT,UPDATE
ON TABLE Student1
TO R1
WITH GRANT OPTION;;

T-SQL语句：
代码：

GRANT SELECT,UPDATE
ON TABLE Student1
TO R1
WITH GRANT OPTION;

授权结果：

(5)将角色R1授予用户U1,并且U1可继续授权给其他角色。
标准SQL语句：
代码：

GRANT R1
TO U1
WITH ADMIN OPTION;

T-SQL语句：
代码：

EXEC sp_addrolemember 'R1','U1';

授权结果：

二、习题七

今有以下两个关系模式：
职工(职工号，姓名，年龄，职务，工资，部门号)
部门(部门号，名称，经理名，地址，电话号)
请用SQL的GRANT和REVOKE语句(加上视图机制)完成以下授权定义或存取控制功能：
首先需创建表：

CREATE TABLE Department
(Deptno INT PRIMARY KEY,
Dname CHAR(10),
Manager CHAR(20),
Dirction CHAR(20),
Number CHAR(11)
);
GO

CREATE TABLE Staff
(Stno CHAR(9) PRIMARY KEY,
Stname CHAR(10),
Stage CHAR(20),
Post CHAR(20),
Wages INT,
Deptno INT,
FOREIGN KEY(Deptno) REFERENCES Department(Deptno)
);

创建结果：

创建用户：

(1)用户王明对两个表有SELECT权限。
标准SQL语句：
代码：

GRANT SELECT
ON TABLE Staff,Department
TO 王明;

T-SQL语句：
代码：

GRANT SELECT
ON Staff
TO 王明;
GRANT SELECT
ON Department
TO 王明;

授权结果：

(2)用户李勇对两个表有INSERT和DELETE权限。
标准SQL语句：
代码：

GRANT INSERT,DELETE
ON TABLE Staff,Department
TO 李勇;

T-SQL语句：
代码：

GRANT INSERT,DELETE
ON Staff
TO 李勇;
GRANT INSERT,DELETE
ON Department
TO 李勇;

授权结果：

(3) 每个职工只对自己的记录有SELECT权限。
(PS：不太会！)
代码：

GRANT SELECT
ON Staff
WHEN USER()=Stname
TO ALL;

(4)用户刘星对职工表有SELECT权限，对工资字段具有更新权限。
标准SQL语句：
代码：

GRANT SELECT,UPDATE(Wages)
ON TABLE Staff
TO 刘星;

T-SQL语句：
代码：

GRANT SELECT,UPDATE(Wages)
ON Staff
TO 刘星;

授权结果：

(5)用户张新具有修改这两个表的结构的权限。
标准SQL语句：
代码：

GRANT ALTER
ON TABLE Staff,Department
TO 张新;

T-SQL语句：
代码：

GRANT ALTER
ON Staff
TO 张新;
GRANT ALTER
ON Department
TO 张新;

授权结果：

(6)用户周平具有对两个表的所有权限(读、插、改、删数据)，并具有给其他用户授权的权限
标准SQL语句：
代码：

GRANT INSERT,SELECT,DELETE,UPDATE
ON TABLE Staff,Department
TO 周平;

T-SQL语句：
代码：

GRANT INSERT,SELECT,DELETE,UPDATE
ON Staff
TO 周平;
GRANT INSERT,SELECT,DELETE,UPDATE
ON Department
TO 周平;

授权结果：

(7)用户杨兰具有从每个部门职工中SELECT最高工资、最低工资、平均工资的权限，她不能查看每个人的工资。
标准SQL语句：
代码：

CREATE VIEW S_Staff --建立视图
AS
SELECT MAX(Wages) MAX,MIN(Wages) MIN,AVG(Wages) AVG
FROM Staff
GROUP BY DepTno;
GRANT SELECT
ON S_Staff
TO 杨兰
WITH GRANT OPTION;

T-SQL语句：
代码：

CREATE VIEW S_Staff --建立视图
AS
SELECT MAX(Wages) MAX,MIN(Wages) MIN,AVG(Wages) AVG
FROM Staff
GROUP BY DepTno;
GO
GRANT SELECT
ON S_Staff
TO 杨兰
WITH GRANT OPTION;

授权结果：

三、习题八

针对习题7中(1) ~ (7) 的每一种情况，撤销各用户所授予的权限。
(1)收回用户王明对两个表有SELECT权限。
标准SQL语句：
代码：

REVOKE SELECT
ON TABLE Staff,Department
FROM 王明;

T-SQL语句：
代码：

REVOKE SELECT
ON Staff
FROM 王明;
REVOKE SELECT
ON Department
FROM 王明;

(2)收回用户李勇对两个表有INSERT和DELETE权限。
标准SQL语句：
代码：

REVOKE INSERT,DELETE
ON TABLE Staff,Department
FROM 李勇;

T-SQL语句：
代码：

REVOKE INSERT,DELETE
ON Staff
FROM 李勇;
REVOKE INSERT,DELETE
ON Department
FROM 李勇;

(3)收回 每个职工只对自己的记录有SELECT权限。
(PS：不太会！)
代码：

REVOKE SELECT
ON Staff
WHEN USER()=Stname
FROM ALL;

(4)用户刘星对职工表有SELECT权限，对工资字段具有更新权限。
标准SQL语句：
代码：

REVOKE SELECT,UPDATE(Wages)
ON TABLE Staff
FROM 刘星;

T-SQL语句：
代码：

REVOKE SELECT,UPDATE(Wages)
ON Staff
FROM 刘星;

(5)收回用户张新具有修改这两个表的结构的权限。
标准SQL语句：
代码：

REVOKE ALTER
ON TABLE Staff,Department
FROM 张新;

T-SQL语句：
代码：

REVOKE ALTER
ON Staff
FROM 张新;
REVOKE ALTER
ON Department
FROM 张新;

(6)收回用户周平具有对两个表的所有权限(读、插、改、删数据)，并具有给其他用户授权的权限
标准SQL语句：
代码：

REVOKE INSERT,SELECT,DELETE,UPDATE
ON TABLE Staff,Department
FROM 周平;

T-SQL语句：
代码：

REVOKE INSERT,SELECT,DELETE,UPDATE
ON Staff
FROM 周平;
REVOKE INSERT,SELECT,DELETE,UPDATE
ON Department
FROM 周平;

(7)收回用户杨兰具有从每个部门职工中SELECT最高工资、最低工资、平均工资的权限，她不能查看每个人的工资。
标准SQL语句：
代码：

REVOKE SELECT
ON S_Staff
FROM 杨兰 CASCADE;

T-SQL语句：
代码：

REVOKE SELECT
ON S_Staff
FROM 杨兰 CASCADE;

第七大题第三小题还是需要理解思考一下，第七小题可以用建立视图进行操作。
OVER!!!