合肥师范学院 数据库实验三[高教成教]

1、合肥师范学院实验报告 姓名： 课程名称：数据库原理 院（系）：计算机学院 专业/年级： 2014计算机科学与技术（嵌入式班） 实验三数据库完整性与安全性（4课时）一、 实验目的1. 理解实体完整性、参照完整性、用户自定义完整性的概念，学习三类完整性的实现，了解违反完整性的结果；2. 掌握MS SQL SERVER中的有关用户登录认证及管理方法，熟练使用自主存取控制进行权限管理；二、 实验预习内容在认真阅读教材及实验指导书【2.1 实体完整性】、【2.2 参照完整性】、【2.3 用户自定义完整性】、【2.4 触发器】、【2.5 综合案例】和【3.1 用户标识与鉴别】、【3.2 自主存取控制】、【

2、3.3 视图机制在自主存取控制上的应用】、【3.4 Public角色在安全性中的应用】、【3.8 综合案例】的基础上，上机前请预习以下内容，并在空白处填写相应的步骤或命令。1. 使用SCHOOL数据库1） 创建一张新表CLASS，包括Class_id（varchar（4），Name（varchar（10），Department（varchar（20）三个属性列，其中Class_id为主码： Create table CLASS (Class_id varchar(4) primary key,name varchar（10）,department varchar（20）);2） 执行两次相同的

3、插入操作（0001，01csc,cs），结果如何； insert into class values(0001，01csc,cs);2. 使用SQL命令创建一张学生互助表，要求：包括学生编号、学生姓名、学生帮助对象的编号，每个学生有且仅有一个帮助对象，帮助的对象必须是已存在的学生。create table studenthelp(sid char(10), sname char(30),shid char(10) unique,foreign key(sid) references students(sid);3. 使用STC数据库，修改设置，完成以下2种不同的要求，请写出对应的语句或简述操作

4、过程：1） 当更新、删除STU表中记录时，若SC表中有此学生的记录，则拒绝；alter table scdrop FK_SC_STU;alter table scadd constraint fk_sno foreign key (sno) references stu (sno)on delete no actionon update no action;2） 当更新、删除STU表中记录时，若SC表中有此学生的记录，则自动更新或删除；alter table scdrop fk_sno;alter table scadd constraint fk_sno foreign key (sno)

5、references stu (sno)on delete cascadeon update cascade;4. 使用SQL命令完成以下任务：创建Worker表（表结构见指导书P73）create table worker(number char(5),name char(8),sex char(1),sage int,deparment char(20);1） 定义约束U1、U2，其中U1规定Name字段取值唯一，U2规定sage字段上限是28；use schoolcreate table worker(number char(5),name char(8) constraint u1 u

6、nique,sex char(1),sage int constraint u2 check(sage=0);6） 创建规则rule_sex，规定更新或插入的值只能是M或F，并绑定到Worker的sex字段；create rule rule_sexas value in (F,M)goexec sp_bindrule rule_sex,worker.sex;7） 插入2条记录，一条满足规则rule_sex，一条违反规则，观察结果。insert into workervalues (20163,李敏,n,27,教育部);insert into workervalues (20163,李敏,F,2

7、5,教育部);5. 使用查询分析器创建触发器并测试，请写出相应的语句：1） 为Worker表创建触发器T1，当插入或更新表中数据时，保证所操作记录的sage大于0；create trigger t1 on workerfor insert,updateasif(select sage from inserted)=0beginprint 年龄必须大于0，否则处理失败！rollback transactionEnd2） 为Worker表创建触发器T2，禁止删除编号为00001的记录；create trigger t2 on workerfor deleteasif(select number f

8、rom deleted)=00001beginprint 禁止删除该信息！rollback transactionend3） 为Worker表创建触发器T3，要求更新一个记录时，表中记录的sage要比老记录的sage的值大。create trigger t3 on workerfor updateasif (select sage from inserted)=(select sage from deleted)beginprint 操作失败！rollback transactionEnd6. 分别用企业管理器和查询分析器实现用户的标识并测试，请写出相应的操作过程或对应的语句：1） 设置SQL

9、 SERVER的安全认证模式；在SQL Server Management studio 窗口中左部的“对象资源管理器”窗口中展开服务器组，用鼠标右击需要设置的SQL服务器，在弹出的菜单中选择“属性”项，则出现SQL Server 服务属性对话框，在SQL Server 服务属性对话框中，选择“安全性”选择页，在“服务器身份验证”一栏选择“SQL Server和Windows身份验证”单选项。2） 建立名为“LiYong”的登录用户、数据库用户，默认访问SCHOOL数据库，密码为“abcd”；exec sp_addlogin liyong,abcd,school;exec sp_grantdb

10、access liyong;3） 取消“LiYong”用户；exec sp_revokedbaccess liyong;exec sp_droplogin liyong;7. 先以sa帐号登录，要实现如下表的权限管理，请按先后顺序写出相应的SQL语句（假设所有用户尚未定义，其密码均为123），并加以必要的说明。编号授权用户名被授权用户名数据库对象名允许的操作类型能否转授权1SAU1StudentsSELECT不能2SAU2StudentsALL不能3SAPUBLICChoicesSELECT不能4SAU3StudentsSELECT能5U3U4StudentsSELECT能6U4U5Stude

11、ntsSELECT不能7SAU6ChoicesINSERT能8SAU7Students.snameUPDATE不能1）exec sp_addlogin u1,123,school;exec sp_grantdbaccess u1;grant select on students to u1;2）exec sp_addlogin u2,123,school;exec sp_grantdbaccess u2;grant all on students to u2;3)grant select on choices to public;4)exec sp_addlogin u3,123,school

12、;exec sp_grantdbaccess u3;grant select on students to u3with grant option;5)exec sp_addlogin u4,123,school;exec sp_grantdbaccess u4;grant select on students to u4with grant option;6)exec sp_addlogin u5,123,school;exec sp_grantdbaccess u5;grant select on students to u57)exec sp_addlogin u6,123,school

13、;exec sp_grantdbaccess u6;grant insert on choices to u6with grant option;8)exec sp_addlogin u7,123,school;exec sp_grantdbaccess u7;grant update on students.sname to u78. 创建在SCHOOL数据库中choices上的视图CS_View，授权给计算机系讲授课程号为10010的数据库用户“LiYong”，让其具有视图上的SELECT权限；create view cs_viewas select * from choices; gra

14、nt select on cs_view to liyong;9. 对视图CS_View上的score属性列的UPDATE权限授予用户“LiYong”，可以修改学生成绩，但不能修改其它属性列的值；grant update on dbo.cs_view(score) to liyong10. 假设SCHOOL数据库中，每个学生的登录名都为自己在STUDENTS表中的sid，实现每个学生只能查询自己选课信息，不能查询别人的成绩，也不能修改自己的成绩。create view CSasselect * from choices where sid=current_user;grant select o

15、nCSto public;11. 结束本次实验三、 实验课后训练1. 自行练习实验指导书P81【实验2.5 综合案例】综合案例1；2. 自行练习实验指导书P122【实验3.8 综合案例】综合案例；四、 实验报告1. 授权给public与授权给指定用户有什么区别？实际应用中，哪个更安全些？ 用户名public指系统的所有当前用户和将来的用户，因此，对pubilc的授权隐含着对所有当前用户和将来用户的授权。在默认情况下，被授权的用户无权把此权限授予其他用户，但SQL允许使用授予权限来指定权限的接受者可以进一步把权限授予其他用户，如果被收回权限的用户已经把权限授予了其他用户，权限的收回会更加复杂 数据库中的每个用户都属于 public 数据库角色。如果想让数据库中的每个用户都能有某个特定的权限，则将该权限指派给 public 角色。如果没有给用户专门授予对某个对象的权限，他们就使用指派给 public 角色的权限2. SQL SERVER中的角色有什么作用？ SQL Server 提供了两种类型的数据库角色：固定的数据库角色和用户自定义的数据库角色。服务器角色是指根据SQL Server的管理任务，以及这些任务相对的重要性等级来把具有SQl Server管理职能的用户划分为不同的用户组，每一组所具有的管理SQL Server