数据库系统原理——实验三

实验三 数据库完整性、安全性实现

一、实验目的

使学生加深对数据库安全性和完整性的理解，并掌握 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)

)

完成如下操作：

1. 在 SQL Server 管理器中，创建一个新的 windows 登录名。

（2）在 SQL Server 管理器中为自己建立一个 SQL SERVER 服务器登录名（自己

学号）。

1. 为所属的 SQL 服务器设置混合认证安全认证模式。

1. 用自己创建的登录名登录服务器。

（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 语句实现，请记录结果

并分析原因。