StrictUserContext = 0 and Auditing who deleted what

Nov 9, 2010 at 8:15 PM

So I was looking at the code for the Delete trigger for the StrictUserContext option

 

-- StrictUserOption
		      + CASE @StrictUserContext
		          WHEN 0 -- allow DML setting of created/modified user and datetimes
		            THEN ' COALESCE(Deleted.Modified, @AuditTime), COALESCE(Deleted.ModifiedBy, Suser_SName()),'
		          ELSE -- block DML setting of user context 
		             ' @AuditTime, Suser_SName(),'
		        END 

When StrictUserContext = 0 it generates code to insert the ModifiedBy value as Deleted.ModifiedBy. Well this doesn't track the user who actually deleted the record, but instead it tracks the user who last updated the record, and we lose the audit trail of who actually deleted the record. I was wondering if anyone had any suggestion on how to fix this ?

The only thing I can think of is have all my procedures that delete from a table call an UPDATE and change the ModifiedBy column to the current @User variable, and then call a DELETE. I was just wondering if anyone has a more elegant solution ? I was hoping for some kinda "Transaction Global Variable" that could be read from inside the DELETE trigger.

 

Thanks,
Raul

 

Coordinator
Nov 9, 2010 at 8:28 PM

Read the docs at the top of the script.

-Paul

From: ismell [mailto:notifications@codeplex.com]
Sent: Tuesday, November 09, 2010 2:15 PM
To: pauln@sqlserverbible.com
Subject: StrictUserContext = 0 and Auditing who deleted what [AutoAudit:234080]

From: ismell

So I was looking at the code for the Delete trigger for the StrictUserContext option

-- StrictUserOption
                     + CASE @StrictUserContext
                         WHEN 0 -- allow DML setting of created/modified user and datetimes
                           THEN ' COALESCE(Deleted.Modified, @AuditTime), COALESCE(Deleted.ModifiedBy, Suser_SName()),'
                         ELSE -- block DML setting of user context 
                            ' @AuditTime, Suser_SName(),'
                       END 

When StrictUserContext = 0 it generates code to insert the ModifiedBy value as Deleted.ModifiedBy. Well this doesn't track the user who actually deleted the record, but instead it tracks the user who last updated the record, and we lose the audit trail of who actually deleted the record. I was wondering if anyone had any suggestion on how to fix this ?

The only thing I can think of is have all my procedures that delete from a table call an UPDATE and change the ModifiedBy column to the current @User variable, and then call a DELETE. I was just wondering if anyone has a more elegant solution ? I was hoping for some kinda "Transaction Global Variable" that could be read from inside the DELETE trigger.

Thanks,
Raul

Read the full discussion online.

To add a post to this discussion, reply to this email (AutoAudit@discussions.codeplex.com)

To start a new discussion for this project, email AutoAudit@discussions.codeplex.com

You are receiving this email because you subscribed to this discussion on CodePlex. You can unsubscribe or change your settings on codePlex.com.

Please note: Images and attachments will be removed from emails. Any posts to this discussion will also be available online at codeplex.com

Nov 9, 2010 at 9:41 PM
Edited Nov 9, 2010 at 9:42 PM

Oh guess I missed that note. I did find another solution to the problem.
There is a "Global" variable we can use called CONTEXT_INFO. I can have the middle tier set the CONTEXT_INFO to the user name when ever it creates a connection. As a result I don't have to alter any of my procedures or do an extra update. Then AutoAudit can be changed to do a

COALESCE(CAST(CONTEXT_INFO() as varchar(128), Deleted.ModifiedBy, Suser_SName()),

Problem Solved!

You can read up on the solution here: http://jasondentler.com/blog/2010/01/exploiting-context_info-for-fun-and-audit/

Coordinator
Nov 9, 2010 at 10:24 PM

I consider and rejected using context info because other utilities use (e.g. the peek/poke call stack system) this limited space and it might cause incompatibilities or errors in AutoAudit or the other utility. Storing the deleting user in the updatedby column is the solution with the least risk.

-Paul

From: ismell [mailto:notifications@codeplex.com]
Sent: Tuesday, November 09, 2010 3:41 PM
To: pauln@sqlserverbible.com
Subject: Re: StrictUserContext = 0 and Auditing who deleted what [AutoAudit:234080]

From: ismell

Oh guess I missed that note. I did find another solution to the problem.
There is a "Global" variable we can use called CONTEXT_INFO. I can have the middle tier set the CONTEXT_INFO to the user name when ever it creates a connection and then I don't have to alter any of my procedures. Then AutoAudit can be changed to do a

COALESCE(CAST(CONTEXT_INFO() as varchar(128), Deleted.ModifiedBy, Suser_SName()),

Problem Solved!

You can read up on the solution here: http://jasondentler.com/blog/2010/01/exploiting-context_info-for-fun-and-audit/

Read the full discussion online.

To add a post to this discussion, reply to this email (AutoAudit@discussions.codeplex.com)

To start a new discussion for this project, email AutoAudit@discussions.codeplex.com

You are receiving this email because you subscribed to this discussion on CodePlex. You can unsubscribe or change your settings on codePlex.com.

Please note: Images and attachments will be removed from emails. Any posts to this discussion will also be available online at codeplex.com

Nov 9, 2010 at 11:37 PM

When you say other utilities do you mean internal to SQL server or applications built by users ? I completely agree that using the UpdatedBy column is the best approach to provide the best comparability. But in my case none of our apps currently make use of it so we can define a standard on what to set in CONTEXT_INFO, but if you have some insight on what or how SQL Server its self uses it that would be helpful.

Coordinator
Nov 10, 2010 at 1:59 AM

There are several other open-source style SQL utilities that make use of context_info. I’m not aware of any MSFT utilities that use Context_Info.

-Paul

From: ismell [mailto:notifications@codeplex.com]
Sent: Tuesday, November 09, 2010 5:37 PM
To: pauln@sqlserverbible.com
Subject: Re: StrictUserContext = 0 and Auditing who deleted what [AutoAudit:234080]

From: ismell

When you say other utilities do you mean internal to SQL server or applications built by users ? I completely agree that using the UpdatedBy column is the best approach to provide the best comparability. But in my case none of our apps currently make use of it so we can define a standard on what to set in CONTEXT_INFO, but if you have some insight on what or how SQL Server its self uses it that would be helpful.

Read the full discussion online.

To add a post to this discussion, reply to this email (AutoAudit@discussions.codeplex.com)

To start a new discussion for this project, email AutoAudit@discussions.codeplex.com

You are receiving this email because you subscribed to this discussion on CodePlex. You can unsubscribe or change your settings on codePlex.com.

Please note: Images and attachments will be removed from emails. Any posts to this discussion will also be available online at codeplex.com

Nov 11, 2010 at 3:26 PM

So I went ahead and added a new parameter to pAutoAudit called @UseContextInfo. This allows the user to decide if they want to take advantage of using CONTEXT_INFO() to pass around the context user. The function is not allowed when using StrictUserContext = 0 because it should be the applications responsibility to manage the CreatedBy and ModifiedBy columns. So in order to use it make sure you set @StrictUserContext = 1. I have added documentation and examples to the script on the format used for CONTEXT_INFO() so you can get the correct value. I created a function called dbo.fnAutoAuditContextUser() that parses the CONTEXT_INFO() string and returns a user name or NULL. The CONTEXT_INFO() can store varchar(127) or nvarchar(63) you can check the documentation on how to use either one.

The patch can be found at http://autoaudit.codeplex.com/SourceControl/PatchList.aspx #7353.