广东海洋大学_计算机科学与技术_数据库实验4.doc

实验四 数据完整性安全性 徐龙琴、刘双印设计制作
实验四 数据的完整性、安全性

实验目的
掌握数据安全性和完整性的概念，以及如何保证数据库中数据安全及完整性。
掌握 SQL Server中有关用户、角色及操作权限的管理方法．
学会创建和使用规则、缺省。
实验内容
1 数据库的安全性实验，通过SSMS设置 SQL Server的安全认证模式．实现对SQL Server的用户和角色管理，设置和管理数据操作权限．
2数据库的完整性实验。使用Transact-SQL设计规则、缺省、约束和触发器。
实验要求
数据的完整性实验
CREATE DATABASE XSCJ
ON PRIMARY
(
NAME=Student_dat,
FILENAME='D:\XSCJ.mdf',
SIZE=10MB,
MAXSIZE=50MB,
FILEGROWTH=10%
)
LOG ON
(
NAME=Student_log,
FILENAME='D:\XSCJ.ldf',
SIZE=5MB,
FILEGROWTH=10%
)
GO
⑴用SQL语句创建一学生成绩数据库(XSCJ)，包括学生(XSQK)、课程(KC)和成绩表(XS_KC)：
学生情况表（XSQK）
列名
数据类型
长度
是否允许为空值
学号
Char
6
N
姓名
Char
8
N
性别
Bit
1
N
出生日期
smalldatetime
2

专业名
Char
10

所在系
Char
10

联系电话
char
11
Y
CREATE TABLE XSQK(
学号CHAR(6) NOT NULL ,
姓名CHAR(8) NOT NULL,
性别BIT NOT NULL,
出生日期smalldatetime ,
专业名CHAR(10) ,
所在系 Char(10),
联系电话char(11)
) ;课程表（KC）
列名
数据类型
长度
是否允许为空值
课程号
Char
3
N
课程名
Char
20
N
教师
Char
10

开课学期
Tinyint
1

学时
Tinyint
1

学分
Tinyint
1
N
CREATE TABLE KC(
课程号 Char(3) NOT NULL,
课程名 Char(20) NOT NULL,
教师 Char(10),
开课学期 Tinyint ,
学时 Tinyint ,
学分 Tinyint NOT NULL
) ;

成绩表（XS_KC）
列名
数据类型
长度
是否允许为空值
学号
Char
6
N
课程号
成绩
Char
Smallint
3
2
N

CREATE TABLE XS_KC(
学号 Char(6) NOT NULL,
课程名 Char(3) NOT NULL,
成绩 Smallint
) ;
⑵数据的实体完整性实验 V
①用SSMS分别将学生情况表（XSQK）的学号字段、课程表（KC）的课程号字段设置为主健
②用T－SQL语句将成绩表（XS_KC）的学号、课程号字段设置为主健
ALTER TABLE XSKC ADD CONSTRAINT XS_KC_KEY PRIMARY KEY (学号,课程号)
⑶数据的参照完整性实验
①用SSMS为成绩表（XS_KC）创建外键FK_ XSQK_ID，外键FK_ XSQK_ID参照学生情况表（XSQK）表的学号

②用T－SQL语句成绩表（XS_KC）创建外键FK_ KC_ID，外键FK_ KC _ID参照课程表（KC）
表的课程号
ALTER TABLE XS_KC ADD CONSTRAINT FK_ KC_ID FOREIGN KEY (课程号) REFERENCES KC(课程号);
⑷数据的用户定义完整性实验
用T－SQL语句为学生情况表（XSQK）的姓名列创建一个唯一约束
ALTER TABLE XSQK ADD CONSTRAINT FK_XSQK_UNIQUE UNIQUE (姓名)
②用SSMS为学生情况表（XSQK）的性别列创建一个检查约束，使得性别的值为男或女

③用T－SQL语句为成绩表（XS_KC）的成绩列创建一个检查约束，使得成绩的值在（0～100）之间
ALTER TABLE XS_KC ADD CONSTRAINT GRADEchk CHECK(成绩BETWEEN 0 AND 100);
④用SSMS语句课程表（KC）的学时列创建一个缺省约束，缺省值为60

