SQL Server 数据库实验课第七周——第四章课后题

6.对下列两个关系模式：

学生（学号、班级、年龄、性别、家庭住址、班级号）
班级（班级号，班级名，班主任，班长）。
建表

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),        /* 列级完整性约束条件,Sno是主码*/                  
 Sname CHAR(20),             /* Sname取唯一值*/
 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

使用GRANT语句完成以下授权功能

(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

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

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;

7.今有以下两个关系模式：

职工（职工号，姓名，年龄，职务，工资，部门号）
部门（部门号，名称，经理名，地址，电话号）
用SQL的GRANT和REVOKE语句（加上视图机制）完成以下授权定义或存取控制功能
建表

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 李勇

（3）每个职工只对自己的记录有SELECT权限。（没实现）

表Employee中插入数据

先创建视图

DROP VIEW USERS
CREATE VIEW USERS
AS SELECT *
FROM Employee
WHERE NAME = SYSTEM_USER;

创建后，对视图查询权限授予所有用户

GRANT SELECT 
ON USERS
TO PUBLIC 

让员工使用自己用户名对应的登录名登录，可查询到关于自己的信息
如
登录王明对应登录名

查询视图

（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 杨兰

登录杨兰对应登录名

查询视图

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

（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 杨兰

问题
关于第七题第三问用到的USERS函数，参考
SQL Server 中 system_user、current_user、user_name([id]) 有什么区别？
唉，又遇到新的问题，本来登录名设的是王明1，李勇1啥的
这样视图建立的代码

DROP VIEW USERS
CREATE VIEW USERS
AS SELECT *
FROM Employee
WHERE CONCAT(NAME, '1')= SYSTEM_USER;

但是呢，CONCAT函数它不成功啊啊啊啊啊啊啊好崩溃。没办法，我只能把所有登录名和用户名设成一样的。
为啥不成功呢？？？┭┮﹏┭┮