SQL Server——从入门到放弃（8）-- GRANT/ REVOKE / AUDIT 练习

从今天开始就要开始第四章——数据库安全性的学习。现在，开始练习。

关系数据库系统中存取控制对象

此次练习需要多个用户，所以首先应该创造几个用户。

然后就是发现，用户的登录名不能相同，不然会报错。

1.GRANT

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

• WITH GRANT OPTION子句：
  。 指定：可以再授予
  。没有指定：不能传播
• 语义：将对指定操作对象的指定操作权限授予指定的用户

【例4.1】把查询Student表权限授给用户U1

grant select
on table Student
to U1

执行之后

正确语句：

grant select
on Student
to U1

【例4.2】把对Student表和Course表的全部权限授予用户U2，U3

grant all privileges
on Student,Course
to U2,U3

执行后

此时可以改为

grant all privileges
on Student
to U2,U3
grant all privileges
on Course
to U2,U3

可以成功执行，但有提示

【例4.3】把对表SC的查询权限授予所有用户

grant select
on SC
to public

【例4.4】把查询Student表和修改学生学号的权限授给用户U4

grant update(Sno),select
on Student
to U4

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

【例4.5】把对表SC的INSERT权限授予U5用户，并允许他再将此权限授予其他用户

grant insert
on SC
to U5
with grant option

执行之后，U5不仅拥有了对表SC的INSERT权限，还可以传播此权限。

【例4.6】

grant insert
on SC
to U6
with grant option

同样，U6还可以将此权限授予U7
（但这里我感觉这也不是U5授予的，前面不都是这么写的吗？
希望有那位读者可以给出解答）

【例4.7】

grant insert
on SC
to U7

U7不能再传播此权限。

2.REVOKE
一般格式：
REVOKE <权限> [,<权限>]…
ON <对象类型> <对象名> [,<对象类型> <对象名>]…
FROM <用户> [,<用户>]…[CASCADE | RESTRICT]

【例4.8】把用户U4修改学生学号的权限收回

revoke update(Sno)
on Student
from U4

【例4.9】收回所有用户对表SC的查询权限

revoke select
on SC
from public

【例4.10】把用户U5对SC表的INSERT权限收回

revoke insert
on SC
from U5 cascade

• 将用户U5的INSERT权限回收时应该使用CASCADE，否则拒绝执行该语句（谢天谢地，CASCADE终于能用了😂）
• 如果U6或U7还从其他用户处获得对SC表的INSERT权限，则它们仍具有此权限，系统只回收直接或间接从U5处获得的权限

角色（ROLE）

被命名的一组与数据库操作相关的权限

• 角色是权限的集合可以为一组具有相同权限的用户创建一个角色。
• 优点：简化授权的过程

1. 角色的创建
   CREATE ROLE <角色名>
2. 给角色授权
   GRANT <权限> [,<权限>]…
   ON <对象类型>对象名
   TO <角色>[,<角色>]…
3. 将一个角色授予其他的角色或用户
   GRANT<角色1> [,<角色2>]…
   TO <角色3> [,<用户1>]…
   [WITH ADMIN OPTION]

• 该语句把角色授予某用户，或授予另一个角色
• 授予者是角色的创建者或拥有在这个角色上的ADMIN OPTION
• 指定了WITH ADMIN OPTION 则获得某种权限的角色或用户还可以把这种权限授予其他角色

一个角色的权限： 直接授予这个角色的全部权限加上其他角色授予这个角色的全部权限

4. 角色权限的收回
   REVOKE <权限> [,<权限>]…
   ON <对象类型> <对象名>
   FROM <角色> [,<角色>]…

• 用户可以回收角色的权限，从而修改角色拥有的权限
• REVOKE 执行者是
  • 角色的创建者
  • 拥有在这个(些)角色上的 ADMIN OPTION

【例4.11】通过角色来实现将一组权限授予一个用户

/*1.首先创建一个角色R1*/
create role R1

/*2.然后使用GRANT语句，使角色R1拥有Student表的
SELECT,UPDATE,INSERT权限*/
grant select,update,insert
on Student
to R1

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

/*4.可以一次性通过R1来回收王平的这3个权限*/
revoke R1
from 王平

执行之后

分步来看的话，前两步没问题，能够正确执行，问题就是从第三步开始的。
还是语法问题，不用想，肯定又是T—SQL不支持。（太难了，这对我这数据库小白可太不友好了）
没办法，百度找原因吧。
我失败了，😭求助求助。

【例4.12】角色的权限修改

grant delete
on Student
to R1

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

【例4.13】

revoke delete
on Student
to R1

这串代码就是使R1减少了SELECT权限

刚刚的练习属于数据库的自主存取控制，但是由于自主存取控制有着它自己的缺点：可能存在数据的“无意泄露”，造成这种缺点的原因是由于这种机制仅仅通过对数据的存取权限来进行安全控制，而数据本身并无安全性标记。 解决办法就是 对系统控制下的所有主客体实施强制存取控制策略

强制存取控制

• 保证更高程度的安全性
• 用户不能直接感知或进行控制
• 适用于对数据有严格而固定密集分类的部门
  * 军事部门
  * 政府部门

强制存取控制规则：

1. 仅当主体的许可证级别大于或等于客体的密级时，该主体才能读相应的客体
2. 仅当主体的许可证级别小于或等于客体的密级时，该主体才能写相应的客体

简记为：

向下读；向上写

视图机制

• 把要保密的数据对无权存取这些数据的用户隐藏起来，对数据提供一定程度的安全保护
• 间接的实现支持存取谓词的用户权限定义

【例4.14】建立计算机系学生的视图，把对该视图的SELECT权限授予U3，把该视图上的所有操作权限授予U4

/*先建立计算机系学生的视图CS_Student*/
CREATE VIEW CS_Student
AS 
SELECT *
FROM Student
WHERE Sdept='CS';

/*在视图上进一步定义存取权限*/
GRANT SELECT
ON CS_Student  
TO U3;
     
GRANT ALL
ON CS_Student  
TO U4; 

审计

• 审计日志：将用户对数据库的所有操作记录在上面
• 审计员利用审计日志：监控数据库中的各种行为，找出非法存取数据的人、时间和内容。

AUDIT语句和NOAUDIT语句

• AUDIT语句：设置审计功能
• NOAUDIT语句：取消审计功能

【例4.15】对修改SC表结构或修改SC表数据的操作进行审计

audit alter,update
on SC

【例4.16】取消对SC表的一切审计

noaudit alter,update
on SC

又有错误。。。
我服了。

不知道为啥，一涉及审计这部分，就显示一堆错误，百度，查CSDN，好多方法都试了，也没啥结果，我现在都怀疑T-SQL支不支持这俩语句😰

好吧，不管怎么说，到这里，第四章的练习就做完了。这篇博客是我写过的章节练习内容最少的一篇，但却是写起来最难受的一篇，坑是一个接一个，刚从一个坑里爬出来，又掉进了另一个坑里，而且还有好多东西不明白，简直了！

所以，还请各位读者大大能不吝赐教，万分感谢！