Turning off @StrictUserContext and Deleting Rows

Nov 6, 2013 at 11:30 AM
Thanks again for such an awesome tool. Its exactly what I'm after.

I have a question relating to turning off @StrictUserContext. From your documentation below:
For most applications leaving @StrictUserContext on is approriate. 
Turning @StrictUserContext off is useful for two use cases: 
   1 - applications that manage their own user security and log into 
       SQL Server using a common security context. These applications 
       can pass the user name to AutoAudit by inserting into the base 
       table's CreatedBy column or updating the base table's Modified 
       column. 
   2 - when importing data from a previous database that already has
       legacy audit data. 
I have an application that uses a generic username to connect to SQL. I want to capture the User Name that they user within that application.

With StrictUserContext = 0 and BaseTableDDL = 1. I have been able to record the CreatedBy, CreatedDate for new records, by passing these values to CreatedBy and CreatedDate columns of the Base Table (i.e. Customers Table) when I create a new record. I have also been able to do the same with the ModifiedBy and ModifiedDate values for any modifications.

For recording the DeletedDate and DeletedBy values, I have taken your advice, i.e.:

Delete: delete DML statements do not include dml columns, so when
the @StrictUserContext is set to 0, the previous modified
and modified values are captured into the audit trail table.
To record the correct delete user and datetime, first touch
(update) the row's Modified and/or ModifiedBy columns.

I have first Touched, or Updated the row's Modified and ModifiedBy Columns with the User Name and Deleted Date, however the Customers Table Row History view shows that it is using values from the CreatedBy and CreatedDate columns to record in the AuditDate and the SysUser columsn of the AuditHeader Table.

Could you please let me know if this is the correct behavior? Many thanks for your help.
Nov 7, 2013 at 3:12 PM
Hi,
I am not sure if I will help you. Maybe :-)

I also use StrictUserContext = 0 and BaseTableDDL = 1, and save by App just CretedBy and ModifiedBy.
Created is set automatically because of dafault value is set to (getdate()) in column definition
And as for as Modified I changed triggers little bit. (in fact changed script creating those triggers)

The key was to do such changes like: COALESCE( src.' + @CreatedColumnName + ', @AuditTime) -> @AuditTime
Throw away COALESCE and pass just @AuditTime, which is always set GetDate(). This way you get always correct Modified dates..

Foe example
original line in AutoAudit 3.20f.sql:
THEN ' COALESCE( src.' + @CreatedColumnName + ', @AuditTime), COALESCE( src.' + @CreatedByColumnName + ', Suser_SName()),'

modified line:
THEN ' @AuditTime, COALESCE( src.' + @CreatedByColumnName + ', Suser_SName()),'
Nov 8, 2013 at 11:20 AM
Hi Rosacek,

Thanks for your suggestion. I might look at using it.

I really wanted to confirm with Paul or John, if it is in fact the Modified and ModifiedBy columns that I need to Touch (Update) before I delete a record, i.e. as per the documentation. Or if it should be the Created and CreatedBy columns, that I have confirmed with my testing. I really don't mind either way, I can modify my code to suit.

My main concern is that if AutoAudit is upgraded at a later stage to match the documentation, i.e. "Update the Modified and ModifiedBy Columns" before deleting a record, then I will need to make modifications to my code to match this change.

Thanks again Rosacek for your example.
Nov 8, 2013 at 12:48 PM
Frankly spoken I think Created and Modified should be set on server, not sent by client. As we cannot believe to client time. From the audit point of view we should use reliable time and such is provided by SQL server. Therefore I always adjust AutoAudit script to set @AuditTime variable instead of COALESCE.
Nov 8, 2013 at 9:56 PM
Thanks Rosacek,

Not a bad point, the only thing that you would need to consider is clients working in difference time zones on the same SQL server. I think that using the client time would be more appropriate in this situation, unless SQL can manage the time zones in some way.
Coordinator
Nov 14, 2013 at 1:41 PM
Thank you both for the conversation and ideas on this topic.

I was looking at the logic that is currently being applied to these four columns (CreatedDate, CreatedBy, ModifiedDate and ModifiedBy) as well as what is written to the AuditHeader table and here's is what I'm thinking I could do to streamline the functionality when @StrictUserContext = 0


