一、习题六
对下列两个关系模式:
学生(学号,姓名,年龄,性别,家庭住址,班级号)
班级(班级号,班级名,班主任,班长)
首先需创建表
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!!!