数据库作业11：SQL练习7 - GRANT/ REVOKE / AUDIT
【4.1】把查询Student表权限授给用户U1
GRANT SELECT
ON TABLE Student
TO U1;
--标准SQL
GRANT SELECT
ON Student
TO U1;
--T-SQL
在SQLserver中要运行此语句，必须删除TABLE。
GRANT [,]...
ON [, ]...
TO [,]...
将某对象的权限授予指定用户。这里把查询Student表的权限给了U1。
然后登陆了U1的账户，新建查询后输入以下语句：
SELECT *
FROM Student
结果报错说“拒绝了对对象 ‘Student’ (数据库 ‘master’，架构 ‘dbo’)的 SELECT 权限。”
报错信息里有一个master系统数据库，接着我看到右上角的可用数据库选中的是master,我之前授权时选中的数据库是Student，所以切换数据库后就没问题了。成功访问。
【4.2】把对Student表和Course表的全部权限授予用户U2和U3
GRANT ALL PRIVILIGES
ON TABLE Student,Course
TO U2,U3;
--标准SQL
GRANT ALL PRIVILEGES
ON Student
TO U2,U3;
GRANT ALL PRIVILEGES
ON Course
TO U2,U3;
--T-SQL
ALL PRIVILIGES 代表所有权限，但是SQLserver中ON后面只能有一个表。
SELECT *
FROM Student,Course
INSERT
INTO Student(Sno,Sname,Ssex,Sdept,Sage)
VALUES('201215555','景天','男','IS',19);
可以进行插入，查询等操作。
【4.3】把对表SC的查询权限授予所有用户
GRANT SELECT
ON TABLE SC
TO PUBLIC;
--标准SQL
GRANT SELECT
ON SC
TO PUBLIC;
--T-SQL
PUBLIC 表示将权限给所有用户。
在U6里边查一查：没有问题。
SELECT *
FROM SC
【4.4】把查询Student表和修改学生学号的权限授给用户U4
GRANT UPDATE(Sno), SELECT
ON TABLE Student
TO U4;
--标准SQL
GRANT UPDATE(Sno), SELECT
ON Student
TO U4;
--T-SQL
对属性列的授权时必须明确指出相应属性列名。
进入U4，执行下列操作。
SELECT *
FROM Student
UPDATE Student
SET Sno='20155550'
WHERE Sno='20155555';
【4.5】对表SC的INSERT权限授予U5用户，并允许他再将此权限授予其他用户
GRANT INSERT
ON TABLE SC
TO U5
WITH GRANT OPTION;
--标准SQL
GRANT INSERT
ON SC
TO U5
WITH GRANT OPTION;
--T-SQL
WITH GRANT OPTION，表明U5不仅拥有了对表SC的INSERT权限，还可以传播此权限。
查询U5是否有此权限：
INSERT
INTO SC
VALUES('201215122','1',67);
SC中多出的数据：
【4.6】
GRANT INSERT
ON TABLE SC
TO U6
WITH GRANT OPTION;
--标准SQL
GRANT INSERT
ON SC
TO U6
WITH GRANT OPTION;
--T-SQL
这里U5不仅授予U6SC中插入数据的权限，还允许他继续传播。
在U6中执行程序：插入成功。
INSERT
INTO SC
VALUES('201215128','3',25);
【4.7】
GRANT INSERT
ON TABLE SC
TO U7;
--标准SQL
GRANT INSERT
ON SC
TO U7;
--T-SQL
U7虽然有插入的权限，但是不能再继续传播了。
在U7查询中输入以下语句,可以看到，U7没有传播的权限。
GRANT INSERT
ON SC
TO U4;
--无法对 用户 'U4' 执行 查找，因为它不存在，或者您没有所需的权限。
【4.8】把用户U4修改学生学号的权限收回
REVOKE UPDATE(Sno)
ON TABLE Student
FROM U4;
--标准SQL
REVOKE UPDATE(Sno)
ON Student
FROM U4;
--T-SQL
之前我们授予了U4修改学号的权限，在在要将它收回。
UPDATE Student
SET Sno='201555500'
WHERE Sno='201555555';
--拒绝了对对象 'Student' (数据库 'Student'，架构 'dbo')的 UPDATE 权限。
--回收权限形式
REVOKE [,]...
ON [,]…
FROM [,]...[CASCADE | RESTRICT];
【4.9】收回所有用户对表SC的查询权限
REVOKE SELECT
ON TABLE SC
FROM PUBLIC;
--标准SQL
REVOKE SELECT
ON SC
FROM PUBLIC;
--T-SQL
可以看到，在U5的查询中已经没有权限了，别的用户也是如此。
SELECT *
FROM SC
--拒绝了对对象 'SC' (数据库 'master'，架构 'dbo')的 SELECT 权限。
【4.10】把用户U5对SC表的INSERT权限收回
REVOKE INSERT
ON TABLE SC
FROM U5 CASCADE ;
--标准SQL
REVOKE INSERT
ON SC
FROM U5 CASCADE ;
--T-SQL
进入用户U5，这里显示U5无法插入：
INSERT
INTO SC
VALUES('201215122','1',67);
--拒绝了对对象 'SC' (数据库 'Student'，架构 'dbo')的 INSERT 权限。
重新授予U5插入权限，但是收回权限时不加CASCADE。这时显示不执行语句。
REVOKE INSERT
ON TABLE SC
FROM U5;
--若要撤消或拒绝可授予的特权，请指定 CASCADE 选项。
REVOKE INSERT
ON SC
FROM U7;
--命令已成功完成。
REVOKE INSERT
ON SC
FROM U6;
--若要撤消或拒绝可授予的特权，请指定 CASCADE 选项。
由于U5授予U6权限，U6授予U7权限。U7取消权限后U6仍旧无法取消权限，这说明只要有 WITH GRANT OPTION，取消权限时就一定要加CASCADE，与它有没有传给别人权限没有关系。
角色是一组权限的集合，一个角色可以包含多种权限 。
一个角色的权限：直接授予这个角色的全部权限加上其他角色，是授予这个角色的全部权限。
【4.11】通过角色来实现将一组权限授予一个用户。
--创建一个角色
CREATE ROLE R1;
--给角色授予权限
GRANT SELECT, UPDATE, INSERT
ON Student
TO R1;
--将这个角色授予王平，张明，赵玲。使他们具有角色R1所包含的全部权限
GRANT R1
TO U5,U6,U7;
--标准SQL
exec sp_addrolemember 'R1', 'U5'
exec sp_addrolemember 'R1', 'U6'
exec sp_addrolemember 'R1', 'U7'
--T-SQL
--可以一次性通过R1来回收王平的这3个权限
REVOKE R1
FROM U5;
exec sp_droprolemember 'R1','U5'
用例子中的语句总是会提示“R1”附近有语法错误。
后来搜索资料说可以通过这个来完成。用U7用户进行试验，sa账户中执行exec sp_addrolemember ‘R1’, 'U7’后，U7中可以进行相应的操作，有权限，执行exec sp_droprolemember ‘R1’,'U7’后则没有了相应的权限。
【4.12】 角色的权限修改
GRANT DELETE
ON Student
TO R1;
在角色中新加DELECT权限。
将R1角色授予U7，在U7中执行以下语句，删除权限能够使用。
DELETE
FROM Student
WHERE Sno='201215556';
【4.13】使R1取消SELECT权限。
REVOKE SELECT
ON Student
FROM R1;
上例中U7与角色之间还没有解除绑定，只执行了修改角色权限的语句，这时候发现U7中的SELECT已经不能查询了。说明用户是随时随着角色变化而变化的。
【4.14】建立计算机系学生的视图，把对该视图的SELECT权限授于U1，把该视图上的所有操作权限授于U2。
CREATE VIEW CS_Student
AS
SELECT *
FROM Student
WHERE Sdept='CS';
GRANT SELECT
ON CS_Student
TO U1;
GRANT ALL PRIVILEGES
ON CS_Student
TO U2;
为CS系的学生建立一个视图。
连接U1，进行查询操作，没有问题，无法进行其他操作：
UPDATE CS_Student
SET Sname='李庸'
WHERE Sname='李勇';
--拒绝了对对象 'CS_Student' (数据库 'Student'，架构 'dbo')的 UPDATE 权限。
连接U2，进行操作，执行成功：
什么是 “存取谓词”？
谓词是计算结果为逻辑值（为：TRUE、FALSE、UNKNOWN）的逻辑表达式。
不直接支持 存取谓词的系统中，通过视图可以间接地实现支持存取谓词的用户权限定义。
问就是不知道，网上一点也无。
为什么要用视图间接实现，直接用基本表不可以吗？
视图可以能分割数据,简化观点。要求是对修改操作计算机系的学生，用试图把计算机系学生筛选出来。
视图能像基本表一样授予或撤消访问许可权，给用户使用表的一部分访问权限而不是全部。通过视图机制可以把要保密的数据对无权存取的用户隐藏起来，数据保护。
【4.15】对修改SC表结构或修改SC表数据的操作进行审计
AUDIT ALTER,UPDATE
ON SC;
一直显示“ “,”附近有语法错误。"我找不出错误，一涉及到SQLserver的审计，就全都是一大堆看不懂的代码，我怀疑SQLserer没有这个语句吧。
AUDIT：设置审计功能 。
找到右侧审核，创建审核，要求找到文件的路径，我找到了数据库Student的路径后，之后就没有了，找不到表的路径，后来我打开文件夹，数据库的权限我不可以访问。我没有找到哪里可以选择审计的功能，开启后，我尝试进行查询修改等操作，审核日志也没有任何变化。
就启用审核出现一条记录，禁用审核出现两条记录。（我启用了两次，禁用了两次，就六条）
【4.16】取消对表SC的审计
NOAUDIT ALTER,UPDATE
ON SC;
NOAUDIT：取消审计功能 。
参考：链接
链接
作者：鹤阕
