触发器与存储过程互调数据库

触发器与存储过程互调数据库（精选2篇）

触发器与存储过程互调数据库 篇1

在流水线生产作业过程中, 设备的意外停机损失是巨大的。为了避免设备故障, 而引起生产过程中断, 企业通常对设备进行预防性维修。预防维修是基于工作量或工作时间的维修, 通过对工作量或工作时间进行统计, 在设备故障发生前, 对零部件进行检查、修理或更换[1]。由于现代设备结构日趋复杂, 零配件很多, 每种零配件的寿命周期长短不一, 人工统计工作量很大。信息技术正改变着企业传统的工作和管理模式, 使人们逐渐从简单、繁杂的手工作业中解脱出来[2]。我们利用SQL Server 2005数据库对设备零配件的更换时间进行统计, 通过数据库的存储过程来实现零配件更换周期的计算, 节省了大量时间, 提高了计算结果的准确性和及时性, 实现了零配件更换的预警提示。通过在设备管理信息系统中的应用, 取得了较好的效果。

一、零配件更换周期的计算模型

零配件更换件周期, 是指某一台设备的某一配件相连两次更换的时间间隔, 通常以“天”为计量单位, 也可以按工作量计算。根据设备的重要程度和零配件的特点, 零配件更换周期通常可以按最短时间间隔、最长时间间隔和平均时间间隔来确定。

1.1最短时间间隔。

对于特别重要的设备, 或零配件失效导致的后果很严重的情况下, 通常采用最短时间间隔来作为零配件的更换周期, 以指导设备维修。其计算模型如下:

Tmin=min{T1, T2, T3, …, Tn}, 其中:Tmin为最短时间间隔, Tn为相连两次之间的时间间隔天数。

Tn= (ti+1-ti) , 其中:i, n为自然数, ti为第i次换件日期, ti+1为第i+1次换件日期。

1.2最长时间间隔。

在实际工作中, 较少会采用以最长时间间隔作为零配件更换周期, 仅在停机损失非常低的情况下采用。设Tmax为最长时间间隔, 则:

Tmax=max{T1, T2, T3, …, Tn}, 其中:Tn为相连两次之间的时间间隔天数。Tn= (ti+1-ti) , 其中:i, n为自然数, ti为第i次换件日期, ti+1为第i+1次换件日期。

1.3平均时间间隔。

在以零配件平均时间间隔作为零配件更换周期的模型中, 以相连两次之间的时间间隔的平均天数 (T) 来表示。

其中:i, n为自然数, 且n≥2, ti为第i次换件日期, ti+1为第i+1次换件日期。

在实践过程中, 较多地采用以平均时间间隔天数作为零配件更换周期。随着时间推移, 积累的数据越多, 计算的结果比较符合实际情况。

二、数据库建模

现代设备具有大型化、高速化、自动化和集中化等特点, 设备结构越来越复杂, 设备所包含的零配件数量成千上万, 设备零配件更换记录很多, 人工统计和计算的工作量很大, 借助信息技术进行数据的管理是一种必然的选择。由于SQL Server是一个全面的、集成的、端到端的数据解决方案, 它为组织中的用户提供了一个更安全可靠和更高效的平台。采用数据库进行数据管理, 可提高数据吞吐量和安全性, 大大提高管理效率, 降低编码的复杂性和编码数量[3]。因此, 我们采用SQL Server 2005数据库对零配件更换记录等数据进行管理。

2.1数据库E-R模型。

我们采用Microsoft SQL Server2005数据库管理系统建立数据库。数据库有三个实体:设备、配件和换件记录。设备的主要属性有:资产编号、设备名称、型号等, 资产编号为主键, 唯一标识这个实体;配件的主要属性有:配件编号、配件名称、型号等, 配件编号为主键;换件记录的主要属性有:记录编号、配件编号、更换日期、更换人员等, 记录编号唯一标识这个实体。系统的ER图 (Entity Relationship Diagram) 如图1所示。

2.2数据库详细设计。

在E-R数据模型的基础上, 进行数据库表设计。每个实体对应一张数据库表, 三个实体分别对应的数据库表是:设备 (DEVICE) 、配件 (ACCESSORIES) 、换件记录 (ACCESSORIES_RECORD) , 数据库表的字段、数据类型、缺省值、注释等详见表1、表2、表3所示。

三、存储过程实现

存储过程是数据库的对象之一, 是封装了可重用代码的、存储在服务器上的程序模块或例程, 具有执行效率高、安全和通信流量小等优点[4]。故我们利用数据库存储过程来实现零配件更换周期的计算。下面仅就零配件平均时间间隔的计算进行介绍。

