数据库实验四:数据完整性和数据安全性实验

数据库实验四:数据完整性和数据安全性实验

一、实验名称

数据完整性和数据安全性

二、实验目的

加深对数据完整性和安全性的理解，并掌握在 SQL Server Management Studio 中设置
身份验证模式，有关用户、角色及操作权限的管理方法。掌握创建和使用规则、默认和触发
器。

三、实验内容

1） 数据库的完整性实验

通过 SQL Server Management Studio 定义规则、缺省、约束和触发器。

2）数据库的安全性实验

在 SQL Server Management Studio 中，设置 SQL Server 的安全认证模式，实现对 SQL
server 的用户和角色管理，设置和管理数据操作权限。

实验内容：

1、设置 SQL Server 身份验证模式，用户和角色的创建，操作权限的管理。
2、创建和使用规则、默认和触发器。

四、实验任务

1. 用用户名为“sa”的用户登录，在“SQL Server Management Studio”中将 SQL Server
   的身份验证模式设置为“Windows 身份验证模式”，断开与对象资源管理器的连接，再
   连接对象资源管理器时分别用“Windows 身份验证”和“SQL Server 身份验证”两种
   模式登录，再将数据库的身份验证模式设置为“SQL Server 和 Windows 身份验证模式”，
   再分别用两种模式登录，分析两种不同的身份验证模式下登录结果。

在SQLSever环境下操作

2. 用用户名为“sa”的用户登录，在“SQL Server Management Studio”中创建名称为
   “Liping”的登录名，再创建用户名为“Liping”的用户，用用户名为“Liping”的
   用户登录，对学生表执行一个查询语句，分析查询失败的原因。

--创建用户Liping
use master
go 
exec sp_addlogin"Liping","1234"
--创建用户
use XSXK
go
exec sp_grantdbaccess 'Liping','Liping'

3. 用用户名为“sa”的用户登录，在“SQL Server Management Studio”中给用户名为
   “Liping”的用户授予查询学生表的权限，再用用户名为“Liping”的用户登录，对
   学生表执行一个查询语句，分析查询成功的原因。
   –授权

grant select on student to Liping with grant option

4. 用用户名为“sa”的用户登录，在“SQL Server Management Studio”中创建名称为
   “Lili”的登录名，再创建用户名为“Lili”的用户，分别用用户名为“Liping”和
   “Lili”的用户登录，对课程表执行一个查询语句，分析查询失败的原因。
   –创建登录名Lili

use master
go 
exec sp_addlogin"Lili","1234"
--创建用户
use XSXK
go
exec sp_grantdbaccess 'Lili','Lili'

5. 用用户名为“sa”的用户登录，在“SQL Server Management Studio”中创建名称为
   “MyRole”的角色，将用户名为“Liping”的用户和用户名为“Lili”的用户加入该
   角色中，对该角色授予查询课程表的权限，分别用用户名为“Liping”和“Lili”的
   用户登录，对课程表执行一个查询语句，分析查询成功的原因。
   –创建角色MyRole

exec sp_addrole 'MyRole'
go
--将用户名为“Liping”的用户和用户名为“Lili”的用户加入该角色中
exec sp_addrolemember'MyRole','Liping'
go
exec sp_addrolemember'MyRole','Lili'
--对该角色授予查询课程表的权限，
--分别用用户名为“Liping”和“Lili”的用户登录
grant select on Course to MyRole
go

6. 用用户名为“sa”的用户登录，创建一个规则，约束值为“男”或“女”，将该规则绑
   定到学生表的“性别”列。在学生表中输入一条非法学生记录（性别值不为“男”和
   “女”），体验规则的作用。

use XSXK
go
create rule s_sex
as 
@ssex in('男','女')
--该语句必须是批处理中唯一的语句
go
sp_bindrule's_sex','student.ssex'
go 
select*from student
insert into student 
values('013','张十三','妖',30,'艺术学院','13626787865')

7. 用用户名为“sa”的用户登录，创建一个默认值，值为“20”，将该默认值绑定到学生
   表的“年龄”列。在学生表中输入一条学生记录（不输入年龄值），体验默认的作用。

use XSXK
GO
create default s_sage
as
20
go
sp_bindefault 's_sage','student.sage'
go
insert into student(sno,sname,ssex,sdept,stel)
values('014','张十四','男','数计学院','15678976465')
go

8. 用用户名为“sa”的用户登录，对选课表定义一个“insert”触发器，要求插入到选
   课表中的记录满足参照完整性约束。
   –需要删除之前建立的选课表的外键

create trigger SC_insert on SC for insert
as
if(select count(*)from student,inserted,Course
where student.sno=inserted.sno 
and Course.cno=inserted.cno)
=0

begin
	rollback transaction
	raiserror('不能插入!',16,10)
end
go
insert into SC
values('13','12',34)

go

9. 用用户名为“sa”的用户登录，对学生表定义一个“Delete”触发器，要求删除学生
   记录的同时把学生的选课记录也删除。

–需要删除之前建立的选课表的外键

create trigger delete_stu on student for delete
as 
declare @sno char(20)
select @sno=sno from deleted
delete from SC where sno=@sno
go
select*from student
select*from SC
delete from student where sno='006'
go

附：相关语句的语法格式：

1）利用 T_SQL 添加 sql server 帐号
增加新的帐号可以执行系统存储过程 sp_addlogin,格式如下：

• SP_addlogin login_id [,passwd,[defdb[,deflanguage]]]

2）利用 T_SQL 删除 sql server 登录帐号
利用存储过程 sp_droplogin
3）利用 T_SQL 添加用户
利用系统存储过程 sp_adduser,格式如下：

• Sp_adduser login_id[,username]

4）利用 T_SQL 删除用户 sp_dropuser
5）利用 T_SQL 创建数据库角色 sp_addrole
6）利用 T_SQL 向角色中增加/ 删除成员 sp_addrolemember /sp_droprolemember
例如：向学生数据库中创建角色 newroles，并将用户 x 添加到该角色中

Use 学生
Exec sp_addrole ‘newroles’
Exec sp_addrolemember ‘newroles’,’x’

四、实验报告要求

1、简要写出操作过程或相关的 T-SQL 语句。
2、实验操作步骤和实验结果，实验中出现的问题和解决方法。
3、体会和提高。