《数据库系统原理》实验指导书参考答案(计本10、电商10)(12)

（5）分别用触发器和存储过程实现对学生情况表（xsqk）和学生成绩表（xscj）表的级联删除。 存储过程：

create procedure p_del @sno varchar(8) as begin delete score where sno=@sno delete student where sno=@sno end 触发器：

create trigger tr_student_delete on xsqk for delete as

declare @num char(8)

44

select @num=学号from deleted delete xscj where 学号=@num go

（6）创建触发器8，要求实现：当向xscj表插入一条记录时，判断该学生的总学分，如果总学分大于等于25，则给出“该学生已修满，不需要再选修！”的提示信息；否则，自动更新该学生的总学分。 create trigger tirgger8 on xscj for insert as

declare @sno char(8)

select @sno=学号from inserted if (select 总学分from xsqk where 学号=@sno)>=25 begin

raiserror('该学生已修满，不需要再选修!',16,1) rollback tran return end else begin

declare @xf tinyint

select @xf=学分from inserted update xsqk

set 总学分=总学分+@xf where 学号=@sno end

45

实验十三：安全性管理

一、 实验目的

通过实验使学生理解SQL Server 2000数据库安全性的管理方法。 二、 原理解析 1、SQL Server登录帐号

与登录有关的存储过程：

sp_addlogin 创建SQL Server登录帐号 sp_droplogin删除SQL Server登录帐号 sp_grantlogin创建windows登录帐号

sp_revokelogin删除windows用户或用户组在SQL Server上的登录信息 sp_denylogin拒绝某一windows用户或用户组连接到SQL Server 2、数据库用户帐号

只有数据库用户才具有访问、操作该数据库的权限，某一登录帐号要获得操作数据库的权限，必须与相应的数据库用户相映射。

与数据库用户帐号有关的存储过程： sp_grantdbaccess创建一个数据库用户 sp_revokedbaccess删除一个数据库用户 3、角色管理

可以把一些用户归入某一角色，这样只要角色具有了一定的权限，用户相应地从该角色继承了相应的权限，角色的提出有利于权限的管理。

角色可分为固定服务器角色和数据库角色。 与角色有关的存储过程：

sp_addsrvrolemember添加固定服务器角色成员 sp_addrole向数据库添加一角色 sp_addrolemember添加数据库角色成员 sp_dropsrvrolemember删除固定服务器角色成员 sp_droprolemember删除数据库角色成员 sp_droprole删除角色

注意：删除角色前，必须删除角色成员。

46

4、许可管理

许可是用来授权用户可以使用数据库中数据和执行数据库操作。 许可授予grant语句 许可禁止deny语句 许可收回revoke语句 三、 实验内容 （7） 登录帐号的管理。 （8） 数据库用户帐号的管理。 （9） 角色管理。 （10）许可管理。 四、 实验步骤

（1）创建SQL Server登录帐号aa，赋予其系统管理员角色。 sp_addlogin 'aa','123'

sp_addsrvrolemember 'aa','sysadmin'

（2）赋予windows登录帐号bb在数据库StuScore上的db_owner访问权限。 sp_grantlogin 'tjf\\bb' use stuscore go

sp_addrolemember 'db_owner','tjf\\bb'

（3）创建SQL Server登录帐号LoginT，其在StuScore数据库上的对应用户为userT。

sp_addlogin 'loginT','T' use stuscore go

sp_grantdbaccess 'loginT','userT'

（4）删除userT所对应的登录帐号loginT。 Sp_droplogin 'loginT'

（5）删除StuScore数据库用户帐号userT。 sp_revokedbaccess 'userT'

47

（6）在服务器上创建一Windows用户组gg，接着创建Windows用户cc，并使cc隶属于组gg，然后分别将gg和cc授权登录和访问SQL Server系统。依次利用此账号作测试登录。测试成功后运行脚本： sp_revokelogin 'cc'

然后以cc登录SQl Server系统，能否成功。运行脚本： sp_addlogin 'cc','cc' Go

sp_denylogin 'cc' Go

再以cc登录SQL Server系统，测试能否成功。

(7) 利用系统存储过程为数据库StuScore创建一数据库角色myrole，并创建一个数据库用户myuser1（对应登录名111），使其属于角色myrole。 sp_addrole myrole sp_addlogin '111','123'

sp_grantdbaccess '111','myuser1' sp_addrolemember 'myrole','myuser1'

(8) 将数据库用户myuser1添加为数据库StuScore的db_owner角色。 sp_addrolemember 'db_owner','myuser1'

(9) 在SQL Server查询分析器中，执行下列脚本，并查看数据库myTestDB。

--创建范例数据库myTestDB Create Database myTestDB Go

--创建测试用表stud01和stud02 use myTestDB Go

Create Table stud01 (

no char(5) , name char(10) ,

48