在SQL Server 2005中创建DDL表格来审计DDL触发活动


在SQL Server 2005之前,我们只能定义Data Manipulation Language (DML)触发器。当执行DML语句时,如UPDATE或DALETE,这些触发器就会启动。在SQL Server 2005发布之后,现在可以定义Data Definition Language (DDL)触发器了。当执行DDL语句,如CREATE TABLE和ALTER VIEW,这些类型的触发器就会启动,并且这使得使用DDL触发器来审计SQL Server中的DDL事件更加容易了。

  其中一个可以用来审计DDL事件的方法是先创建一个表来存储相关的事件数据,然后创建一个DDL触发器来记录事件。在本文中,我将阐述每个步骤并举例说明每个概念。对于所举的例子,我将在传输SQL Server 2005的AdventureWorks示例数据库中创建一个审计表和DDL触发器。注意,本文的前提条件就是假定你已经非常熟悉Transact-SQL和DDL概念。

  创建DDL审计表

  审计表存储每次指定类型的DDL事件发生时产生的事件相关信息。比如,如果从数据库中删除一个视图,那么就会产生一个DROP_VIEW事件。我们可以使用DDL触发器来捕捉事件信息并存储在表中。

  每个审计表都必须至少包含一个XML字段来存储事件相关数据。后面将介绍SQL Server是如何生成XML格式的数据的。当然,这个表也必须包含一个主键字段。下面的语句在AdventureWorks数据库中创建了一个基本的审计表:


Code highlighting produced by Actipro CodeHighlighter (freeware)
http://www.CodeHighlighter.com/

-->CREATE TABLE dbo.EventLog
(EventID INT PRIMARY KEY IDENTITY,
EventInstance XML NOT NULL)
GO
  注意,我已经将EventID列作为主键,以及EventInstance,并介绍了字段是如何保存与每个事件相关的XML数据的。每次DDL事件生成时,就会有一行添加到表格中。然后,我们可以检索EventInstance字段的内容来查看指定事件的信息。

  创建DDL触发器

  在创建了审计表之后,我们必须定义DDL触发器。下面的CREATE TRIGGER语句定义了一个触发器,当每次在AdventureWorks数据库中发生DDL事件时,它将在EventLog表中插入事件相关的数据:


Code highlighting produced by Actipro CodeHighlighter (freeware)
http://www.CodeHighlighter.com/

-->CREATE TRIGGER LogEvents
ON DATABASE
AFTER DDL_DATABASE_LEVEL_EVENTS
AS
INSERT INTO dbo.EventLog (EventInstance)
VALUES (EVENTDATA())
  让我们详细地来看看每一行的代码以便更好的理解它。CREATE TRIGGER子句只是简单地标识新的触发器的名称,也就是LogEvents。第二行——ON DATEBASE——表示触发器将在数据库级上创建。当然,也可以在服务器上创建触发器,它会在服务器发生DDL事件时启动,这时就应该使用ON ALL SERVER选项。然而,对于这个例子,我们关注的仅仅是与AdventureWorks数据库相关的DDL事件。

  下一行代码——AFTER DDL_DATABASE_LEVEL_EVENTS——是一个AFTER子句。这个子句的第一部分是一个AFTER关键字,它表示只有在相关的操作(特别是子句的第二部分)已经成功的执行了才启动触发器。如果不用AFTER,可以用FOR关键字,这就意味着一发生DDL事件就启动事件。因此,我更喜欢在它们成功的运行了之后才记录这些操作。

  AFTER子句的下一个部分指定了事件类型或组。因此你必须规定哪件DDL事件能够引发触发器启动。因为我想要审计数据库级的所有DDL事件,所以我选择了DDL_DATABASE_LEVEL_EVENTS选项(一个事件组)。但是,你可以选择其它的组或某个事件类型。如果指定一个以上的事件组或类型,那么需要使用逗号分隔选项。更多关于每个事件类型和组的详细信息,可以阅读Microsoft SQL Server Books Online上的“CREATE TRIGGER (Transact-SQL)”专栏。

  当我指定了事件组之后,我使用了一个AS关键字,然后是INSERT语句。这个语句在每次触发器启动时插入一行数据到EventLog表中。我通过调用EVENDATE()系统方法获得了EventInstance字段的值,它可以检索事件相关的数据(以XML格式)。当事件发生时,该方法提供所需的值。

  这就是设置基本的审计解决方法的所有代码。现在,让我们来验证一下结果。