存储过程设计的总体思路:从ACCESSORIES表查询设备编号 (Device_Id) 和配件编号 (Accessories_Id) , 以 (设备编号“Device_Id”, 配件编号“Accessories_Id”) 为参数, 从ACCESSORIES_RECORD表中查询某一设备 (编号为:Device_Id) 的某一配件 (编号为:Accessories_Id) 的历次更换时间, 通过历次更换时间来计算零配件更换的平均间隔时间 (即:零配件的更换周期) 。

首先, 设计带参数的存储过程, 以 (Device_Id, Accessories_Id) 为参数, 计算设备编号为“Device_Id”和配件编号为“Accessories_Id”的零配件平均时间间隔。其计算流程如下:

零配件更换的时间间隔可以用DATEDIFF (dd, @Date1, @Date2) 函数来计算。假设第一次更换时间为@Replace_Date1, 第二次更换时间为@Replace_Date2, 则第二次更换日期与第一次更换日期之间的间隔天数为:@Cycle= (SELECT DATEDIFF (dd, @Replace_Date1, @Replace_Date2) ) , 平均时间时隔为:@ReplaceCycle=@Cycle/ (@Row_Count-1) 。带参数的存储过程详细设计如下表4所示:

然后, 设计另外一个存储过程, 从ACCESSORIES表查询设备编号 (Device_Id) 和配件编号 (Accessories_Id) , 以 (Device_Id, Accessories_Id) 为参数, 调用前面设计的存储过程, 计算所有设备的所有更换零件的换件周期。其详细设计如下表5所示:

四、效果

我们在设计设备管理信息系统的过程中, 利用Microsoft SQL Server2005数据库管理系统的作业管理, 来定期执行零配件更换周期的存储过程。我们将计算作业安排在服务器空闲时间执行, 每天执行一次存储过程, 以提高整个系统的性能。通过信息技术的利用, 缩短了零配件更换周期计算的时间, 减轻了手工计算零配件更换周期的工作量, 为设备预防性维修工作提供了参考依据, 提高了设备管理效率。

参考文献

[1]李葆文.设备管理新思维模式[M].北京:机械工业出版社, 2003:2-50页.

[2]徐仕良.计算机公共基础[M].北京:清华大学出版社, 1998:2-10页.

[3]蒙祖强.SQLS erver 2005应用开发大全[M].清华大学出版社, 2007:41-49页.

触发器与存储过程互调数据库 篇2

随着计算机网络的发展和现代社会信息量的爆炸式增长，网络数据库应用系统已经在政府、公司、医院、学校等地得到广泛应用。网络数据库应用系统具有良好的灵活性、高效性和开放性，同时也对安全性有更高的要求。系统安全性涉及到多个层面：操作系统级、数据库管理系统级和应用系统级。本文讨论的核心问题是如何保证应用系统级的安全性。

1 网络数据库应用系统安全性分析

网络数据库应用系统允许多个客户端通过网络访问系统，客户端成为了入侵者的首选之地。常见入侵方法有：

(1)破解客户端软件，从多角度获得系统的使用权限。既可以读取数据库的连接字符串，得到数据库服务器的地址、登录数据库服务器的用户名、密码，直接登录数据库服务器;也可以直接获得用户登录应用系统的用户名、密码;还可以跳过用户密码验证直接使用系统。

(2)使用SQL注入攻击读取数据库服务器用户表中的用户名和密码[1]。

(3)截取网络报文，分析报文结构，从而得到用户名和密码。

(4)利用系统漏洞在客户端植入木马，使用木马盗取密码。

可以看出系统设计中用户权限设置、权限验证、密码管理和密码传输等各方面存在的缺陷和漏洞都有可能被入侵者利用。因此，在数据库应用系统设计时应尽可能遵循以下原则来提高系统安全性。

(1)连接数据库的用户不可使用超级用户，权限应尽可能低;

(2)用户每次处理数据时服务器端都需要做权限审核;

(3)数据库中存储密码需用密文,且加密算法不可逆;

(4)网络中传输密码不可用明文,且尽可能减少传输密码的次数。

2 基于存储过程的安全体系

本文以员工管理系统为例，阐述如何应用存储过程来构造数据库应用系统的安全性，和系统安全性相关的密码保护问题。系统安全体系框架如图一所示：

2.1 存储过程

存储过程(Stored Procedure)是一组为了完成特定功能的SQL语句集，经编译后存储在数据库中。存储过程不仅能够实现较快的执行速度，减少网络流量，还可被作为一种安全机制来充分利用。存储过程具有访问当前数据库的所有权限，用户不需要有表和视图的直接访问权限，只要有存储过程的执行权限(EXEC权限)就可以通过存储过程操纵相应的数据信息。例如，下面的存储过程就可以查询到员工信息表的内容。

