are lots of articles about how to do auditing, but there are few discussions
about how to use the auditing results in a real time environment, for example,
an employee salary table has a delete operation during non-working hours, a
responsible DBA should be notified for this unusual operation asap.
article, I propose a new security mechanism based on the auditing trace of SQL
Server 2000. There are two distinct advantages to this security model:
monitored events can be reported to the concerned parties as soon as
responding actions can be centered in a same place.
are four major components in building this security model
auditing trace is established to monitor any concerned events
job is setup to export the trace file to a trace table every fixed period
alert is created with notification functions set
trigger on the trace table will fire out notifications (email, pager) to
concerned people by raising a low severity error.
process flow is as follows:
auditing trace will record any concerned events to a trace file, and the job
will from time to time import the trace file to a table, on which there is a
trigger. The trigger will detect whether there is any specific events occurring
to specific targets, if yes, raise error, which trigger the alert to be fired
and the necessary notification will be issued as a result.
an auditing trace
Start the profiler, create a trace that can monitor your interested
events, as shown in Fig 1.
Fig 1: Create a trace for your interested events
is better to filter out any events generated by SQL Alert engine as shown in Fig
Filter out events generated by SQL Agent
Go to menu File -> Script trace -> SQL Server 2000, you will save
an sql file. It is similar to the following script:
Created by: SQL Profiler
Date: 02/28/2004 11:35:51 AM
procedure usp_MyTrace as /* this line is added by myself to create a stored proc
Create a Queue
@maxfilesize = 5
Please replace the text InsertFileNameHere, with an appropriate
filename prefixed by a path, e.g., c:\MyFolder\MyTrace. The .trc extension
will be appended to the filename automatically. If you are writing from
remote server to local drive, please use UNC path and make sure server has
write access to your network share
@rc = sp_trace_create @TraceID output, 0, N'InsertFileNameHere', @maxfilesize,
note: change “InsertFileNameHere” to your local file with full path, e.g.
(@rc != 0) goto error
Client side File and Table cannot be scripted
Set the events
@on = 1
sp_trace_setevent @TraceID, 12, 1, @on
sp_trace_setevent @TraceID, 12, 6, @on
this SQL Profiler created file and add a line of code (create
proc usp_MyTrace as ) in the beginning of the code, so
you will get a stored procedure.
the procedure is created, generate the stored procedure in master
database if you want to use sp_procoption to make it run automatically every
time SQL Server is started.
Exec sp_procoption ‘usp_MyTrace’, ‘startup’, true
Otherwise, you can generate the stored proc in your own database and use a job
to schedule this procedure execution.
Create a table with similar columns in the trace, plus an identity column (can
be omitted, but I prefer to have it for clarity reason only)
RowID int identity primary key, TextData nvarchar(4096), Reads int, Writes int,
StartTime datetime, LoginName sysname, NTUserName sysname)
job contains only one sql statement
into TraceTale (textdata, Reads, Writes, StartTime, LoginName, NTUserName)
left(textdata, 4096), Reads, Writes, StartTime, LoginName, NTUserName
from ::fn_trace_gettable('c:\test.trc', default)
my case, I created an alert with an error number 50005 and made the notification
responding to this alert. The notification is to send email to an operator. (You
can page the operator too if your system supports the function.)
my case, I try to detect whether there is a read statement to a specific table
trigger tr_Read_ on xx
if update (TextData)
if exists (select * from inserted
raiserror (50005, 10, 1)
paper, a new auditing solution is proposed in an environment that quick response
is critical. The solution design is based on a combined use of SQL Server’s
various components, such as trace file, trigger, job and alerts. It has an
advantage when used in monitoring multiple database environments so a DBA can
have a centered place to monitor all those sensitive operations. It can be
considered as a mimic implementation of “select” trigger.