北邮数据库实验五 数据库完整性与安全性实验

实验五实验五 数据库完整性与安全性实验数据库完整性与安全性实验 1 实验目的 实验目的 1 通过对完整性规则的定义实现 熟悉了解 kingbase 中完整性保证的规则和实现方法 加深对数据完整性的理解 2 通过对安全性相关内容的定义 熟悉了解 kingbase 中安全性的内容和实现方法 加深 对数据库安全性的理解 2 实验环境 实验环境 操作系统 Microsoft Windows 7 旗舰版 32 位 数据库版本 MySQL 6 2 3 实验内容 实验内容 完整性实验 完整性实验 1 分别定义学生数据库中各基表的主键 外键 实现实体完整性约束和参照完整性分别定义学生数据库中各基表的主键 外键 实现实体完整性约束和参照完整性 约束 约束 在 create table 时已经定义了各个基表的主键 因此 首先对每个基表删除主键 再添加主键 实现实体完整性约束 如下图 删除主键 添加主键 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 table 命令来查看各基表信息 2 分别向学生表 课程表插入具有相同学号和相同课程编号的学生数据和课程数据 分别向学生表 课程表插入具有相同学号和相同课程编号的学生数据和课程数据 验证其实体完整性约束 验证其实体完整性约束 向课程表中插入课程号为 C01 的课程 向学生表中插入学号为 30201 的学生 由上面两个实验来看 分别对 student 和 course 表插入具有相同学号和相同课程号 的学生信息和课程信息 都显示 sql 语句错误信息为 Duplicate entry for key PRIMARY 验证了实体完整性约束 3 向学生选课表中插入一条数据 课程编号是课程表中没有的 验证参照完整性约向学生选课表中插入一条数据 课程编号是课程表中没有的 验证参照完整性约 束 束 插入成绩信息是学号为 31428 student 中存在 课程号为 C07 course 中不存在 的学生成绩信息 受参照完整性约束 插入失败 4 删除学生表中的所有数据 验证参照完整性约束 删除学生表中的所有数据 验证参照完整性约束 因为 sc 表中的 sno 是 student 表的外键 删除学生表中的所有数据违背了参照完 整性约束 所以无法删除 5 定义存储过程 完成查询某个学生的选课情况 并执行 定义存储过程 完成查询某个学生的选课情况 并执行 本次实验设计的存储过程完成的是查询某个学生的选课情况 参数指定为 sno 学 号 存储过程是一组为了完成特定功能的 SQL 语句集 存储在数据库中经过第一次 编译后再次调用不需要再次编译 用户通过指定存储过程的名字并给出参数来执行它 因为在存储过程中会用到结束符 所以首先用 delimiter 修改结束符为 创 建存储过程的 sql 语句为 create procedure search sc num char 6 begin select sno cno from sc where sno num end 其中定义了一个读入的参数为 num 用来读入所要查询的学生的学号 创建成功 调用该存储过程 查看学号为 31428 的学生的选课信息 6 定义触发器 当向学生表插入新的一条记录时 将所有学生出生日期加定义触发器 当向学生表插入新的一条记录时 将所有学生出生日期加 1 并对 并对 其进行测试 其进行测试 触发器的功能是监视某种情况 并触发某种操作 创建触发器的语句为 create trigger update bdate after insert on student for each row begin update 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 的操作来触 发对新 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 表如下 student 表中的数据没有发生改变 只是插入了一条新的学生信息 而 new student 表中所有学生信息的出生日期都为 1 月 2 号 比 student 表中的日期加 1 但是没有新插入的学生信息 安全性实验内容安全性实验内容 1 定义一新的登陆帐号 数据库用户 并授予其访问学生数据库的读权限 定义一新的登陆帐号 数据库用户 并授予其访问学生数据库的读权限 创建新的登陆账号 数据库用户有两种方式 分别为 grant 授予权限时创建和 create user 下面分别用两种方式创建用户 grant usage on to lqy1 localhost identified by 123456 with grant option with grant option 是使 lqy1 用户拥有授予权限的权利 是一句表示权限传递的语 句 create user lqy2 localhost identified by 123456 以 lqy1 为例 授予其访问学生数据库的读权限 发现在 user table 中并没有找到 lqy1 用户 发生这一错误的原因是在变更了 mysql user 表之后 没有使用 FLUSH PRIVILEGES 命令来更新权限表 grant tables flush privileges 命令本质上的作用是将当前 user 和 privilige 表中的用户信息 权限设置 从 mysql 库 MySQL 数据库的内置库 中提取到内存里 MySQL 用户数据和权限有修 改后 希望在 不重启 MySQL 服务 的情况下直接生效 那么就需要执行这个命令 通常是在修改 ROOT 帐号的设置后 怕重启后无法再登录进来 那么直接 flush 之后 就可以看权限设置是否生效 而不必冒太大风险 更新如下 查看 user table 发现 lqy1 和 lqy2 已更新 再次执行授权语句 发现仍然无法找到该用户 然后查找了关于 mysql 新建用 户本地无法登录 的资料 发现问题在 localhost 和 上 当你试图连接 MySQL 服务器时 服务器基于你的身份以及你是否能通过供应正确的密码验证身份来接受或 拒绝连接 你的身份取决于两个信息 一是从哪个主机连接 二是 mysql 用户名 Host 值 匹配任何主机名 因此将定义新的用户的 sql 语句改为 grant usage on to lqy1 identified by 123456 with grant option 再次执行授权语句 授予 lqy3 用户访问学生数据库的读权限 2 分别用分别用 root 用户和新定义的用户访问学生数据库 并对其中的学生表数据进行修用户和新定义的用户访问学生数据库 并对其中的学生表数据进行修 改 改 root 超级用户登录 lqy3 用户登录 Root 超级用户查询及修改课程信息 Lqy3 用户查询及修改课程信息 因为 lqy3 用户仅有读权限 所以可以查询数据库 但是不能够对数据库中的信息进行修改 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 是另一关系 Z 的主关键字 则称 M 为关系 S 对应关系 Z 的外关键字 若 M 是关系 S 的外关键字 则 S 中每一个元组在 M 上的值必须是空值或是对应关系 Z 中某 个元组的主关键字值 因此 存在三个 sno 10111 的元组 而 student 表中并不存在 sno 10111 的学生信息 相当于三个不存在的学生却拥有成绩表 这显然是不合理的 因 此只要将这三行元组删除即可 2 定义存储过程中遇到的问题 定义存储过程中遇到的问题 注意要先更改结束符 因为存储过程中的 sql 语句以通用的分号为结束符 其作为定 义存储过程的 sql 语句的一部分 会引发语句错误 3 定义触发器时遇到的问题 定义触发器时遇到的问题 使用 create trigger 语句创建触发器是可以成功的 但是向 student 插入记录 触发触发 器的操作时 出现了循环的错误信息 后来将 stud