数据库作业：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：取消审计功能 。

参考：链接
链接