数据库的完整性和安全性实验报告

信息工程学院实验报告

课程名称：《数据库原理》

实验项目名称：数据库的完整性和安全性

一、实 验 目 的:

（1）掌握数据库约束的概念；

（2）熟悉SQL SERVER 的完整性约束技术。 （3）了解SQL SERVER 的违反完整性处理措施。 （4）了解登录账户的管理理念与具体方法。 （5）了解数据库用户的管理的要则。 （6）了解用户权限管理的内涵与方法。 二、实 验 设 备 与 器 件 Win7 +Sql server 2008 三、实 验 内 容 与 步 骤

（一）测试完整性

运行附录中的SQL 语句，理解SQL 语句中包含的完整性定义。然后执行下面的SQL 语句，看是否能正常运行，若无法执行，请说明原因。

1.对dept 表进行数据增删改，并检查完整性规则 Dept 已存在的完整性规则如下： dno

CHAR(2)

PRIMARY KEY

dname VARCHAR(20) NOT NULL,UNIQUE (1)增加数据

INSERT INTO dept VALUES('D1','计科系');----正常插入

INSERT INTO dept VALUES('D2','电信系');----正常插入

INSERT INTO dept VALUES(NULL,'机械系'); ----违反dno 主键（NOT NULL ）规则

INSERT INTO dept VALUES('D2','机械系'); ----违反dno 主键（UNIQUE ）

INSERT INTO dept VALUES('D3',NULL); ----违反dname 的NOT NULL 规则

INSERT INTO dept VALUES('D3','计科系'); ----违反dname的UNIQUE规则

INSERT INTO dept VALUES('D3','机械系');----正常插入

(2)删除数据

DELETE FROM dept WHERE dno='D3';----正常删除

(3)修改数据

UPDATE dept SET dname='计算机科学系' WHERE dno='D1';----正常修改

UPDATE dept SET dname='电信系' WHERE dno='D1'; ----违反dname的UNIQUE规则

UPDATE dept SET dname=NULL WHERE dno='D1'; 违反dname的UNIQUE规则

2.对student表进行数据增删改，并检查完整性规则

Student已存在完整性规则如下：

sno CHAR(2) PRIMARY KEY

sname VARCHAR(20) NOT NULL,

ssex CHAR(2) NOT NULL, CHECK(ssex in('男','女'))

sage INT NOT NULL,

dno CHAR(2) NOT NULL, FOREIGN KEY REFERENCES dept(dno)

ON DELETE CASCADE ON UPDATE CASCADE

(1)增加数据

INSERT INTO student VALUES('S1','张刚','男',20,'D1');----正常插入

INSERT INTO student VALUES('S2','李梅','女',21,'D2');----正常插入

INSERT INTO student VALUES('S2','吴敏','男',20,'D1'); ----正常插入

INSERT INTO student VALUES(NULL,'吴敏','男',20,'D1'); ----违反sno 的主键（NOT NULL）规则

INSERT INTO student VALUES('S3','吴敏','男',NULL,'D1'); 违反sage 的主键（NOT NULL）规则

INSERT INTO student VALUES('S3','吴敏','M',20,'D1'); 违反ssex 的CHECK规则

INSERT INTO student VALUES('S3','吴敏','男',20,'D3'); ----正常插入

INSERT INTO student VALUES('S3','吴敏','男',20,'D1');----正常插入

(2)删除数据

DELETE FROM student WHERE sno='S3'; ----正常删除

(3)修改数据

UPDATE student SET sname='赵强',dno='D2' WHERE sno='S1'-----正常修改

UPDATE student SET ssex='F' WHERE sno='S1'; -----违反ssex的CHECK 规则

UPDATE student SET sno='S2' WHERE sno='S1'; -----违反sno的主键（UNIQUE）规则

UPDATE student SET dno='D3' WHERE sno='S1'; 违反dno 的外键规则

UPDATE dept SET dno='D3' WHERE dno='D1';----检查dno的外键ON UPDATE规则，观察运行后效果

DELETE FROM dept WHERE dno='D2';----检查dno的外键ON DELETE规则，观察运行后效果

（二）使用规则实现数据完整性

（1）在查询分析器中，利用命令（CREATE RULE），创建一个关于年龄(sage)约束的规则，将“sage”

列的值约束在0~200之间；然后将所创建的规则绑定到“sage”列（提示：用命令Sp_bindrule）。

CREATE RULE rule_sage as@sage between 1 and 100;

Sp_bindrule'rule_sage','student.sage';

（2）在查询分析器中，利用命令（CREATE RULE），创建一个关于学分(credit)约束的规则，将“credit”

