实验五：数据控制（安全性部分）

实验内容与完成情况(记录所有的实验过程)： 使用SQL对数据进行自主存取控制，包括：授权和权力回收。操作完成后看看已授权的用户是否真正具有授予的数据操作的权力了；权力收回操作之后的用户是否确实丧失了收回的数据操作的权力。SQL Server DBMS通过角色来管理数据库权限可以简化授权过程，所以要掌握数据库角色的创建、授权和删除方法。根据以下要求认真填写实验报告，记录所有的实验用例。 一、授权与回收 1. 授权： 建立多个用户，给他们赋予不同的权限，然后查看是否真正拥有被授予的权限了。具体如下： 建立用户U1、U2、U3、U4、U5、U6、U7，选择其数据库角色全部允许为public。 EXEC SP_ADDLOGIN U1,'U1U1'; EXEC SP_ADDLOGIN U2,'U2U2'; EXEC SP_ADDLOGIN U3,'U3U3'; EXEC SP_ADDLOGIN U4,'U4U4'; EXEC SP_ADDLOGIN U5,'U5U5'; EXEC SP_ADDLOGIN U6,'U6U6'; EXEC SP_ADDLOGIN U7,'U7U7';   EXEC SP_GRANTDBACCESS U1,U1; EXEC SP_GRANTDBACCESS U2,U2; EXEC SP_GRANTDBACCESS U3,U3; EXEC SP_GRANTDBACCESS U4,U4; EXEC SP_GRANTDBACCESS U5,U5; EXEC SP_GRANTDBACCESS U6,U6; EXEC SP_GRANTDBACCESS U7,U7; (2)在DBA（在SQL Server中是以sa这个内置的管理员身份登录查询分析器）与七个刚建的用户之间进行授权 [1-1] 把查询Course表权限授给用户U1。 授权前： GRANT SELECT ON Course TO U1; 授权后： [1-2] 把对Student表和Course表的全部权限授予用户U2和U3。 授权前： U2: U3:   GRANT ALL PRIVILEGES ON Student TO U2,U3;   GRANT ALL PRIVILEGES ON Course TO U2,U3;   授权后： U2: U3: [1-3] 把对表SC的查询权限授予所有用户。 GRANT SELECT ON SC TO PUBLIC [1-4] 把查询Student表和修改学生学号的权限授给用户U4。 授权前: GRANT SELECT,UPDATE(Sno) ON Student TO U4; 授权后：（修改学号的截图不能实现，因为它的学号跟另外一张表是级联的，除非先另外一张才可以改这张） [1-5] 把对表SC的INSERT权限授予U5用户，并允许他再将此权限授予其他用户。 授权前： GRANT INSERT ON SC TO U5 WITH GRANT OPTION   授权后：(因为Sno是Student表的主码，在级联表进行添加肯定是无法运行成功的。) [1-6] 用户U5将对表SC的INSERT权限授予U6，并允许将权限转授给其他用户。 GRANT INSERT ON SC TO U6 WITH GRANT OPTION [1-7] 用户U6将对表SC的INSERT权限授予U7。 GRANT INSERT ON SC TO U7; (3)在授权之后验证用户是否拥有了相应的权限。 在执行完上面七个语句之后，我们可以分别以不同用户的身份登录数据库，进行相关操作，检查系统是否许可。例如： [1-8] U4更新Student表的学生学号。 以下截图的结果是符合的，可以验证U4拥有这个功能的权限，说明他是有条件可以可以更新的，但因为这个学号的属性是级联的关系，要先在SC表里的学号先进行更新，然后才能在Student表进行更新。 [1-9] U7向SC表中插入一条数据：（200215132,2,88）。 以下截图是可以验证U7是拥有这个功能的权限的，因为在SC的Sno是级联的关系，要先在Student表先进行插入后，才能在SC表里进行插入。 二、回收权限 (1)回收权限 [2] 回收权限。将[1]授予的权限部分收回。   [2-1] 将用户U4修改学生学号的权限收回。 REVOKE UPDATE(Sno) ON Student FROM U4; [2-2] 收回所有用户对表SC的查询权限。 REVOKE SELECT ON SC FROM PUBLIC [2-3] 把用户U5对SC表的INSERT权限收回 REVOKE INSERT ON SC FROM U5 CASCADE; (2)在回收权限之后，验证用户是否真正丧失了该权限。 [2-4] 用户U3查询表SC。 由截图可以得出，U3对SC表的查询的功能的权限已经丧失了 [2-5] 用户U6向表SC中插入一条记录（‘200215133’，‘3’，92）。 由截图可以得出，U6已经不能对SC表进行插入的功能的了，说明它已经丧失了这个功能了。 (2)数据库角色 [3]角色的创建与授权。 [3-1] 首先创建一个角色 R1 CREATE ROLE R1; [3-2] 然后使用GRANT语句，使角色R1拥有Student表的SELECT、UPDATE、INSERT权限 GRANT SELECT,UPDATE,INSERT ON Student TO R1; [3-3] 将这个角色授予U1,U3,U7。使他们具有角色R1所包含的全部权限。 EXEC SP_ADDROLEMEMBER R1,U1; EXEC SP_ADDROLEMEMBER R1,U3; EXEC SP_ADDROLEMEMBER R1,U7; [3-4] 对角色R1的权限进行修改，增加对Student表的DELETE权限，并回收对Student表的INSERT权限。 GRANT DELETE ON Student TO R1;   REVOKE INSERT ON Student TO R1; [3-5] 删除角色R1 EXEC SP_DROPROLEMEMBER R1,U1; EXEC SP_DROPROLEMEMBER R1,U3; EXEC SP_DROPROLEMEMBER R1,U7; EXEC SP_DROPROLE R1; 附录： sp_droprole 从当前数据库删除 Microsoft® SQL Server™ 角色。 sp_helpuser 报告有关当前数据库中 Microsoft SQL Server™ 用户Microsoft Windows NT® 用户和数据库角色的信息。 附加题：P155 习题 7 7.今有以下两个关系模式: 职工(职工号，姓名，年龄，职务，工资，都门号) 部门(部门号，名称， 经理名，地址，电话号) 请用SQL的GRANT和REVOKE语句(加上视图机制)完成以下投权定义或存取控制功能:   今有两个关系模式：emp (职工)和dep（部门）： 建立数据库以及两个表如下： CREATE DATABASE WD;   CREATE TABLE emp( empno CHAR(3), ename CHAR(8), age SMALLINT, job VARCHAR(15), sal MONEY, dno CHAR(2) );   INSERT INTO emp VALUES ('001','王明',35,'MANAGER',2300,'01'), ('002','李勇',33,'CLERK',1300,'01'), ('003','刘星',38,'PRESIDENT',4300,'01'), ('004','张新',23, 'MANAGER',2500,'02'), ('005','周平',23, 'ANALYST',2100,'02'), ('006','杨兰',41, 'MANAGER',2900,'03');   CREATE TABLE dep( dno CHAR(2), dname CHAR(8), maname CHAR(8), addr VARCHAR(15), telephone VARCHAR(11) );   INSERT INTO dep VALUES ('01','aaa','王明','dkjfsd;lk','87818443'), ('02','bbb','张新','ddf;lk','82233443');   建立用户王明、李勇、刘星、张新、周平、杨兰，选择其数据库角色全部允许为public。 在DBA与六个新建用户之间进行授权 (1)用户王明对两个表有SELECT权限。 EXEC SP_ADDLOGIN 王明,'wangming'; EXEC SP_GRANTDBACCESS 王明,王明; GRANT SELECT ON emp TO 王明   GRANT SELECT ON dep TO 王明 (2)用户李勇对两个表有INSERT和DELETE权限。 EXEC SP_ADDLOGIN 李勇,'liyong'; EXEC SP_GRANTDBACCESS 李勇,李勇; GRANT INSERT ON emp TO 李勇   GRANT INSERT ON dep TO 李勇   GRANT DELETE ON emp TO 李勇   GRANT DELETE ON dep TO 李勇   (3)每个职工只对自己的记录有SELECT权限。 CREATE VIEW view_user AS SELECT * FROM emp WHERE ename=USER_NAME(); GO   GRANT SELECT ON view_user TO PUBLIC (4)用户刘星对职工表有SELECT权限，对工资字段具有更新权限。 EXEC SP_ADDLOGIN 刘星,'liuxing'; EXEC SP_GRANTDBACCESS 刘星,刘星; GRANT SELECT,UPDATE(sal) ON emp TO 刘星   (5)用户张新具有修改这两个表的结构的权限。 EXEC SP_ADDLOGIN 张新,'zhangxin'; EXEC SP_GRANTDBACCESS 张新,张新;   GRANT ALTER ON emp TO 张新   GRANT ALTER ON dep TO 张新 (6)用户周平具有对两个表的所有权限(读、插、改、删数据)，并具有给其他用户授权的权限。 EXEC SP_ADDLOGIN 周平,'zhouping'; EXEC SP_GRANTDBACCESS 周平,周平; GRANT ALL PRIVILEGES ON emp TO 周平;   GRANT ALL PRIVILEGES ON dep TO 周平; (7)用户杨兰具有从每个部门职工中SELECT最高工资、最低工资、平均工资的权限，他不能查看每个人的工资。 EXEC SP_ADDLOGIN 杨兰,'yanglan'; EXEC SP_GRANTDBACCESS 杨兰,杨兰; GO CREATE VIEW view_salary AS SELECT MAX(sal) 最高工资,MIN(sal) 最低工资,AVG(sal) 平均工资 FROM emp; GO GRANT SELECT ON view_salary TO 杨兰