Insert trigger
Currently in the base table we have
  • for CreatedDate: COALESCE( inserted.AutoAudit_CreatedDate, @AuditTime)
  • for CreatedBy: COALESCE( inserted.AutoAudit_CreatedBy, Suser_SName())
  • for ModifiedDate: COALESCE( inserted.AutoAudit_ModifiedDate, inserted.AutoAudit_CreatedDate, @AuditTime)
  • for ModifiedBy: COALESCE( inserted.AutoAudit_ModifiedBy, inserted.AutoAudit_CreatedBy, Suser_SName())
Currently in the AuditHeader table we have (src is aliased to inserted)
  • for AuditDate: COALESCE( src.AutoAudit_CreatedDate, @AuditTime)
  • for SysUser: COALESCE( src.AutoAudit_CreatedBy, Suser_SName())
As far as inserts go, I think this is all good, if values are supplied, they are used, if not, the system defaults kick in as if @StrictUserContext was set to 1


Update trigger
Currently in the base table we have
  • for ModifiedDate: COALESCE( inserted.AutoAudit_ModifiedDate, inserted.AutoAudit_CreatedDate, @AuditTime)
  • for ModifiedBy: COALESCE( inserted.AutoAudit_ModifiedBy, inserted.AutoAudit_CreatedBy, Suser_SName())
Currently in the AuditHeader table we have
  • for AuditDate: COALESCE( inserted.AutoAudit_ModifiedDate, @AuditTime)
  • for SysUser: COALESCE( inserted.AutoAudit_ModifiedBy, Suser_Sname())
I don't like the way the update works in the base table. I propose to change the statements to...
  • for ModifiedDate: COALESCE( inserted.AutoAudit_ModifiedDate, @AuditTime)
  • for ModifiedBy: COALESCE( inserted.AutoAudit_ModifiedBy, Suser_SName())
I don't think the CreatedDate and CreatedBy have anything to do with updates hence the reason for removing the CreatedDate and CreatedBy members of the coalesce statements.
What this will do for you is:
  1. If you want to use your own application values for ModifiedDate and ModifiedBy, simply include those columns with values in your update statement and that is what will get used.
  2. If you set one or both of them to null, AutoAudit will use getdate() and Suser_Sname in the same fashion as when @StrictUserContext = 1
  3. If you do not update the ModifiedDate and ModifiedBy columns at all, then they will stay unchanged with whatever value they had previous to the update of other columns in the table.
Delete trigger
Currently in the AuditHeader table we have (src is aliased to deleted)
  • for AuditDate: COALESCE( src.AutoAudit_CreatedDate, @AuditTime)
  • for SysUser: COALESCE( src.AutoAudit_CreatedBy, Suser_SName())
As far as I'm concerned this is a mistake that I made. I should never have referenced the Created columns. I should be using the Modified columns instead. I propose to change the statements to...
  • for AuditDate: COALESCE( src.AutoAudit_ModifiedDate, @AuditTime)
  • for SysUser: COALESCE( src.AutoAudit_ModifiedBy, Suser_SName())
What this will do for you is:
  1. If you want to use your own application values for ModifiedDate and ModifiedBy which will end up in AuditDate and SysUser of AuditHeader, simply include those columns with values in your update statement and that is what will get used.
  2. If you set one or both of them to null, AutoAudit will use getdate() and Suser_Sname in the same fashion as when @StrictUserContext = 1
  3. If you do not update the ModifiedDate and ModifiedBy columns at all, then the last values will get used in the AuditHeader entry.
Please let me know if you agree with these behaviour changes when @StrictUserContext = 0 is used with AutoAudit.

Thanks,
John
Nov 14, 2013 at 5:06 PM
Hi John,
I think that was not your mistake to use CreatedDate. The same flaw was in v.2.x
In fact I always used adjusted autoaudit script to overcome some issues (this was one, and also I had problem with dynamic SQL, so I had to adjust code for @NestLevel
Say due to taht fact I am aware about the logic inside, not just download+use user :)

Your idea should work. Anyway I also think of this logic.
I consider to add another two DateTime fields to base tables as named like CreatedOnServerDate, ModifiedOnServerDate.
I cannot believe to any BFU users. They do not keep proper time on their PCs, playing with dates on PC because they need do issue someting in SW to looks like it was issued yesterday etc :)
Then it is hard to find who did what and when and if more users changed the same record, then mess and had job to study RowVersion etc

I think I would need both time. ClientTime, and ServerTtime. ServerTime is reliable and fixed. On the other hand as TPhallet pointed, there are also users in different time zones. And we need to know situation from their time point of view.

Therefore I consider to save both Server and Client timestamp. Server dates should be managed automatically by triggers, while clienttime should be sent by application, if not send by app, then keep NULL.
Coordinator
Nov 14, 2013 at 6:28 PM
Hi rosacek,

I agree that on occasion it can be very useful to save the local PC times including different time zones.

What I would suggest you do to solve that problem is to add a single ClientTime column to your table as a standard data column rather than changing AutoAudit to handle server and client time.
The idea is that you let AutoAudit manage the CreatedDate and ModifiedDate as it will do when I implement the @StrictUserContext = 0 changes discussed above by passing a null value for the CreatedDate and ModifiedDate columns. This will ensure that AutoAudit used the SQLServer time for logging all changes.

Now, when it comes to the ClientTime column, when you insert, update or delete (with a previous update touch) a row, set the ClientTime column value to the local pc time with time zone if appropriate. As the rows change, AutoAudit will log each setting of the local time changes in the same way as it logs any other column change in the table.
With the _RowHistory UDF and view you will always know the client PC time by looking at the ClientTime column in _AuditHistory query results. The beauty of this is you can collect the local PC time details that you need without having to do any changes to AutoAudit and you only need to add a single column to your table. Maybe you could name your column LatestChangeClientTime or something like that.

John
Nov 15, 2013 at 8:46 AM
Yes, you're right, agree 100%
Nov 16, 2013 at 4:54 AM
Thanks for your reply John,

I agree with your suggestions. Its seems that this conversation has created some good ideas. From my perspective, I merely wanted to clarify which columns I should be using when I delete a record, the answer you provided will help me out.

Would you be looking at adding the suggested changes to your next release? How long would you expect for this?

Thanks again for a great product.
Coordinator
Nov 16, 2013 at 5:21 AM
I coded and tested the changes I described above today.
I want to add a couple of other small change request this weekend and I will be releasing the next update later this weekend or Monday.

I appreciate the feedback.

John
Coordinator
Nov 19, 2013 at 6:30 PM
AutoAudit 3.20h had been released and addresses this issue.
Marked as answer by JohnSigouin on 11/19/2013 at 10:30 AM
Nov 25, 2013 at 9:13 PM
Hi John,
I went thru 3.20h to check deeply UPDATE and DELETE trigers and did some testing today.

I usually send from apps CreatedBy and ModifiedBy. On the other hand Created and Modified fields are managed by triggers.

And because your logic is:
  • if NULL values are updated to those columns with the user query, then AutoAudit uses the server time and the logged in user name in the base table and in the AuditHeader table
  • if the values for the ModifiedBy and ModifiedDate are left unchanged with the update statement, then the previous values will remain in the base table and be used again in the AuditHeader table
Then after UPDATE I got Modified fields unchanged - keeping the original timestamp. And this timestamp is also saved into AuditHeader.

In order to get it work I will replace "COALESCE( src.AutoAudit_ModifiedDate, @AuditTime)" to "@AuditTime" in UPDATE and DELETE triggers (in trigger build script in fact).

The question is why do you make the difference if NULL is sent to ModifiedDate or if there is not ModifiedDate in update SQL at all. I think it does not matter.
If I send NULL OR not send ModifiedDate parameter in SQL update, then @AuditTime should be saved anyway.
If I send ModifiedDate, then of course such date should be saved.

I am ok to modify script by myself, just I wanted to let you know my testing results.
Coordinator
Nov 26, 2013 at 12:54 PM
I make the difference between NULL and unchanged because this gives you three option with @StrictUserContext = 0
  1. Supply values for ModifiedBy and/or ModifiedDate and that what you get in the table
  2. Supply NULL for ModifiedBy and/or ModifiedDate and you get the system values (as if @StrictUserContext = 1)
  3. Leave the values unchanged (don't update ModifiedBy and/or ModifiedDate) and the previous existing values will be used
I realize that there are not many reasons why you would use option 3 but it is there if you ever need it.