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 ,内容好理解,就是容易忘需要多看多记。