⑤用T－SQL语句课程表（KC）的学分列创建一个缺省约束，缺省值为2
ALTER TABLE KC ADD CONSTRAINT FK_KC_DEFAULT DEFAULT 2 FOR 学分
２．将如下数据分别转换成文本数据或Excel的格式，并分别将其导入数据库的各个表中：
学号
姓名
性别
出生日期
专业
所在系
联系电话
020101
杨颖
0
1980-7-20
计算机应用
计算机
88297147
020102
方露露
0
1981-1-15
计算机应用
计算机
88297147
020103
俞奇军
1
1980-2-20

信息管理
计算机
88297151
020104
胡国强
1
1980-11-7
信息管理
计算机
88297151
020105
薛冰
1
1980-7-29
水利工程
水利系
88297152
020201
秦盈飞
0
1981-3-10
电子商务
经济系
88297161
020202
董含静
0
1980-9-25
电子商务
经济系
88297062
020203
陈伟
1
1980-8-7
电子商务
经济系
88297171
020204
陈新江
1
1980-7-20
房建
水利系
88297171
学生情况表（XSQK）：

课程表（KC）：
课程号
课程名
教师
开课学期
学时
学分
101
计算机原理
陈红
2
45
3
102
计算方法
王颐
3
45
3
103
操作系统
徐格
2
60
4
104
数据库原理及应用
应对刚
3
75
5
105
网络基础
吴江江
4
45
3
106
高等数学
孙中文
1
90
6
107
英语
陈刚
1
90
6
108
VB程序设计
赵红韦
3
70
5

成绩表（XSKC）:
学号
课程号
成绩
020101
101
85
020101
102
87
020101
107
88
020102
101
58
020102
102
63
020104
107
76
020202
103
55
020202
108
80
020203
103
57
020204
103
71

3. 理解默认值的概念和作用
①用语句创建名为Xi_default，值为 “计算机系”的默认值
CREATE DEFAULT Xi_default AS '计算机系'

②将默认值Xi_default绑定到学生表中的所在系的属性列上
sp_bindefault 'Xi_default' ,'XSQK.所在系'
③解除学生表所在系的属性列上的默认值
sp_unbindefault 'XSQK.所在系'
④删除默认值Xi_default
Drop default Xi_default
4.理解规则的概念和作用
①用语句创建规则“rule_kkxq”，用以限制插入该规则所绑定的列中的值只能取1、2、3、4、5、6
CREATE RULE rule_kkxq AS @INTCLUOMN IN (1,2,3,4,5,6)
②将“rule_ kkxq”规则绑定到课程表的开课学期属性上，并执行以下语句，看看能否正
常执行，为什么：
Insert into 课程表
Values(109,'C语言','李方',8,64,4)
sp_bindrule rule_kkxq ,'KC.开课学期'
不能正常执行,因为数值8不在规则rule_kkxq所绑定的数据范围内
③不解除规则，能否将规则rule_ kkxq直接删除？
不能,需先解除规则才能删除规则rule_ kkxq :
sp_unbindrule 'KC.开课学期'
DROP RULE rule_kkxq
5.数据的安全性实验：
(1)设置身份验证模式
①写出查看当前SQL Server身份验证模式的过程，即查看当前SQL Server系统到底是采用Windows身份验证还是混合身份验证模式。
服务器->右键"属性"->选择"安全性",即可查看当前SQL Server系统的服务器验证模式

②将系统设置为Windows身份验证模式，重启SQL Server，使之生效并验证
③将系统设置为混合身份验证模式，重启SQL Server，使之生效并验证
⑵创建和管理登录账户
1）用对象资源管理器创建、查看、删除SQL Server登录账户
①分别创建两个登录帐户LoginA、LoginA1，其登录密码分别为：123456、456，并指定LoginA登录默认的数据库为学生成绩数据库(XSCJ)，指定LoginA1登录默认的数据库为master数据库。

②查看登录账户LoginA、LoginA1

③删除登录账户LoginA1

