触发器存储过程数据库安全.docx
- 文档编号:12674910
- 上传时间:2023-04-21
- 格式:DOCX
- 页数:17
- 大小:30.79KB
触发器存储过程数据库安全.docx
《触发器存储过程数据库安全.docx》由会员分享,可在线阅读,更多相关《触发器存储过程数据库安全.docx(17页珍藏版)》请在冰豆网上搜索。
触发器存储过程数据库安全
基于SQLSERVER触发器技术的实现
时间:
2006-6-12上午07:
51:
28 来源:
DZY学习资料库 阅读946次
在数据库管理系统中,如何保证数据库中的数据完整性是一项重要的课题。
数据完整性是指存储在数据库的数据的一致性。
主要体现在以下几个方面:
实体完整性(Entity Integrity)、域完整性(Domain Integrity)、参照完整性(Referential Integrity)和用户的自定义完整性(User define Integrity)。
目前,已有多种方法来解决这个问题。
从最基本的数据类型,到多种形式的约束条件,虽然都提出了数据完整性的解决方案,但由于这些方法较为简单,不能解决比较复杂的数据完整性问题。
而触发器(Trigger)作为一种高级的技术,可以轻松地解决任何有关保证数据完整性的问题。
一、在SQL SERVER环境中使用触发器
1触发器的工作原理
触发器是一种特殊类型的存储过程,它与表紧密联系在一起,在对表进行插入、删除和更新时,如该表(也称触发器表)有相应操作类型的触发器,则触发器便会自动触发执行。
触发器分为INSERT触发器、DELETE触发器和UPDATE触发器3类。
当向触发器表中插入数据时,INSERT触发器将触发执行,新的记录会增加到触发器表和inseted表中;当删除触发器表中的数据时,DELETE触发器将触发执行,被删除的记录会存放到deleted表中;当更新触发器表中的数据时,相当于插入一条新记录和删除一条旧记录,此时UPDATE触发器将触发执行,表中原有的记录存放到deleted表中,修改后的记录插入到inserted表中。
其中inserted表和deleted表是两个逻辑表,由系统来维护,不允许用户直接对这两个表进行修改。
它们存放于内存中,不存放在数据库中。
这两个表的结构总是与被该触发器作用的表的结构相同。
触发器工作完成后,与该触发器相关的这两个表也会被删除。
deleted表用于存储SQL语言中DELETE和UPDATE语句所影响的行的复本。
在执行DELETE或 UPDATE语句时,行从触发器表中删除,并传输到 deleted 表中。
deleted 表和触发器表通常没有相同的行;inserted 表用于存储 SQL语言中INSERT 和 UPDATE 语句所影响的行的副本。
在一个插入或更新事务处理中,新建行被同时添加到 inserted 表和触发器表中。
inserted 表中的行是触发器表中新行的副本。
2触发器的实现步骤
在笔者开发的教材管理系统中,建立了一个教材数据库JCSJK,其中有教材表JCB和教材进出明细表MXB,需要在对MXB进行插入、删除和修改时,动态地修改JCB中对应教材的库存数量。
下面举例说明触发器的实现步骤。
为减少篇幅,对表结构作了简化处理。
以下操作在Microsoft SQL SERVER环境中完成。
(1)建立教材表JCB,并定义主键
CREATE TABLE [dbo].[JCB] (
[教材代码] [char] (10) NOT NULL ,
[教材名] [char] (30) NOT NULL ,
[价格] [decimal](18, 2) NOT NULL ,
[库存量] [int] NULL ,
[出版社] [char] (20) NULL
) ON [PRIMARY]ALTER TABLE [dbo].[JCB] WITH NOCHECK ADD
CONSTRAINT [PK-JCB] PRIMARY KEY CLUSTERED
([教材代码]
) ON [PRIMARY]
(2)建立教材进出明细表MXB,并定义主键和外?
br> CREATE TABLE [dbo].[MXB] (
[教材代码] [char] (10) NOT NULL ,
[日期] [datetime] NOT NULL ,
[教材进] [int] NULL ,
[教材出] [int] NULL ,
[备注] [char] (40) NULL
) ON [PRIMARY]
ALTER TABLE [dbo].[MXB] WITH NOCHECK ADD
CONSTRAINT [PK-MXB] PRIMARY KEY CLUSTERED
([教材代码],
[日期]
) ON [PRIMARY]
ALTER TABLE [dbo].[MXB] ADD
CONSTRAINT [FK-MXB-JCB] FOREIGN KEY
([教材代码]) REFERENCES [dbo].[JCB] (
[教材代码]) ON DELETE CASCADE ON UPDATE CASCADE
图1显示了JCB和MXB两个表的关系。
(3)在MXB上建立INSERT触发器
CREATE TRIGGER MXB-INSE ON [dbo].[MXB]
FOR INSERT
ASUPDATE JCB SET 库存量=库存量+
(SELECT 教材进-教材出 FROM INSERTED)
FROM JCB,INSERTEDWHERE JCB.教材代码=INSERTED.教材代码
(4)在MXB上建立DELETE触发器
CREATE TRIGGER MXB-DELE ON [dbo].[MXB]
FOR DELETE
AS
UPDATE JCB SET 库存量=库存量-
(SELECT 教材进-教材出 FROM DELETED)
FROM JCB,DELETEDWHERE JCB.教材代码=DELETED.教材代码〖HT〗
(5)在MXB上建立UPDATE触发器
CREATE TRIGGER MXB-UPDA ON [dbo].[MXB]
FOR UPDATE
AS
BEGINUPDATE JCB SET 库存量=库存量-
(SELECT 教材进-教材出 FROM DELETED)
FROM JCB,DELETED
WHERE JCB.教材代码=DELETED.教材代码
UPDATE JCB SET 库存量=库存量+
(SELECT 教材进-教材出 FROM INSERTED)
FROM JCB,INSERTEDWHERE JCB.教材代码=INSERTED.教材代码
END
通过以上步骤,设置了MXB表的3类触发器,当用户对MXB表进行插入、删除和修改时,将根据MXB中教材进出的情况动态地修改JCB中对应教材的库存量。
由于在触发器中,涉及到的inserted表和deleted表均存放在内存中,因此,触发器的执行速度较快。
3设计触发器的考虑
在写触发器代码时需要考虑的一个重要问题就是,引发触发器的语句可以是一个影响单行的语句,也可以是一个影响多行的语句。
这在 UPDATE 和DELETE 触发器中很常见,因为这些语句经常作用于多行。
而这在 INSERT 触发器中就比较少见,因为基本的 INSERT 语句只添加一行。
然而,由于 INSERT 触发器可由 INSERT INTO (table_name) SELECT 语句激发,所以,插入许多行可能导致单个的触发器调用。
上面讨论的涉及MXB的3类触发器都是针对影响单行的语句。
因此,有必要考虑影响多行的语句,这里对MXB的INSERT触发器进行讨论。
(1)可处理多行的MXB上的INSERT触发器
如果要进行多行插入,上面示例中的触发器可能就不能正确处理,因为 UPDATE 语句赋值表达式右边的表达式只能是一个值,而不能是一个值列表。
因此,该触发器的作用就是获取 inserted 表中任意一行的值,并将其添加到JCB表中特定教材代码值的已有库存量值上。
如果某个教材代码值在inserted 表中出现了多次,则可能无法得到预期的结果。
为了正确地更新JCB表,触发器就必须适应inserted表中出现多行的可能性。
这可以通过 SUM 函数实现,它为 inserted 表中每个教材代码计算教材进出的总计。
SUM 函数存放于相关子查询中(SELECT 语句在括号内)。
该子查询为 inserted 表中与JCB表的教材代码匹配或相关的每个教材代码返回一个单一值。
CREATE TRIGGER MXB-INSE ON [dbo].[MXB]
FOR INSERT
AS
UPDATE JCB SET 库存量=库存量+
(SELECT SUM(教材进-教材出) FROM INSERTED
WHERE JCB.教材代码=INSERTED.教材代码)
WHERE JCB.教材代码 IN
(SELECT 教材代码 FROM INSERTED)
该触发器对单行插入同样适用,不过,使用该触发器时,WHERE 子句中所使用的相关子查询和 IN 运算符需要额外处理,而这对于单行插入来说是不必要的。
(2)可区分单行和多行插入的MXB上的INSERT触发器可以通过系统函数@@ROWCOUNT以区分单行插入和多行插入,以使触发器针对不同行数使用最优方法。
CREATE TRIGGER MXB_INSE ON [dbo].[MXB]
FOR INSERT
AS
IF @@ROWCOUNT=1
BEGIN
UPDATE JCB SET 库存量=库存量+
(SELECT 教材进-教材出 FROM INSERTED)
FROM JCB,INSERTED
WHERE JCB.教材代码=INSERTED.教材代码
END
ELSE
BEGIN
UPDATE JCB SET 库存量=库存量+
(SELECT SUM(教材进-教材出) FROM INSERTED
WHERE JCB.教材代码=INSERTED.教材代码)
WHERE JCB.教材代码 IN
(SELECT 教材代码 FROM INSERTED)
END
二、结论
触发器应用于支持企业级商业解决方案时,是一个功能十分强大的工具。
它可以用于实现业务规则,可以检查事务,可以在同一表上创建多个触发器来分离代码的功能。
另外,通过使用触发器收集的信息,可以提高数据库的性能,可以用来维护那些使用外键所不能实现的复杂参数完整性。
因此,对于维护数据表之间一致性,保持数据的相关完整性的情况,触发器应作为首先考虑的技术。
储过程中返回结果
从存储过程中返回结果有三种方式:
1、 返回结果集
这是客户端应用程序返回结果的最通用的方法。
结果集是通过使用SELECT语句选择数据产生的。
结果集可以从永久表、临时表或局部变量中产生。
将结果返回到另一个存储过程不是一种有效的方法。
存储过程不能访问另一个存储过程建立的结果集。
例如从永久表中返回结果集:
USE pubs
GO
CREATE PROCEDURE ap_CreateResultFromPermtable
AS
SELECT au_iname FROM authors
GO
例如从局部变量中创建结果集:
USE pubs
GO
CREATE PROCEDURE ap_CreateResultFromVariable
AS
DECLARE @au_iname char(20)
SELECT @au_iname = au_iname FROM authors
WHERE au_id = ‘172-32-1176’
SELECT @au_id
GO
2、 设置OUTPUT参数的值
输出参数经常用来从存储过程中检索出结果。
如果某个参数在传输到存储过程中时被定义成OUTPUT,则对该参数的任何修改在退出存储之后仍然有效。
例如:
USE pubs
GO
CREATE PROCEDURE ap_SetOutputVar @count integer OUTPUT
AS
SELECT @count = count(*) FROM authors
GO
从输出参数中检索出值:
USE pubs
GO
CREATE PROCEDURE ap_GetOutputVar
AS
DECLARE @num integer
EXECUTE ap_SetOutputVar @num OUTPUT
PRINT “the count is”+convert(char,@num)
GO
• 将游标使用成OUTPUT参数。
游标可以使用OUTPUT(输出)参数,但不能使用成输入参数。
也就是说,游标可以作为结果返回,但却不能传输到过程中去。
当游标被用作参数时,需要限定其为OUTPUT和VARYING。
VARYING关键字指出该结果集要用来支持输出参数。
这样就提供了将结果集返回到调用过程的能力。
例如:
USE pubs
GO
CREATE PROCEDURE GetTitleCount @count_cursor CURSOR VARYING OUTPUT
AS
SET @count_cursor = CURSOR
FOR
SELECT au_id,count(*)
FROM titleauthors
GROUP BY au_id
OPEN @count_cursor
GO
3、 通过RETURN参数返回状态
这是一种从存储过程返回错误码的方法。
存储过程总是返回一个状态值,用户也可以使用RETURN语句返回自己的状态。
例如:
USE pubs
GO
CREATE PROCEDURE ap_SetReturnStatus
AS
DECLARE @count integer
SELECT @count = count(*) FROM authors
IF @count = 0
RETURN
(1)
ELSE
RETURN (0)
GO
例如检索出返回的状态:
USE pubs
GO
CREATE PROCEDURE ap_GetReturnStatus
AS
DECLARE @status integer
EXECUTE @status = ap_SetReturnStatus
IF @status = 1
PRINT “No rows found”
ELSE
PRINT “successful”
GO
在存储过程中进行错误处理
如同其它程序一样,在存储过程中进行错误处理是非常重要的。
系统变更@@error在执行每一个Transact SQL语句之后都会得到一个值。
对于成功的执行,@@error的值为0,如果出现错误,则@@error中将包含错误信息。
@@error系统变量对存储过程的错误处理是非常重要的。
注意:
为了防止错误,@@error所能设置的值在sysmessages表的“error”中反映了出来。
在存储过程中的错误有两种类型:
1、 数据库相关的错误
这些错误是由数据库的不一致性引起的,系统使用非0的@@error值表示特定的数据库问题。
在Transact SQL执行之后,可以通过@@error获得所出现的错误。
如果发现@@error不为0,则必须采取必要的行动,大多数情况下,存储将不再继续进行处理而返回。
下面的示例展示了典型的获取数据库错误的方法。
该过程将错误代码放置到输出变量中,这样,调用程序就能够访问到。
USE pubs
GO
CREATE PROCEDURE ap_TrapDatabaseError @return_code integer OUTPUT
AS
UPDATE authors SET au_iname = “Jackson”
WHERE au_iname = “Smith”
IF @@error <> 0
BEGIN
SELECT @return_code = @@error
RETURN
END
ELSE
@return_code = 0
GO
2、 业务逻辑错误
这些错误是由于违反了业务规则而引起的。
要获取这些错误,首先需要定义业务规则,基于这些规则,需要在存储过程中增加必要的错误检测代码。
人们经常使用RAISERROR语句通报这些错误。
RAISERROR提供了返回用户定义错误及将@@error变量设置成用户定义错误号的能力。
错误消息可以被动态地建立,或者基于错误号从“sysmessages”表中检索到。
一旦出现了错误,错误就会以一种服务器错误消息的方式返回到客户机。
下面是RAISERROR命令的语法:
RAISERROR (msg_id | msg_str, severity, state
[, argument ][,…n]))
[WITH options]
Msg_id指明用户定义消息的id,该消息存储在“sysmessages”系统表中。
Msg_str用于动态创建消息的消息字符串。
这与C语言中的“printf”非常相似。
Severity定义用户赋值的错误消息严重程度。
State是从1到127的任意整数值,它表示错误的调用状态信息。
负数的state值将缺省为1。
OPTIONS指明错误的定制选项。
OPTIONS的有效值如下:
1) LOG。
将错误记录到服务器错误日志和NT事件日志中。
该选项需要消息带有从19到25的严重程度。
而只有系统管理员才能发出这种消息。
2) NOWAIT。
将消息立即发送到客户端服务器。
3) SETERROR。
不管其严重级别如何,将@@error的值设置为msg_id或5000。
远程过程调用
SQL Server提供了调用驻留在不同服务器上的存储过程的能力。
调用这样的存储过程称谓远程存储过程调用。
为了使得调用能从一个SQL Server转移到另一个服务器,两个服务器应该相互定义成对方的有效远程服务器。
设置远程服务器的配置:
• 扩展某个服务器的组。
• 右击该服务器并点击“Properties”。
• 设置选项“Allow other SQL Servers to connect remotely to this SQL server via RPC”。
• 设置“Query time out”选项的值,该值指定从一个查询处理返回所能等待的秒数。
缺省值为0,表示允许无限的等待时间。
• 设置完成配置选项之后,点击“OK”。
• 重新启动服务器之后,修改将会生效。
• 在另一台远程服务器上重复相同的步骤。
调用远程存储过程需要指明服务器的名称,后带数据库的名称和拥有者的名称。
下面是在不同的服务器(Server2)上调用一个存储过程的示例。
Exec server2.pubs.dbo.myproc
豆豆的后话:
这里只是粗浅的介绍了SQL Server常用的知识,对象也是基于SQL Server数据库编写应用程序的编程人员,而非数据库管理者。
但对于应用程序编程者,了解数据库的管理也是非常有用的。
建议在以后的时间自行去了解数据库的管理,这对于优化程序也是相当有用的。
查看锁信息
create table #t(req_spid int,obj_name sysname)
declare @s nvarchar(4000)
@rid int,@dbname sysname,@id int,@objname sysname
declare tb cursor for
select distinct req_spid,dbname=db_name(rsc_dbid),rsc_objid
from master..syslockinfo where rsc_type in(4,5)
open tb
fetch next from tb into @rid,@dbname,@id
while @@fetch_status=0
begin
set @objname=name from ['+@dbname+']..sysobjects where id=@id'
exec sp_executesql @s,N'@objname sysname out,@id int',@objname out,@id
insert into #t values(@rid,@objname)
fetch next from tb into @rid,@dbname,@id
end
close tb
deallocate tb
select 进程id=a.req_spid
数据库=db_name(rsc_dbid)
类型=case rsc_type when 1 then 'NULL 资源(未使用)' when 2 then '数据库'
when 3 then '文件'
when 4 then '索引'
when 5 then '表'
when 6 then '页'
when 7 then '键'
when 8 then '扩展盘区'
when 9 then 'RID(行 ID)'
when 10 then '应用程序'
end
对象id=rsc_objid
对象名=b.obj_name
rsc_indid
from master..syslockinfo a left join #t b on a.req_spid=b.req_spid
go
drop table #t
Transact-SQL 参考
sysobjects
在数据库内创建的每个对象(约束、默认值、日志、规则、存储过程等)在表中占一行。
只有在 tempdb 内,每个临时对象才在该表中占一行。
列名 数据类型 描述
name sysname 对象名。
Id int 对象标识号。
xtype char
(2) 对象类型。
可以是下列对象类型中的一种:
C = CHECK 约束
D = 默认值或 DEFAULT 约束 F = FOREIGN KEY 约束
L = 日志
FN = 标量函数
IF = 内嵌表函数
P = 存储过程
PK = PRIMARY KEY 约束(类型是 K)
RF = 复制筛选存储过程
S = 系统表
TF = 表函数
TR = 触发器
U = 用户表
UQ = UNIQUE 约束(类型是 K)
V = 视图
X = 扩展存储过程
uid smallint 所有者对象的用户 ID。
info smallint 保留。
仅限内部使用。
status int 保留。
仅限内部使用。
base_schema_
ver int 保留。
仅限内部使用。
replinfo int 保留。
供复制使用。
parent_obj int 父对象的对象标识号(例如,对于触发器或约束,该标识号为表 ID)。
crdate datetime 对象的创建日期。
ftcatid smallint 为全文索引注册的所有用户表的全文目录标识符,对于没有注册的所有用户表则为 0。
schema_ver in
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- 触发器 存储 过程 数据库 安全