SQL练习7 - GRANT/ REVOKE / AUDIT

用户权限：

SQL中使用GRAND和REVOKE语句向用户授予或收回对数据的操作权限。GRANT语句向用户授予权限。REVOKE语句收回已经授予用户的权限
1、GRANT：
GRANT语句的格式一般为：
GRANT<权限>[,<权限>]…
ON<对象类型><对象名>[,<对象类型><对象名>]…
TO<用户>[,<用户>]…
[WITH GRANT OPTION];
语义为：将对指定操作对象的指定操作权限授予指定的用户。发出GRANT语句的可以是数据库的管理员，也可以是数据库对象的创建者，还可以是已经拥有该权限的用户。接受权限的用户可以是一个或者是多个具体用户，也可以是PUBLIC，即全体用户。
如果指定了WITH GRANT OPTION子句，则获得某种权限的用户还可以把这种权限再授予其他的用户。如果没有指定WITH GRANT OPTION子句，则获得某种权限的用户只能使用该权限，不能传播该权限。
SQL标准允许具有WITH GRANT OPTION的用户把相应权限或其子集传递授予其他用户，但不允许循环授权，即被授权者不能把权限再授予给授权者或其祖先。
在进行下列的练习之前应该先【创建用户】
右击【用户】，点击【新建用户】

输入用户名：U1，点击对应的地方

在选第一个报错的时候，可以选择第二个


原因是之前用该登录名为另一个用户开立账户
[ERROR]:用户“U1”创建失败.

【例4.1】把查询Student表的权限授予给用户U1
课本里的例题

GRANT SELECT
ON TABLE Student
TO U1

但是在此时会报错，错误为：“TO”附近有语法错误。应为DOUBLECOLON，或ID
[ERROT]:“TO”附近有语法错误。应为DOUBLECOLON，或ID.
此时需要将代码改为：

GRANT SELECT
ON Student
TO U1

此时点击执行，命令就可以成功完成。
为了检查U1是否有Student表的权限，可以点开U1，在里面可以看见安全对象里面有Student表

【例4.2】把对Student表和Course表的全部操作权限授予用户U2和U3
创建用户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

ALL权限已不再推荐使用，并且只保留用于兼容性的目的，它并不表示对实体定义了ALL权限
点开用户，可以看见用户U2和用户U3有了表Student和表Course权限。
【例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

执行此SQL语句后，U5不仅拥有了对表SC的INSERT权限，还可以传播此权限，即由U5用户发上述GRAND命令给其他用户。
【例4.6】U5可以将此权限授予U6

GRANT INSERT
ON SC
TO U6
WITH GRANT OPTION

【例4.7】U6将此权限授予U7

GRANT INSERT
ON SC
TO U7

此时U6未给U7传播的权限，因此U7不能再传播此权限
由以上的例子可知，GRANT语句可以一次向一个用户授予（例4.1），也可以一次向多个用户授予（例4.2、4.3），还可以一次传播多个同类对象的权限(例4.2)，还可以完成对基本表和属性列这些不同对象的授予（例4.4）
2、REVOKE：
授予用户的权限可以由数据库管理员或其他授权者REVOKE语句收回，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

数据库角色：

数据库角色是被命名的一组与数据库操作相关的权限，角色是权限的集合。因此可以为一组具有相同权限的用户创建一个角色，使用角色来管理数据库权限可以简化授权的过程。
在SQL中首先使用CREATE ROLE语句创建角色，然后用GRANT语句给角色授权，用REVOKE语句收回授予角色的权限。
1、角色的创建
语句格式为：
CREATE ROLE<角色名>
刚刚创建的角色是空的，没有任何内容，可以用GRANT角色为角色授权
2、给角色授权
GRANT<权限>[,<权限>]…
ON<对象类型>对象名
TO<角色>[,<角色>]…
数据库管理员和用户可以利用GRANT语句将权限授予某个或几个角色。
3、将一个角色授予其他的角色或用户
GRANT<权限>[,<权限>]…
ON<对象类型>对象名
TO<角色>[,<角色>]…
数据库管理员和用户可以利用GRANT语句将权限授予某一个或几个角色
该语句吧角色授予给某个用户，或者是授予另一个角色。这样一个角色拥有的权限就是授予它的全部角色所包含的权限的总和。
授予者就是角色的创建者，或者拥有在这个角色上的ADMIN OPTION
如果指定了WITH ADMIN OPTION子句，则获得某种权限的角色或者用户或用户还可以把这种权限再授予其他角色
一个角色包含的权限包括直接授予这个角色的全部权限加上其他角色授予这个角色的全部权限
4、角色权限的收回
REVOKE<权限>[,<权限>]…
ON<对象类型><对象名>
FROM<角色>[,<角色>]…
用户可以收回角色的权限，从而修改角色拥有的权限
REVOKE动作的执行者或者是角色的创建者，或者拥有在这个角色上的ADMIN OPTION
【例4.11】通过角色来实现将一组权限授予一个用户

/*首先创建一个角色R1*/
CREATE ROLE R1

/*使用GRANT语句，使角色R1拥有Student表的SELECT、UPDATE、INSERT权限*/
GRANT SELECT,UPDATE,INSERT
ON Student
TO R1

/*创建角色王平、张明、赵琳（略），将这个角色授予王平、张明、赵琳，使他们具有角色R1所包含的全部权限*/
GRANT R1
TO 王平,张明,赵琳

但是在SQL Server不支持该语法，所以会报错：“R1”附近有语法错误。
可以手动的为R1角色添加或删除角色成员，或者是使用SQL语句

/*增加*/
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 王平

/*通过R1来收回王平的者三个权限*/
REVOKE R1
FROM 王平

【例4.12】角色权限的修改

GRANT DELETE
ON Student
TO R1

【例4.13】减少了R1的SELECT权限

REVOKE SELECT 
ON Student
FROM R1

视图机制

通过视图机制把要保密的数据对无权存取的用户隐藏起来，从而自动对数据提供一定程度的安全保护
视图机制间接的实现支持存取谓词的用户权限定义。
【例4.14】建立计算机系学生的视图，把对该视图的SELECT权限授予U2，把该视图上的所有权限都授予给U3

/*建立视图CS_Student*/
CREATE VIEW CS_Student
AS 
SELECT *
FROM Student
WHERE Sdept='CS'
/*U2可以检索计算机系学生的信息*/
GRANT SELECT
ON CS_Student
TO U2
/*U3可以检索和增删改计算机系学生信息的所有权限*/
GRANT ALL PRIVILEGES
ON CS_Student
TO U3

审计

审计功能把用户对数据库的所有操作自动记录下来放到审计日志中，审计员可以利用审计日志监控数据库中的各种行为，重新导致数据库现有状况的一系列事件，找出非法存取数据的人、时间和内容
1、AUDIT语句和NOAUDIT语句
AUDIT语句来设置审计功能，NOTAUDIT语句则取消审计功能
审计功能一般可以分为用户级审计和系统级审计。用户级审计是任何用户可设置的审计，主要是用户针对自己创建的数据库表或视图进行审计，记录所有用户对这些表或视图的一切成功和（或）不成功的访问要求以及各种类型的SQL操作
【例4.15】对修改SC表结构或修改SC表数据的操作进行审计

AUDIT ALTER,UPDATE
ON SC

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

NOAUDIT ALTER,UPDATE
ON SC