实验四 数据的完整性安全性.docx
《实验四 数据的完整性安全性.docx》由会员分享，可在线阅读，更多相关《实验四 数据的完整性安全性.docx（32页珍藏版）》请在冰豆网上搜索。
实验四数据的完整性安全性
徐龙琴、刘双印设计制作实验四数据完整性安全性
数据的完整性、安全性实验四
实验目的一、掌握数据安全性和完整性的概念，以及如何保证数据库中数据安全及完整性。
1．SQLServer中有关用户、角色及操作权限的管理方法．掌握2．学会创建和使用规则、缺省。
3．二、实验内容
1数据库的安全性实验，通过SSMS设置SQLServer的安全认证模式．实现对SQLServer的用户和角色管理，设置和管理数据操作权限．
2数据库的完整性实验。
使用Transact-SQL设计规则、缺省、约束和触发器。
三、实验要求．数据的完整性实
⑴用SQL语句创建一学生成绩数据库（XSCJ），包括学生（XSQK）、课程（KC）和成绩表（XS_KC）：
学生情况表（XSQK）
列名数据类型长度是否允许为空值
N6Char学号
N8Char姓名N性别Bit
1
smalldatetime出生日期2
Char10专业名
Char10所在系
Y
11
char
联系电话
createdatabasexscj
usexscj
createtablexsqk（学号char（6）notnull,
姓名char（8）notnull,
性别bitnotnull,
出生日期smalldatetime,
专业名char（10）,
所在系char（10）,
null）
）11（char联系电话
徐龙琴、刘双印设计制作实验四数据完整性安全性
课程表（KC）
列名数据类型长度是否允许为空值
NChar课程号3
NChar课程名20
10Char教师开课学期1Tinyint
Tinyint1学时
N
Tinyint
学分1
createtablekc（课程号char（3）notnull,
课程cha2nonull,
教师char（10）,
开课学期tinyint,
学时tinyint,
学分tinyintnotnull）
成绩表（XS_KC）
数据类型长度是否允许为空值列名CharN学号6
Char课程号N3
Smallint
成绩2
createtablexs_kc（学号char（6）notnull,
课程号char（3）notnull,
）
smallint成绩
实验四数据完整性安全性徐龙琴、刘双印设计制作
⑵数据的实体完整性实验
①用SSMS分别将学生情况表（XSQK）的学号字段、课程表（KC）的课程号字段设置为主健
②用T－SQL语句将成绩表（XS_KC）的学号、课程号字段设置为主健
altertablexs_kc
addprimarykey（学号,课程号）
实验四数据完整性安全性徐龙琴、刘双印设计制作
⑶数据的参照完整性实验
①用SSMS为成绩表（XS_KC）创建外键FK_XSQK_ID，外键FK_XSQK_ID参照学生情况表（XSQK）表的学号
②用T－SQL语句成绩表（XS_KC）创建外键FK_KC_ID，外键FK_KC_ID参照课程表（KC）
表的课程号
altertablexs_kc
addconstraintFK_KC_IDforeignkey（课程号）referenceskc（课）
程号．
实验四数据完整性安全性徐龙琴、刘双印设计制作
⑷数据的用户定义完整性实验
①用T－SQL语句为学生情况表（XSQK）的姓名列创建一个唯一约束
altertablexsqk
addconstraintch_xsqk_uniqueunique（姓名）
）的性别列创建一个检查约束，使得性别的值为男或女XSQK为学生情况表（SSMS②用．
实验四数据完整性安全性徐龙琴、刘双印设计制作
③用T－SQL语句为成绩表（XS_KC）的成绩列创建一个检查约束，使得成绩的值在（0～100）之间
altertablexs_kc
addconstraintch_gradecheck（成绩between0and100）
60
）的学时列创建一个缺省约束，缺省值为KC语句课程表（SSMS④用．
实验四数据完整性安全性徐龙琴、刘双印设计制作
⑤用T－SQL语句课程表（KC）的学分列创建一个缺省约束，缺省值为2
altertablekc
addconstraintdefault_kc_creditdefault2for学分
的格式，并分别将其导入数据库的各个表中：
Excel２．将如下数据分别转换成文本数据或．
徐龙琴、刘双印设计制作数据完整性安全性实验四
）：
学生情况表（XSQK学号姓名性别出生日期专业所在系联系电话
88297147020101杨颖1980-7-200计算机应用计算机
88297147计算机计算机应用1981-1-15020102方露露0
882971511980-2-20俞奇军1信息管理020103计算机88297151胡国强11980-11-7020104计算机信息管理
882971520201051980-7-29薛冰1水利工程水利系88297161电子商务经济系1981-3-100202010秦盈飞
88297062电子商务经济系1980-9-250020202董含静
88297171电子商务经济系1980-8-71020203陈伟88297171
房建1980-7-20
020204
陈新江1
水利系
实验四数据完整性安全性徐龙琴、刘双印设计制作
实验四数据完整性安全性徐龙琴、刘双印设计制作
：
）KC课程表（．
徐龙琴、刘双印设计制作实验四数据完整性安全性
课程号课程名教师开课学期学时学分
3计算机原理245101陈红
33102王颐计算方法454602103操作系统徐格53数据库原理及应用应对刚10475
3网络基础吴江江410545
6高等数学孙中文190106
6英语陈刚1071905
VB赵红韦3
108
程序设计70
实验四数据完整性安全性徐龙琴、刘双印设计制作
实验四数据完整性安全性徐龙琴、刘双印设计制作
:
）XS_KC成绩表（．
徐龙琴、刘双印设计制作数据完整性安全性实验四
学号课程号成绩
85020101101
870201011028810702010158101020102
63020102102
76020104107
550202021038010802020257103020203
71
020204
103
实验四数据完整性安全性徐龙琴、刘双印设计制作
3.理解默认值的概念和作用
①用语句创建名为Xi_default，值为“计算机系”的默认值
createdefaultXi_defaultas'计算机系'
实验四数据完整性安全性徐龙琴、刘双印设计制作
②将默认值Xi_default绑定到学生表中的所在系的属性列上
sp_bindefaultXi_default,'xsqk.所在系'
③解除学生表所在系的属性列上的默认值
sp_unbindefault'xsqk.所在系'
④删除默认值Xi_default
dropdefaultXi_default
注：
创建默认值的格式：
createdefault默认值名as‘默认值'
默认值绑定的格式：
sp_bindefault默认值名,<'表名.列名'︱自定义数据类型名称>
解除默认值绑定格式：
sp_unbindefault<'表名.列名'︱自定义数据类型名称>
删除默认值格式：
Dropdefault默认值名
4.理解规则的概念和作用
①用语句创建规则“rule_kkxq”，用以限制插入该规则所绑定的列中的值只能取1、2、3、4、5、6
createrulerule_kkxqas@ssin（1,2,3,4,5,6）
②将“rule_kkxq”规则绑定到课程表的开课学期属性上，并执行以下语句，看看能否正
常执行，为什么：
Insertinto课程表
Values（109,'C语言','李方',8,64,4）
sp_bindrulerule_kkxq,'kc.开课学期'
不能正常执行因为数值8不在规则rule_kkxq所绑定的数据范围内
③若不解除规则，能否将规则rule_kkxq直接删除？
不能,需先解除规则才能删除规则rule_kkxq:
sp_unbindrule'kc.开课学期'
droprulerule_kkxq
注：
创建规则的格式：
createrule规则名as＠列名约束条件
规则绑定的格式：
sp_bindrule规则名,<'表名.列名'︱自定义数据类型名称>
解除规则绑定格式：
sp_unbindrule<'表名.列名'︱自定义数据类型名称>
删除规则格式：
Droprule规则名
5.数据的安全性实验：
（1）设置身份验证模式
①写出查看当前SQLServer身份验证模式的过程，即查看当前SQLServer系统到底是采用Windows身份验证还是混合身份验证模式。
安全性选择->属性右键->服务器．
实验四数据完整性安全性徐龙琴、刘双印设计制作
②将系统设置为Windows身份验证模式，重启SQLServer，使之生效并验证
③将系统设置为混合身份验证模式，重启SQLServer，使之生效并验证
⑵创建和管理登录账户
1）用对象资源管理器创建、查看、删除SQLServer登录账户
①分别创建两个登录帐户LoginA、LoginA1，其登录密码分别为：
123456、456，并指定LoginA登录默认的数据库为学生成绩数据库（XSCJ），指定LoginA1登录默认的数据库为master数据库。
．
实验四数据完整性安全性徐龙琴、刘双印设计制作
实验四数据完整性安全性徐龙琴、刘双印设计制作
LoginA1
、LoginA②查看登录账户．
实验四数据完整性安全性徐龙琴、刘双印设计制作
LoginA1
③删除登录账户．
实验四数据完整性安全性徐龙琴、刘双印设计制作
2）用T-SQL语句创建、查看、删除SQLServer登录账户
①用系统存储过程，分别创建两个登陆帐户LoginB、LoginB1，其登录密码分别为：
666、888，登录后然后所连接到的数据库均为学生成绩数据库（XSCJ）。
sp_addlogin'LoginB','666','XSCJ'
'XSCJ'
'888','LoginB1'sp_addlogin
实验四数据完整性安全性徐龙琴、刘双印设计制作
注：
创建新的SQLServer登录账号和登录密码的语句为：
sp_addlogin[@loginame=]'login'//登录的名称
[,[@passwd=]'password']//登录密码。
[,[@defdb=]'database']//登录的默认数据库（登录后登录所连接到的
数据库）。
默认设置为master。
例：
创建一个登陆帐户Login，登录密码为：
A，登录后然后所连接到的数据库
master数据库。
为master'sp_addlogin'Login','A','
master'sp_addlogin@loginame='Login',@passwd='A',@defdb='或
登录账户SQLServer查看sp_helplogins②使用系统存储过程
实验四数据完整性安全性徐龙琴、刘双印设计制作
注：
查看登录账户的格式：
execsp_helplogins
③使用系统存储过程sp_droplogin从SQLServer中将登录账户LoginB1删除
sp_droplogin'LoginB1'
注：
删除登录账户的格式：
sp_droplogin[@loginame=]'登录名称'
⑶创建和管理数据库用户
1）用对象资源管理器创建、查看、删除数据库用户
LoginA
，使其所对应的帐号为UserA中创建数据库用户XSCJ）（①在学生成绩数据库
实验四数据完整性安全性徐龙琴、刘双印设计制作
②能否再创建一个数据库用户ABC，使其对应登录的帐号为LoginA，若不能写出其原因？
不能,同一个数据库的数据库用户只能有一个登录账户
“该登陆已用另一开立账户用户名”
2）用语句创建、查看、删除数据库用户
①用系统存储过程sp_grantdbaccess在学生成绩数据库（XSCJ）中创建一个数据库用户UserB，使其所对应的登录帐号为LoginB
'UserB'
'LoginB'sp_grantdbaccess
实验四数据完整性安全性徐龙琴、刘双印设计制作
注：
为SQLServer登录在当前数据库中添加一个安全用户的语句为：
sp_grantdbaccess[@loginame=]'login'//当前数据库中的登录名称
[,[@name_in_db=]'name_in_db'//数据库中帐户的名称
②用系统存储过程sp_helpuser查看数据库用户
sp_helpuser'UserB'
注：
查看数据库用户的格式：
sp_helpuser[[@name_in_db=]'security_account']
③用系统存储过程sp_revokedbaccess删除数据库用户ABC1
执行删除，因为它不存在，或者您没有所需的权限。
无法对用户'ABC1'
注：
删除数据库用户的格式：
sp_revokedbaccess[@name_in_db=]'name'
⑷创建和管理角色
1）服务器角色的管理
的角色中。
sysadmin加到LoginA①用对象资源管理器将登录帐户
实验四数据完整性安全性徐龙琴、刘双印设计制作
②用系统存储过程sp_addsrvrolemember将登录帐户LoginB加到sysadmin的角色中。
sp_addsrvrolemember'LoginB',sysadmin
注：
为登录账户指定服务器角色的系统存储过程的格式为：
sp_addsrvrolemembersrvrolemember'登录账号'，服务器角色名称sp_add③用系统存储过程sp_dropsrvrolemember删除登录账户LoginA的服务器角色sysadmin
sp_dropsrvrolemember'LoginA',sysadmin
注：
删除登录账户的服务器角色的系统存储过程sp_dropsrvrolemember的格式为：
sp_dropsrvrolemember'登录账号'，服务器角色名称
2）数据库角色的管理
数据库的角色中db_owner添加到UserA①用对象资源管理器将数据库用户．
实验四数据完整性安全性徐龙琴、刘双印设计制作
②用系统存储过程sp_addrolemember将数据库用户UserB添加到db_owner数据库角色中。
sp_addrolemember'db_owner',UserB
实验四数据完整性安全性徐龙琴、刘双印设计制作
注：
为数据库用户指定数据库角色的系统存储过程sp_addrolemember的格式为：
sp_addrolemember[@rolename＝]'role',//当前数据库角色的名称
security_account/*添加到角色的安全帐户。
]security_account可以[@membername=
是所有有效的SQLServer用户、SQLServer角色或是所有
已授权访问当前数据库的MicrosoftWindowsNT?
用户或组。
*/
③用系统存储过程sp_droprolemember删除数据库用户UserB的db_owner数据库角色
sp_droprolemember'db_owner',UserB
注：
删除数据库用户的数据库角色的系统存储过程sp_droprolemember的格式为：
sp_droprolemember'数据库角色名称'，数据库用户账户
3）用户自定义角色的管理
①在学生成绩数据库（XSCJ）中创建一个角色RoleB
createroleRoleB
注：
在当前数据库创建新的角色：
sp_addrole[@rolename=]'role'新角色的名称//
[,[@ownername=]'owner']owner默认值为/*新角色的所有者，owner*/必须是当前数据库中的某个用户或角色。
dbo
实验四数据完整性安全性徐龙琴、刘双印设计制作
②将用户UserB加入到自定义的RoleB这个角色中
sp_addrolemember'RoleB',UserB
注：
将数据库户添加到自定义数据库角色的格式为：
sp_addrolemember[@rolename＝]'role',//当前数据库角色的名称
security_account/*添加到角色的安全帐户。
security_account=]可以[@membername
是所有有效的SQLServer用户、SQLServer角色或是所有
已授权访问当前数据库的MicrosoftWindowsNT?
用户或组。
*/
③将学生成绩数据库（XSCJ）中学生表的Select权限授予RoleB
grantselect
onxsqk
toRoleB
④用帐户LoginB身份登陆，并学生成绩数据库（XSCJ）中执行
CreateTableTest（aavarchar（10））
Select*from学生情况表
Select*from成绩表
并查看以上各语句能否正常执行，为什么？
如何才能使不能执行的语句执行？
如果用帐号LoginA登陆，上面的语句能否执行。
不行,因为UserB没有CreateTable权限
GRANTCREATETABLETOUserB
如果用帐号LoginA登陆，上面的语句可以执行。
⑤从角色RoleB中去除用户UserB
sp_droprolemember'RoleB',UserB
⑥从学生成绩数据库（XSCJ）中删除用户UserB
sp_dropuserUserB
⑦从学生成绩数据库（XSCJ）中删除角色RoleB
sp_droproleRoleB
⑧从SQLServer中删除登陆帐户LoginB
sp_droploginLoginB
注：
在删除登录帐号之前，应先将登录帐号所对应的用户帐号全部删除，不然将无法删除登录帐号。
⑸权限的授予与回收
1）权限的授予
①把查询XSQK表的权限授予用户UserA
select
grant
实验四数据完整性安全性徐龙琴、刘双印设计制作
onxsqk
toUserA
②把对KC表全部操作权限授予用户UserA
grantselect,insert,update,delete
onkc
toUserA
③把对XS_KC表的查询权限授予所有用户
grantselect
onxs_kc
topublic
④把删除XSQK表和修改学生学号的权限授予用户UserA
grantdelete,update（学号）
onxsqk
toUserA
⑤把插入XSQK表的权限授予用户UserA，并允许将此权限再授予其它用户
grantinsert
onxsqk
toUserA
withgrantoption
⑥把在学生成绩数据库（XSCJ）中建立表的权限授予用户UserA
grantcreatetable
toUserA
2）权限的回收
①撤销用户UserA修改学号的权限
revokeupdate（学号）
onxsqk
fromUserA
②撤销所有用户对XS_KC表的查询权限
revokeselect
onxs_kc
frompublic
③撤销用户UserA对XSQK表的插入权限
revokeinsert
onxsqk
fromUserACASCADE
实验四数据完整性安全性徐龙琴、刘双印设计制作
四、实训思考题
1．一个用户要访问数据库须要经过哪几个安全认证阶段？
答:
三个阶段及相应的安全认证过程：
第一阶段：
用户首先要登录到SQLServer实例。
在登录时，系统要对其进行身份验证，被认为合法才能登录到SQLServer实例。
第二阶段：
用户在每个要访问的数据库里必须获得一个用户账号。
SQLServer实例将SQL
Server登录映射到数据库用户账号上，在这个数据库的用户账号上定义数据库的管理和数据对象的访问的安全策略。
第三阶段：
用户访问数据库。
用户访问数据库对象时，系统要检查用户是否具有访问数据库对象、执行动作的权限，经过语句许可权限的验证，才能实现对数据的操作。
升级会员 