数据库语言的练习7 - GRANT/ REVOKE/ AUDIT（2020.3.25作业）

GRANT

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

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

首先，建立登录名U1-U7，再建立用户U1-U7
具体步骤是，先建设登录名
在这里右键新建登录名

选择SQL serever身份验证，设置密码

在数据库中右键用户，新建用户

登录名和用户一一对应
建立成功

此方法可以完成后续测试，方法不唯一

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

GRANT SELECT 
ON TABLE Student 
TO U1

SQL serever报错：关键字 ‘TO’ 附近有语法错误。
去掉TABLE
执行后，用U1登录，执行一条SELECT语句
查询成功

查下SC试试啊

果然不行呢~回到sa
[例4.2] 把对Student表和Course表的全部权限授予用户U2和U3

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

报错：“,”附近有语法错误。
SQL serever on的后面只能接一个对象
分开来

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

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

INSERT、DELETE、UPDATE都可以进行

U3一样

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

GRANT SELECT 
ON TABLE SC 
TO PUBLIC

所有用户都可以查询SC表，以下用U4登录

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

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

用U4登录测试


当然只能修改学号

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

GRANT INSERT 
ON TABLE SC 
TO U5
WITH GRANT OPTION;

登录U5测试

U5不仅拥有了对表SC的INSERT权限， 还可以传播此权限：
[例4.6]

GRANT INSERT 
ON TABLE SC 
TO U6
WITH GRANT OPTION;

这是在U5完成的

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

GRANT INSERT 
ON TABLE SC 
TO U7;

(例3已经把对表SC的查询权限授予所有的用户)

在U7可以进行INSERT操作

REVOKE

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

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

REVOKE UPDATE(Sno)
ON TABLE Student 
FROM U4;

在U4不能再进行UPDATE操作
（不再截图了，错误报告都是拒绝权限）

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

REVOKE SELECT 
ON TABLE SC 
FROM PUBLIC;

收回后，U1-U7都不能再对SC进行SELECT操作

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

REVOKE INSERT 
ON TABLE SC 
FROM U5 CASCADE

将用户U5的INSERT权限收回的时候应该使用CASCADE，否则拒绝执行该语句

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

角色

角色是权限的集合。可以为一组具有相同权限的用户创建一个角色。
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 TABLE Student 
TO R1

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

GRANT R1 
TO 王平,张明,赵玲

报错：“R1”附近有语法错误。
可以手动的为R1角色添加或删除角色成员，或者是使用SQL语句

右键角色R1属性
添加-浏览

/*增加*/
EXEC sp_addrolemember  'R1','王平'
EXEC sp_addrolemember  'R1','张明'
EXEC sp_addrolemember  'R1','赵玲'
/*删除*/
EXEC sp_droprolemember 'R1','王平'
EXEC sp_droprolemember 'R1','张明'
EXEC sp_droprolemember 'R1','赵玲'

/*增加*/
ALTER ROLE R1
ADD  MEMBER 王平
/*删除*/
ALTER ROLE R1
DROP  MEMBER 王平
``

（4） 可以一次性通过R1来回收王平的这3个权限
```sql
REVOKE R1 
FROM 王平

[例4.12] 角色的权限修改

GRANT DELETE 
ON TABLE Student
TO R1

角色R1在原来拥有的权限不变，增加了Student表的DELETE权限

[例4.13]

REVOKE SELECT 
ON TABLE Student
FROM  R1

使R1失去SELECT权限，被授予角色的王平，张明，赵玲的SELECT权限也失去

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

CREATE VIEW CS_Student
AS 
SELECT  *
FROM   Student
WHERE  Sdept='CS';

在视图上进一步定义存取权限

GRANT  SELECT
ON  CS_Student  
TO  王平
 
GRANT ALL PRIVILEGES
ON  CS_Student  
TO  张明

Q1:什么是存取谓词？
像Sdept='CS’这样的查询条件

Q2:为什么要用视图间接实现，直接用基本表不可以吗？
对不同用户定义不同视图能使机密数据不会让不应该看到这些数据的人看到。比如我只让王平看计算机系学生的信息，不想让他看到别的系的学生信息，就可以建立计算机系学生的视图，把对该视图的SELECT权限授于王平。这样可以对机密数据建立安全保护。

AUDIT

AUDIT语句：设置审计功能
NOAUDIT语句：取消审计功能
[例4.15] 对修改SC表结构或修改SC表数据的操作进行审计

AUDIT ALTER,UPDATE  
ON SC;

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

NOAUDIT  ALTER,UPDATE  
ON  SC;

用时一个半小时，主要是来回试验，内容相对简单