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 ««123»»

Audit Trails and Logging Part I Expand / Collapse
Author
Message
Posted Monday, June 9, 2008 10:58 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: Wednesday, October 15, 2014 7:55 AM
Points: 934, Visits: 1,007
Very good article but you should add trace files to your option for auditing. I'm currently using Idera's tool which uses this method and been pretty happy with the results. There are some bugs and work around needed but no software is perfect.

This process is obviously bit more invasive then reading a log file but from what I've seen the impact is not noticeable... especially since the actual manipulation of the trace files should be on a separate server. One possible downside though, is that you will not have the actual data manipulated like you would with a log reader or trigger, instead you would just have the DML statement. On the positive side, you can audit login activity and SELECT statements.

David



Post #513857
Posted Monday, June 9, 2008 11:27 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, October 14, 2014 12:10 PM
Points: 1,414, Visits: 4,541
article missed prevention and catching fraud as a reason for logging. we have triggers and management has caught people giving freebies to friends in the audit tables.

https://plus.google.com/100125998302068852885/posts?hl=en
http://twitter.com/alent1234
x-box live gamertag: i am null
[url=http://live.xbox.com/en-US/MyXbox/Profile[/url]
Post #513876
Posted Monday, June 9, 2008 2:15 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Friday, June 27, 2014 12:43 PM
Points: 13,872, Visits: 9,596
SQL Noob (6/9/2008)
article missed prevention and catching fraud as a reason for logging. we have triggers and management has caught people giving freebies to friends in the audit tables.


I would count that as a combination Reports/Blamethrower audit.


- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Post #513997
Posted Monday, June 9, 2008 2:18 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Friday, June 27, 2014 12:43 PM
Points: 13,872, Visits: 9,596
David (6/9/2008)
Very good article but you should add trace files to your option for auditing. I'm currently using Idera's tool which uses this method and been pretty happy with the results. There are some bugs and work around needed but no software is perfect.

This process is obviously bit more invasive then reading a log file but from what I've seen the impact is not noticeable... especially since the actual manipulation of the trace files should be on a separate server. One possible downside though, is that you will not have the actual data manipulated like you would with a log reader or trigger, instead you would just have the DML statement. On the positive side, you can audit login activity and SELECT statements.

David


I guess I'm not sure what you mean by trace files. Are you talking about having some piece of the database/application write data to a separate file and use that for logging?

If so, yeah, that would be another means of active logging. I didn't actually think of having the logging take place outside of the database, but something like that was brought up by another person as part of a means of preventing SQL injection attacks from getting into the database, and tracking the attempted attacks. It certainly is an option, but not having used it, I don't think I can write more on that subject myself.


- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Post #514000
Posted Monday, June 9, 2008 2:18 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Wednesday, October 15, 2014 9:14 AM
Points: 9,294, Visits: 9,483
Nice article, Gus.

-- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoung
Proactive Performance Solutions, Inc.
"Performance is our middle name."
Post #514001
Posted Monday, June 9, 2008 2:37 PM
SSC Eights!

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

Group: General Forum Members
Last Login: Wednesday, October 15, 2014 7:55 AM
Points: 934, Visits: 1,007
I guess I'm not sure what you mean by trace files. Are you talking about having some piece of the database/application write data to a separate file and use that for logging?


I was referring to the SQL Server trace files created by sp_trace_create, sp_trace_setevent, etc which is the method SQL Profiler uses.


David



Post #514020
Posted Monday, June 9, 2008 3:00 PM


SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, October 1, 2014 1:30 PM
Points: 32, Visits: 122
I've been using trigger-based Audit Trails since SQL6, and every customer I've set them up for has loved them. Although there have been some use cases for workflow reporting and undo, the primary purpose has been tracking who-changed-what-when. Although you have classified this as Blamethrowing, it actually serves many other purposes. For instance, a user asks why their scheduled search did not return an expected result when expected, and I can show them that a) they added a criteria after the matching event had happened, and b) how to figure that out without asking me next time. It also answers questions about how a record got into a certain strange state, when there can be many users using multiple applications... and it can thereby turn up cases that need to be handled better in code.

I agree that the audit trail tables can be tampered with by anyone with permission, but so far I haven't had to satisfy a legal requirement... if I did, I would just keep the log files as a backup in case there was any question.

Also, although there seems to be a consensus that triggers need to be hard-coded to the table structure for speed, and thus rebuilt when the schema changes, I have worked out a way to quickly loop through existing columns, and only save changes, eliminating updates to the same value. I will be interested to see where your next article stands on this.
Post #514039
Posted Monday, June 9, 2008 8:59 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, March 28, 2012 2:21 PM
Points: 1, Visits: 5
Hi,

I just want to point you to a free tool for monitoring the SQL Log files.
It is Log Parser currently in v2.2 form Microsoft.
It is using a query engine and can read all kinds of log files and event logs.
It can output to the SQL database so you can build your own reporting on top.
SQL server uses the event log too for sending info so it is not enough to look into the logs but also into the Event log (I ussually start from there).

It would be interesting if you investigate the tool against those $1000 bucks tools.

Florin
Post #514127
Posted Tuesday, June 10, 2008 4:38 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 1:53 PM
Points: 35,366, Visits: 31,905
Good tip! Thanks!

--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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #514274
Posted Tuesday, June 10, 2008 11:28 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Friday, June 27, 2014 12:43 PM
Points: 13,872, Visits: 9,596
fmuntean (6/9/2008)
Hi,

I just want to point you to a free tool for monitoring the SQL Log files.
It is Log Parser currently in v2.2 form Microsoft.
It is using a query engine and can read all kinds of log files and event logs.
It can output to the SQL database so you can build your own reporting on top.
SQL server uses the event log too for sending info so it is not enough to look into the logs but also into the Event log (I ussually start from there).

It would be interesting if you investigate the tool against those $1000 bucks tools.

Florin


I was actually looking at that product a little while ago. From what I could tell, it could parse the event file, error log, etc., but I can't tell from the web page whether it will read an SQL Server transaction log. Have you tried that? Does it work well for it?


- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Post #514663
« Prev Topic | Next Topic »

Add to briefcase ««123»»

Permissions Expand / Collapse