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


Audit Trails and Logging Part I


Audit Trails and Logging Part I

Author
Message
DavidSimpson
DavidSimpson
SSCommitted
SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)

Group: General Forum Members
Points: 1954 Visits: 1101
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



alen teplitsky
alen teplitsky
SSChampion
SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)

Group: General Forum Members
Points: 12492 Visits: 4674
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.
GSquared
GSquared
SSC Guru
SSC Guru (106K reputation)SSC Guru (106K reputation)SSC Guru (106K reputation)SSC Guru (106K reputation)SSC Guru (106K reputation)SSC Guru (106K reputation)SSC Guru (106K reputation)SSC Guru (106K reputation)

Group: General Forum Members
Points: 106309 Visits: 9730
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
GSquared
GSquared
SSC Guru
SSC Guru (106K reputation)SSC Guru (106K reputation)SSC Guru (106K reputation)SSC Guru (106K reputation)SSC Guru (106K reputation)SSC Guru (106K reputation)SSC Guru (106K reputation)SSC Guru (106K reputation)

Group: General Forum Members
Points: 106309 Visits: 9730
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
RBarryYoung
RBarryYoung
SSC Guru
SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)

Group: General Forum Members
Points: 61594 Visits: 9519
Nice article, Gus.

-- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoung
Proactive Performance Solutions, Inc.
"Performance is our middle name."
DavidSimpson
DavidSimpson
SSCommitted
SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)

Group: General Forum Members
Points: 1954 Visits: 1101
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



Thomas Keller
Thomas Keller
Old Hand
Old Hand (396 reputation)Old Hand (396 reputation)Old Hand (396 reputation)Old Hand (396 reputation)Old Hand (396 reputation)Old Hand (396 reputation)Old Hand (396 reputation)Old Hand (396 reputation)

Group: General Forum Members
Points: 396 Visits: 158
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.
fmuntean
fmuntean
Forum Newbie
Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)

Group: General Forum Members
Points: 7 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
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (382K reputation)SSC Guru (382K reputation)SSC Guru (382K reputation)SSC Guru (382K reputation)SSC Guru (382K reputation)SSC Guru (382K reputation)SSC Guru (382K reputation)SSC Guru (382K reputation)

Group: General Forum Members
Points: 382641 Visits: 43030
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
GSquared
GSquared
SSC Guru
SSC Guru (106K reputation)SSC Guru (106K reputation)SSC Guru (106K reputation)SSC Guru (106K reputation)SSC Guru (106K reputation)SSC Guru (106K reputation)SSC Guru (106K reputation)SSC Guru (106K reputation)

Group: General Forum Members
Points: 106309 Visits: 9730
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
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