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

在此之前，我们来新建以下用户：

权限的授予与回收：

1. grant(将对指定操作对象的指定操作权限授予指定的用户)

grannt <权限>[,<权限>]... 
on <对象类型> <对象名>[,<对象类型> <对象名>]…
to <用户>[,<用户>]...
[with grant option];

2. revoke（将对指定操作对象的指定操作权限授予回收）

revoke <权限>[,<权限>]... 
on <对象类型> <对象名>[,<对象类型><对象名>]…
from <用户>[,<用户>]...[cascade| restrict];

[例4.1] 把查询Student表权限授给用户U1

grant select 
on Table Student
to U1;

这条代码报错：

去掉table之后，可运行的代码：

grant select on  Student to U1;

[例4.2] 把对Student表和Course表的全部权限授予用户U2和U3

--错误代码：
grant all privileges 
on table Student,Course 
to U2,U3;

运行出现错误：

原因：grant语句一次只能对一个对象进行赋权，不能同时对两个表进行赋权；但是我们可以同时赋给对象多个不同的权限。

正确代码：

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 option : 授权或者收回给其他用户你给予的权限
grant insert 
on SC 
to U5
with grant option;

执行例4.5后，U5不仅拥有了对表SC的INSERT权限，还可以传播此权限
[例4.6] U5讲权限赋予U6：

grant insert 
on SC 
to U6
with grant option;

同样，U6还可以将此权限授予U7：
[例4.7]

grant insert 
on SC 
to U7;

但U7不能再传播此权限:

收回权限：

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

revoke update(Sno)
on Student 
from U4;

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

revoke select
on SC
from public;

[例4.10] 把用户U5对SC表的INSERT权限收回

--将用户U5的INSERT权限收回的时候应该使用CASCADE，否则拒绝执行该语句
revoke insert
on SC
from U5 cascade;

    如果U6或U7还从其他用户处获得对SC表的INSERT权限，则他们仍具有此权限，系统只收回直接或间接从U5处获得的权限。

数据库角色

定义：是被命名的一组与数据库操作相关的权限，角色是权限的集合。可以为一组具有相同权限的用户创建一个角色。

1. 角色的创建：

--创建空角色，没有任何内容：
create role<角色名>

2. 为角色授权：

grant<权限>[,<权限>]... 
on <对象类型>对象名  
to <角色>[,<角色>...

3. 将一个角色授予其他的角色或用户

--把角色授予某用户，或授予另一个角色
--指定了WITH ADMIN OPTION则获得某种权限的角色或用户还可以把这种权限授予其他角色
grant<角色1>[,<角色2>]…
to <角色3>[,<用户1>]… 
[with admin option]

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

4. 角色权限的收回

revoke <权限>[,<权限>]...
on <对象类型> <对象名>
from <角色>[,<角色>]...

首先我们新建角色R1：

create role R1;

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

grant select, update, insert 
on Student 
to R1;

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

--运行不通的语句（R1附近出现错误）：
grant R1 
to 王平,张明,赵玲;
--接受的语句：
alter role R1
add member 王平;
alter role R1
add member 张明;
alter role R1
add member 赵玲;

可以一次性通过R1来回收王平的这3个权限

--这句话和上一步骤相同错误：
revoke R1 
from 王平;
--解决：
exec sp_droprolemember 'R1','王平';

[例4.12] 角色的权限修改（使角色R1在原来的基础上增加Student表的delete权限）

grant delete 
on Student
to R1;

[例4.13] 删除R1select权限

revoke select
on Student
from R1;

视图机制

1. 间接的实现支持存取谓词的用户权限定义；
2. 可以通过视图机制把保密的数据对无权存取的

[例4.14] 建立计算机系学生的视图，把对该视图的SELECT权限授于王平，把该视图上的所有操作权限授于张明

--建立计算机系学生的视图CS_Student
create view CS_Student
as 
select  *
from  Student
where Sdept='CS';

--在视图上进一步定义存取权限
grant select
on CS_Student  
to U5;

grant all 
on CS_Student  
to U6; 

审计

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

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

audit alter,update  
on SC;

[例4.16] 取消对SC表的一切审计

noaudit alter,update 
on SC;