第十一次实验报告：第四章课后题

第六大题

建表

DROP TABLE IF EXISTS Student
DROP TABLE IF EXISTS Class
CREATE TABLE Class
(
 ClassNum INT PRIMARY KEY,
 ClassName CHAR(5) UNIQUE NOT NULL,
 HeadTeacher CHAR(5) NOT NULL,
 Monitor CHAR(5)
)
CREATE TABLE Student          
(	
 Sno CHAR(9),                      
 Sname CHAR(20),             
 Ssex CHAR(2),
 Sage SMALLINT,
 Sdept CHAR(20),
 Saddress CHAR(20),
 Sclassnum INT FOREIGN KEY
 REFERENCES Class(ClassNum) ON DELETE CASCADE
); 

新建角色R1,用户U1,U2

CREATE USER U1 FOR LOGIN U_1; 
CREATE USER U2 FOR LOGIN U_2; 
CREATE ROLE R1

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

GRANT ALL PRIVILEGES
ON Class
TO U1
WITH GRANT OPTION
GRANT ALL PRIVILEGES
ON Student
TO U1
WITH GRANT OPTION

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

GRANT SELECT,UPDATE(SAddress)
ON Student
TO U2

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

GRANT SELECT
ON Class
TO PUBLIC

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

GRANT SELECT,UPDATE
ON Student
TO R1

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

GRANT R1
TO U1
WITH GRANT OPTION;

第七大题

建表

DROP TABLE IF EXISTS Employee
DROP TABLE IF EXISTS Department
CREATE TABLE Department
(
	ID CHAR(11) PRIMARY KEY,
	Name CHAR(5) UNIQUE NOT NULL,
	Manager CHAR(5) NOT NULL,
	PhoneNumber CHAR(11),
	Salary SMALLINT
)
CREATE TABLE Employee
(
	ID CHAR(11) PRIMARY KEY,
	Name CHAR(5) NOT NULL,
	Age SMALLINT CHECK(Age>0 AND Age<100),
	Salary Money,
	DepartmentId CHAR(11) FOREIGN KEY
	REFERENCES Department(ID) ON DELETE CASCADE
)

创建用户

CREATE LOGIN 王明 WITH PASSWORD='12345';
CREATE USER 王明 FOR LOGIN 王明; --新建用户名
CREATE LOGIN 李勇 WITH PASSWORD='12345';
CREATE USER 李勇 FOR LOGIN 李勇; --新建用户名
CREATE LOGIN 刘星 WITH PASSWORD='12345';
CREATE USER 刘星 FOR LOGIN 刘星; --新建用户名
CREATE LOGIN 张新 WITH PASSWORD='12345';
CREATE USER 张新 FOR LOGIN 张新; --新建用户名
CREATE LOGIN 周平 WITH PASSWORD='12345';
CREATE USER 周平 FOR LOGIN 周平; --新建用户名
CREATE LOGIN 杨兰 WITH PASSWORD='12345';
CREATE USER 杨兰 FOR LOGIN 杨兰; --新建用户名

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

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

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

GRANT SELECT,INSERT
ON Employee
TO 李勇;
GRANT SELECT,INSERT
ON Department
TO 李勇

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

GRANT SELECT,UPDATE(Salary)
ON Employee
TO 刘星

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

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

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

GRANT ALL PRIVILEGES
ON Employee
TO 张新;
GRANT ALL PRIVILEGES
ON Department
TO 张新;

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

CREATE VIEW DS(Sname,Smax,Smin,Savg)
AS
SELECT DepartmentId,max(Employee.Salary),min(Employee.Salary),avg(Employee.Salary)
FROM Employee,Department
WHERE DepartmentId=Department.ID
GROUP BY DepartmentId;

GRANT SELECT
ON DS
TO 杨兰

查询视图

第八大题

（1）

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

（2）

REVOKE INSERT,DELETE
ON Department
FROM 李勇
REVOKE INSERT,DELETE
ON Employee
FROM 李勇

（3）

REVOKE SELECT 
ON USERS
FROM PUBLIC

（4）

REVOKE SELECT,UPDATE(Salary)
ON Employee
FROM 刘星

（5）

REVOKE ALTER
ON Employee
FROM 张新
REVOKE ALTER 
ON Department
FROM 张新

（6）

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

（7）

REVOKE SELECT 
ON SalaryLevel
FROM 杨兰