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


Audit Trail


Audit Trail

Author
Message
kbonthala
kbonthala
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
Points: 1 Visits: 1
Where can i find info on Audit Trail Using Triggers with examples



Andy Warren
Andy Warren
SSCoach
SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)

Group: Moderators
Points: 15253 Visits: 2730
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
http://www.sqlservercentral.com/columnists/awarren/

Andy
SQLAndy - My Blog!
Connect with me on LinkedIn
Follow me on Twitter
Antares686
Antares686
SSCoach
SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)

Group: Moderators
Points: 15188 Visits: 785
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)



Robert W Marda
Robert W Marda
SSCommitted
SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)

Group: General Forum Members
Points: 1795 Visits: 111
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
j.roumimper
j.roumimper
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
Points: 1 Visits: 1
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)





Antares686
Antares686
SSCoach
SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)

Group: Moderators
Points: 15188 Visits: 785
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)



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