数据库编程审计

• 下载源代码 - 5.7 KB

引言

作为DBA，您需要了解其他程序员如何修改数据库表和存储过程，以便进行审计或仅仅出于好奇。虽然企业级IT审计可能有很多解决方案，但您可能需要一个更简单的方案。这里有一个。

我在示例中总结了三种方法，使用事件通知、跟踪和触发器。我认为事件通知是首选，因为它们不在事务范围内，并且是异步处理的。但限制是数据库必须是MS SQL Server 2005及以上版本。（示例文件：直接打开并执行提取的.sql文件。）

背景

如果您熟悉SQL跟踪、触发器、DDL和事件通知，将会更好。

目的

目的很简单：每当我CREATE/ALTER/DROP表或存储过程时，数据库都会记录所有查询。

假设我发送以下查询

CREATE TABLE TestTable (a int)
go

我期望看到如下图像所示的CREATE TABLE日志

使用代码

设置

create database hagendaaz
GO
use hagendaaz
GO
--//0) Enable Service Broker if it's disabled.
ALTER DATABASE hagendaaz SET  EnABLE_BROKER    
GO
--//1) Create a queue to receive messages. 
CREATE QUEUE NotifyQueue with STATUS=ON, RETENTION = OFF;  
GO
--//2) Create a service on the queue that references the event notifications contract.
CREATE SERVICE NotifyService ON QUEUE NotifyQueue 
  ([http://schemas.microsoft.com/SQL/Notifications/PostEventNotification]);
GO
--//3) Create a route on the service to define the address 
--      to which Service Broker sends messages for the service.
CREATE ROUTE NotifyRoute WITH SERVICE_NAME = 'NotifyService', ADDRESS = 'LOCAL';
GO
--//4) Create the event notification 
CREATE EVENT NOTIFICATION Notify_Table_Proc_Modifications
ON DATABASE  
WITH FAN_IN --// send message only once
FOR CREATE_TABLE, ALTER_TABLE, DROP_TABLE, CREATE_PROCEDURE, 
       ALTER_PROCEDURE, DROP_PROCEDURE   --// DDL event of your interest
TO SERVICE 'NotifyService', --// broker service name 
'current database'; --// 'broker_instance_specifier' GUID
GO

测试

--// Test
CREATE TABLE TestTable (a int)
go 
--// The Result 
select convert(xml,message_body)as XMLlog, *  from dbo.NotifyQueue
go

XMLlog列中的数据如下所示

<EVENT_INSTANCE>
  <EventType>CREATE_TABLE</EventType>
  <PostTime>2008-03-25T15:22:43</PostTime>
  <LoginName>dev</LoginName>
  <UserName>dbo</UserName>
...
  <TSQLCommand>
    <SetOptions ANSI_NULLS="ON" ANSI_NULL_DEFAULT="ON" 
       ANSI_PADDING="ON" QUOTED_IDENTIFIER="ON" ENCRYPTED="FALSE" />
    <CommandText>CREATE TABLE TestTable (a int)</CommandText>
  </TSQLCommand>
</EVENT_INSTANCE>

这个XML是一个SOAP消息代理服务传输。可以将此消息发送到WMI服务，以便WMI消费者软件可以远程查看日志。执行'~_more_practical.sql'文件以测试一个更实用的版本。

比较

事件通知、触发器和跟踪都响应DDL事件，因此可以轻松记录数据库系统修改。但触发器是同步处理的，在触发它们的事务范围内。与DDL触发器不同，事件通知可以在数据库应用程序中使用，以响应事件，而无需使用立即事务定义的任何资源。此外，跟踪会创建一个跟踪文件（.trc），需要对其进行处理才能正确显示日志。因此，在我的情况下，我更喜欢事件通知。但事件通知也需要考虑其他因素。创建XML格式的事件数据和发送事件通知可能会产生性能开销，并且事件通知无法回滚。（更多信息：事件通知与触发器与跟踪）

历史

• First version.