数据库三 数据安全管理–视图、访问控制

【实验名称】：数据安全管理–视图、访问控制

【实验目标】：

1. 理解视图消解机制；
2. 理解视图的可更新性；分析更新过程中出现失败的原因；
3. 理解视图的安全性支持。
4. 学会使用grant和revoke命令来操作用户的权限，包括系统、对象等权限；
5. 理解基于角色的访问控制；

【实验具体要求】：

1. 视图

1. 视图的消解与更新
   1. 根据学生表、教师表、课程表、学生选课表，创建一个包含学号、学生姓名、课程号、课程名称、学分、任课教师工号、任课教师姓名的视图v_stu_lesson；
   2. 对视图v_stu_lesson进行查询操作，查询教师号为3的老师所教授课程的学生选课信息，包含视图所有字段
   3. 对学生表、教师表、课程表、学生选课表进行联合查询操作，查询教师号为3的老师所教授课程的学生选课信息，包括学号、学生姓名、课程号、课程名称、学分、任课教师工号、任课教师姓名；（理解视图消解机制）
   4. 更新视图v_stu_lesson，更新课程4的学分为3分，分析更新操作可以执行成功或失败的原因；
   5. 更新视图v_stu_lesson，将教授课程1的老师变为teachnum为2，分析更新操作可以执行成功或失败的原因；
2. 视图授权
   1. 由课程表创建一个只有‘C++编程’的课程信息的视图 v_c_lesson
   2. 将v_c_lesson的查询和修改权限授予一个新用户user_03，并测试查询和修改操作在授权前后的执行情况

1. 权限

1. 使用root用户登录MYSQL数据库系统，创建数据库role_demo,创建表t1和t2，分别创建user1和user2两个用户，user1具有t1的insert，t2的select权限；user2具有t1的select，t2的INSERT权限；
2. 使用user1用户登录系统，user1能查看t1表吗？使用user2用户登录系统，user2能对查看t1、t2表吗？
3. 现在交换两个用户的权限（撤回重新授权，麻烦）
4. 删除user1和user2

1. 角色

（1）创建两个角色，角色1具有t1的insert，t2的select权限；角色2具有t1的select，t2的INSERT权限；

（2）创建user1和user2，将角色1分配给user1，角色2分配给user2

（3）使用user1用户登录系统，user1能查看t1表吗？使用user2用户登录系统，user2能对查看t1、t2表吗？

（4）现在交换两个用户的权限（交换角色即可）

【实验步骤】：

（1）视图的消解与更新

1）根据学生表、教师表、课程表、学生选课表，创建一个包含学号、学生姓名、课程号、课程名称、学分、任课教师工号、任课教师姓名的视图v_stu_lesson；

2）对视图v_stu_lesson进行查询操作，查询教师号为3的老师所教授课程的学生选课信息，包含视图所有字段

3）对学生表、教师表、课程表、学生选课表进行联合查询操作，查询教师号为3的老师所教授课程的学生选课信息，包括学号、学生姓名、课程号、课程名称、学分、任课教师工号、任课教师姓名；（理解视图消解机制？）

4)  更新视图v_stu_lesson，更新课程4的学分为3分，分析更新操作可以执行成功或失败的原因；

原因：因为视图的select目标中没有包含聚集函数，也没有使用UNIQUE和DISTINCT,同时视图中没有使用group by子句，也不包括算术表达式算出来的列。所以可以进行更新。视图是基于多个表连接而成的，对这个表进行更新操作时，只影响了其中一个表，所以更新成功。

5)  更新视图v_stu_lesson，将教授课程1的老师变为teachnum为2，分析更新操作可以执行成功或失败的原因；

原表：

第一次错误：

Error Code: 1451. Cannot delete or update a parent row: a foreign key constraint fails (`student_system`.`leave_apply`, CONSTRAINT `FK_leave_apply_teacherNum` FOREIGN KEY (`teacherNum`) REFERENCES `teacher` (`teacherNum`))

原因：无法删除或更改这个字段，因为外链约束会失效。因为在更改这条操作的时候会自动检查外链。

解决方法：SET FOREIGN_KEY_CHECKS = 0;

第二次错误;

Error Code: 1062. Duplicate entry '2' for key 'teacher.PRIMARY'

原因：主键值重复，导致插入失败。因为表中已经存在有teacherNum=2的相关信息存在，主键值重复。而当视图是基于多个表连接而成的，对这个表进行更新操作时，如果影响了其中多个表，更新会失败。

（2）视图授权

1)  由课程表创建一个只有‘C++编程’的课程信息的视图 v_c_lesson

2)  将v_c_lesson的查询和修改权限授予一个新用户user_03，并测试查询和修改操作在授权前后的执行情况

测试：

授权前：

授权后：

1. 权限

（1）  使用root用户登录MYSQL数据库系统，创建数据库role_demo,创建表t1和t2，分别创建user1和user2两个用户，user1具有t1的insert，t2的select权限；user2具有t1的select，t2的INSERT权限；

（2）  使用user1用户登录系统，user1能查看t1表吗？使用user2用户登录系统，user2能对查看t1、t2表吗？

选择授予权限。

然后切换用户到user1

可以看到已经连接到role_demo库

可以对t1表进行insert操作，不能进行select操作

（3）  现在交换两个用户的权限（撤回重新授权，麻烦）

先进行权限的撤回

重新进行授权

（4）  删除user1和user2

1.   角色

（1）创建两个角色，角色1具有t1的insert，t2的select权限；角色2具有t1的select，t2的INSERT权限；

（2）创建user1和user2，将角色1分配给user1，角色2分配给user2

然后要进行激活，才能使用

（3）使用user1用户登录系统，user1能查看t1表吗？使用user2用户登录系统，user2能对查看t1、t2表吗？

（4）现在交换两个用户的权限（交换角色即可）

【小结】：

用户

一、创建用户

Oracle内部有两个建好的用户：SYSTEM和SYS。用户可直接登录到SYSTEM用户以创建其他用户，因为SYSTEM具有创建别的用户的权限。在安装Oracle时，用户或系统管理员首先可以为自己建立一个用户。

create user ‘用户名’ identified by ‘用户密码’;

该命令还可以用来设置其他权限。要改变一个口令，可以使用alter user命令：

alter user test identified by usr01;

二、删除用户

删除用户，可以使用drop user命令，如下所示：

 drop user test;

如果用户拥有对象，则不能直接删除，否则将返回一个错误值。指定关键字cascade，可删除用户所有的对象，然后再删除用户。下面的例子用来删除用户与其对象：

drop user test cascade;

三、查看用户状态

   当前ORACLE用户的状态可查看视图DBA_USERS;一般情况下在使用的正常用户均处于OPEN状态。

select username,account_status from dba_users;

  用户状态一共有九种状态，可分为两类：1.基本状态；2.组合状态。

五种基本状态：OPEN、EXPIRED、EXPIRED(GRACE)、LOCKED(TIMED)、LOCKED；

  四种组合状态：EXPIRED & LOCKED(TIMED)、EXPIRED(GRACE) & LOCKED(TIMED)、EXPIRED & LOCKED、EXPIRED(GRACE) & LOCKED。

  后四种的组合状态可通过状态号STATUS#获得其状态的两个组合。锁定的两种状态LOCKED、LOCKED(TIMED)和密码过期的两种状态EXPIRED、EXPIRED(GRACE)之间任意组合即2×2=4，因此有四种组合状态。

  五种基本状态可分为三类：正常状态、锁定状态、密码过期状态。其中OPEN状态表示用户处于正常状态；LOCKED和LOCKED(TIMED)表示用户被锁定状态

用户被锁定有两种：

  LOCKED：是DBA显式的通过SQL语句对用户进行锁定，见下述语句：

alter user [username] account lock

  LOCKED(TIMED)：这一种是被动的锁定，默认情况下如果密码输入错误超过10次则被动锁定。

  EXPIRED、EXPIRED(GRACE)表示用户密码过期状态

  修改PROFILE中的PASSWORD_LIFE_TIME实现密码是否过期：

  alter profile default limit password_life_time unlimited;

  密码过期后也可修改PROFILE中的PASSWORD_GRACE_TIME控制使用的天数：

  alter profile default limit password_grece_time 180;

  对于密码过期的用户OPEN：

  alter user [username] identified by account unlock;

角色

一、创建角色  

用户还可以在Oracle创建自己的role。用户创建的role可以由表或系统权限或两者的组合构成。为了创建role，用户必须具有CREATE ROLE系统权限。下面给出一个create role命令的实例：

create role STUDENT;

这条命令创建了一个名为STUDENT的role。

二、角色授权

一旦创建了一个role，用户就可以给他授权。给role授权的grant命令的语法与对用户授权的语法相同。在给role授权时，在grant命令的to子句中要使用role的名称，如下所示：

grant select on CLASS to STUDENT;

三、删除角色

要删除角色，可以使用drop role命令，如下所示：

drop role STUDENT;