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

实验平台及环境
本机为 windows 7 操作系统平台， SQL Server 2008 数据库。
实验内容
完整性实验 （1） 分别定义学生数据库中各基表的主键、外键，实现实体完整性约束和参照完整性约
束； （2） 分别向学生表、课程表插入具有相同学号和相同课程编号的学生数据和课程数据，
验证其实体完整性约束； （3） 向学生选课表中插入一条数据，课程编号是课程表中没有的，验证参照完整性约束； （4） 删除学生表中的所有数据，验证参照完整性约束； （5） 定义存储过程，完成查询某个学生的选课情况，并执行。 （6） 定义触发器，当向学生表插入新的一条记录时，将所有学生出生日期加 1；并对其
执行即可得到吴磊学生的选课情况，exec wlxx
6、定义触发器，当向学生表插入新的一条记录时，将所有学生出生日期加 1；并对其进行 测试
这里我定义了触发器 dadd，实现功能：当向学生表插入新的一条记录时，将所有学生出生 日期加 1 create trigger dadd on 学生 after insert as begin
学号 31430 的张明同学的信息已经添加到学生表，修改没问题
实验小结
这次做实验收获蛮多的，遇到不明白的上网找资料，看课件，一一迎刃而解，尤其是给同学 讲解时很有成就感。通过这次实验，我对数据库完整性和安全性有了更深一步的认识。以下 是我这次实验遇到的问题和分析总结： 1、设置主键比较容易，但是在设置外键时要注意数据类型的一致性。比如：学生表中学号 数据类型设置 varchar（50），那么选修表中学号的数据类型也应为 varchar（50），否则执 行外键定义操作时会报错。 2、一开始对于datetime数据类型的加减操作有所怀疑，拿不准是直接写update 学生 set 年龄=年龄+1，还是更为复杂的，经过尝试直接+1操作就好，这里要强调的是不要定义为 varchar类型，那样会反受其累。 3、例如存储过程、触发器等等操作按照资料和课件上的讲解一步步来， 例如：create procedure wlxx as…… create trigger dadd on 学生 after insert as
4、将角色赋予（1）中定义的用户，建立用户和角色联系 这里我将刚刚定义好的角色 duxie 赋予（1）中定义的用户 wjh， EXEC sp_addrolemember 'duxie', 'wjh'
执行成功后即建立了用户和角色联系 5、再次用此用户访问学生数据库，并对其中的学生表数据进行修改 再次用 wjh 用户访问学生数据库，发现可以对学生表数据进行修改了， 例如： insert into 学 生 values('31430',' 张 明 ',' 女 ','1980-1-2 00:00',' 计 算 机 ','3146') 执行成功后
执行插入操作时， insert into 学 生 values('31430',' 张 明 ',' 女 ','1980-1-2 00:00',' 计 算 机 ','3146') 报错：
3、创建数据库角色，并授予访问学生数据库的读、写权限 这里我创建了数据库角色 duxie，并授予访问学生数据库的读、写权限 create role duxie grant select,insert on 学生 to duxie grant select,insert on 课程 to duxie grant select,insert on 选修 to duxie 执行成功后可以在对象资源管理器中可以找到它
实验步骤和结果分析
一、完整性实验 1、定义学生数据库中各基表的主键、外键，实现实体完整性约束和参照完整性约束 alter table 课程 add primary key (课程编号) alter table 选修 add primary key (课程编号,学号) alter table 学生 add primary key (学号) alter table 选修 add foreign key (课程编号) references 课程(课程编号) alter table 选修 add foreign key (学号) references 学生(学号) 结果如下图：
进行测试。 （7） 用 sql 完成以上操作。 安全性实验 （1） 定义一新的登陆帐号、数据库用户，并授予其访问学生数据库的读权限； （2） 分别用 sa 用户和新定义的用户访问学生数据库，并对其中的学生表数据进行修改； （3） 创建数据库角色，并授予访问学生数据库的读、写权限； （4） 将角色赋予（1）中定义的用户，建立用户和角色联系； （5） 再次用此用户访问学生数据库，并对其中的学生表数据进行修改。 （6） 用 SQL 语句分别完成以上内容。
2、向学生表、课程表插入具有相同学号和相同课程编号的学生数据和课程数据，验证其实 体完整性约束 例如：向课程表中插入计算机网络课程， insert into 课程 values('C01','计算机网络','51','3','秋') 但由于课程编号与原表数据有重，故而执行报错：
同理：向学生表中插入张明的信息， insert into 学 生 values('30201',' 张 明 ',' 女 ','1980-1-2 00:00',' 计 算 机 ','3146') 但由于其学号与原表数据有重，故而执行报错：
即验证了参照完整性约束 5、定义存储过程，完成查询某个学生的选课情况，并执行 例如：定义了存储过程 wlxx，用来查询吴磊学生的选课情况， create procedure wlxx as select 学生.学号,姓名,课程名称,学生成绩
from 学生,课程,选修 where 学生.学号='30201' and 学生.学号=选修.学号 and 选修.课程编号=课程.课 程编号 go 创建成功后在对象资源管理器中可以找到它
update 学生 set 年龄=年龄+1 end 执行成功后在对象资源管理器中可以找到它
原学生表的数据为：
可见出生日期都是 1980-01-02 ……，在有触发器 dadd 后， 测试：当向学生表插入新的一条记录时，将所有学values('31430',' 张 明 ',' 女 ','1980-1-2 00:00',' 计 算 机 ','3146')
begin …… end 都还是比较简单的。 4、注意在定义登录帐号时最好加上 default_database=学生选课数据库，否则使默认数 据库设置为master。
default_database=学生选课数据库 create user wjh
for login aaa with default_schema=dbo 由于只有三个表，授权访问学生数据库的读权限， grant select on 学生 to wjh grant select on 课程 to wjh grant select on 选修 to wjh 执行成功后可在对象资源管理器中可以找到它
可见出生日期都加了 1，变为 1980-01-03 ……，即该触发器符合要求 二、安全性实验 1、定义一新的登录帐号、数据库用户，并授予其访问学生数据库的读权限 这里我定义了新的登录帐号 aaa（密码为 123） ，并定义了数据库用户 wjh create login aaa with password='123',
北京邮电大学
实验报告
课程名称 数据库系统原理 实验内容 实验五
实验名：数据库完整性与安全性实验
2012 年 05 月 10 日
实验五 数据库完整性与安全性实验
实验目的
1、通过对完整性规则的定义实现，熟悉了解 sql sever 中完整性保证的规则和实现方法， 加深对数据完整性的理解。 2、通过对安全性相关内容的定义，熟悉了解 sql sever 中安全性的内容和实现方法，加深 对数据库安全性的理解。
2、分别用 sa 用户和新定义的用户访问学生数据库，并对其中的学生表数据进行修改 用 sa 用户时，修改学生表数据没有问题， 例如： insert into 学 生 values('31430',' 张 明 ',' 女 ','1980-1-2 00:00',' 计 算 机 ','3146')
用自定义的 wjh 用户时，修改学生表数据会产生问题， 例如：
即验证了其实体完整性约束 3、向学生选课表中插入一条数据，课程编号是课程表中没有的，验证参照完整性约束 例如：向选课表中插入学号为 30201 的同学的某项选课信息， insert into 选修 values('30201','CO6','51')
但由于其课程编号是课程表中没有的，执行报错
即验证了参照完整性约束 4、删除学生表中的所有数据，验证参照完整性约束 使用 delete from 学生 但由于参照完整性的约束，执行报错