第四章作业

6.
要先建好两个表

DROP TABLE IF EXISTS STUDENT;
DROP TABLE IF EXISTS CLASS;

CREATE TABLE STUDENT(
ID CHAR(10),
SNAME CHAR(10),
SAGE INT,
SSEC CHAR(9),
SHOME CHAR(10),
SCID CHAR(10)
);

CREATE TABLE CLASS(
CID CHAR(10),
CNAME CHAR(10),
CHEAD CHAR(10),
CMON CHAR(10)
);

(1)授权用户U1对两个表的所有权限，并可给其他用户授权

GRANT ALL PRIVILEGES
ON TABLE STUDENT,CLASS
TO U1
WITH GRANT OPTION;

注意上面是标准SQL中的使用，而在T-SQL中要分开授权,且不能写TABLE

GRANT ALL PRIVILEGES
ON STUDENT
TO U1
WITH GRANT OPTION;

GRANT ALL PRIVILEGES
ON CLASS
TO U1
WITH GRANT OPTION;

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

GRANT SELECT,UPDATE(SHOME) 
ON STUDENT
TO U2;

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

GRANT SELECT
ON CLASS
TO PUBLIC;

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

GRANT SELECT,UPDATE
ON STUDENT
TO R1;

(5) 将角色R1授予用户U1,并且U1可继续授权给其他角色。

GRANT R1
TO U1
WITH GRANT OPTION;

这条语句在T-SQL中不适用，应该使用

ALTER ROLE R1
ADD MEMBER U1;

7.
首先要建立两个关系模式

CREATE TABLE WORKERS(
WID CHAR(10),
WNAME CHAR(10),
WAGE INT,
WPOST CHAR(10),
WMONN INT,
DID CHAR(10)
);

CREATE TABLE DEP(
DID CHAR(10),
DNAME CHAR(10),
DBOSS CHAR(10),
DHOME CHAR(10),
DTEL CHAR(15)
);

（1）用户U3对两个表有SELECT权限
标准SQL

GRANT SELECT
ON TABLE WORKERS,DEP
TO U3:

T-SQL

GRANT SELECT
ON WORKERS
TO U3;
GRANT SELECT
ON DEP
TO U3;

(2)用户U4对两个表有INSERT 和DELETE 权限

GRANT INSERT,DELETE
ON WORKERS
TO U4;
GRANT INSERT,DELETE
ON DEP
TO U4;

(3)每个职工对职工表有SELECT权限
因为要保证每个职工只能看到自己的记录，所以要先建一个视图保证每个职工只能看到自己的记录

CREATE VIEW WUSERS
AS
SELECT *
FROM WORKERS
WHERE WID=USER;

然后在这个视图上给所有职工授权

GRANT SELECT
ON WUSERS
TO PUBLIC;

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

GRANT select,update(WMONN)
on WORKERS
to U5;

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

GRANT ALTER 
ON WORKERS
TO U6;
GRANT ALTER
ON DEP
TO U6;

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

GRANT ALL PRIVILEGES
ON WORKERS
TO U7
WITH GRANT OPTION;

GRANT ALL PRIVILEGES
ON DEP
TO U7
WITH GRANT OPTION;

(7)用户WANG具有从每个部门职工中SELECT最高工资，最低工资，平均工资的权限，他不能查看每个人的工资。
这道题仍然要先建个视图

CREATE VIEW SALARY
AS
SELECT MAX(WMONN)MAXS,MIN(WMONN)MINS,AVG(WMONN)AVGS
FROM WORKERS,DEP
WHERE WORKERS.DID=DEP.DID
GROUP BY WORKERS.DID

然后进行授权

GRANT SELECT
ON SALARY
TO WANG;

8.针对习题7中（1）到（7）的每一种情况，撤销各用户所授予的权限
（1）

REVOKE SELECT
ON WORKER
FROM U3;
REVOKE SELECT
ON DEP
FROM U3;

(2)

REVOKE  INSERT,DELETE
ON WORKERS
FROM U4;
REVOKE INSERT,DELETE
ON DEP
FROM U4;

(3)

REVOKE SELECT
ON WUSERS
FROM PUBLIC;
DROP VIEW WUSERS

(4)

REVOKE select,update(WMONN)
on WORKERS
FROM U5;

(5)

REVOKE ALTER 
ON WORKERS
FROM U6;
REVOKE ALTER
ON DEP
FROM U6;

(6)

REVOKE ALL PRIVILEGES
ON WORKERS
FROM U7;
REVOKE ALL PRIVILEGES
ON DEP
FROM U7;

(7)

REVOKE SELECT
ON SALARY
FROM WANG;
DROP VIEW SALARY;

至此第四章的内容已经全部完成了，第四章其实主要的内容就是授权和收回，而这两个用法的主要形式是相似的，一个是GRANT TO,一个是REVOKE FROM ，内容好理解，就是容易忘需要多看多记。