CREATE PROCEDURE select_employee_info AS

SELECT*FROM employee_info

用户只要被授予存储过程的执行权限，就可以通过“EXEC select_employee_info”查看员工信息。使用存储过程的优点在于不必对表和视图的访问权限进行分配，而是使用权限映射表记录用户权限，并在存储过程中加入权限审核来判断用户是否有相应的操作权限，以实现应用级的权限管理。客户端不需要使用高权限用户连接数据库，只需要提供一个具有执行存储过程权限的普通用户即可。服务器端通过存储过程可以保护一些关键性的数据，如员工的个人信息、用户登录的密码和权限，这部分数据是系统的核心数据。而数据库中有些表和视图是完全可以公开的，如存放公司有哪些部门的部门表等，属于非核心数据，无需通过存储过程保护，可以直接提供读取权限。

2.2 密码管理

密码管理是安全体系中的关键部分，涉及到密码的存储形式和传输形式。采用明文保存密码，或者是可逆的加密算法保存密码，都会给入侵者提供机会。最佳的方法是使用不可逆的加密算法，在客户端通过对密码加密之后再传送，在服务器端判断密码密文与数据库中保存的是否一致。服务器端对于用户密码的验证不需要通过解密的手段去获得明文，只需去校验密文是否一致即可。

信息摘要加密MD5[2]是一种不可逆的加密算法，通过HASH的方法将字符串加密为128位二进制。从数学的角度，不存在一种算法可以将一个MD5串还原为原来的字符串，但是现在有很多暴力破解的方法。根据数据字典可以较快的破解一些简单密码(如英文单词、数字等)，而用户往往容易使用简单密码，这样就容易被暴力破解。为了解决这一问题，可以采用SALT MD5的算法，在原始字符串上加上一些字符串(即SALT部分)，即使用户设置的密码很简单，加上SALT之后就变成一个复杂的字符串，增大了MD5值被暴力破解的难度。另一种方法可以采用多次取MD5值的方法，一般两次加密的强度就很大了。第一次生成MD5值后，第二次对第一次生成的MD5值再做一次MD5加密。

2.3 临时连接码

由于用户每次处理数据时服务器端都需要做权限审核，所以每次处理数据的请求都需要提供用户身份标识。用户名和密码如果频繁地在网络上传递(即使是使用密文传递)，也容易被分析出数据结构，直接通过密码密文构造出想要的数据处理请求。因此，在网络上应尽量减少密码的传输。为了实现这个目的，我们提出了使用临时连接码来代替密码，作为验证用户的唯一标识。

客户端使用临时连接码访问服务器步骤如下：

(1)客户端登录时通过用户名(UID)和密码(PWD)调用Login存储过程，获得临时连接码(CID);

(2)进行数据操作时使用CID做为身份标记调用相应存储过程，存储过程通过检验临时连接码判断用户权限并执行相应数据操作;

(3)客户端每隔三分钟连接一次服务器，服务器端将三分钟内未刷新的连接字符串置空。

使用连接码可以加强系统的网络安全性，临时连接码在连接系统时临时生成，每次生成的连接码均不相同，且每隔一段时间要重新生成一次连接码，即使连接码被解析出来也失效了。临时连接码作为用户的唯一标识，不允许有相同值，同时用户每次登录系统生成的临时连接码也应该是不同的。因此，可以使用MD5算法整合用户名、密码和登录时间(LOGINTIME)生成临时连接码，即CID=MD5(UID+PWD+LOGI-NTIIME)。

2.4 用户权限管理

对于数据库应用系统而言，用户权限管理应该是指应用级的用户权限。为了管理用户权限，我们使用三张表：USER、OBJECT、PERMISSION。USER表存放用户的基本信息，包括用户名、密码、登录时间、临时连接码等;OBJECT表存放安全对象的基本信息，如员工信息、员工考勤等均属于应用级对象的信息;PERMISSION表存储User和Object之间的权限映射。在实际应用中，每一个角色往往会对操纵多个表，需要对各个数据表的访问权限进行设置。

3 结束语

人们在大量关注系统底层的安全性之外，往往容易忽略上层的安全性，为了简化设计将安全性控制完全放在客户端。本文从加强客户端安全的角度出发，提出了一套较为完整的基于存储过程的数据库应用系统的安全体系。

摘要：文章从应用系统级角度分析网络数据库应用系统的安全问题,提出一套较为完善的基于存储过程的安全体系。

关键词：数据库应用系统,安全性,存储过程

参考文献

[1]张卓.SQL注入攻击技术及防范措施研究[M].上海:上海交通大学,2007.