数据完整性与安全性控制

一.实验内容、步骤以及结果

1. 1. 利用图形用户界面对Student库中，增加以下的约束和索引，并验证违反相关完整性时的数据库系统的违约处理。（10分）
   1. 1. S表的非空约束：为出生日期添加非空约束。
      2. S表的主键约束：将学号(sno)设置为主键。

1. 1. 1. S表的唯一约束：为姓名(sname)添加唯一约束(唯一键)。

1. 1. 1. S表的缺省约束：为性别(sgender)添加默认值，其值为“男”。

当违反约束时,语句将不能被执行,直接报错.

1. 利用图形用户界面针对Student库，给SC表添加外键约束，将sno,cno设置为外键，其引用表分别是S表和C表，外键名分别为：sc_fk_sno和sc_fk_cno。（10分）

     具体步骤：

   在图形界面中找到改变表,然后找到外部键约束栏,逐条修改即可.

运行结果：

(2). 尝试不同的违反外键约束的策略并进行验证NO action/ restrict/ cascade/ set null。

具体步骤: 1.先为两个外键添加约束, 然后尝试更新数据表, 观察使用不同约束方式时表的操作.

运行结果:

可以在日志中看到,

对于cascade约束,删除主表时自动删除从表。删除从表，主表不变;更新主表时自动更新从表。更新从表，主表不变.

    对于RESTRICT约束, 删除：从表记录不存在时，主表才可以删除。删除从表，主表不变;更新：从表记录不存在时，主表才可以更新。更新从表，主表不变. 而我更新了从表,但是该项在主表中存在, 故不可以更新,报错.

对于set null约束,删除主表时自动更新从表值为NULL。删除从表，主表不变

更新：更新主表时自动更新从表值为NULL。更新从表，主表不变.

1. 删除student库中的三张表，用SQL语言重新创建有以下约束的表。

     

• S表：基本约束要求同第1小题。

• C表：cpno是c表的外键，引用自C表的cno。

• SC表：外键约束如第2小题，同时要求成绩grade的有效值为[0,100]。另外，SC表增加新列id：将id设为主键，且自增字段，每次插入一条选课记录自动+1。

1. 用SQL语言针对student库添加或者删除以下的完整性约束。(10分）
   1. 1. 增加sgender的取值约束：S表中的sgender取值只能是“男”或“女”。

1. 1. 1. 删除第(1)小题所创建的sgender取值约束。

1. 1. 1. 删除SC表的外键约束。

1. 1. 1. 学生表S中增加新列tname(表示该学生的导师姓名)，且要求导师名必须全部是字母（大写小写均可以）、且长度不能低于8个字符。

（提示：可以使用char_length()函数和REGEXP正则表达式）

1. 1. 在图形用户界面中创建新用户并针对student数据库进行赋权。（20分）
   1. 1. 创建两个可以登录的本地用户: 王明，李勇。

可以使用如下方法创建用户,两个用户同理

• 1. 1. 完成以下的权限赋予：
• 用户王明对所有表均有select和insert的权力。

• 用户李勇对该数据库select，insert，delete, update和create权限。

1. 用SQL语句授权和收回权限并对权限加以验证。（40分）

要求：首先创建职工表e和部门表d，表结构如下：

职工表e（职工号，姓名，年龄，职务，工资，部门号）

部门表d（部门号，名称，经理名，地址，电话号）

然后创建每道题中涉及的用户，针对每个小题先完成授权并进行权限验证，然后再收回权限并进行权限验证。

1. 用户王明对两个表有查询权限。

进行用户权限验证时,需要更换用户登录mysql, 然后行使相关权限,如果成功就是权限授予成功.

移除授权:

检查权限:

1. 用户李勇对两个表有insert和delete权限。

检查权限:

回收权限:

检查权限:

可以看到权限被成功移除.

1. 每个职工只对自己的记录有查询权限。

权限检查:

可以看到, 用户liyong 无法对 wangming的数据进行select.

权限回收:

权限检查:

可以看到, 用户liyong 也没有了对liyong的数据进行select 的权限.

1. 用户刘星对职工表有查询权限，对工资字段具有更新权限。

可以看到用户liuxing 创建成功, 并且权限赋予成功.

用户权限移除:

1. 用户张新具有修改这两个表的结构的权限。

用户授权:

用户权限回收:

1. 用户周平具有对两个表的所有权限，并具有给其他用户授权的权限。

用户授权:

权限回收:

1. 用户杨兰具有从每个部门职工中查询最高工资，最低工资，平均工资的权限，他不能查看每个人的工资。

回收权限:

• 实验中出现的问题以及解决方案(对于未解决问题请将问题列出来)

       实验开始并不了解约束是什么 ,后来查阅资料学习约束分为主键约束和外键约束, 主键约束主要用于维护数据完整性, 外键约束主要作用于多表间数据的关联完整性. 后来认真学习内容, 学习使用各种约束的方法, 比如外键约束的各种违反策略和不同的处理方式, 最后顺利完成实验.

       在授权和权限回收时, 一开始并不了解语法, 不知道 授权用to 而回收权限使用from,总是写语法错误,后来细致核对书写方式, 完成实验