授权：授予与收回 以及 数据库角色、视图机制

一、授权

SQL中使用GRANT和REVOKE语句向用户授予或收回对数据的操作权限。GRANT语句向用户授予权限，REVOKE语句收回已经授予用户的权限。

1、授予

需提前建立用户：

创建结果：

GRANT语句的一般格式：

GRANT <权限>[,<权限>]...
ON <对象类型><对象名>[,<对象类型><对象名>]...
TO <用户>[,<用户>]... 
--可以是一个或多个具体用户，也可以是PUBLIC，即全体用户
[WITH GRANT OPTION];
--如果指定了，则获得某种权限的用户还可以把这种权限在授予其他用户

注：授权不允许循环授权

标准SQL语句：
例1：把查询Student表的权限授予用户U1
代码：

GRANT SELECT
ON TABLE Student
TO U1;

授予给某一位用户形式

例2：把对Student表和Course表的全部操作权限授予用户U2和U3

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

授予给多位用户形式，全部操作ALL PRIVILEGES

例3：把对表SC的查询权限授予所有用户

GRANT SELECT
ON TABLE SC
TO PUBLIC;

授予给全体用户PUBLIC

例4：把查询Student表和修改学生学号的权限授予用户U4

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

将部分属性列的相关权限授予给用户
对属性列授权时必须明确指明相应的属性列名

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

GRANT INSERT
ON TABLE SC
TO U5
WITH GRANT OPTION;

使用WITH GRANT OPTION，则用户拥有该操作的授权能力

例6：用户U5可以将此权限授予U6

GRANT INSERT
ON TABLE SC
TO U6
WITH GRANT OPTION;

U6还可以将此权限授予给U7

GRANT INSERT
ON TABLE SC
TO U7;

由上面的例子可以看到，GRANT语句可以一次向一个用户授权，也可以一次向多个用户授权，还可以一次传播多个同类对象的权限，甚至一次可以完成对基本表和属性列这些不同对象的授权。

运行以上代码出现错误：
因此，T-SQL语句应为：
例1：把查询Student表的权限授予用户U1
代码：

GRANT SELECT
ON Student
TO U1;

授予给某一位用户形式

例2：把对Student表和Course表的全部操作权限授予用户U2和U3

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

授予给多位用户形式，全部操作ALL PRIVILEGES
语句出现错误：
T-SQL中GRANT语句一次只能对一个对象进行授权，不能同时对两个表进行授权。
修改为

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

出现提示信息：

例3：把对表SC的查询权限授予所有用户

GRANT SELECT
ON SC
TO PUBLIC;

授予给全体用户PUBLIC

例4：把查询Student表和修改学生学号的权限授予用户U4

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

将部分属性列的相关权限授予给用户
对属性列授权时必须明确指明相应的属性列名

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

GRANT INSERT
ON SC
TO U5
WITH GRANT OPTION;

使用WITH GRANT OPTION，则用户拥有该操作的授权能力

例6：用户U5可以将此权限授予U6

GRANT INSERT
ON SC
TO U6
WITH GRANT OPTION;

U6还可以将此权限授予给U7

GRANT INSERT
ON SC
TO U7;

最终结果：
U1：

U2：

U3：

U4：

U5：

U6：

U7：

2、收回

授予用户的权限可以由数据库管理员或其他授权者用REVOKE语句收回，语句格式：

REVOKE <权限>[,<权限>]...
ON <对象类型><对象名>[,<对象类型><对象名>]...
TO <用户>[,<用户>]...[CASCADE | RESTRICT];--默认值为cascade

例1：把用户U4修改学生学号的权限收回
代码：

REVOKE UPDATE(Sno)
ON Student --与标准SQL不同
FROM U4

例2：收回所有用户对表SC的查询权限

REVOKE SELECT
ON SC
FROM PUBLIC;

例3：把用户U5对SC表的INSERT权限收回

REVOKE INSERT
ON SC
FROM U5 CASCADE;

将用户U5的INSERT权限收回的时候应该使用CASCADE，否则拒绝执行该语句。因为U5将对SC表的INSERT权限赋予U6，U6又授予U7。如果U6或U7还从其他用户处获得对SC表的INSERT权限，则他们仍具有此权限，系统只收回直接或间接从U5处获得的权限 。

二、数据库角色

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

1、角色的创建

语句格式：

CREATE  ROLE  <角色名> 

2、给角色授权

语句格式：

 GRANT  <权限>[,<权限>]… 
 ON <对象类型>对象名  
 TO <角色>[,<角色>]…

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

语句格式：

GRANT  <角色1>[,<角色2>]…
TO  <角色3>[,<用户1>]… 
[WITH ADMIN OPTION]

该语句把角色授予某用户，或授予另一个角色。指定了WITH ADMIN OPTION则获得某种权限的角色或用户还可以把这种权限授予其他角色
一个角色的权限：直接授予这个角色的全部权限加上其他角色授予这个角色的全部权限

4、角色权限的收回

语句格式：

REVOKE <权限>[,<权限>]…
ON <对象类型> <对象名>
FROM <角色>[,<角色>]…;

用户可以回收角色的权限，从而修改角色拥有的权限。即先删除，再重新授权。

例1：通过角色来实现将一组权限授予一个用户。
步骤如下：
（1）首先创建一个角色 R1
代码：

CREATE ROLE R1;

（2）然后使用GRANT语句，使角色R1拥有Student表的 SELECT、UPDATE、INSERT权限
代码：

GRANT SELECT,UPDATE,INSERT
ON Student--标准SQL：ON TABLE Student
TO R1;

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

代码：

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

出现错误：

与标准SQL产生差别。
T-SQL：

exec sp_droprolemember[sp_addrolemember] 'db_owner','hehcong'

EXEC sp_addrolemember 'R1','王平';
EXEC sp_addrolemember 'R1','张明';
EXEC sp_addrolemember 'R1','赵玲';

（4）可以一次性通过R1来回收王平的这三个权限

REVOKE R1
FROM 王平;

出现错误：

与标准SQL产生差别。
T-SQL：

EXEC sp_droprolemember 'R1','王平';

例2：角色的权限修改

GRANT DELETE
ON Student
TO R1;

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

例3：使R1减少了SELECT权限

REVOKE SELECT
ON Student
FROM R1;

三、视图机制

可以为不同的用户定义不同的视图，把数据对象限制在一定的范围内。也就是说，通过视图机制把要保密的数据对无权存取的用户隐藏起来，从而自动对数据提供定程度的，安全保护。
视图机制间接地实现: 支持存取谓词助用户权限定义。

例：建立计算机系学生的视图，把对该视图的SELECT权限授予王平，把该视图上的所有操作权限授予张明。
代码：

CREATE VIEW CS_Student
AS
SELECT *
FROM Student
WHERE Sdept='CS';
GRANT SELECT
ON CS_Student
TO 王平;
GRANT ALL PRIVILEGES
ON CS_Student
TO 张明;

OVER!!!