SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Audit systems: a good idea or a mess to maintain?


Audit systems: a good idea or a mess to maintain?

Author
Message
bkubicek
bkubicek
SSCrazy Eights
SSCrazy Eights (8.7K reputation)SSCrazy Eights (8.7K reputation)SSCrazy Eights (8.7K reputation)SSCrazy Eights (8.7K reputation)SSCrazy Eights (8.7K reputation)SSCrazy Eights (8.7K reputation)SSCrazy Eights (8.7K reputation)SSCrazy Eights (8.7K reputation)

Group: General Forum Members
Points: 8688 Visits: 1038
Comments posted to this topic are about the item Audit systems: a good idea or a mess to maintain?
Dave Poole
Dave Poole
SSC Guru
SSC Guru (58K reputation)SSC Guru (58K reputation)SSC Guru (58K reputation)SSC Guru (58K reputation)SSC Guru (58K reputation)SSC Guru (58K reputation)SSC Guru (58K reputation)SSC Guru (58K reputation)

Group: General Forum Members
Points: 58722 Visits: 3951
They have their uses but in my experience are rarely driven by requirements. The ones I've seen tend to be massively over engineered and have been put in place by people desperately trying to 2nd guess what the customer needs vs the inadequate statement of what they want. Failure to 2nd guess correctly results in the project being deemed ITs failure for not satisfying the NSRs (Non-Stated Requirements) that were assumed to be so obvious that they weren't specified

LinkedIn Profile
www.simple-talk.com
peter.midgley
peter.midgley
SSC-Enthusiastic
SSC-Enthusiastic (131 reputation)SSC-Enthusiastic (131 reputation)SSC-Enthusiastic (131 reputation)SSC-Enthusiastic (131 reputation)SSC-Enthusiastic (131 reputation)SSC-Enthusiastic (131 reputation)SSC-Enthusiastic (131 reputation)SSC-Enthusiastic (131 reputation)

Group: General Forum Members
Points: 131 Visits: 80
David.Poole - Tuesday, February 27, 2018 12:29 AM
They have their uses but in my experience are rarely driven by requirements. The ones I've seen tend to be massively over engineered and have been put in place by people desperately trying to 2nd guess what the customer needs vs the inadequate statement of what they want. Failure to 2nd guess correctly results in the project being deemed ITs failure for not satisfying the NSRs (Non-Stated Requirements) that were assumed to be so obvious that they weren't specified

Staying with the sad topic of blame apportionment (!) we have implemented a few audits where experience showed that users denied having changed whatever it was and the existence of a table, that could demonstrate when and who and what was done, has rebalanced the conversation beautifully :-)
More neutrally, we find that when users know changes are tracked, more care is often taken, which is generally a good thing.

William Rayer
William Rayer
SSC-Addicted
SSC-Addicted (480 reputation)SSC-Addicted (480 reputation)SSC-Addicted (480 reputation)SSC-Addicted (480 reputation)SSC-Addicted (480 reputation)SSC-Addicted (480 reputation)SSC-Addicted (480 reputation)SSC-Addicted (480 reputation)

Group: General Forum Members
Points: 480 Visits: 583
I use a simple approach which works well:

1. Each table being audited should have a PK (or unique constraint). For each table create an audit table of the same schema (columns and data types). Give the audit table a name something like MainTable_A and add 3 extra columns for DateOfChange, Person, Action.

2. Create one trigger for insert, update and delete on the main table. The insert and update actions append the 'inserted' rows to the audit table, and the delete action appends the 'deleted' rows to the audit table. The extra audit columns can be populated with GETDATE(), SUSER_SNAME() and I, U or D to indicate the action.

The result is the audit table is maintained automatically and there is minimal coding effort. The edit history of each PK can be obtained from the audit table, and the most recent row of a PK value in the audit table will match the current value in the main table. The technique is loosely based on what I've seen in commercial apps, also on conversations with colleagues about different ways of implementing auditing,
xsevensinzx
xsevensinzx
SSC-Insane
SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)

Group: General Forum Members
Points: 20187 Visits: 5439
This is just me. I view audit systems as only verbose. I think when you start trying to make your audit system do everything including making changes to the environment with something like say, rolling back a change, then it becomes more than just an audit system. I know it seems like less value if you can't audit something and act on it with the same system, but you start going down a pretty complicated path of turning your audit system into an actual being that can not only tell you the historic history of what has happen, but also act on it by reverting that history if you dislike something or do it automatically based on triggered events.

I've found just a logging system is good enough for auditing where I can act on the history myself. Not exactly all the bells and whistles of some of these systems, but it's much easier to create, maintain, and implement into any environment holistically.
roger.plowman
roger.plowman
SSCertifiable
SSCertifiable (8K reputation)SSCertifiable (8K reputation)SSCertifiable (8K reputation)SSCertifiable (8K reputation)SSCertifiable (8K reputation)SSCertifiable (8K reputation)SSCertifiable (8K reputation)SSCertifiable (8K reputation)

Group: General Forum Members
Points: 7964 Visits: 1930
I second Peter Midgley. Everything he said happens in our company too.

We use a trigger based system. We track every time a row is add/deleted/changed by user and time , and we record the old data for "important" fields that get changed. But we don't create an entire duplicate database.

This keeps the space needed for audit logs to a sane level (it's an OLTB system).

I also have to agree that accountability is a WONDERFUL way to keep users careful...
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (847K reputation)SSC Guru (847K reputation)SSC Guru (847K reputation)SSC Guru (847K reputation)SSC Guru (847K reputation)SSC Guru (847K reputation)SSC Guru (847K reputation)SSC Guru (847K reputation)

Group: General Forum Members
Points: 847803 Visits: 46682
William Rayer - Tuesday, February 27, 2018 4:20 AM
I use a simple approach which works well:

1. Each table being audited should have a PK (or unique constraint). For each table create an audit table of the same schema (columns and data types). Give the audit table a name something like MainTable_A and add 3 extra columns for DateOfChange, Person, Action.

2. Create one trigger for insert, update and delete on the main table. The insert and update actions append the 'inserted' rows to the audit table, and the delete action appends the 'deleted' rows to the audit table. The extra audit columns can be populated with GETDATE(), SUSER_SNAME() and I, U or D to indicate the action.

The result is the audit table is maintained automatically and there is minimal coding effort. The edit history of each PK can be obtained from the audit table, and the most recent row of a PK value in the audit table will match the current value in the main table. The technique is loosely based on what I've seen in commercial apps, also on conversations with colleagues about different ways of implementing auditing,


There is absolutely no need to ever audit INSERTs. All they do is double the storage requirements. If you INSERT a row and never change it, the original row is in both the original table and the audit table. Instant duplication of data and audit tables get large enough without that guaranteed duplication of data.

Only audit updates and deletes (in other words, only audit changes to the data). If a row is INSERTED and never updated, the original data will be in the original table. If you update an original row in the original table, the old row (which is the original INSERTED data) will be copied to the audit table and the last update will be in the original table.

Auditing INSERTs is a complete waste of time and resources and even the built in auditing methods in SQL Server don't audit INSERTs... they only audit modifications.

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
peter.midgley
peter.midgley
SSC-Enthusiastic
SSC-Enthusiastic (131 reputation)SSC-Enthusiastic (131 reputation)SSC-Enthusiastic (131 reputation)SSC-Enthusiastic (131 reputation)SSC-Enthusiastic (131 reputation)SSC-Enthusiastic (131 reputation)SSC-Enthusiastic (131 reputation)SSC-Enthusiastic (131 reputation)

Group: General Forum Members
Points: 131 Visits: 80
Jeff Moden - Tuesday, February 27, 2018 7:45 AM
William Rayer - Tuesday, February 27, 2018 4:20 AM
I use a simple approach which works well:

1. Each table being audited should have a PK (or unique constraint). For each table create an audit table of the same schema (columns and data types). Give the audit table a name something like MainTable_A and add 3 extra columns for DateOfChange, Person, Action.

2. Create one trigger for insert, update and delete on the main table. The insert and update actions append the 'inserted' rows to the audit table, and the delete action appends the 'deleted' rows to the audit table. The extra audit columns can be populated with GETDATE(), SUSER_SNAME() and I, U or D to indicate the action.

The result is the audit table is maintained automatically and there is minimal coding effort. The edit history of each PK can be obtained from the audit table, and the most recent row of a PK value in the audit table will match the current value in the main table. The technique is loosely based on what I've seen in commercial apps, also on conversations with colleagues about different ways of implementing auditing,


There is absolutely no need to ever audit INSERTs. All they do is double the storage requirements. If you INSERT a row and never change it, the original row is in both the original table and the audit table. Instant duplication of data and audit tables get large enough without that guaranteed duplication of data.

Only audit updates and deletes (in other words, only audit changes to the data). If a row is INSERTED and never updated, the original data will be in the original table. If you update an original row in the original table, the old row (which is the original INSERTED data) will be copied to the audit table and the last update will be in the original table.

Auditing INSERTs is a complete waste of time and resources and even the built in auditing methods in SQL Server don't audit INSERTs... they only audit modifications.

Quite so; however for completeness of information the 'original' table could include an applicationUser column and a DateLoaded column.

GeorgeCopeland
GeorgeCopeland
SSCertifiable
SSCertifiable (5.6K reputation)SSCertifiable (5.6K reputation)SSCertifiable (5.6K reputation)SSCertifiable (5.6K reputation)SSCertifiable (5.6K reputation)SSCertifiable (5.6K reputation)SSCertifiable (5.6K reputation)SSCertifiable (5.6K reputation)

Group: General Forum Members
Points: 5598 Visits: 1132
There is a lot of excellent advice on this thread. My contribution is to caution against over-engineering and complexity. I have seen audit systems that were so full of data and so cumbersome that they were almost impossible to understand. Yeah the audit data is in there somewhere, thanks a heap for meeting your requirement, but good bleeding luck finding what you need. Give design consideration to usability.
Rod
Rod
One Orange Chip
One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)

Group: General Forum Members
Points: 26955 Visits: 2616
In my previous job we didn't do much auditing. There was some. We knew who had touched some tables, both for inserts and updates, but that wasn't done in many places.

In my current position one thing that really struck me was just how much they audit. It is very impressive. It's easy to say who did what and when. And even in some cases what machine they did it on. I'm quite impressed with how my current work environment does auditing. Kudos to them!!

Kindest Regards,Rod
Connect with me on LinkedIn.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum








































































































































































SQLServerCentral


Search