数据库第四章习题作业

6.对下列两个关系模式： 学生（学号、班级、年龄、性别、家庭住址、班级号） 班级（班级号，班级名，班主任，班长）。 使用GRANT语句完成以下授权功能：

（1）授予用户U1对两个表的所有权限，并可给其他用户授权。

第一步，新建题目中的表

CREATE TABLE Class
(Classnum INT PRIMARY KEY,
Classname CHAR(4) UNIQUE NOT NULL,
Teacher CHAR(6) NOT NULL,
Monitor CHAR(6) NOT NULL
)
CREATE TABLE Student_A
(Sno CHAR(11) PRIMARY KEY,
Sname CHAR(6) NOT NULL,
Sage SMALLINT NOT NULL,
Ssex CHAR(2) CHECK (Ssex='男' OR Ssex='女'),
Saddress CHAR(20),
Classnum INT ,
FOREIGN KEY (Classnum) REFERENCES Class(Classnum)
);

第二步，新建用户U1：


第三步，授权

GRANT ALL PRIVILEGES
ON Class
TO U1
WITH GRANT OPTION;
GRANT ALL PRIVILEGES
ON Student_A
TO U1
WITH GRANT OPTION;

(2)授予用户U2对学生表具有查看权限，对家庭住址具有更新权限。

GRANT SELECT,UPDATE(Saddress)
ON Student_A
TO U2;

(3)将对班级表查看权限授予所有用户。

GRANT SELECT
ON Student_A
TO PUBLIC;

(4)将对学生表的查询、更新权限授予角色。

首先，新建角色。

CREATE ROLE R1;

然后，授权

GRANT SELECT,UPDATE
ON Student_A
TO R1;

（5）将角色R1授予用户U1，并且U1可继续授权给其他角色。

GRANT R1
TO U1
WITH GRANT OPTION;

但是，无法实现

7.今有以下两个关系模式： 职工（职工号，姓名，年龄，职务，工资，部门号）

部门（部门号，名称，经理名，地址，电话号）

请用SQL的GRANT和REVOKE语句（加上视图机制）完成以下授权定义或存取控制功能：

（1）用户王明对两个表有SELECT权限。

首先，建表

CREATE TABLE Department
(Sdeptnum CHAR(6) PRIMARY KEY,
Dname CHAR(5) NOT NULL,
Manager CHAR(8) NOT NULL,
Address CHAR(15),
PH CHAR(11)
);
CREATE TABLE Staff(
Sno CHAR(11) PRIMARY KEY,
Sname CHAR(10) UNIQUE NOT NULL,
Sage INT,
Sjod CHAR(5),
Salary INT,
Sdeptnum CHAR(6) REFERENCES  Department(Sdeptnum)
);

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

（2）用户李勇对两个表有INSERT和DELECT权限。

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

（3）每个职工只对自己的记录有SELECT权限。

先建立可查询的视图

CREATE VIEW USERS
AS
SELECT *
FROM Staff
WHERE Staff.Sno=USER;

GRANT SELECT
ON USERS
TO PUBLIC;

(4)用户刘星对职工表有SELECT权限，对工资字段具有更新权限。

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

(5)用户张新具有修改这两个表的结构的权限。

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

(6)用户周平具有对两个表的所有权限（读、插、改、删数据），并具有给其他用户授权的权限。

GRANT ALL PRIVILEGES
ON Staff
TO 周平
WITH GRANT OPTION;
GRANT ALL PRIVILEGES
ON Department
TO 周平
WITH GRANT OPTION;

但是，暂且忽略吧

（7）用户张兰具有从每个部门职工中SELECT最高工资、最低工资、平均工资的权限，他不能查看每个人的工资。

创建视图

CREATE VIEW Salary_D(dname,maxsal,minsal,avgsal)
AS
SELECT Department.Dname,MAX(Salary),MIN(Salary),AVG(Salary)
FROM Staff,Department
WHERE Staff.Sdeptnum=Department.Sdeptnum
GROUP BY Staff.Sdeptnum,Department.Dname;

注意Department.Dname也要写在group by语句中，且要给视图中变量命名Salary_D(dname,maxsal,minsal,avgsal)
授予权限

GRANT SELECT
ON Salary_D
TO 杨兰;

8.对第7题的情况，撤销各用户所授予的权限。

(1)撤销用户王明对两个表的SELECT权限。

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

(2)撤销用户李勇对两个表的INSERT和DELECT权限。

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

（3）撤销每个职工只对自己的记录的SELECT权限。

REVOKE SELECT
ON USERS
FROM PUBLIC;
DROP VIEW USERS;

注意删除USERS视图。

（4）撤销用户刘星对职工表的SELECT权限，对工资字段的更新权限。

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

（5）撤销用户张新修改这两个表的结构的权限。

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

（6）用户周平具有对两个表的所有权限（读、插、改、删数据），并具有给其他用户授权的权限。

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

注意：周平可以转让权限，报错
so，需要指明cascade，加一个cascade会级联删除所有

（7）撤销用户张兰从每个部门职工中SELECT最高工资、最低工资、平均工资的权限。

REVOKE SELECT
ON Salary_D
FROM 杨兰;
DROP VIEW Salary_D;--记得删除视图Salary_D

注：
1.需要先建表，新建用户，新建角色等操作
2.7.（3）中，需要先建立视图，视图保证了每个用户只对自己的记录有查询功能
3.7.（7）由于Dname在Department表中，所以要写到group by语句中
4.关于cascade的报错
5.撤销权限时，注意删除创建的视图

总结：感觉有难度，看上去简单，做起来难，还是要多动手实践哇