Audit for DB programming

• Download AuditDBprogramming - 5.71 KB

Introduction

Given that you are a DBA and 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 in enterprise level, you may need simpler one. Here it is.
I summarized three approaches using EVENT NOTIFICATION, TRACE and TRIGGER in the sample. I think EVENT NOTIFICATION preferable in that event notification is not in transaction scope and processed asynchronously. But the restriction is that the DB should MS-SQL 2005 and aboves.
(Sample: just open and execute extracted .sql files. )

Background

It would be better if you know SQL-TRACE, TRIGGER, DDL and EVENT NOTIFICATION

Purpose

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

Suppose that I send a 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 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 WMI service that WMI consumer software can see the log remotely. Execute the '~_more_practical.sql' file to test a little more pratical version.

Comparison

Event notification, trigger and trace, all respond to DDL events so it is possible to 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 trace creates trace file(.trc) that needs to be processed to show the log properly. So I prefered event notification in my situation.
But event notification also needs other consideration. There could be performance overhead associated with creating the XML-formatted event data and sending the event notification and event notification cannot be rolled back. (For more information : Comparison between Event Notification vs Trigger vs Trace )

History

1. Created Article