实验三、数据库安全性与完整性（新）刘宗奇

河南工业大学实验报告

课 程 数据库原理 实验名称 实验三 数据库安全性与完整性

院 系____信息科学与工程学院____ 专业班级__ 计科0805 姓 名_________刘宗奇______ 学 号__200848140513__________

指导老师： 许速 日 期 2010-10-20

一.实验目的

1. 掌握数据库安全性控制的概念；

2. 通过在SQL Server 2000查询分析器创建登录、数据库用户和角色，以及权限管理，来加深对自主存取控制方法（DAC）的理解和掌握。

3. 掌握数据库完整性控制的概念；

4. 熟练掌握实体完整性，参照完整性和用户自定义完整性的实现方法； 5. 掌握主键和外键的概念； 6. 掌握触发器的使用方法。

7. 掌握分别用SQL SERVER 2000企业管理器和T-SQL完成完整性控制的方法。

二.实验内容及要求

假设有下面两个关系模式：

职工（职工号，姓名，年龄，职务，工资，部门号），其中职工号是主码； 部门（部门号，名称，经理名，电话），其中部门号为主码。 注意：首先创建一个数据库，用自己名字全拼命名（如Zhangsan）。

1.请用T-SQL语句完成以下内容：（数据安全性）

1）创建一个SQL Server身份验证的登录（命名方式：数据库名称+Login，如ZhangsanLogin），默认数据库是你创建的数据库，为该登录指定sysadmin服务器角色； EXEC sp_addlogin 'liuzongqiLogin', '19890123', 'liuzongqi' EXEC sp_addsrvrolemember 'liuzongqiLogin','SysAdmin'

2）在已有数据库中创建一个数据库用户（命名方式：数据库名称 + User，如ZhangsanUser），与上面的登录关联；

EXEC sp_grantdbaccess 'liuzongqiLogin','liuzongqiUser'

3）在已有数据库中创建一个自定义数据库角色（命名方式：数据库名称 + Role，如ZhangsanRole），并把上面用户指定为该角色成员； EXEC sp_addrolemember 'liuzongqiRole','liuzongqiUser'

4）授予该角色对部门表具有SELECT权限； GRANT SELECT ON bumen TO liuzongqiRole

5）授予该角色对职工表具有DELETE和INSERT权限； GRANT DELETE,INSERT ON worker TO liuzongqiRole

6）授予该角色对职工表的工资字段的UPDATE权限； GRANT UPDATE(wpay) ON worker TO liuzongqiRole

7）授予该用户生成、改变和删除数据库对象的权限（提示：将该用户指定为某一固定数据库角色成员。）；

GRANT CREATE,ALTER,DELETE ON liuzongqiLogin TO liuzongqiRole GRANT liuzongqiRole TO liuzongqiUser

8）收回该角色对职工表的DELETE权限；

REVOKE DELETE ON WORKER FROM liuzongqiRole

9）拒绝该用户对部门表的SELECT权限。 REVOKE SELECT ON bumen FROM liuzongqiUser

备注：完成以上功能所需要的系统存储过程有sp_addrolemember,sp_addsrvrolemember, sp_grantdbaccess,sp_addrole,sp_addlogin等，具体含义和使用方式可参考SQL Server 2000联机帮助。

2．用企业管理器和SQL语句两种方式实现如下数据完整性控制：

创建读者表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）将RB表的RBno和Bno设为主键，其中外键RBno参照表Reader，外键Bno参照表Book。

（3）W要求读者的编号必须是以L开头的六位数，性别只能取“男”或“女”，读者的年龄小于55岁。 CREATE TABLE Reader

(Rno CHAR(9) PRIMARY KEY CHECK(Rno LIKE 'L______'), Rname CHAR(20) NOT NULL,

Rsex CHAR(2) CHECK(Rsex IN('男','女')), Rage SMALLINT CHECK(Rage<55), RDept CHAR(20), RBno CHAR(9), Rtype CHAR(20) )

CREATE TABLE Book (Bno CHAR(9) PRIMARY KEY, Bname CHAR(20) NOT NULL, Bauthor CHAR(20), Bpub CHAR(20), Date DATETIME, Bdate DATETIME )

CREATE TABLE RB (RBno CHAR(9), Bno CHAR(9),

BorrowDate DATETIME, ReturnDate DATETIME, PRIMARY KEY(RBno,Bno),

FOREIGN KEY (RBno) REFERENCES Reader(Rno), FOREIGN KEY (Bno) REFERENCES Book(Bno) )

3．触发器的实现

定义一个INSERT触发器，当读者所在院系写为“计算机科学系”时，自动改为“信息科学与工程学院”。

CREATE TRIGGER INSERT_ BEFORE INSERT ON Reader FOR EACH ROW AS BEGIN

IF(new.RDept='计算机科学系') THEN new.RDept:='信息科学与工程学院'; END IF; END

三.实验中的问题及心得

本次试验是一次比较难的实验，实验中也遇到了一些问题，在老师和同学们的帮助下，都一一解决了。