数据库函数存储过程触发器数据库安全的实验文档格式.docx

数据库函数存储过程触发器数据库安全的实验文档格式.docx

《数据库函数存储过程触发器数据库安全的实验文档格式.docx》由会员分享，可在线阅读，更多相关《数据库函数存储过程触发器数据库安全的实验文档格式.docx（43页珍藏版）》请在冰豆网上搜索。

）

createfunctionFU2_SPXS（@yearint,@quarterint,@cpmcchar（10））

returns@xsqktable

（产品名称char（10）,

销售数量int,

销售金额int）

begininsertinto@xsqk

select产品名称,sum（销售额）as销售总额,sum（数量）as销售数量

fromcpxsb,cp

anddatepart（year,销售日期）=@year

anddatepart（quarter,销售日期）=@quarter

groupby产品名称

end

（3）根据销售商名称，统计其在某年某季度内销售商品名称、数量及金额。

（函数名为FU3_CPXS）

createfunctionFU3_CPXS（@xssmcchar（10）,@yearint,@quarterint）

returnstable

fromCPXSB,CP,XSS

whereCPXSB.产品编号=CP.产品编号

andCPXSB.客户编号=XSS.客户编号

and客户名称=@xssmc

anddatepart（year,销售日期）=@year

groupby产品名称）

2、函数的调用

（1）对函数FU_CP，查询产品名称为“mp3”的产品情况；

mp3'

g

（2）对函数FU1_CPXS，查询2004年第1季度彩色电视机的销售数量和销售金额；

select*fromFU321_CPXS（2004,1,'

彩电电视机'

（3）对函数FU2_CPXS，查询2004年第1季度洗衣机的销售数量和销售金额；

select*fromFU1_CPXS（2004,1,'

洗衣机'

（4）对函数FU3_CPXS，查询广电公司2004年第1季度销售的产品名称、销售数量和销售金额。

三、分析与回答

试说明内嵌表值函数、多语句表值函数的联系与区别

答：

内嵌表值函数返回的是一个表；

而多语句表值函数的功能是试图与存储过程的组合，可以利用多语句表值函数返回一个表，表中的内容可由复杂的逻辑和多条SQL语句构建，可以在select语句中句的from子句中使用多语句表值函数。

实验十一索引、默认值约束和默认值对象

一、实验内容和步骤

1、索引的创建和删除

（1）对CP表，在产品名称上定义一个唯一非聚簇的索引ind_cp。

createuniqueindexind_cp6oncp（产品名称）

（2）先创建各客户购买产品的情况VIEW1视图，包括客户编号、客户名称、产品编号、产品名称、价格，购买日期、购买数量，然后在客户编号+产品编号+购买日期定义一个唯一聚簇索引ind_view1。

（请注意7个SET选项设置）。

创建视图如下：

createviewcp_gmqk

selectxss.客户编号,客户名称,

cp.产品编号,产品名称,价格,

cpxsb.销售日期,数量

fromcpjoincpxsboncp.产品编号=cpxsb.产品编号

joinxssonxss.客户编号=cpxsb.客户编号

定义一个唯一聚簇索引ind_view1：

createuniqueclusteredindexind_view

oncpxsb（客户编号,产品编号,销售日期）

2、索引的删除

删除ind_view1索引

dropindexcpxsb.ind_view

3、默认值约束的定义

对库存量字段添加默认值为0的约束def_kcl

altertablecp

addconstraintdef_kcl

default0for库存量

4、默认值约束的删除

删除def_kcl默认值约束

dropconstraintdef_kcl

5、默认值对象的定义、使用和删除

（1）定义一值为0的默认值对象kcl_def，并将其绑定到CP表的库存量字段；

然后再删除此默认值对象，请叙述该过程，并写出相关SQL语句。

createdefaultkcl_def

as0

execsp_bindefault'

kcl_def'

'

cp.库存量'

go

dropdefaultkcl_def

sp_unbindefault'

（2）用SQL命令定义一名为city的用户自定义数据类型，要求char（6），NULL，再定义一值为“北京”的默认值对象city_def，然后将city_def默认值对象绑定到city自定义数据类型，最后删除city_def默认值对象，请叙述该过程，并写出相关SQL语句。

自定义数据类型：

sp_addtype'

city'

'

char（6）'

null'

绑定到自定义数据类型：

createdefaultcity_def

as'

北京'

city_def'

删除city_def默认对象：

dropdefaultcity_def

sp_unbindefault'

（1）说明索引的概念和作用。

索引的概念:

数据库中的索引是一个表中包含列表的列值，其中注明了表中包含值的行数据所在的存储位置；

其作用是加快数据的查询。

（2）说明聚簇索引和非聚簇索引的含义和区别。

聚集索引的概念：

聚集索引定义了数据在表中存储的物理顺序，它是指表中数据行的的物理存储顺序与索引顺序完全相同。

聚集索引有上下两层构成，上层为索引页，包含表中的索引页面，下层为数据页。

非聚集索引的概念：

非聚集索引不改变表中的物理存储顺序，数据与索引分开存储。

