February 10, 2011 at 5:32 am
Hi to all,
I have a question, I think all expert DBA can give me detailed answer on this one.
Is it good to use trigger to handle audit trail? so meaning every time there's an inserted or updated row on the table, there's a trigger that will handle the insert to audit trail table.
I asked this because there's another way to do this like you can create another stored procedure that will do the insert to the audit trail table. So you will call this stored procedure every time you do some insert or update on the table.
Our DB Architect don't want to use trigger because he said it's "destructive" and he said you don't have full control. If you will asked me his complete explanation. I don't know because that's the only sentence that he gave me.
Personally I want to use trigger because for me it's more efficient. And I don't want to use the other way which is to call an stored procedure that will do the same thing but it will not be called inside the trigger.
Can someone enlightened me if I'm wrong on choosing Trigger to handle the Audit Trail of an application.
Thanks in advance
February 10, 2011 at 7:26 am
In my experience, using a DML trigger to create an audit trail of some sort is one of the strengths of a DML trigger. I've used this before in the past to fill an audit table with past instances of data in a production sales header and line table.
Triggers can be a nuisance if not deployed properly. For instance, in the above example, a faulty trigger could make it difficult to know why certain issues are being encountered when trying to insert, update or delete data in the sales header/line, but triggers are no more destructive/dangerous than giving a primed grenade to a 4 yr old.
The same could likely be accomplished with a stored proc, but with much more work involved.
February 10, 2011 at 7:45 am
Audit triggers are basically an unavoided way of hadling the audit right from earlier versions of SQL Server. There are always pros and cons from performance point of view when using the triggers. The pros could be "It's easier to write & deploy" and the cons is "It gets worst when deployed incorrectly".
The approach you mentioned here is very common to have one stored procedure called by triggers, amd its a good practice to have it in this way. Using such approach will be useful when you have some custom logic involve for audit. As triggers are always an overhead on performance instead SQL Server has introduce Change Data Capture [CDC], which offers you the same functionality handled by SQL Server.
Abhijit - http://abhijitmore.wordpress.com
February 10, 2011 at 2:58 pm
Well that depends on the audit
as far as the trigger you probably want to put the audit on a different drives any way, for performance stripped
February 10, 2011 at 6:59 pm
Abhijit More (2/10/2011)
Audit triggers are basically an unavoided way of hadling the audit right from earlier versions of SQL Server. There are always pros and cons from performance point of view when using the triggers. The pros could be "It's easier to write & deploy" and the cons is "It gets worst when deployed incorrectly".The approach you mentioned here is very common to have one stored procedure called by triggers, amd its a good practice to have it in this way. Using such approach will be useful when you have some custom logic involve for audit. As triggers are always an overhead on performance instead SQL Server has introduce Change Data Capture [CDC], which offers you the same functionality handled by SQL Server.
I'm sorry I thought we are using SQL Server 2008. We are just using SQL Server 2005. So I think CDC is not available in 2005 but I'm not sure because by profession I was a .Net Developer not DB Developer. I think If they really don't want to use trigger and want to used a separate stored procedure, that will be fine with me. But if they will asked me to call that stored procedure on .net application. then that is not fine with me. because for me it's illogical to call it inside .net application because it's not part of the business layer of .net application.
And regarding to performance on using trigger vs store procedure. Who will win? the trigger will do the insert to the audit table. and the stored procedure will do the same thing.
February 11, 2011 at 2:26 am
CELKO (2/10/2011)
Triggers are a bad idea for audits and in some cases they are illegal.
:blink: News to me. Could you give some examples of that?
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
September 1, 2014 at 7:10 am
Please shed more light on this. How is it illegal?
September 1, 2014 at 6:40 pm
There are cases where every access has to be audited (legal requirement). It's impossible to do this auditing by using triggers, since we don't have ON SELECT triggers. So if auditing is done that way in these cases, that's illegal bevause the legal requirement isn't being met.
There are cases where auditing is required only on changes (update, insert, delete) and in this case it may be possible to do it by trigger. If the audit requirement includes identification of who invoked the change it isn't possible to do it by trigger unless knowing the database principla on behalf of whom each operation is executed implies knowing the end-user who initiated that action, so once again doing it by triggers might not enable the required auditing, so if the required auditing is a legal requirement doing the audit be triggers will usually (because databases usually don't have a separate database principal for each end user of each application that uses the data) be illegal just as in the case where all access is required to be audited.
If you require only to audit what was changed and and when but not who did it (exept that the database principal used can be recored, which is almost useless for identifying the "who" then it's possible to do auditing with triggers. Usuually any better identification of who did it and always any requirement to audit read access means you can't do it that way. One option is to do the auditing in the app, but it probably isn't a good option; another is to insist that the apps always call stored procedures (which is a very useful way of placing a nice modular boundary between app and db, so it's best practice anyway - I was somehat amazed to see a .net developer saying he would refuse to follow best practice) and have each SP to which the apps have access take a user-identity parameter indicating who the end-user was, and then the auditing can be done (or invoked) from those stored procedures. Those interface SPs are then the software wall Joe C referred to.
Tom
September 2, 2014 at 12:56 am
Fantastic explanation Tom. Very clear. Thanks
September 2, 2014 at 4:25 am
CELKO (9/1/2014)
Could you give some examples of that?
HIPPA. You must record everyone who saw the medical record, not changed it, saw it. I could not carry my own sonogram to the doctor in the next room; it has to be a NURSE! It is a medical record and I am not authorized.
You can only catch INSERT, UPDATE and DELETE (aka database events in SQL-speak), not SELECT. This is as bad as the days I did Cold War era contracts.
I am told that BASEL II, and some other financial things are almost as silly.
What you want to do is put wrap the database in a software wall (aka Iron Curtain) that logs, blocks and filters everything that cross the boundary. Ever work with Multics OS or learn about its ring model in school?
I challenge your definition of "Illegal". Generally I thought that this mean't "Against the law". You can ABSOLUTELY audit all changes (Insert, Update, Delete) in all databases and it not be against the law (aka Illegal). HOWEVER, it does not meet the legal requirement of auditing all SELECT statements. You would have to take additional measures. Please do some basic research on terms (i.e. Illegal) before you throw them out. I know of a teacher that would beat you with a stick for that error to help you learn better.
September 2, 2014 at 2:52 pm
CELKO (9/2/2014)
You can ABSOLUTELY audit all changes (Insert, Update, Delete) in all databases and it not be against the law (aka Illegal). HOWEVER, it does not meet the legal requirement of auditing all SELECT statements.
It gets worse. In some systems, you have to audit the users who did not make any changes! They attempted a change, and for some reason, the change was never made. System failure? Criminal action that was blocked? Bi difference between those.
Another one that gets messy is multiple signatures. A purchase order requires Mr. A to be passed to the shipping department. Mr. B must sign the shipping papers. Mr. A <> Mr. B.
In an accounting system, any flow needs an audit on the start and the end of it.
Then there are quorum audits: we need (k of n) "signatures" for an action to take place in a system. And levels of security clearances, etc.
Audit is not easy!
And it depends on what you are auditing. Not all auditing is for compliance to some federal, state, or local law or an industry standard such as PCI.
Triggers make good sense if you are simply auditing data changes over time. Being able to see what has changed and when. If you can capture who, great.
September 3, 2014 at 10:42 am
Triggers can capture the originating user as well if you provide that info to the trigger. This can be passed in several ways, including CONTEXT_INFO, APP_NAME or even a table row. Given pooled connections, it can take some care to make sure the data is set correctly per user.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
September 3, 2014 at 11:50 am
One of the issues when using triggers to audit changes is the ability to disable the trigger, apply the change and re-enable the trigger.
Without any audit for DDL changes the trigger itself isn't really an audit...
And still there's a chance for a sysadmin to overrule it (or to make the change almost untraceable...).
It all ends with the question "Who Watches the Watchers"?
From my point of view audition is nothing but to raise the bar for untraceable changes.
I don't know of a bullet-proof way to guarantee that each and every change/access will be audited.
September 3, 2014 at 11:55 am
LutzM (9/3/2014)
One of the issues when using triggers to audit changes is the ability to disable the trigger, apply the change and re-enable the trigger.Without any audit for DDL changes the trigger itself isn't really an audit...
And still there's a chance for a sysadmin to overrule it (or to make the change almost untraceable...).
It all ends with the question "Who Watches the Watchers"?
From my point of view audition is nothing but to raise the bar for untraceable changes.
I don't know of a bullet-proof way to guarantee that each and every change/access will be audited.
There are 3rd party solutions that do that, but they are rather expensive. At least one we investigated uses its own "black box" to hold the data -- DBAs can't even see it.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
September 3, 2014 at 12:22 pm
ScottPletcher (9/3/2014)
...There are 3rd party solutions that do that, but they are rather expensive. At least one we investigated uses its own "black box" to hold the data -- DBAs can't even see it.
From my understanding there's still a chance for an OS system admin to stop the process of the auditing tool (if it's on the same server) or drop the connection (if it's on a separate system), create a database user, disable triggers, make some changes, clean up logs and traces, re-start the tool(s) and leave the system.
The effort it takes to make a undetected change will take quite some time, but is still possible, I think. From my point of view there's no audit technology out there that really is "unbreakable"...
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply