Audit for DB programming

• Download source code - 5.7 KB

Introduction

Given that you are a DBA, you would need to know how other programmers are modifying the DB tables and Stored Procedures for the purpose of audit or just curiosity. Though there could be a lot of solutions for IT audit on the enterprise level, you may need a simpler one. Here it is.

I summarized three approaches using Event notifications, Traces, and Triggers in the sample. I think Event notifications are preferable in that they are not in the transaction scope and are processed asynchronously. But the restriction is that the database should be MS SQL Server 2005 and above. (Sample file: just open and execute the extracted .sql files.)

Background

It would be better if you are familiar with SQL-Trace, Triggers, DDL, and Event notifications.

Purpose

The purpose is simple: whenever I CREATE/ALTER/DROP a table or Stored Procedure, the DB records all of the queries.

Suppose that I send the query below:

CREATE TABLE TestTable (a int)
go

I expect to see the CREATE TABLE log like in the following image:

Using the code

Set up:

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:

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

The data from the XMLlog column looks like this:

<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>

This XML is a SOAP message broker service transfer. This message can be sent to a WMI service so that a WMI consumer software can see the log remotely. Execute the '~_more_practical.sql' file to test a little more practical version.

Comparison

Event notifications, triggers, and traces, all respond to DDL events, so it is possible to easily record DB system modifications. But triggers are processed synchronously, within the scope of the transactions that cause them to fire. Unlike DDL triggers, event notifications can be used inside a database application to respond to events without using any resources defined by the immediate transaction. Additionally, a trace creates a trace file (.trc) that needs to be processed to show the log properly. So I preferred event notifications in my situation. But event notifications also need other consideration. There could be a performance overhead associated with creating the XML-formatted event data and sending the event notification, and event notifications cannot be rolled back. (For more information: Event Notification vs. Trigger vs. Trace)

History

• First version.