数据库作业8：SQL Server 的部分操作（授权：授予与收回）

4.2数据库安全性控制

三.自主存取控制方法

关系数据库系统中的存取权限：

四.授权：授予与收回

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

1.GRANT

语句的一般格式：

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

WITH GRANT OPTION 子句 : 若 指定了，获得权限的用户可以再把权限授予给其他用户。
若没有指定，获得权限的用户 只能使用该权限，不能传播。

再进行下列操作之前，首先得创建用户U1,U2,U3,U4,U5,U6,U7。
创建方法详见第一次作业。
创建结果：

【例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 Table Student,Course
TO U2,U3; ---报错

经查询，T-SQL 不支持同时授予用户多个操作权限 也不支持同时对两个表赋权

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;

操作结果：PUBLIC 权限被授予。
查询SC表的属性得到：

【例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;

操作结果：

【例4.6】执行例4.5后，U5不仅拥有了对表SC的INSERT权限，
还可以传播此权限 ：

GRANT INSERT
ON SC
TO U6
WITH GRANT OPTION;

操作结果：U5将此权限授权给了U6

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

GRANT INSERT 
ON SC 
TO U7;

操作结果：

注意：没有 WITH GRANT OPTION;语句，U6未给U7传播的权限，所以U7不能再传播此权限。

2.REVOKE

语句的一般格式为：

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

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

REVOKE UPDATE(Sno)
ON Student
FROM U4

操作结果：U4的权限被收回

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

REVOKE SELECT
ON SC
FROM PUBLIC;

操作结果：查询权限被收回。
查看SC表的权限可得：

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

REVOKE INSERT
ON SC
FROM U5 CASCADE;

操作结果：U5的权限被收回。

注意！： 将用户U5的 INSERT 权限收回的同时，级联（CASCADE） 收回了 U6 和 U7 的 INSERT 权限，否则系统将拒绝执行该命令。

五.数据库角色

数据库角色 是被命名的 一组 与数据库操作相关的权限，角色是权限的集合。

1.角色的创建

SQL语句格式：

CREATE ROLE <角色名> 

2.给角色授权

SQL语句格式：

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

数据库管理员和用户可以利用 GRANT 语句将权限授予某一个或几个角色。

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

SQL语句格式：

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

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

注意！ ：一个角色的权限包括 直接授予这个角色的全部权限 加上 其他角色授予这个角色的全部权限 。

4.角色权限的收回

SQL语句：

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所包含的全部权限。
1）首先新建用户王平、张明、赵玲

2）授予权限

GRANT R1
TO 王平,张明,赵玲; ---报错

经查询，T-SQL 支持的语言：

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

操作结果：

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

REVOKE R1
FROM 王平 ---报错

经查询，T-SQL 支持的语言：

EXEC sp_droprolemember 'R1','王平'

操作结果：

【例4.12】角色权限的修改

GRANT DELETE
ON Student
TO R1

操作结果：使角色R1在原来的基础上增加了Student表的 DELETE 权限。

【例4.13】

REVOKE SELECT
ON Student
FROM R1

操作结果：使R1减少了SELETE 权限。

总结：本次实验难度不大，但是要特别注意T-SQL语句与标准SQL语句的不同。