2）用T-SQL语句创建、查看、删除SQL Server登录账户
①用系统存储过程，分别创建两个登陆帐户LoginB、LoginB1，其登录密码分别为：666、888，登录后然后所连接到的数据库均为学生成绩数据库(XSCJ)。
sp_addlogin 'LoginB','666','XSCJ'
sp_addlogin 'LoginB1','888','XSCJ'
②使用系统存储过程sp_helplogins查看SQL Server登录账户
注：查看登录账户的格式： exec sp_helplogins

③使用系统存储过程sp_droplogin从SQL Server中将登录账户LoginB1删除
sp_droplogin @loginame = 'LoginB1'
⑶创建和管理数据库用户
1）用对象资源管理器创建、查看、删除数据库用户
①在学生成绩数据库(XSCJ)中创建数据库用户UserA，使其所对应的帐号为LoginA
②能否再创建一个数据库用户ABC，使其对应登录的帐号为LoginA，若不能写出其原因？
不能,同一个数据库的数据库用户只能有一个登录账户
“该登陆已用另一开立账户用户名”
2）用语句创建、查看、删除数据库用户
①用系统存储过程sp_grantdbaccess 在学生成绩数据库(XSCJ)中创建一个数据库用户UserB，使其所对应的登录帐号为LoginB
sp_grantdbaccess 'LoginB','UserB'
②用系统存储过程sp_helpuser查看数据库用户
sp_helpuser 'UserB'
③用系统存储过程sp_ revokedbaccess删除数据库用户ABC1
sp_revokedbaccess ' ABC1'
无法对用户' ABC1' 执行删除因为它不存在，或者您没有所需的权限。
⑷创建和管理角色
1）服务器角色的管理
①用对象资源管理器将登录帐户LoginA加到sysadmin的角色中。

②用系统存储过程sp_addsrvrolemember将登录帐户LoginB加到sysadmin的角色中。
sp_addsrvrolemember 'LoginB',sysadmin
③用系统存储过程sp_dropsrvrolemember删除登录账户LoginA的服务器角色sysadmin
sp_dropsrvrolemember 'LoginA',sysadmin
2）数据库角色的管理
①用对象资源管理器将数据库用户UserA添加到db_owner数据库的角色中

②用系统存储过程sp_addrolemember将数据库用户UserB添加到db_owner数据库角色中。
sp_addrolemember 'db_owner',UserB
③用系统存储过程sp_droprolemember删除数据库用户UserB的db_owner数据库角色
sp_droprolemember 'db_owner',UserB
3）用户自定义角色的管理
①在学生成绩数据库(XSCJ)中创建一个角色RoleB
CREATE ROLE RoleB
②将用户UserB加入到自定义的RoleB这个角色中
sp_addrolemember 'RoleB',UserB
③将学生成绩数据库(XSCJ)中学生表的Select权限授予RoleB
GRANT SELECT
ON XSQK
TO RoleB
④用帐户LoginB身份登陆，并学生成绩数据库(XSCJ)中执行
Create Table Test (aa varchar(10))
Select * from 学生情况表
Select * from 成绩表
并查看以上各语句能否正常执行，为什么？如何才能使不能执行的语句执行？
不行,因为UserB没有Create Table权限
GRANT CREATE TABLE TO UserB
如果用帐号LoginA登陆，上面的语句能否执行。
可以
⑤从角色RoleB中去除用户UserB
sp_droprolemember 'RoleB',UserB
⑥从学生成绩数据库(XSCJ)中删除用户UserB
sp_dropuser UserB
⑦从学生成绩数据库(XSCJ)中删除角色RoleB
sp_droprole RoleB
⑧从SQL Server中删除登陆帐户LoginB
sp_droplogin 'LoginB'
⑸权限的授予与回收
1）权限的授予
①把查询XSQK表的权限授予用户UserA
GRANT SELECT
ON XSQK
TO UserA
②把对KC表全部操作权限授予用户UserA
GRANT SELECT ,INSERT, UPDATE, DELETE
ON KC
TO UserA
③把对XS_KC表的查询权限授予所有用户
GRANT SELECT
ON XS_KC
TO PUBLIC
④把删除XSQK表和修改学生学号的权限授予用户UserA
GRANT DELETE
ON XSQK
TO UserA
⑤把插入XSQK表的权限授予用户UserA，并允许将此权限再授予其它用户
GRANT INSERT
ON XSQK
TO UserA
WITH GRANT OPTION
⑥把在学生成绩数据库(XSCJ)中建立表的权限授予用户UserA
GRANT CREATE TABLE
TO UserA
2）权限的回收
①撤销用户UserA修改学号的权限
REVOKE UPDATE(Sno)
ON XSQK
FROM UserA
②撤销所有用户对XS_KC表的查询权限
REVOKE SELECT
ON XS_KC
FROM PUBLIC
③撤销用户UserA对XSQK表的插入权限
REVOKE INSERT
ON XSQK
FROM UserA CASCADE

