SQL数据库实验.doc

数据库系统原理实验报告实验七 数据安全性和完整性控制 电子与信息工程系2011年 11 月 19 日实验七 数据安全性和完整性控制一、 实验目的（1） 熟悉通过SQL对数据进行安全性和完整性控制的方法；（2） 熟悉SQL SERVER实用数据库的基本操作。二、实验内容1.使用企业管理器建立数据库用户，使用SQL对数据进行安全性控制，包括：授权和权力回收。操作完成后看看已授权的用户是否真正具有授予的数据操作的权力了；权力收回操作之后的用户是否确实丧失了收回的数据操作的权力。2.使用SQL对数据进行完整性控制(三类完整性、CHECK短语、CONSTRAIN子句)。用实验证实，当操作违反了完整性约束条件时，系统是如何处理的。三、实验要求1.根据实验要求认真填写实验报告，记录所有的实验用例；2.在实验报告中要给出具体的操作要求和过程，并针对各种情况做出具体的分析和讨论。四、实验用例（包括相应的SQL语句）1)假设有下面两个关系模式： 职工(职工号，姓名，年龄，职务，工资，部门号)，其中职工号为主码； 部门(部门号，名称，经理名，电话)，其中部门号为主码； 用SQL语言定义这两个关系模式，要求在模式中完成以下完整性约束条件的定义： (1)定义每个模式的主码；(2)定义参照完整性；(3)定义职工年龄不得超过60岁。 2)用SQL的GRANT和REVOKE语句(加上视图机制)完成以下授权定义或存取控制功能：(a)用户王明对两个表有SELECT权力； (b)用户李勇对两个表有INSERT和DELETE权力； (c)用户刘星对职工表有SELECT权力，对工资字段具有更新权力； (d)用户周平具有对两个表所有权力(读，插，改，删数据)，并具有给其他用户授权的权力； (e)用户杨兰具有从每个部门职工中SELECT最高工资，最低工资，平均工资的权力，他不能查看每个人的工资。3)把(a)(e)的每一种情况，撤销各用户所授予的权力。实验具体操作如下： (1)定义关系模式代码：Create table dept(部门号 numeric(3) primary key, 名称 char(6), 经理名 char(6), 电话 char(11);Create table emp(职工号 numeric(4) primary key, 姓名 char(6), 年龄 int check(年龄=60), 职务 char(10), 工资 int, 部门号 numeric(3),Foreign key(部门号)references 部门(部门号);(2)授权功能 （a）用户王明对两个表有SELECT权力授权前： 授权：grant select on dept to 王明; grant select on emp to 王明; 授权后: (b)用户李勇对两个表有INSERT和DELETE权力 以对emp表的insert权限为例授权前：授权：grant delete on emp to 李勇;grant insert on emp to 李勇;grant delete on dept to 李勇;grant insert on dept to 李勇; 授权后：(c)用户刘星对职工表有SELECT权力，对工资字段具有更新权力select 权限和（a）一样，下面实现对工资字段具有更新权利授权前：授权：grant select,update(工资) on emp to 刘星;授权后：(d)用户周平具有对两个表所有权力(读，插，改，删数据)，并具有给其他用户授权的权力 对表的所有权利代码实现，下面实现对其他用户授权的权力授权前：授权：grant all privilegeson emp to 周平 with grant option;授权后 ：(e)用户杨兰具有从每个部门职工中SELECT最高工资，最低工资，平均工资的权力，他不能查看每个人的工资首先建立视图SQL语句如下： create view v_emp(最高工资,最低工资,平均工资) as select max(工资),min(工资),avg(工资) from emp group by 部门号对用户杨兰授权 授权前： 授权：grant select on v_emp to 杨兰;授权后：（3）对每个用户解除权限SQL语句：revoke select on dept from 王明;revoke select on emp from 王明;revoke insert on emp from 李勇;revoke insert on dept from 李勇;revoke delete on emp from 李勇;revoke delete on dept from 李勇;revoke select,update(工资) on emp from 刘星;revoke all privileges on emp from 周平 cascade;revoke all privileges on dept from 周平 cascade;revoke select on v_emp from 杨兰;五、实验内容完成情况 根据实验要求和参考实例中的题目运用所学课程，基本上实现了所有功能，对每个用户的授权GRANT 和收回权限REVOKE,对用户周平实现了可以对其他用户授权的权限，用户杨兰的SELECTzZ最高最低平均工资权限，通过建立视图，然后授权用户SELECT 视图的权限实现的。REVOKE用户权限，通过批量的方式实现的。六、出现的问题及解决方案1、 对用