数据库系统原理实验三
数据库系统原理——实验三
实验三 数据库完整性、安全性实现
一、实验目的
使学生加深对数据库安全性和完整性的理解，并掌握 SQL Server 中有关用户、角
色及操作权限的管理方法，学会创建和使用规则、缺省和触发器以及存储过程。
二、实验要求
通过实验对数据进行完整性控制、安全性维护。
三、实验步骤
1
、 开始→程序→Microsoft SQL Server→SQL Server Management Stdio。
2
、 在“连接到服务器”对话框中，选择“Windows 身份验证”，点击“连接”，进
入 SQL Server Management Stdio 操作界面。
3
、数据库的安全性实验：设置 SQL Server 的安全认证模式，实现对 SQL Server
的用户和角色管理，设置和管理数据操作权限。
4
、数据库的完整性实验：使用 Transact-SQL 设计规则、缺省、约束和触发器，
通过 SQLServer 管理器定义它们。
创建 students 数据库。利用如下 sql 语句在 students 数据库中创建表（具体
含义见课本 P37 页）。
CREATE TABLE Student (
Sno char ( 7 ) PRIMARY KEY,
Sname char ( 10 ) NOT NULL,
Ssex char (2),
Sage tinyint ,
Sdept char (20)
)
CREATE TABLE Course (
Cno char(10) NOT NULL,
Cname char(20) NOT NULL,Ccredit tinyint ,
Semester tinyint,
PRIMARY KEY(Cno)
)
CREATE TABLE SC
(
Sno char(7) NOT NULL,
Cno char(10) NOT NULL,
Grade tinyint,
XKLB char(4) ,
PRIMARY KEY ( Sno, Cno ) ,
FOREIGN KEY (Sno) REFERENCES Student (Sno),
FOREIGN KEY (Cno) REFERENCES Course (Cno)
)
完成如下操作：
- 在 SQL Server 管理器中，创建一个新的 windows 登录名。
（2）在 SQL Server 管理器中为自己建立一个 SQL SERVER 服务器登录名（自己
学号）。
- 为所属的 SQL 服务器设置混合认证安全认证模式。
- 用自己创建的登录名登录服务器。
（5）在 SQL Server 管理器中为自己建立一个 students 数据库用户（自己学号
-user
）和角色（自己学号-role）。
（6）用管理器实现如下权限的分配：
① 将 student 表中“sname”、“ssex”、“sage”的修改权限赋予自己创建的数据库用户。
use
shiyan3
go
grant
update
on
Student
(
Sname
,
Ssex
,
Sage
)
to
[20192163-user]
go
② 将 student 表、course 表、sc 表的查询权限授予自己创建的角色。
grant
select
on
Student
to
[20192163-role]
;
grant
select
on
Course
to
[20192163-role]
;
grant
select
on
SC
to
[20192163-role]
;
③ 拒绝自己创建的角色拥有 sc 表中 grade 的修改权限。
deny
update
on
SC
(
grade
)
to
[20192163-role]
④ 将自己创建的数据库用户添加为自己创建的角色成员。
exec
sp_addrolemember
‘20192163-role’
,
‘20192163-user’
（7）用管理器创建触发器实现如下安全性和完整性：
①定义 course 表中 credit 属性为“3”的缺省。
create
trigger
dbo
.
st1
on
[dbo]
.
[Course]
after
insert
,
update
as
begin
set
nocount
on
;
update
Course
set
Ccredit
= 3 from
Course
join
SC
on
SC
.
Cno
=
Course
.
Cno
where
Sno
in(
select
Sno
from
inserted
where
Cno
=null)
end
go
②建立 course 与 sc 表间来维护参照完整性而使用的一个级联删除触发器、一 个级联修改触发器和一个受限插入触发器。
一个级联删除触发器：
Create
trigger
st2
On
student
After
delete
as
begin
set
nocount
on
;
delete
from
SC
where
Sno
=(
select
sno
from
deleted
)
end
go
一个级联修改触发器：
Create
trigger
st3
On
student
After
update
as
begin
set
nocount
on
;
update
SC
set
Sno
=(
select
Sno
from
inserted
)
where
Sno
=(
select
sno
from
deleted
)
end
go
一个受限插入触发器：
Create
trigger
st4
On
student
After
insert
as
begin
set
nocount
on
;
delete
from
SC
where
Sno
=
(
select
Sno
from
inserted
where
Sno
not
in(
select
Sno
from
student
))
or
Cno
=
(
select
Cno
from
inserted
where
Cno
not
in(
select
Cno
from
course
))
end
go
（8）对“students 数据库”编写存储过程，完成下面功能：
① 统计成绩 60 分以下的人数；
create
procedure
pr1
as
begin
set
nocount
on
;
select
count
(*)
from
SC
where
Grade
< 60
end
② 统计给定 cno 的课程的平均成绩，并返回平均成绩；
create
procedure
pr2
as
begin
set
nocount
on
;
select
Cno
,
AVG
(
Grade
)
from
SC
group
by
Cno
end
③将 sc 表中 grade 从百分制改为等级制（5、4、3、2、1）。即 0-20 分为 1，
21-40
为 2，4 1-60 为 3，61-80 为 4,81-100 为 5。
create
procedure
pr5
as
begin
declare
@Grade
int
if
(
@Grade
between 81 and 100 )
set
@Grade
= 5
else
if
(
@Grade
between 61 and 80 )
set
@Grade
= 4
else
if
(
@Grade
between 41 and 60 )
set
@Grade
= 3
else
if
(
@Grade
between 21 and 40 )
set
@Grade
= 2
else
set
@Grade
= 1 ;
end
要求：仔细阅读操作要求， 按要求用 SSMS 工具或 T-SQL 语句实现，请记录结果
并分析原因。