列的值约束在1~8之间的整数；然后将所创建的规则绑定到“credit”列（提示：用命令Sp_bindrule）。

CREATE RULE rule_credit as@credit between 1 and 8;

Sp_bindrule'rule_credit','course.credit';

（3）在企业管理器中，创建一个关于成绩(grade)约束的规则，将“grade”列的值约束在0~100以内；然后将所创建的规则绑定到“grade”列。

CREATE RULE rule_grade as@grade between 1 and 100;

Sp_bindrule'rule_grade','study.grade';

（三）使用触发器实现数据完整性(选做)

（1）在查询分析器中，输入以下CREATE TRIGGER语句，创建以下触发器：为student表创建一个INSERT触发器，当插入的新行中年龄的值不是0~180时，就激活该触发器，撤销该插入操作，并使用RAISERROR语句返回一个错误信息。

（2）在查询分析器中，执行INSERT语句为student表添加一年龄为220的学生，此时触发器是否执行？分析原因。

（四）测试数据库安全性

设置身份验证模式:右键服务器实例，选择"属性"，在"Security"(安全性)项中，将"服务器身份验证"设置为"SQL Server和Windows身份验证模式"，确定。根据提示重新启动sql服务。然后执行下面的语句启用sa用户。

sp_password [ [ @old = ] 'old_password' , ] { [ @new =] 'new_password' } [ , [ @loginame = ] 'login' ]

EXEC sp_password null, db123456, 'sa'

ALTER LOGIN sa ENABLE

1.建立登录、授予登录的服务器角色。

(1)建立和删除登录

打开企业管理器和查询分析器，以超级用户sa连接SQL Server服务器，建立SQL Server登录，

登录名为loginsql, 并设置口令为123456 。

sp_addlogin 'loginsql','123456'

另外打开SQL Server Management Studio，以loginsql登录连接服务器，测试服务器权限。试运行CREATE DATABASE test，能否成功？

(2) 以sa登陆查询分析器，授予loginsql登录的服务器角色——'dbcreator',

sp_addsrvrolemember 'loginsql','dbcreator'

然后以loginsql登录查询分析器，并运行CREATE DATABASE test，能否成功？

2.创建用户、授予用户的权限。

(1)以sa登陆查询分析器，建立数据库。

CREATE DATABASE dbstudent

go

USE dbstudent

go

CREATE TABLE tbstudent(

sno int,

sname varchar(10)

);

以loginsql登录查询分析器，测试能否使用数据库？

(2) 以sa登陆查询分析器，创建用户，授予用户的权限

sp_adduser 'loginsql','usera'

以loginsql登录另一个查询分析器，进入数据库dbstudent(usera用户)，测试权限。

可以使用数据库dbstudent

①sa授予用户语句权限

GRANT CREATE TABLE TO usera

②授予用户的对象权限

GRANT SELECT ON tbstudent TO usera

GRANT INSERT ON tbstudent TO usera

再次以loginsql登录进入数据库dbstudent(usera用户)，测试被授予权限。

3.创建数据库角色，授予数据库角色的权限。

(1) sa进入数据库，创建数据库角色

USE dbstudent

go

sp_addrole 'rolea'

(2)授予数据库角色的权限

GRANT CREATE TABLE TO rolea

GRANT SELECT,INSERT,delete ON tbstudent TO rolea

(3)授予用户的数据库角色

sp_addrolemember 'rolea','usera'，测试loginsql登录是否具有update权限

insert into tbstudent values('103','jake')

update tbstudent set sname='tom'where sno='103'

不具有update权限

以sa登录运行语句sp_addrolemember 'db_datawriter','usera' ，再次测验loginsql的update权限。

4. 回收数据库角色、用户、登录的权限，删除数据库角色、用户、登录。

①回收用户的数据库角色

sp_droprolemember 'rolea','usera'

sp_droprolemember 'db_datawriter','usera'

②回收数据角色权限

REVOKE CREATE TABLE FROM rolea

REVOKE SELECT,INSERT ON tbstudent FROM rolea

③删除数据库角色

sp_droprole 'rolea'

④回收用户的对象权限

REVOKE SELECT ON tbstudent FROM usera

REVOKE INSERT ON tbstudent FROM usera

⑤回收用户的语句权限

REVOKE CREATE TABLE FROM usera

⑥删除用户

sp_dropuser 'usera'

⑦回收登录的服务器角色

sp_dropsrvrolemember 'loginsql','dbcreator'

⑧删除SQL Server登录

Drop database test

sp_droplogin 'loginsql'

四、实验总结：

我在此过程中不但应用了所学的知识，而且还不断的学习新的知识、工具，以完成设计的需要