目录
- 6.对下列两个关系模式: 学生(学号、班级、年龄、性别、家庭住址、班级号) 班级(班级号,班级名,班主任,班长)。 使用GRANT语句完成以下授权功能:
- (1)授予用户U1对两个表的所有权限,并可给其他用户授权。
- (2)授予用户U2对学生表具有查看权限,对家庭住址具有更新权限。
- (3)将对班级表查看权限授予所有用户。
- (4)将对学生表的查询、更新权限授予角色。
- (5)将角色R1授予用户U1,并且U1可继续授权给其他角色。
- 7.今有以下两个关系模式: 职工(职工号,姓名,年龄,职务,工资,部门号)
- 部门(部门号,名称,经理名,地址,电话号)
- (1)用户王明对两个表有SELECT权限。
- (2)用户李勇对两个表有INSERT和DELECT权限。
- (3)每个职工只对自己的记录有SELECT权限。
- (4)用户刘星对职工表有SELECT权限,对工资字段具有更新权限。
- (5)用户张新具有修改这两个表的结构的权限。
- (6)用户周平具有对两个表的所有权限(读、插、改、删数据),并具有给其他用户授权的权限。
- (7)用户张兰具有从每个部门职工中SELECT最高工资、最低工资、平均工资的权限,他不能查看每个人的工资。
- 8.对第7题的情况,撤销各用户所授予的权限。
- (1)撤销用户王明对两个表的SELECT权限。
- (2)撤销用户李勇对两个表的INSERT和DELECT权限。
- (3)撤销每个职工只对自己的记录的SELECT权限。
- (4)撤销用户刘星对职工表的SELECT权限,对工资字段的更新权限。
- (5)撤销用户张新修改这两个表的结构的权限。
- (6)用户周平具有对两个表的所有权限(读、插、改、删数据),并具有给其他用户授权的权限。
- (7)撤销用户张兰从每个部门职工中SELECT最高工资、最低工资、平均工资的权限。
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.撤销权限时,注意删除创建的视图
总结:感觉有难度,看上去简单,做起来难,还是要多动手实践哇