非聚集索引中仅包含索引值和指向数据存储位置的指针。

索引中的项目按索引键值的顺序存储，而表中的信息按另一种顺序存储。

区别在于：

1：

数据不按非聚集索引中的关键字值的顺序排列和存储；

2：

非聚集索引的叶级节点不是存放数据的数据页

实验十二数据完整性的实现

1、实验内容和步骤

1、实体完整性的实现

（1）对CP表、CPXSB表、XSS表，定义主键约束（用企业管理器和SQL语句分别实现，写出相应过程和语句）。

对cp表：

altertableCP

addconstraintpk_cp

primarykey（产品编号）

对xss表：

altertableXSS

addconstraintpk_xss

primarykey（客户编号）

对cpxsb表:

altertablecpxsb

addconstraintpk_cpxsb

（2）在CP表的产品名称列定义一个唯一约束（用企业管理器和SQL语句分别实现，写出相应过程和语句）。

addconstraintuk_cp

unique（产品名称）

2、参照完整性的实现

（1）利用企业管理器建立CP表与CPXSB之间的参照关系，当对主表CP表进行更新和删除操作时，从表CPXSB采用NOACTION方式，写出其过程。

（2）利用SQL语句建立CPXSB与XSS表之间的参照关系，当对主表XSS表进行更新和删除操作时，从表CPXSB采用CASCADE（级联）方式，写出其过程。

3、域完整性的实现

（1）在CP表的价格列上定义大于等于0的检查（CHE约束。

addconstraintck_cp

check（价格>

=0）

（2）定义一个大于等于0的规则check_kcl，并将其绑定到CP表的库存量列，然后解除绑定，删除该规则，请写出相关SQL语句。

createrulecheck_kcl

as@rangelike'

k[>

=0]'

sp_bindrule'

check_kcl'

先解除绑定

sp_unbindrule'

删除该规则：

droprulecheck_kcl

4、综合训练

创建一个“学生档案”数据库，并用CREATETABLE在该数据库中创建“人事”表和“成绩”两个表，其中“人事”表包括学号、姓名、性别、电话号码、家庭住址字段组成。

“成绩”表包括学号、选修课程名称、成绩三个字段。

分别在“人事”表的学号字段设置主键约束，在“成绩”表的学号字段设置外键约束，在成绩字段设置大于等于0小于等于100的检查约束，在家庭住址字段设置惟一约束，在性别字段设置默认值为“男”。

createdatabase学生档案

use学生档案

createtable人事

（学号char（6）notnull,

姓名varchar（10）,

性别char

（2）,

电话号码char（20）,

家庭住址char（30）,

createtable成绩

学号char（6）notnull,

选修课程名称varchar（20）,

成绩int,

在“人事”表的学号字段设置主键约束：

altertable人事

addconstraintpk_rs

primarykey（学号）

在“成绩”表的学号字段设置外键约束：

altertable成绩

addconstraintfk_cj

foreignkey（学号）references成绩（学号）

在成绩字段设置大于等于0小于等于100的检查约束

addconstraintck_rs

check（成绩<

=100and成绩>

在家庭住址字段设置惟一约束：

addconstraintuq_rs

unique（家庭住址）

在性别字段设置默认值为“男”：

addconstraintdt_rs

default'

男'

for性别

在SQLServer中讨论参照完整性，请体会如下含义：

●插入从表中记录时：

有限制和忽略两种方式；

●删除主表中的记录时：

有限制、忽略和级联三种方式；

●修改主表中的主键时：

有限制、忽略和级联三种方式。

试根据自身学习，分别说明其含义。

实验十三存储过程

⏹熟练掌握存储过程的创建、调用和删除。

对于CPXS数据库，创建如下存储过程：

1、无参存储过程

编写一无参存储过程用于查询每个客户购买产品的情况（包括客户编号、产品编号、客户名称、产品名称、价格、购买日期、购买数量），然后调用该存储过程。

createprocedurecustomer123

selectCPXSB.客户编号,CPXSB.产品编号,客户名称,产品名称,价格,销售日期,数量fromCPXSB

joinCPonCPXSB.产品编号=CP.产品编号

joinXSSonCPXSB.客户编号=XSS.客户编号

executecustomer123

2、带有参数的存储过程

编写一加密存储过程，查询指定客户购买产品的情况。

并调用该存储过程查询客户编号为“000002”的客户购买情况。

createprocedurespecialcus123456@cusnumchar（20）

leftjoinCPonCPXSB.产品编号=CP.产品编号

leftjoinXSSonCPXSB.客户编号=XSS.客户编号

whereCPXSB.客户编号=@cusnum

execspecialcus123456'

000002'

3、带有通配符参数的存储过程

编写一存储过程，查询指定产品的销售情况。

如果没有提供参数，则查询产品名称中包含有“冰箱”的产品销售情况。

createprocedurecp_xsqk123

@产品名称char（10）='

冰箱'

select*fromCPXSB

4、带有OUTPUT参数的存储过程

编写一存储过程，查询指定客户在指定时间段内购买指定产品的数量，存储过程中使用了输入和输出参数。

并调用该存储过程查询名称为“家电市场”的客户在2004年购买“洗衣机”的数量。

createprocedurespecial

@cusnumchar（20）,@timedatetime,

@producechar（16）,@numintoutput

As

select@num=数量fromCPXSB

leftjoinCPonCPXSB.产品编号=CP.产品编号

whereCPXSB.产品编号=@time

and销售日期=@time

and产品名称=@produce

查询指定客户在指定时间段内购买指定产品的数量，存储过程中使用了输入和输出参数。

Executespecial'

100001'

'

2004-03-1800:

00:

00.000'

试说明存储过程的分类和特点。

SQLsever支持5种形式的存储过程：

1系统存储过程

2本地存储过程

3临时存储过程

4远程存储过程

5扩展存储过程

存储过程的特点是：

1：

存储过程在服务器端运行，实行速度快；

2：

存储过程执行一次后，其执行规划就驻留在高速缓冲存储器中，在以后的操作中，只需要从高速缓冲存储器中调用已经编好的二进制代码执行即可，提高了系统的性能；

3：

确保数据库的安全，使用存储过程可以完成所有数据库的操作，并通过编程方式控制上述操作对数据库信息的访问权限；

4：

自动完成需要与执行的任务。

存储过程可以在系统启动时自动执行，完成一些需要与执行的任务，而不必在系统启动后在进行手工操作，大大方便了用户的使用

实验十四触发器

⏹熟练掌握后触发器和替代触发器的区别；

⏹熟练掌握后触发器和替代触发器的创建。

对于CPXS数据库，完成如下各项：

1、编写存储过程，对产品销售表进行插入操作，并通过触发器保证插入时，产品编号与CP表中的对应字段一致，销售商编号与销售商表中对应字段一致。

其中触发器分别用后触发器和替代触发器实现。

2、在CPXSB上创建一后触发器，若对产品编号列和客户编号列修改，则给出提示信息，并取消修改操作，用两种方法实现。

createtriggertri_remind

onCPXSB

forupdate

if（columns_updated（）&

3）>

begin

raiserror（'

正在对产品编号列和客户编号列修改'

19,1）

rollbacktransaction

end

试说明后触发器和替代触发器的区别。

Aafter型触发器和insteadof型触发器的区别：

对于after型触发器在一种操作上可以建立多个触发器；

而insteadof型的触发器在一种操作上只能创建一个触发器；

实验十五系统安全管理

⏹理解SQLServer中的安全管理机制。

⏹理解帐户、用户、服务器角色和数据库角色的含义。

⏹熟练掌握WindowsNT登录帐户和SQLServer登录帐户的建立与删除。

⏹熟练掌握添加与删除服务器角色成员。

⏹熟练掌握添加和删除一个登录帐户为某个数据库的用户。

⏹熟练掌握添加和删除数据库角色。

⏹熟练掌握添加和删除数据库角色成员。

⏹熟练掌握用户、角色的授权、收回权限和拒绝权限。

1、创建WindowsNT登录帐户

在SQLServer中添加一个帐户名为“WANG”的WindowsNT登录帐户。

第一步：

创建windows2003的用户”WANG”,截图如下所示：

第二步：

将windowsNT用户添加到sqlsever中：

executesp_grantlogin'

MAWEIFEI\WANG'

2、创建SQLServer登录帐户

在SQLServer中添加一个帐户名为“LIU”，密码为“123456”的SQLServer登录帐户。

第一步:

首先应将SQLsever的认证模式设置为混合模式，其截图如下所示：

通过命令创建SQLsever的登录账户：

executesp_addlogin'

LIU'

123456'

运行后进行更新，且结果如下所示：

3、添加服务器角色成员

将“WANG”这个WindowsNT登录帐户添加到系统管理员服务器角色中。

executesp_addsrvrolemember'

sysadmin'

4、添加一个登录帐户为某个数据库的用户

将“LIU”这个SQLServer登录帐户添加为CPXS数据库中一个用户，名称也为“LIU”。

executesp_grantdbaccess'

5、添加数据库角色

在CPXS数据库中添加一个名为“ROLE”的角色。

executesp_addrole'

ROLE'

6、添加数据库角色成员

将CPXS数据库中名为“LIU”这个用户添加为“ROLE”角色成员。

executesp_addrolemember'

7、用户、角色的授权

授予“LIU”用户和“ROLE”角色对CPXSB表的查询权限和数量列的修改权限。

grantselect,updateonCPXSBtoLIU,ROLE

8、收回用户、角色的

收回“LIU”用户的所有权限。

并查看是否能打开CPXSB表。

revokeselect,updateonCPXSBfromLIU

9、拒绝用户、角色的权限

重新授予“LIU”用户对CPXSB表的查询权限和数量列的修改权限。

再拒绝该用户的所有权限。

并查看是否能打开CPXSB表，并通过此实例阐述收回权限与拒绝权限的区别。

grantselect,updateonCPXSBtoLIU

授予用户LIU的select和update的权限后，其结果如下所示：

denyselect,updateonCPXSBtoLIU

拒绝后的结果如下所示：

取消用户LIU的权限的结