数据库作业11：SQL练习7 - GRANT/ REVOKE / AUDIT

一、授权：授予与收回

1、GRANT

GRANT <权限>[,<权限>]... 
ON <对象类型> <对象名>[,<对象类型> <对象名>]… 
TO <用户>[,<用户>]... 
[WITH GRANT OPTION]; 

①其语义为：将对指定对象操作的指定操作权限授予指定的用户。
②如果指定了WITH GRANT OPTION]则可以把某种权限再授予其他用户， 若没有指定则不能传播该权限。
③SQL标准允许具有WITH GRANT OPTION的用户把相应权限或其子集传递授予其他用户，但不允许循环授权。（就是我把权限给了你，你就不能再还给我！）

GRANT SELECT
ON TABLE Student
TO U1;

显示
此时SQL Server出现报错：“TO”附近有语法错误。应为DOUBLECOLON，或ID
我百度了一下：这时候需要去掉TABLE，程序改为：

GRANT SELECT
ON Student
TO U1;

然后就可以啦！

GRANT ALL PRIVILEGES
ON Table Student,Course
TO U2,U3;

又出现了错误，查了查授权语句一次只授权一个表,改写后：

GRANT ALL PRIVILEGES
ON Student
TO U2,U3;

GRANT ALL PRIVILEGES
ON Course
TO U2,U3;

出现了提示：

GRANT SELECT
ON SC
TO PUBLIC;

GRANT SELECT,UPDATE(Sno)
ON Student
TO U4;

对属性列授权时必须明确指出相应的属性列名。

GRANT INSERT
ON SC
TO U5
WITH GRANT OPTION;

执行此语句后，U5不仅拥有了对表SC的INSERT权限，还可以传播此权限，即由U5用户发上述GRANT命令给其他用户。
例如U5可有将权限传递给U6：

GRANT INSERT
ON SC
TO U6
WITH GRANT OPTION;

同样U6还可以将权限授予U7：

GRANT INSERT
ON SC
TO U7;

因为U6未给U7传播的权限，因此U7不能再传播。

根据这几个例题总结： T-SQL授权语句一次只授权一个表还有在ON后面不用加TABLE！

---

2、REVOKE

授予用户的权限可以由REVOKE语句收回。

REVOKE <权限>[,<权限>]... 
ON <对象类型><对象名>[, <对象类型><对象名>] ...
FROM <用户>[,<用户>]...[CASCADE|RESTRICT];

REVOKE UPDATE(Sno)
ON Student
FROM U4;

REVOKE SELECT
ON SC
FROM PUBLIC;

如果写成如下代码，则会出现错误：

REVOKE INSERT
ON SC
FROM U5;

因为将用户U5的INSERT权限收回的同时，级联（CASCADE）收回了U6和U7的INSERT权限，否则系统将拒绝执行该命令。则应改成：

REVOKE INSERT
ON SC
FROM U5 CASCADE;

但是为啥U5，U7都没有权限了，U6还有…



用户可以自主的决定将数据的存取权限授予何人、决定是否也将授权的权限再授予他人，称这样的存取控制时自主存取控制。

---

二、数据库角色

角色时权限的集合。用CREATE ROLE语句创建角色，同GRANT语句给角色授权，用REVOKE语句收回授予角色的权限。

①首先创建一个角色R1：

CREATE ROLE R1;

②然后使用GRANT语句，使角色R1拥有Student表的SELECT,INSERT,UPDATE权限。

GRANT SELECT,UPDATE,INSERT
ON Student
TO R1;

③将这个角色授予王平,赵玲,张明，使他们具有角色R1所包含的全部权限。

GRANT R1
TO 王平,赵玲,张明;

又出现错误：
（才想起来我没这些个用户😁就有个WANG，ZHANG）
解决办法：

EXEC sp_addrolemember 'R1','WANG';/*这个函数后面还只能有一个人，不然就会出现错误说指定了过多参数*/
/*或者*/
ALTER ROLE R1
ADD MEMBER WANG;

④一次性通过R1来收回WANG的这三个权限。

REVOKE R1
FROM WANG;

同样不能执行！需改成：

EXEC sp_droprolemember 'R1','WANG';

使角色R1在原来的基础上增加了Student表的DELETE权限。

GRANT DELETE
ON Student
TO R1;

使R1减少了SELECT权限。

REVOKE SELECT
ON Student
FROM R1;

---

三、视图机制

CREATE VIEW CS_Student		/*先建立视图CS_Student*/ 
AS 
SELECT * 
FROM Student 
WHERE Sdept='CS'; 

GRANT SELECT		/*王平老师只能检索计算机系学生的信息*/ 
ON CS_Student 
TO WANG; 

GRANT ALL PRIVILEGES		/*系主任张明有检索和增删改计算机系学生信息的所有权限*/ 
ON CS_Student 
TO ZHANG;

---

四、审计

审计功能把用户对数据库的所有操作自动记录下来放入审计日志中。
审计员可以利用审计日志监控数据库中的各种行为，重现导致数据库现有状况的一系列事件，找出非法存取数据的人、时间和内容等。

AUDIT语句来设置审计功能；
NOAUDIT语句来取消审计功能。

AUDIT ALTER,UPDATE
ON SC;

NOAUDIT ALTER,UPDATE
ON SC;

T-SQL =-=我还是慢慢研究把🙃