测试审计方法

  最佳的测试方法就是运行一对针对AdventureWorks数据库的DDL语句。下面的语句创建了Person、Contact2表,接着删除了表:


Code highlighting produced by Actipro CodeHighlighter (freeware)
http://www.CodeHighlighter.com/

-->SELECT FirstName, LastName, EmailAddress
INTO Person.Contact2
FROM Person.Contact
GO
DROP TABLE Person.Contact2

  两个语句都应该在AdventureWorks上生成DDL事件,接着启动LogEvents触发器。为了验证触发器是否正确地记录了这两个事件,只需简单的运行下面的SELECT语句:


Code highlighting produced by Actipro CodeHighlighter (freeware)
http://www.CodeHighlighter.com/

-->SELECT * FROM dbo.EventLog

  语句必须返回两行。在每一行,EventInstance字段都必须包含一个与具体事件相关的XML。如果查看第一行的XML,那么结果应该是这样的:

  注意,事件数据包含了事件类型、创建的对象以及用于创建表格的命令和其它的信息。如果只想从EventInstance字段中检索特定的信息,可以使用XQuery表达式来访问单个元素值。比如,下面的SELECT语句使用了XML value()方法来检索事件类型、模式名和对象名:


Code highlighting produced by Actipro CodeHighlighter (freeware)
http://www.CodeHighlighter.com/

-->SELECT EventID,
EventInstance.value('(//EventType)[1]',  
'nvarchar(30)') AS EventType,
EventInstance.value('concat((//SchemaName)[1],  
".", (//ObjectName)[1])', 'nvarchar(60)')  
AS ObjectName
FROM dbo.EventLog

  正如你所看到的,我先通过指定XML数据类型定义的EventInstance字段调用了value()方法。我可以以这种方法调用任意的XML方法。value()方法包含两个参数。第一个定义了我所要检索的XML元素。第二个定义了数据类型。第一个参数必须包含在括号中,并且后面加上[1],因为value()方法总是返回标量值,。即使指定的元素是在XML(如本例子)中是唯一的也必须指定[1]。当运行这个SELECT语句时,会得到下面的结果:

EventID

EventType

ObjectName

1

CREATE_TABLE

Person.Contact2

2

DROP_TABLE

Person.Contact2

                    (2 row(s) affected)

  结果显示的是CREATE_TABLE and DROP_TABLE事件类型,这正是我所运行的DDL语句所应该产生的结果。因为SQL Server现在支持Data Definition Language (DDL)触发器,因此可以非常容易地审计由这些语句生成的事件。正如我所阐述的,一个简单的审计事件的方法就是在SQL Server上创建一个审计表,并定义一个触发器。然而,我们也可以使用DDL触发器来执行一些操作,而不是在审计表中插入一行。比如,当一个特别的DDL事件发生时,发送一封邮件到一个指定的接受者。当创建一个DDL触发器时,我们可以定义一个或多个Transact-SQL语句来完成一些特定的操作,这样就可以通过创建触发器来执行各种不同的任务



相关阅读:
ASP视频教程:建立后台管理的框架页面
ASP正则判断取出HTML的图片地址的函数
用ISA Server 2000发布内部网络的IIS FTP服务器
让广告代码不影响网页站打开速度
PHP 生成的XML以FLASH获取为乱码终极解决
动态指定任意类型的ObjectDataSource对象的查询参数
Sql Server datetime问题
针对浏览器隐藏CSS
Ubuntu Linux系统下Apache SSL配置方法
一个PHP的String类代码
Discuz!7.0标签聚合功能详解
html+css实现数据图表的展示效果
jQuery Selectors(选择器)的使用(九、表单对象属性篇)
Linux下安装Mplayer只有声音而没有图像的解决
快速导航

Copyright © 2016 phpStudy |