July 17, 2002 at 12:46 am
Where can i find info on Audit Trail Using Triggers with examples
July 17, 2002 at 5:37 am
We've covered it a little in various threads, dont think we have an article on it so far. Your choices are to use a trigger to capture various changes or to use a server side trace. Which you use depends on your needs. I typically just add triggers to log the before image to a history table, this lets me see the entire state of the row prior to the change. Some approaches just log the actual changes. There are also some 3rd party tools that will create the triggers for you.
Andy
July 17, 2002 at 6:12 am
It also depends on several factors of what you are wanting to audit. If it is basic access on tables for INSERT, UPDATE, and DELETE then a trigger is sufficient. If for SELECT then either use Profiler or setup running traces to capture it. Another method which I use is to use procedures and no direct access whatsoever. Then I can write the procedures access to the audit table (this way I can know exactly what they ran).
Then it boils down to what type of audit. Is it change audit? Query audit? Access audit?
I do a little of each in various ways. What is your audit for and I can offer better suggestions.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
July 17, 2002 at 6:26 am
A simple type of auditing using triggers is described in the following article:
http://www.sqlservercentral.com/columnists/rmarda/auditingtriggers.asp
This should at least give you an idea of how to create the triggers you want.
Robert Marda
Robert W. Marda
Billing and OSS Specialist - SQL Programmer
MCL Systems
July 18, 2002 at 3:05 am
For our applications we have legal requirements to keep a full audit trail, including data retrieval. Also, we need to be able to replay the trace to re-generate result-sets to be reviewed by external auditors. Our idea is to run a server-side trace and use profiler to replay, but we have a number of issues
1. Replaying per database; we need to trace activity for all databases, but may want to replay actions for 1 database only. We also noticed that creating 1 audit table containing the full trace and then creating views on top of that filtering by database ID doesn't work, because Profiler doesn't let you replay from a view, you need a physical table.
2. Replaying cross server; from an application point of view, we may access multiple databases deployed on different servers. From an auditor point of view, all application activity needs to be replayed, which means replaying accross servers. We will have two traces that somehow have to be merged and replayed correctly. Is this feasible???
3. Going back in time; auditors want to replay, starting from a certain point back in time. We then need to restore the latest backup prior to that date and start replaying the trace from there. However, Profiler does not let you start the replay from somewhere in the middle of the trace, you have to start at the beginning.
4. Filtering; how do we determine what activity needs be traced in order to be able to do a 'functional' replay, from an application point of view. We do not need any other system stuff in the trace, unless of course it affects the results. What is 'safe' to filter out?
Any comments on this?
quote:
It also depends on several factors of what you are wanting to audit. If it is basic access on tables for INSERT, UPDATE, and DELETE then a trigger is sufficient. If for SELECT then either use Profiler or setup running traces to capture it. Another method which I use is to use procedures and no direct access whatsoever. Then I can write the procedures access to the audit table (this way I can know exactly what they ran).Then it boils down to what type of audit. Is it change audit? Query audit? Access audit?
I do a little of each in various ways. What is your audit for and I can offer better suggestions.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
July 18, 2002 at 8:05 am
quote:
For our applications we have legal requirements to keep a full audit trail, including data retrieval. Also, we need to be able to replay the trace to re-generate result-sets to be reviewed by external auditors. Our idea is to run a server-side trace and use profiler to replay, but we have a number of issues
These are my thoughts on it, which I have not tested since I don't have all the details nor doI have this type of setup. Others may want to throw in here, please feel free.
quote:
1. Replaying per database; we need to trace activity for all databases, but may want to replay actions for 1 database only. We also noticed that creating 1 audit table containing the full trace and then creating views on top of that filtering by database ID doesn't work, because Profiler doesn't let you replay from a view, you need a physical table.
Create a table with the exact same struture as the current and copy the items over to be used in Profiler. If should be okay with this unless there is an item noting step items.
quote:
2. Replaying cross server; from an application point of view, we may access multiple databases deployed on different servers. From an auditor point of view, all application activity needs to be replayed, which means replaying accross servers. We will have two traces that somehow have to be merged and replayed correctly. Is this feasible???
This I don't think can be done since Profiler is run only against one server at a time. However if your app is connecting to the other servers via a linked server then all activity can be recorded at a single server source and should be able to be played back from the single server.
quote:
3. Going back in time; auditors want to replay, starting from a certain point back in time. We then need to restore the latest backup prior to that date and start replaying the trace from there. However, Profiler does not let you start the replay from somewhere in the middle of the trace, you have to start at the beginning.
Same thing as answer 1.
quote:
4. Filtering; how do we determine what activity needs be traced in order to be able to do a 'functional' replay, from an application point of view. We do not need any other system stuff in the trace, unless of course it affects the results. What is 'safe' to filter out?
You only need to capture the events from the Appliction itself since triggers and constriants will react the same if not changed. Depending on hos you setup you applications connection to SQL I try to use the AppName parameter in ADO connections to make them more visible. But all you need are those event from the Applciation itself. Let profiler decide how to handle them. For consistency and data integrity you should not filter out any items from the replay. One change can potentially alter the entire outcome.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply