数据库作业10：第四章例题

本文可分为一下几个内容

• 授权：授予与收回
• • 1、GRANT
  • 2、REVOKE
• 数据库角色
• • 1、角色的创建
  • 2、给角色授权
  • 3、将一个角色授予其他的角色或用户
  • 4、角色权限的收回
• 视图机制
• 审计

授权：授予与收回

新建登录名

像以下方式创建登录名以及对应的用户“U1~U7”；

新建用户；

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

1、GRANT

GRANT语句的一般格式为

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

• "[WITH GRANT OPTION];"是指被授权用户还可以授予其他用户权限，但不允许循环授予；

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

按照书上语句运行，实验报错，查询后是T-SQL与标准SQL的区别！删除“TABLE”即可。

链接：https://zhidao.baidu.com/question/2073697404154667628.html

GRANT SELECT
ON Student
TO U1;

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

按照书上的语句，提示“，”附近有语法错误；

查找资料后发现：

“权限主体，权限，要授予对其权限的对象，都必须要求具体指明；所以如果是以对象为单位，确实只能一次授予一个对象；”
链接：https://zhidao.baidu.com/question/1770654523778836780.html

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 INSERT
ON SC
TO U5
WITH GRANT OPTION;

• U5不仅拥有了对SC表的INSERT权限，还可以传播此权限，由U5用户发上述GRANT命令给其他用户。

[例4.6]由用户U5将此权限授予U6。

• 为了测试，将数据库断开连接，改用U5登录

GRANT INSERT
ON SC
TO U6
WITH GRANT OPTION;

[例4.7]U6将此权限授予U7。

• 同样，先将数据库断开连接，使用用户U6登录；

GRANT INSERT
ON SC
TO U7;

• 此时U6未给U7传播的权限，因此U7不能再传播此项权限！

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;

• 将用户U5的INSERT权限收回的同时，级联(CASCADE)收回了U6和U7的INSERT权限，否则系统将拒绝执行该命令。

数据库角色

1、角色的创建

创建角色的SQL语句格式是：

CREATE ROLE<角色名>

• 创建角色为空，没有任何内容，可以用GRANT语句为角色授权。

2、给角色授权

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

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

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

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

• 授予或者是角色的创建者，或者拥有在这个角色上的ADMIN OPTION。
• 如果制定了WITH ADMIN OPTION子句，则获得某种权限的角色或用户还可以把这种权限再授予其他角色。

4、角色权限的收回

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

• 用户可以收回角色的权限，从而修改角色拥有的权限。

[例4.11]通过角色来实现将一组权限授予一个用户。

1）创建一个角色R1；

CREATE ROLE R1;

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

GRANT SELECT,UPDATE,INSERT
ON Student
TO R1;

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

• 先为王平、张明、赵玲新建数据库角色；

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

alter role R1
add member 王平;
alter role R1
add member 张明;
alter role R1
add member 赵玲;

• 书中语句提示在“R1”附近出现语法错误，查询资料修改后命令成功完成

4)当然，也可以一次性地通过R1来回收王平的这三个权限。

exec sp_droprolemember 'R1','王平';

• 书中语句同样提示在“R1”附近出现语法错误，查询资料修改后命令成功完成

[例4.12]角色的权限修改。

REVOKE DELETE
ON Student
FROM R1

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

[例4.13]使R1减少了SELECT权限。

REVOKE SELECT
ON Student
FROM R1;

• 数据库角色是一组权限的集合。使用角色来管理数据库权限可以简化授权的过程，使自主授权的执行更加灵活、方便。

视图机制

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

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

GRANT SELECT
ON CS_Student
TO 王平;

GRANT ALL PRIVILEGES
ON CS_Student
TO 张明;
GO

在运行时提示CREATE VIEW必须是批处理中仅有的语句
百度发现，添加GO即可
链接：https://jingyan.baidu.com/article/a3a3f8112d40eb8da2eb8aa4.html

审计

• 审计功能就是数据库管理系统达到C2以上安全级别必不可少的一项指标。
• 审计日志是将用户对数据库的所有操作记录在上面。

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

AUDIT ALTER,UPDATE
ON SC;

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

NOAUDIT ALTER,UPADATE
ON SC;

• 课本给出的审计语言在SQL Server中并不适用，查询资料也未找到结果。若有方法，望请读者分享~
• 查询到相关资料如下：https://blog.51cto.com/gaowenlong/1908381

本次实验相对难度较大，T-SQL与标准SQL有诸如查询时"TABLE"以及审计语言等差别，在学习时应该仔细区分。

以上为第四章例题全部内容，包含授权、数据库角色、审计等内容。

感谢阅读~