Click here to Skip to main content
Click here to Skip to main content
Go to top

Audit for DB programming

, 24 Mar 2008
Rate this:
Please Sign up or sign in to vote.
Do you want to know what your DB programmers are doing?

AuditDBprogramming

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:

TheAim.jpg

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.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

jangtimjang
Team Leader An Online Market
Korea (Republic Of) Korea (Republic Of)
Software engineer having worked 8 years.
The main skill would be C++, ATL, COM
But for the past three years I have been digging on Web service related skill - .Net MS-SQL etc in current company.
My other interest is business, playing traditional music, dealing with people and evolutionary biology.

Comments and Discussions

 
QuestionAudit for DB programming Pinmemberclivej26-Jul-10 12:05 
QuestionSQL 2000 PinmemberNoman Aftab26-Mar-08 19:03 
GeneralRe: SQL 2000 Pinmemberjangtimjang27-Mar-08 15:35 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Mobile
Web02 | 2.8.140916.1 | Last Updated 25 Mar 2008
Article Copyright 2008 by jangtimjang
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid