Search This Blog

Wednesday, June 9, 2010

Data Auditing using Service Broker (Complete Solution)

Data Auditing via Service Broker


Last year, I was assigned task to build our own Data Audit
mechanism in SQL Server 2005. To accomplish this task, there were number of
articles and scripts on the internet. I planned to get the best out of those
articles and scripts and created my own Trigger + Service Broker Based
solution.


Trigger based audit solution? Sounds like a lot of
performance issues. But I used service broker in the triggers to get the things
done.


Core Logic

The core logic is;

1.     Create New Audit Database
2.     Activate Service Broker on New Audit Database and your own database
         3.     Build tables for Audit. It can be all tables as well.
4.     Ruin script to add trigger on the fly on the specified tables.
5.     Enjoy Audit reports from Audit Database.


I will discuss each point in detail.

1.  Create New Audit Database

This database will hold the New and old values of the columns changed on the
production database. Create new database as AuditDatabase.
It will have the following Tables;

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO


CREATE TABLE [dbo].[tblAud](
[tID] [int] IDENTITY(1,1) NOT NULL,
[xmlQuery] [xml] NULL,
[Upd_Det] [bit],
[tDate] [datetime] NULL CONSTRAINT [DF_tblAud_tDate] DEFAULT (getdate()),
[ColU] [varbinary](128) NULL,
CONSTRAINT
[PK_tblAud] PRIMARY KEY CLUSTERED
(
[tID] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING
OFF


tblAud will hold the XML of the changed record in xmlQuery. Column upd_Det bit column will tell us if the XML is parsed or not. Column tDate will tell us the date
when the record was initialized.


SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


CREATE TABLE [dbo].[tblAudDetail](
[AudDetID] [int] IDENTITY(1,1) NOT NULL,
[tid] [int] NULL,
[dbName] [nvarchar](1000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[TABLENAME] [nvarchar](1000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[FieldName] [nvarchar](1000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Priorval] [nvarchar](1000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[CurrVal] [nvarchar](1000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
CONSTRAINT
[PK_tblAudDetail] PRIMARY KEY CLUSTERED
(
[AudDetID] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

tblAudDetail will hold the details of changed information from XML in tblAud.
You can see from the schema of the table that it has columns dbName. It means we can use one Audit Database for multiple Production databases.

tblErrorlog will hold the details of errors in messages so that the XML can be debugged.

USE
[AuditDatabase]


GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


CREATE TABLE [dbo].[tblErrorLog](
[ErrorNumber] [int] NOT NULL,
[ErrorSeverity] [int] NULL,
[ErrorState] [int] NULL,
[ErrorLine] [int] NULL,
[ErrorProcedure] [nvarchar](126) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ErrorMessage] [nvarchar](4000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ErrorTime] [datetime] NULL CONSTRAINT [DF__tblErrorL__Error__4865BE2A] DEFAULT (getdate()),
[DbUserName] [sysname] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[sysUserName] [sysname] COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]


Audit Database will also have the three stored procedures.
The following stored procedure will be activated by the service broker queue
when any message from the production database is sent;

USE
[AuditDatabase]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[uspInsertAuditRec]
AS
BEGIN
SET NOCOUNT ON;

Begin Try
Declare @ConversationHandle as uniqueidentifier
Declare @MessageBody as varbinary(max)
Declare @MessageXML as XML
Declare @MessageType as sysname
Declare @pBQuery nvarchar(max)

Begin TRANSACTION
Print 'Started Receiving';

WAITFOR(
RECEIVE top (1)
@MessageType = message_type_name,
@ConversationHandle = conversation_handle,
@MessageBody = message_body
FROM BLOB_AdtQueue_Remote1
), TIMEOUT 1000;


Select @pBQuery = convert(nvarchar(max),@MessageBody)
Select @MessageXML = cast(@pBQuery as XML)
Declare @sID as INT
Declare @DBName as nvarchar(100)
Declare @TableName as nvarchar(100)


IF @MessageType = 'BLOB'
BEGIN
   select @DBName = x.header.value('(//SourceDb)[1]', 'nvarchar(50)') ,
   @TableName = x.header.value('(//SourceTable)[1]', 'nvarchar(50)')

   FROM @MessageXML.nodes('//AuditMsg') AS x(header)


   Insert into tblAud (xmlQuery,upd_det) values (cast(@pBQuery as XML),0)
   Select @sID = Scope_identity()
END
ELSE
BEGIN
   INSERT INTO tblAud (xmlQuery,upd_det) values    ('<a>aaa<a/>','<a>aaa<a/>')


END
END CONVERSATION @ConversationHandle
COMMIT
End Try
Begin Catch
   Rollback
   print 'In Catch...'
   INSERT INTO DBO.tblErrorLog
   SELECT
   ERROR_NUMBER() AS ErrorNumber,
   ERROR_SEVERITY() AS ErrorSeverity,
   ERROR_STATE() AS ErrorState,
   ERROR_LINE() AS ErrorLine,
   ERROR_PROCEDURE() AS ErrorProcedure,
   ERROR_MESSAGE() AS ErrorMessage,GETDATE(),
   CONVERT(sysname ,USER_NAME())AS DBUSERNAME,
   CONVERT(SYSNAME,SUSER_SNAME()) AS SYSUSERNAME


   Insert into dbo.tblErrorXML
      Select @MessageBody
End Catch
END


The following stored procedures will parse the XML and will mark the record in tblAud;


USE
[AuditDatabase]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

--
=============================================
-- Author: Atif Sheikh
-- Create date: 24-May-2010
-- Description: Update Detail
--
=============================================

CREATE PROCEDURE [dbo].[uspUpdateDetail]
AS
BEGIN
SET NOCOUNT ON;

Declare @tid int
Declare @DbName varchar(100)
Declare @TableName varchar(100)
Declare d1 Cursor for Select tid from tblAud where upd_det = 0
Open d1
Fetch Next from d1 into @tid
while @@Fetch_Status = 0
begin
   PRINT @tid

   select @DbName = xmlQuery.value('(//SourceDb)[1]', 'varchar(50)') ,
   @TableName = xmlQuery.value('(//SourceTable)[1]', 'varchar(50)')
   FROM tblAud where tid = @tid


   if @DbName is Not Null and @TableName is Not Null
      exec uspUpdateAuditDetailFromAuditTableID @tid,@DbName,@TableName


   Fetch Next from d1 into @tid
end
Deallocate d1
END




USE
[AuditDatabase]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
--
=============================================
-- Author: Atif-ullah Sheikh
-- Create date: 24-May-2008
-- Description: Insert into tblAudDetail
--
=============================================

CREATE PROCEDURE [dbo].[uspUpdateAuditDetailFromAuditTableID]
(
   @pID int,
   @pDBName nvarchar(100),
   @pTableName nvarchar(100)
)
AS
BEGIN 

Begin Try
Begin Tran
Declare @ColName nvarchar(max)
Declare @Chk1 nvarchar(max)
Declare @Chk2 nvarchar(max)
SET NOCOUNT ON;
Declare @sSql varchar(max)
Declare @SQL1 nvarchar(max)
Declare @SQL nvarchar(max)
Declare @pDef nvarchar(max)
Declare @sSql2 varchar(max)
Declare @SelectCols varchar(max)
Declare @SelectCols2 varchar(max)
Declare @ObjId int


Set @sql1 = N'Select @ObjId = Object_ID
from ' + @pDBName + '.sys.objects where name = ''' + @pTableName + ''''

Set @pDef = N'@ObjId varchar(100) OUTPUT'
Exec sp_executesql @sql1 ,@pDef,@ObjId=@ObjId OUTPUT

Set @sql = 'Declare col2 Cursor for
Select name from ' + @pDBName + '.sys.columns where
Object_Id = '+ cast(@ObjID as varchar(10))+ ' and system_type_id not
in (34,35,99,241,165,173)'

print(@sql)
Exec (@sql)
Open col2

Declare @XMLvar XML

Select @XMLvar = xmlQuery from tblAud where tid = @pID

Set @sql = ''

Declare @SQL2 varchar(max)
Declare @SQL3 varchar(max)
Declare @SQL4 varchar(max)

Set @sql2 = ''
Set @sql3 = ''
Set @sql4 = ''

Declare @cname varchar(100)

Fetch next From Col2 into @cname
Set @sql3 = '(Order By x.header.value(''@' + @cname + ''',''varchar(100)'')) '
Set @sql = 'Declare @XMLvar XML
Select @XMLvar = xmlQuery from tblAud where tid = ' + cast(@pID as varchar(10))+ ' Select * into
##ctbl from ( Select Row_Number() Over '

while @@Fetch_Status = 0
begin
   if @Sql2 <> '' Set @Sql2 = @Sql2 + ','

   Set @Sql2 = @Sql2 + 'x.header.value(''@' + @cname + ''',
   ''varchar(50)'') ' + @cname + '_d'

   if @Sql4 <> ''
       Set @Sql4 = @Sql4 + ','

   Set @Sql4 = @Sql4 + 'x.header.value(''@' + @cname + ''',
''varchar(50)'') ' + @cname + '_i'


   Fetch next From Col2 into @cname
end

Set @sql = @sql + @sql3 + ' d_rid,' + @sql2
Set @sql = @sql + ' FROM @XMLvar.nodes(''//ChangedData/t'') AS x(header)) xx
Left
OUter join (
select Row_number() over ' + @sql3 + ' i_rid,' + @sql4 + '
FROM @XMLvar.nodes(''//NewRec/t'') AS x(header))yy
on yy.i_rid = xx.d_rid '

print(@sql)
Deallocate Col2
Exec (@sql)

Declare @dval varchar(100)
Declare @ival varchar(100)
Declare @rid int

Set @sql = 'Declare col2 Cursor SCROLL
for Select name from ' + @pDBName + '.sys.columns where
Object_Id = '+ cast(@ObjID as varchar(10))+ ' and system_type_id not
in (34,35,99,241,165,173)'

Exec (@sql)
Open col2
Declare @vRID int
Declare Col3 Cursor for Select Case when i_RID is Null then d_RID else i_RID end from ##ctbl

Open Col3
Fetch Next from col3 into @rid

while @@Fetch_Status = 0
begin
   Fetch next From Col2 into @cname
   While @@Fetch_Status = 0
   begin
      Set @sql1 = ''
      Set @sql1 = @sql1 + N' Select @dval1 = '+ @cname + '_d ,@ival1 =
      '+ @cname +'_i from ##ctbl where case when i_RID is Null then d_rid else
      i_rid end = ' + cast(@rid as varchar(10))


      Set @pDef = N'@dval1 varchar(100) OUTPUT, @ival1 varchar(100) OUTPUT'
      Set @vRID = 0


      Select @vRID = i_rid from ##ctbl where case when i_RID is Null then d_rid else i_rid end = @rid


       Exec sp_executesql @sql1 ,@pDef,@dval1=@dval OUTPUT,@ival1=@ival OUTPUT


      if (IsNull(@dval,'') <> IsNull(@ival,'')) or @cname Like '%ID'
      begin
          Insert into
              tblAudDetail (tid,DBName,TableName,FieldName,PriorVal,CurrVal)
          Values
             (@pID,@pDBName,@pTableName,@cname,case when IsNull(@vRID,0) <> 0 then @dval else @ival end,case when IsNull(@vRID,0) <> 0 then @ival
else @dval end)

      end
      Fetch next From Col2 into @cname
   end
   Fetch FIRST From Col2 into @cname
   Fetch PRIOR From Col2 into @cname
   Fetch Next from col3 into @rid
end

Deallocate Col2
Deallocate Col3
Drop table ##ctbl
update tblAud set upd_det = 1 where tid = @pID
Commit Tran
End Try
begin Catch
Rollback
INSERT INTO dbo.tblErrorLog
SELECT
ERROR_NUMBER() AS ErrorNumber,
ERROR_SEVERITY() AS ErrorSeverity,
ERROR_STATE() AS ErrorState,
ERROR_LINE() AS ErrorLine,
ERROR_PROCEDURE() AS ErrorProcedure,
ERROR_MESSAGE() AS ErrorMessage,GETDATE(),
CONVERT(sysname ,USER_NAME())AS DBUSERNAME,
CONVERT(SYSNAME,SUSER_SNAME()) AS SYSUSERNAME
end catch
END


2.    Activate Service Broker on New Audit Database and your own database

Next step is to activate service broker on both databases.

ALTER DATABASE MyProductionDB
SET ENABLE_BROKER
WITH ROLLBACK IMMEDIATE
GO

ALTER DATABASE AuditDatabase
SET ENABLE_BROKER
WITH ROLLBACK IMMEDIATE
GO


Run the following script to create service broker objects in MyProductionDB. First the master key is created. According to MSDN, “The database master key is a symmetric key used to protect the private keys of certificates and asymmetric keys that are present in the database.”

use
MyProductionDB

drop master key

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'onteuhoeu'
GO
A message type defines the name of a message and the validation that Service Broker performs on messages that have that name. Both sides of a conversation must define the same message types.

CREATE MESSAGE TYPE BLOB VALIDATION = NONE
GO

A contract defines the message types that are used in a Service Broker conversation and also determines which side of the conversation can send messages of that type. Each conversation follows a contract. The initiating service specifies the contract for the conversation when the conversation starts. The target service specifies the contracts that the target service accepts conversations for.

CREATE CONTRACT BLOB_Contract
(BLOB SENT BY ANY)
GO

Queues store messages. When a message arrives for a service, Service Broker puts the message on the queue associated with the service.

CREATE QUEUE BLOB_Queue_Init
GO

A Service Broker service is a name for a specific task or set of tasks. Service Broker uses the name of the service to route messages, deliver messages to the correct queue within a database, and enforce the contract for a conversation.

CREATE SERVICE BLOB_Service_Init
ON QUEUE BLOB_Queue_Init
(BLOB_Contract)

Run the following script to create service broker objects in AuditDatabase. Same types of objects as above are created on AuditDatabase. But with different names;
Use
AuditDatabase
GO

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'onteuhoeu'
GO

CREATE MESSAGE TYPE BLOB
VALIDATION = NONE
GO

create CONTRACT BLOB_Contract
(BLOB SENT BY ANY)
GO

CREATE QUEUE BLOB_AdtQueue_Remote1
GO
CREATE SERVICE BLOB_AdtSrv_Remote1
ON QUEUE BLOB_AdtQueue_Remote1
(BLOB_Contract)
GO

We need to alter the queue BLOB_AdtQueue_Remote1 to set its status to ON and associate procedure to be executed when a message is inserted in a queue. MAX_QUEUE_READERS specifies the maximum number of instances of the activation stored procedure that the queue starts simultaneously. The value of max_readers must be a number between 0 and 32767.

ALTER QUEUE BLOB_AdtQueue_Remote1
WITH ACTIVATION
(
   STATUS = ON,
   PROCEDURE_NAME = dbo.uspInsertAuditRec ,
   MAX_QUEUE_READERS = 1,
   EXECUTE AS OWNER
)
GO

The security principal that owns the initiating service must have SEND permission on the target service. So we need to execute,

GRANT SEND ON SERVICE::BLOB_Service_Remote1 TO [Public];
GO


3.    Build tables for Audit. It can be all tables as well.

Next we will create stored procedures to apply Triggers on the tables we need to Audit.

Run the following script on Production Database to create stored procedure to add trigger on the fly;

Use
MyProductionDB
go
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
-- =============================================
-- Author: Atif Sheikh
-- Create date: 28-May-2010
-- Description: Create Audit Trigger
--
=============================================

CREATE PROCEDURE [dbo].[uspCreateTriggerToAudit]
   @TABLENAME varchar(100)
AS
BEGIN
SET NOCOUNT ON;


Declare @SCHEMA varchar(100)
Declare @colnames varchar(max)
Declare @objid int
Declare @sql varchar(max)


Select @objid = object_id from sys.objects where name = @tableName
Select @TABLENAME = table_name, @Schema
= Table_Schema from information_schema.tables where table_name = @tableName


Select @colnames = COALESCE(@colnames+', ','')+ '['+ sys.columns.name +']'
from sys.columns where object_id = @objid

and system_type_id not in (34,35,99,241,165,173)




SET @SQL='CREATE TRIGGER ['+@TABLENAME+'_AuditTrigger]
ON ['+@SCHEMA+'].['+@TABLENAME+']
AFTER UPDATE, INSERT, DELETE
AS
BEGIN

SET NOCOUNT ON;


DECLARE @auditBody varchar(max)
DECLARE @auditBody1 varchar(max)
DECLARE @AppUserID varchar(max)
DECLARE @Objectid bigint
DECLARE @DMLType CHAR(1)
Set @auditBody1 = ''''
Set @auditBody = ''''

Set @AppUserID = ''''


Select @Objectid = parent_object_id
from sys.objects where name = ''' + @tableName + '_AuditTrigger''
if Exists(Select column_id from
sys.columns where object_id = @Objectid and name = ''UpdatedBy'')
begin
--Select @AppUserID = UpdatedBy from inserted as ii
   Set @AppUserID = ''''
end




IF NOT EXISTS (SELECT * FROM inserted)
BEGIN
SELECT @auditBody = (select '+ @colnames +' FROM deleted AS t FOR XML AUTO)


Select @DMLType = ''D''
END


-- after update or insert statement


ELSE


BEGIN


IF EXISTS (SELECT * FROM
deleted)


begin


SELECT @auditBody = (select '+ @colnames +' FROM deleted AS t FOR XML
AUTO)


SELECT @auditBody1 = (select '+ @colnames +' FROM Inserted AS t FOR XML
AUTO)


SELECT @DMLType = ''U''


end


ELSE


begin


SELECT @auditBody = (select '+ @colnames +' FROM inserted AS t FOR XML
AUTO)


SELECT @DMLType = ''I''


end


END


SELECT @auditBody =


''<AuditMsg>


<SourceDb>'' +
DB_NAME() + ''</SourceDb>


<SourceTable>' + @tableName + '</SourceTable>


<UserId>'' +
SUSER_SNAME() + ''</UserId>


<AppUserId>'' +
@AppUserID + ''</AppUserId>


<DMLType>'' +
@DMLType + ''</DMLType>


<ChangedData>'' +
CAST(@auditBody AS NVARCHAR(MAX)) + ''</ChangedData>''


if @DMLType = ''U''


SELECT @auditBody =
@auditBody + ''<NewRec>'' + @auditBody1 + ''</NewRec>''





SELECT @auditBody = @auditBody +
''</AuditMsg>''





if @auditBody <> ''''


begin


DECLARE @h UNIQUEIDENTIFIER


Declare @CXml varchar(max)




Set @CXml = @auditBody





BEGIN DIALOG CONVERSATION @h


FROM SERVICE BLOB_Service_Init


TO SERVICE
''BLOB_AdtSrv_Remote1''


ON CONTRACT BLOB_Contract


WITH ENCRYPTION=OFF ;




SEND ON CONVERSATION @h


MESSAGE TYPE BLOB
(CONVERT(VARBINARY(max), @CXml))


end


END'


print @SQL


set ANSI_NULLS
ON


set QUOTED_IDENTIFIER
ON

exec (@SQL)


END



The following stored procedure on production database takes
the comma separated list of object_id of tables we
want to audit on Production database.


Use MyProductionDB
go


CREATE PROCEDURE [dbo].[uspGetAuditTables]
@pObjectID varchar(max) ,
@pAlias varchar(max) = NULL
AS
BEGIN
SET NOCOUNT ON;
Declare @val varchar(1000)
Declare @val2 varchar(1000)
Declare @tab as Table (ObjectID bigint,
Alias nvarchar(1000))

Insert into @tab
   Select * from tblAuditTables

delete from tblAuditTables
insert into tblAuditTables (ObjectID,Alias)
   select a.value,b.value from dbo.fnSplit(@pObjectID,',') a, dbo.fnSplit(@pAlias,',') b where a.tid = b.tid


Declare C1 Cursor For select name from dbo.fnSplit(@pObjectID,',') inner join sys.objects on sys.objects.Object_id = value
Open C1
Fetch Next from C1 into @val
while @@Fetch_Status = 0
begin
   print(@val)
   if not Exists(Select object_id from sys.objects where name = rtrim(ltrim(@val)) + '_AuditTrigger' and Type = 'TR')

begin
   Set @val = rtrim(ltrim(@val))

   exec uspCreateTriggerToAudit @val

end

Fetch Next from C1 into @val
end

Close C1
Deallocate C1

Declare C1 Cursor For
Select sys.objects.name,sys.schemas.name from sys.objects inner join sys.schemas on sys.schemas.schema_id = sys.objects.schema_id where Object_id in
(Select objectid from @tab
Except
Select objectid from tblAuditTables)

Declare @sSql varchar(1000)

Open C1

Fetch Next from C1 into @val,@val2
while @@Fetch_Status = 0
begin
   Set @sSql = 'Drop Trigger [' + @val2 + '].[' + @val + '_AuditTrigger]'
   Exec (@sSql)
   Fetch Next from C1 into @val,@val2
end


Close C1

Deallocate C1

END


The following table has to be created on production database;


SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[tblAuditTables](
[ObjectID] [int] NOT NULL,
[Alias] [nvarchar](1000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

CONSTRAINT [PK_tblAuditTables] PRIMARY KEY CLUSTERED

(
[ObjectID] ASC


)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]


) ON [PRIMARY]





Once you have created a comma separated list of tables you want to audit on your production database, you can run the following script;

exec [dbo].[uspGetAuditTables] '159196,1591244’,’MyTable1,
MyTable2’


This will create the trigger on MyTable1 and MyTable2.


4.    Enjoy Audit reports from Audit Database.




Now change any value in MyTable1 or MyTable2 and it will be
reflected in tblAud as XML in xmlQuery
column. You can create reports on Audit data from Audit database according to
your requirements. I have created the Insert / Update /Delete statements of the
production database from the AuditDatabase.




This long solution might have some drawbacks. I am currently using
it on one of my systems in QA without any error (till now). And we are planning
to ship it to production server. Any comments / suggestions are most welcome.




Posted by
Labels:

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)