1、实验五 数据库完整性与安全性实验1、实验目的1. 通过对完整性规则的定义实现，熟悉了解 kingbase 中完整性保证的规则和实现方法，加深对数据完整性的理解。2. 通过对安全性相关内容的定义，熟悉了解 kingbase 中安全性的内容和实现方法，加深对数据库安全性的理解2、实验环境操作系统：Microsoft Windows 7 旗舰版 （32 位） 。 数据库版本：MySQL 6.23、实验内容完整性实验：（1） 分别定义学生数据库中各基表的主键、外键，实现实体完整性约束和参照完整性约束；在 create table 时已经定义了各个基表的主键。因此，首先对每个基表删除主键，再添加主键。实
2、现实体完整性约束。如下图。删除主键：添加主键：sc 表中的 cno 和 sno 分别是 course 表和 student 表的外关键字。下图为给 sc 表添加 student 和 course 的外键，实现参照完整性约束。alter table sc add constraint fk_student foreign key(sno) references student(sno);alter table sc add constraint fk_course foreign key(cno) references course(cno);主键和外键均添加成功，输入 show create
3、table 命令来查看各基表信息。（2） 分别向学生表、课程表插入具有相同学号和相同课程编号的学生数据和课程数据，验证其实体完整性约束； 向课程表中插入课程号为 C01 的课程。 向学生表中插入学号为 30201 的学生。由上面两个实验来看，分别对 student 和 course 表插入具有相同学号和相同课程号的学生信息和课程信息，都显示 sql 语句错误信息为：Duplicate entry * for key PRIMARY。验证了实体完整性约束。（3） 向学生选课表中插入一条数据，课程编号是课程表中没有的，验证参照完整性约束；插入成绩信息是学号为 31428（student 中存在）
4、，课程号为 C07（course 中不存在）的学生成绩信息。受参照完整性约束，插入失败。（4） 删除学生表中的所有数据，验证参照完整性约束；因为 sc 表中的 sno 是 student 表的外键，删除学生表中的所有数据违背了参照完整性约束，所以无法删除。（5） 定义存储过程，完成查询某个学生的选课情况，并执行。本次实验设计的存储过程完成的是查询某个学生的选课情况。参数指定为 sno 学号。存储过程是一组为了完成特定功能的 SQL 语句集，存储在数据库中经过第一次编译后再次调用不需要再次编译，用户通过指定存储过程的名字并给出参数来执行它。因为在存储过程中会用到结束符； ，所以首先用 delim
5、iter 修改结束符为/，创建存储过程的 sql 语句为：create procedure search_sc（ num char（ 6） ）beginselect sno， cno from sc where sno=num；end其中定义了一个读入的参数为 num，用来读入所要查询的学生的学号。创建成功。调用该存储过程，查看学号为31428的学生的选课信息。（6） 定义触发器，当向学生表插入新的一条记录时，将所有学生出生日期加 1；并对其进行测试。触发器的功能是监视某种情况，并触发某种操作。创建触发器的语句为： create trigger update_bdateafter insert
6、 on studentfor each row beginupdate student set bdate=bdate+1;end表示当对 student 表进行插入操作后，对 student 表进行出生年月日加 1 的 sql 语句的执行。向 student 里插入一条新纪录，触发触发器 update_bdate 的操作，但是出现了下述错误信息：it is already used by statement which invoked this stored function/trigger。经过了解，mysql 中需要复制重建一个新的 table，然后通过对旧 table 的操作来触发对新
7、 table 的操作。create table new_student select * from student; /建立新表create trigger t_bdate /建立触发器before insert on student for each row update new_student set bdate=date_add(bdate,interval 1 day)insert into student /插入查询语句values(31011,lihua,男 ,1991-01-01 00:00:00,计算机 ,3147) 查看更新后的 student 表和 new_student
8、表如下：student 表中的数据没有发生改变，只是插入了一条新的学生信息，而 new_student 表中所有学生信息的出生日期都为 1月 2 号，比 student 表中的日期加 1，但是没有新插入的学生信息。安全性实验内容（1） 定义一新的登陆帐号、数据库用户，并授予其访问学生数据库的读权限；创建新的登陆账号、数据库用户有两种方式，分别为 grant 授予权限时创建和create user。下面分别用两种方式创建用户。grant usage on *.* to lqy1localhost identified by 123456 with grant option;with grant
9、option 是使 lqy1 用户拥有授予权限的权利，是一句表示权限传递的语句。create user lqy2localhost identified by 123456;以 lqy1 为例，授予其访问学生数据库的读权限。发现在 user table 中并没有找到 lqy1 用户，发生这一错误的原因是在变更了mysql.user 表之后，没有使用 FLUSH PRIVILEGES 命令来更新权限表（grant tables） 。flush privileges 命令本质上的作用是将当前 user 和 privilige 表中的用户信息/权限设置从 mysql 库(MySQL 数据库的内置库)
10、 中提取到内存里。MySQL 用户数据和权限有修改后，希望在“ 不重启 MySQL 服务“ 的情况下直接生效，那么就需要执行这个命令。通常是在修改 ROOT 帐号的设置后，怕重启后无法再登录进来，那么直接 flush 之后就可以看权限设置是否生效。而不必冒太大风险。更新如下：查看 user table ，发现 lqy1 和 lqy2 已更新。再次执行授权语句，发现仍然无法找到该用户。然后查找了关于“mysql 新建用户本地无法登录”的资料，发现问题在localhost和%上。当你试图连接 MySQL服务器时，服务器基于你的身份以及你是否能通过供应正确的密码验证身份来接受或拒绝连接。你的身份取决
11、于两个信息：一是从哪个主机连接，二是 mysql 用户名。Host 值% 匹配任何主机名，因此将定义新的用户的 sql 语句改为：grant usage on *.* to lqy1% identified by 123456 with grant option;再次执行授权语句,授予 lqy3 用户访问学生数据库的读权限。（2） 分别用 root 用户和新定义的用户访问学生数据库，并对其中的学生表数据进行修改；root 超级用户登录：lqy3 用户登录：Root 超级用户查询及修改课程信息：Lqy3 用户查询及修改课程信息，因为 lqy3 用户仅有读权限，所以可以查询数据库，但是不能够对数据
12、库中的信息进行修改。（3） 再次用此用户访问学生数据库，并对其中的学生表数据进行修改。登录 root 用户，授予 lqy3 用户写权限：再次用 lqy3 用户登录，更改课程信息，成功。实验总结1、实现参照完整性约束时遇到的问题最初建立外键时 sql 语句错误，错误信息是 “cannot add or update a child row: a foreign key constraint fails”，原因在于：查询优化实验中，对 sc 表插入了三个元组，sno 的值为10111，而该值并没有在 student 表中存在。参照完整性约束的规定是：若 M 是关系 S 中的一属性组，且 M 是另一
13、关系 Z 的主关键字，则称 M 为关系 S 对应关系 Z 的外关键字。若M 是关系 S 的外关键字，则 S 中每一个元组在 M 上的值必须是空值或是对应关系 Z 中某个元组的主关键字值。因此，存在三个 sno=10111的元组，而 student 表中并不存在sno=10111的学生信息，相当于三个不存在的学生却拥有成绩表。这显然是不合理的。因此只要将这三行元组删除即可。2、定义存储过程中遇到的问题注意要先更改结束符，因为存储过程中的 sql 语句以通用的分号为结束符，其作为定义存储过程的 sql 语句的一部分，会引发语句错误。3、定义触发器时遇到的问题使用 create trigger 语句创建触发器是可以成功的，但是向 student 插入记录，触发触发器的操作时，出现了循环的错误信息，后来将 student 表复制了一份，重写 create trigger 语句，定义触发器为：在对旧表 student 进行插入操作之前，更新 new_student 表，使每一元组的 bdate 增加一天。4、定义新的登陆账号、数据库用户遇到的问题grant 和 create 均可创建用户，但注意创建的用户是基于所有主机（%）的。另外在命令行创建了用户，可以使用 flush privileges 更新权限表，而不用重新登录。
