实验三数据库安全性与完整性(新)

1、 河南工业大学实验报告课 程 数据库原理 实验名称 实验三 数据库安全性与完整性 院 系_信息科学与工程学院_ 专业班级_ 计科0803班 _姓 名_李方彬_ 学 号_ 200848140323 _ 指导老师： 张雪萍 日 期 一. 实验目的1. 掌握数据库安全性控制的概念；2. 通过在SQL Server 2000查询分析器创建登录、数据库用户和角色，以及权限管理，来加深对自主存取控制方法（DAC）的理解和掌握。3. 掌握数据库完整性控制的概念；4. 熟练掌握实体完整性，参照完整性和用户自定义完整性的实现方法；5. 掌握主键和外键的概念；6. 掌握触发器的使用方法。7. 掌握分别用SQL S

2、ERVER 2000企业管理器和T-SQL完成完整性控制的方法。二. 实验内容及要求假设有下面两个关系模式：职工（职工号，姓名，年龄，职务，工资，部门号），其中职工号是主码；部门（部门号，名称，经理名，电话），其中部门号为主码。注意：首先创建一个数据库，用自己名字全拼命名（如Zhangsan）。三. 实验过程及结果1.请用T-SQL语句完成以下内容：（数据安全性）1）创建一个SQL Server身份验证的登录（命名方式：数据库名称+Login，如ZhangsanLogin），默认数据库是你创建的数据库，为该登录指定sysadmin服务器角色；EXEC sp_addlogin 'lifa

3、ngbinLogin','','lifangbin'sp_addsrvrolemember 'lifangbinLogin','sysadmin'gosp_grantdbaccess 'lifangbinLogin','lifangbinuser'sp_addrole 'lifangbinRole'sp_addrolemember 'lifangbinRole','lifangbinuser'2）在已有数据库中创建一个数据库用户（命名方式：数据

4、库名称 + User，如ZhangsanUser），与上面的登录关联；create table zhigong(no char(10) primary key,name char(20),age int,job char(20),salary int,bno char(10)3）在已有数据库中创建一个自定义数据库角色（命名方式：数据库名称 + Role，如ZhangsanRole），并把上面用户指定为该角色成员；create table bumen(bno char(10) primary key,bname char(20),jingli char(20),phone char(11)4）授

5、予该角色对部门表具有SELECT权限；grant select on bumen to lifangbinRole5）授予该角色对职工表具有DELETE和INSERT权限；grant DELETE,INSERTon zhigongto lifangbinRole6）授予该角色对职工表的工资字段的UPDATE权限；grant update(salary)on zhigongto lifangbinRole7）授予该用户生成、改变和删除数据库对象的权限（提示：将该用户指定为某一固定数据库角色成员。）；exec sp_addrolemember 'db_datawriter',

6、9;lifangbinuser'8）收回该角色对职工表的DELETE权限；revoke deleteon zhigongfrom lifangbinRole9）拒绝该用户对部门表的SELECT权限。revoke selecton bumenfrom lifangbinRole备注：完成以上功能所需要的系统存储过程有sp_addrolemember,sp_addsrvrolemember,sp_grantdbaccess,sp_addrole,sp_addlogin等，具体含义和使用方式可参考SQL Server 2000联机帮助。2用企业管理器和SQL语句两种方式实现如下数据完整性控制

7、：创建读者表Reader（Rno（读者编号），Rname（读者姓名），Rsex(读者性别)，Rage（读者年龄），RDept（所在院系），RBno（借书证号），Rtype（类别）；创建图书表Book（Bno（图书编号），Bname（书名），Bauthor（作者），Bpub（出版社），Date（出版日期），Bdate（入库日期）；创建借阅表RB（RBno（借书证号），Bno（图书编号），BorrowDate（借出日期），ReturnDate（应还日期）（1） 将Reader表中的读者编号Rno设为主键；将Book表中的读者编号Bno设为主键。（2） （2）将RB表的RBno和Bno设为主键，其中

8、外键RBno参照表Reader，外键Bno参照表Book。（3） W要求读者的编号必须是以L开头的六位数，性别只能取“男”或“女”，读者的年龄小于55岁。create table Reader(Rno varchar(6)primary key check(Rno like 'L_'),Rname char(20),Rsex char(2)check(Rsex in ('男','女'),Rage int check(Rage between 0 and 55),RDept char(20),RBno char(10) unique,Rtype c

9、har(20)create table Book(Bno char(10)primary key,Bname char(20),Bauthor char(20),Bpub char(20),Date datetime,Bdate datetime)create table RB(RBno char(10) FOREIGN KEY REFERENCES Reader(RBno),Bno char(10) FOREIGN KEY REFERENCES Book(Bno),BorrowDate datetime,ReturnDate datetime,primary key(RBno,Bno)3触发器的实现定义一个INSERT触发器，当读者所在院系写为“计算机科学系”时，自动改为“信息科学与工程学院”。create trigger Rinserton Readerfor insertasdeclare RDept as char(20),Rno as char(6)select RDept=i.RDept,Rno=i.Rno from inserted iif(RDept='计算机科学系')begin Update ReaderSet RDept='信息科学与工程学院'where Rno=Rnoen