数据库作业12：第四章：数据库安全性 习题

/*建班级表*/
CREATE TABLE Class
(Cnumber SMALLINT PRIMARY KEY,
Cname CHAR(10) UNIQUE,
Cleader CHAR(10),
Cmonitor CHAR(10)
);
/*建学生表*/
CREATE TABLE Student
(Sno CHAR(9) PRIMARY KEY,
Sname CHAR(10) UNIQUE,
Sage SMALLINT,
Ssex CHAR(2),
Saddress CHAR(30),
Cnumber SMALLINT
FOREIGN KEY (Cnumber) REFERENCES Class(Cnumber),
);

先建班级表再建学生表，因为学生表的外码来自班级表。否则外码写参照关系的时候会出现错误。

/*新建用户U1*/
CREATE USER U1 WITHOUT LOGIN;

/*授权*/
GRANT ALL
ON Student
TO U1
WITH GRANT OPTION;

GRANT ALL
ON Class
TO U1
WITH GRANT OPTION;

ALL 权限已不再推荐使用，并且只保留用于兼容性目的。它并不表示对实体定义了 ALL 权限。

/*新建用户U2*/
CREATE USER U2 WITHOUT LOGIN;

GRANT SELECT,UPDATE(Saddress)
ON Student
TO U2;

GRANT SELECT
ON Class
TO PUBLIC;

/*创建角色R1*/
CREATE ROLE R1; 

GRANT SELECT,UPDATE
ON Student
TO R1;

写WITH ADMIN OPTION显示有错误。

GRANT R1
TO U1
WITH GRANT OPTION;

“R1”附近有语法错误。

ALTER ROLE R1
ADD MEMBER U1;

先建部门表，再建职工表。职工表的部门号是外码，参照表是部门表。需要先建好参照表。

/*部门表*/
CREATE TABLE Depart
(Dnumber SMALLINT PRIMARY KEY,
Dname CHAR(5),
Dmanname CHAR(6),
Daddress CHAR(10),
Dtel CHAR(15)
);
/*职工表*/
CREATE TABLE Emp
(Enumber CHAR(10) PRIMARY KEY,
Ename CHAR(5),
Eage SMALLINT ,
Eduty CHAR(20), /*职务*/
Ewage CHAR(9),  /*工资*/
Dnumber SMALLINT FOREIGN KEY REFERENCES Depart(Dnumber)
);

/*新建用户王明*/
CREATE USER 王明 WITHOUT LOGIN;

一次授权一个表。否则会出错。

GRANT SELECT
ON Depart
TO 王明;

GRANT SELECT
ON Emp
TO 王明;

/*新建用户李勇*/
CREATE USER 李勇 WITHOUT LOGIN;

一次授权一个表。

GRANT INSERT,DELETE
ON Depart
TO 李勇;

GRANT INSERT,DELETE
ON Emp
TO 李勇;

这个语句不能执行。

GRANT SELECT
ON Emp
WHEN USER()=NAME
TO ALL;

/*新建用户刘星*/
CREATE USER 刘星 WITHOUT LOGIN;

GRANT SELECT,UPDATE(Ewage)
ON Emp
TO 刘星;

CREATE USER 张新 WITHOUT LOGIN;

GRANT UPDATE
ON Emp
TO 张新;
/*一次授权一张表*/
GRANT UPDATE
ON Depart
TO 张新;

CREATE USER 周平 WITHOUT LOGIN;

GRANT ALL
ON Emp 
TO 周平
WITH GRANT OPTION;

GRANT ALL
ON Depart 
TO 周平
WITH GRANT OPTION;

CREATE USER 杨兰 WITHOUT LOGIN;

/*建立视图*/
CREATE VIEW S_DE
AS
SELECT Dname,MAX(Ewage),MIN(Ewage),AVG(Ewage)
FROM Emp,Depart
WHERE Depart.Dumber=Emp.Dnumber;

GRANT SELECT
ON S_DE
TO 杨兰;

（不能执行，其实我也不会做）

（1）

REVOKE SELECT
ON Emp
FROM 王明;

REVOKE SELECT
ON Depart
FROM 王明;

收回权限，一次只能收回一个表的。
（2）

REVOKE INSERT,DELETE
ON Depart
FROM 李勇;

REVOKE INSERT,DELETE
ON Emp
FROM 李勇;

（3）

REVOKE SELECT
ON Emp
WHEN USER()=NAME
FROM ALL;

（4）

REVOKE SELECT,UPDATE(Ewage)
ON Emp
FROM 刘星;

（5）

REVOKE UPDATE
ON Emp
FROM 张新;

REVOKE UPDATE
ON Depart
FROM 张新;

（6）

REVOKE ALL
ON Emp 
FROM 周平 CASCADE;

REVOKE ALL
ON Depart 
FROM 周平 CASCADE;

（7）

REVOKE SELECT
ON S_DE
FROM 杨兰;

7.（3）和7.（7）不会，参考的别的同学的（应该是标准SQL，在SQL server上不能执行）。所以8.（3）和8.（7）就是根据上边的授权语句，把GRANT改为REVOKE、把ON改为FROM写出来的。别的题做着还可以，巩固了一下建表、新学的授权和收回语句，感觉很有收获，之前写一遍博客感觉实际记住的不太多，但是在做题的时候再回过去看自己写的，印象就很深刻了。后边的题没有截图是感觉有点浪费时间（毕竟后边还有俩作业呢…），但是能运行的都运行并查看结果了(除了说的这两个不会的)。