Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Audit Trail Expand / Collapse
Author
Message
Posted Wednesday, July 17, 2002 12:46 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, October 07, 2002 12:00 AM
Points: 1, Visits: 1
Where can i find info on Audit Trail Using Triggers with examples




Post #5514
Posted Wednesday, July 17, 2002 5:37 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: Moderators
Last Login: Today @ 9:01 AM
Points: 6,705, Visits: 1,680
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
Post #37702
Posted Wednesday, July 17, 2002 6:12 AM
SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: Moderators
Last Login: Yesterday @ 3:54 PM
Points: 8,369, Visits: 733
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)



Post #37703
Posted Wednesday, July 17, 2002 6:26 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Tuesday, November 05, 2013 9:05 AM
Points: 976, Visits: 59
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
SQL Programmer
Ipreo
Post #37704
Posted Thursday, July 18, 2002 3:05 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, March 01, 2005 7:57 AM
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)






Post #37705
Posted Thursday, July 18, 2002 8:05 AM
SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: Moderators
Last Login: Yesterday @ 3:54 PM
Points: 8,369, Visits: 733
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)



Post #37706
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse