1、信息工程学院实验报告课程名称：数据库原理 实验项目名称：数据库的完整性和安全性 一、实 验 目 的: （1）掌握数据库约束的概念； （2）熟悉 SQL SERVER 的完整性约束技术。 （3）了解 SQL SERVER 的违反完整性处理措施。 （4）了解登录账户的管理理念与具体方法。（5）了解数据库用户的管理的要则。（6）了解用户权限管理的内涵与方法。二、实 验 设 备 与 器 件Win7 +Sql server 2008三、实 验 内 容 与 步 骤 （一）测试完整性运行附录中的 SQL 语句，理解 SQL 语句中包含的完整性定义。然后执行下面的 SQL 语句，看是否能正常运行，若无法执行，
2、请说明原因。1.对 dept 表进行数据增删改，并检查完整性规则Dept 已存在的完整性规则如下：dno CHAR(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）INSER
3、T 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; -违反 dnam
4、e 的 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 CASCA
5、DE 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
6、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 WHER
7、E 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）在查询分析器中，利用命
8、令（CREATE RULE） ，创建一个关于年龄(sage)约束的规则，将“sage”列的值约束在 0200 之间；然后将所创建的规则绑定到“sage ”列（提示：用命令Sp_bindrule） 。CREATE RULE rule_sage as sage between 1 and 100;Sp_bindrule rule_sage,student.sage;（2）在查询分析器中，利用命令（CREATE RULE） ，创建一个关于学分(credit)约束的规则，将“credit”列的值约束在 18 之间的整数；然后将所创建的规则绑定到“credit ”列（提示：用命令Sp_bindrule）
9、 。CREATE RULE rule_credit as credit between 1 and 8;Sp_bindrule rule_credit,course.credit;（3）在企业管理器中，创建一个关于成绩(grade)约束的规则，将“grade”列的值约束在 0100 以内；然后将所创建的规则绑定到“grade”列。CREATE RULE rule_grade as grade between 1 and 100;Sp_bindrule rule_grade,study.grade;（三）使用触发器实现数据完整性(选做)（1）在查询分析器中，输入以下 CREATE TRIGGER
10、 语句，创建以下触发器：为 student 表创建一个INSERT 触发器，当插入的新行中年龄的值不是 0180 时，就激活该触发器，撤销该插入操作，并使用 RAISERROR 语句返回一个错误信息。（2）在查询分析器中，执行 INSERT 语句为 student 表添加一年龄为 220 的学生，此时触发器是否执行？分析原因。（四）测试数据库安全性设置身份验证模式:右键服务器实例，选择 “属性“，在“Security“(安全性) 项中，将“服务器身份验证“ 设置为“SQL Server 和 Windows 身份验证模式“ ，确定。根据提示重新启动 sql 服务。然后执行下面的语句启用 sa 用
11、户。 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
12、 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 tbstuden
13、t(sno int,sname varchar(10);以 loginsql 登录查询分析器，测试能否使用数据库？(2) 以 sa 登陆查询分析器，创建用户，授予用户的权限sp_adduser loginsql,usera以 loginsql 登录另一个查询分析器，进入数据库 dbstudent(usera 用户)，测试权限。可以使用数据库 dbstudentsa 授予用户语句权限GRANT CREATE TABLE TO usera授予用户的对象权限GRANT SELECT ON tbstudent TO useraGRANT INSERT ON tbstudent TO usera再次以
14、loginsql 登录进入数据库 dbstudent(usera 用户)，测试被授予权限。3.创建数据库角色，授予数据库角色的权限。(1) sa 进入数据库，创建数据库角色USE dbstudentgosp_addrole rolea(2)授予数据库角色的权限GRANT CREATE TABLE TO roleaGRANT SELECT,INSERT,delete ON tbstudent TO rolea(3)授予用户的数据库角色sp_addrolemember rolea,usera，测试 loginsql 登录是否具有 update 权限insert into tbstudent val
15、ues(103,jake)update tbstudent set sname=tom where sno=103不具有 update 权限以 sa 登录运行语句 sp_addrolemember db_datawriter,usera ，再次测验 loginsql 的 update 权限。4. 回收数据库角色、用户、登录的权限，删除数据库角色、用户、登录。回收用户的数据库角色sp_droprolemember rolea,userasp_droprolemember db_datawriter,usera回收数据角色权限REVOKE CREATE TABLE FROM roleaREVOKE
16、 SELECT,INSERT ON tbstudent FROM rolea删除数据库角色sp_droprole rolea回收用户的对象权限REVOKE SELECT ON tbstudent FROM useraREVOKE INSERT ON tbstudent FROM usera回收用户的语句权限REVOKE CREATE TABLE FROM usera删除用户sp_dropuser usera回收登录的服务器角色sp_dropsrvrolemember loginsql,dbcreator删除 SQL Server 登录Drop database testsp_droplogin loginsql四、实 验 总 结：我在此过程中不但应用了所学的知识，而且还不断的学习新的知识、工具，以完成设计的需要