实训思考题
一个用户要访问数据库须要经过哪几个安全认证阶段？
答: 三个阶段及相应的安全认证过程：
第一阶段：用户首先要登录到 SQLServer 实例。在登录时，系统要对其进行身份验证，被认 为合法才能登录到 SQLServer 实例。
第二阶段：用户在每个要访问的数据库里必须获得一个用户账号。SQL Server 实例将 SQL Server 登录映射到数据库用户账号上， 在这个数据库的用户账号上定义数据库的管理和 数据对象的访问的安全策略。

第三阶段：用户访问数据库。用户访问数据库对象时，系统要检查用户是否具有访问数 据库对象、执行动作的权限，经过语句许可权限的验证，才能实现对数据的操作。
简述登录账户和用户账户间区别与联系
答: 登录账户是登录SQLServer 实例的账户;
用户账户是访问数据库的账户.
用户、角色和权限的职能，以及它们之间的关系
答:
1. 权限
系统的所有权限信息。权限具有上下级关系，是一个树状的结构。
2.    用户
应用系统的具体操作者，用户可以自己拥有权限信息，可以归属于0～n个角色，它的权限集是自身具有的权限、所属的各角色具有的权限、所属的各组具有的权限的合集。它与权限、角色 之间的关系都是n对n的关系。
3.    角色
可以对许多拥有相似权限的用户进行分类管理,具有上下级关系，可以形成树状视图，父级角色的权限是自身及它的所有子角色的权限的综合。父级角色的用户、父级角色的组同理可推。

服务器角色有哪些，并分别简述其功能？用户可以创建服务器角色吗？
答:
服务器角色有:
sysadmin 可以在 SQL Server 中执行任何活动。
serveradmin 可以设置服务器范围的配置选项，关闭服务器。
setupadmin 可以管理链接服务器和启动过程。
securityadmin 可以管理登录和 CREATE DATABASE 权限还可以读取错误日志和更改密码。
processadmin 可以管理在 SQL Server 中运行的进程。
dbcreator 可以创建、更改和除去数据库。
diskadmin 可以管理磁盘文件。
bulkadmin 可以执行 BULK INSERT 语句。
用户不可以创建服务器角色
数据库角色有哪些，并分别简述其功能？用户可以创建数据库角色吗？
答: 数据库角色有:
db_owner   在数据库中有全部权限。     
db_accessadmin   可以添加或删除用户   ID。     
db_securityadmin   可以管理全部权限、对象所有权角色和角色成员资格。     
db_ddladmin   可以发出   ALL   DDL，但不能发出   GRANT、REVOKE   或   DENY 语句。     
db_backupoperator   可以发出   DBCC、CHECKPOINT   和   BACKUP   语句。     
db_datareader   可以选择数据库内任何用户表中的所有数据。     
db_datawriter   可以更改数据库内任何用户表中的所有数据。     
db_denydatareader   不能选择数据库内任何用户表中的任何数据。     
db_denydatawriter   不能更改数据库内任何用户表中的任何数据。
用户可以创建数据库角色
6、什么是数据库的安全性，可以通过哪些措施来保证数据库的安全性？
答:数据库的安全性是指:保护数据库以防止非法使用所造成的数据泄密、更改或破坏.
保证数据库的安全性的方法: 用户标识和鉴别、用户存取权限控制、定义视图、数据加密、安全审计以及事务管理和故障恢复等几类