信息工程学院实验报告课程名称：《数据库原理》实验项目名称：数据库的完整性和安全性一、实 验 目 的:（1）掌握数据库约束的概念；（2）熟悉SQL SERVER 的完整性约束技术。

（3）了解SQL SERVER 的违反完整性处理措施。

（4）了解登录账户的管理理念与具体方法。

（5）了解数据库用户的管理的要则。

（6）了解用户权限管理的内涵与方法。

二、实 验 设 备 与 器 件 Win7 +Sql server 2008 三、实 验 内 容 与 步 骤（一）测试完整性运行附录中的SQL 语句，理解SQL 语句中包含的完整性定义。

然后执行下面的SQL 语句，看是否能正常运行，若无法执行，请说明原因。

1.对dept 表进行数据增删改，并检查完整性规则 Dept 已存在的完整性规则如下： dnoCHAR(2)PRIMARY KEYdname 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 KEYsname 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 ENABLE1.建立登录、授予登录的服务器角色。

(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 dbstudentgoUSE dbstudentgoCREATE TABLE tbstudent(sno int,sname varchar(10));以loginsql登录查询分析器，测试能否使用数据库？(2) 以sa登陆查询分析器，创建用户，授予用户的权限sp_adduser 'loginsql','usera'以loginsql登录另一个查询分析器，进入数据库dbstudent(usera用户)，测试权限。