<a href="http://g.adspeed.net/ad.php?do=clk&zid=15220&wd=468&ht=60&pair=as" target="_blank"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15220&wd=468&ht=60&pair=as" alt="i" width="468" height="60"/></a>
Log in
::
Register
::
Not logged in
Home
Tags
Articles
Editorials
Stairways
Forums
Scripts
Videos
Blogs
QotD
Books
Ask SSC
SQL Jobs
Training
Authors
About us
Contact us
Newsletters
Write for us
<a href="http://g.adspeed.net/ad.php?do=clk&zid=15491&wd=120&ht=300&pair=as" target="_top"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15491&wd=120&ht=300&pair=as" alt="i" width="120" height="300"/></a>
Recent Posts
Recent Posts
Popular Topics
Popular Topics
Home
Search
Members
Calendar
Who's On
Home
»
Article Discussions
»
Article Discussions by Author
»
Discuss content posted by GSquared
»
Audit Trails and Logging Part I
21 posts, Page 1 of 3
1
2
3
»
»»
Audit Trails and Logging Part I
Rate Topic
Display Mode
Topic Options
Author
Message
GSquared
GSquared
Posted Sunday, June 08, 2008 10:13 PM
SSCoach
Group: General Forum Members
Last Login: 2 days ago @ 1:45 PM
Points: 15,442,
Visits: 9,572
Comments posted to this topic are about the item
Audit Trails and Logging Part I
- 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 #513518
Jeff Moden
Jeff Moden
Posted Monday, June 09, 2008 12:19 AM
SSC-Dedicated
Group: General Forum Members
Last Login: Today @ 10:05 PM
Points: 33,112,
Visits: 27,039
Nicely done, Gus... Can't wait for the others!
--Jeff Moden
"
RBAR
is pronounced "ree-bar" and is a "Modenism" for "
R
ow-
B
y-
A
gonizing-
R
ow".
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."
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Post #513541
Anipaul
Anipaul
Posted Monday, June 09, 2008 12:35 AM
SSCarpal Tunnel
Group: General Forum Members
Last Login: Tuesday, June 11, 2013 5:01 AM
Points: 4,815,
Visits: 1,343
Excellent one... nicely explained. :)
Post #513545
Cbo Spikes
Cbo Spikes
Posted Monday, June 09, 2008 4:23 AM
Grasshopper
Group: General Forum Members
Last Login: Friday, July 31, 2009 11:03 AM
Points: 10,
Visits: 36
Good article.
I use audit trails for management reporting as well as automatically generated emails of certain type of changes. I currently use procs in jobs that run every minute or so, collecting the entire changed row into a seperate table. I then run a another job to analyze the changed rows that have been collected. This analysis produces specific "events" that are of intrest to users. Those events are stored in a seperate table. I then report and send notifications from there.
This works well so far but I'll be looking for your next round of articles for any better ideas!
Post #513607
margo
margo
Posted Monday, June 09, 2008 5:19 AM
SSC Rookie
Group: General Forum Members
Last Login: Wednesday, February 27, 2013 7:26 AM
Points: 41,
Visits: 111
A nice summary, always useful to check what we're doing with what others are saying.
One thing I would add to the downside of "passive" audits... the cost of keeping the log files around. One auditor suggested we keep our sql log files for a rolling 15 months (1 year plus a quarter or something like that). We did some quick math on the disk requirements and were well over a couple terabytes.
True, disk space is becoming a rather inexpensive commodity, but still, it has to be considered. And heaven help you if you need to back sure that is backed up as well and now your doubling your disk requirements and cost, etc.
fwiw.
Post #513628
SuperDBA-207096
SuperDBA-207096
Posted Monday, June 09, 2008 6:05 AM
UDP Broadcaster
Group: General Forum Members
Last Login: Wednesday, January 02, 2013 12:15 PM
Points: 1,443,
Visits: 711
Excellent work! I really enjoyed this one, it's timely as we've been discussing different approaches to auditing.
Post #513644
GSquared
GSquared
Posted Monday, June 09, 2008 6:37 AM
SSCoach
Group: General Forum Members
Last Login: 2 days ago @ 1:45 PM
Points: 15,442,
Visits: 9,572
margo (6/9/2008)
A nice summary, always useful to check what we're doing with what others are saying.
One thing I would add to the downside of "passive" audits... the cost of keeping the log files around. One auditor suggested we keep our sql log files for a rolling 15 months (1 year plus a quarter or something like that). We did some quick math on the disk requirements and were well over a couple terabytes.
True, disk space is becoming a rather inexpensive commodity, but still, it has to be considered. And heaven help you if you need to back sure that is backed up as well and now your doubling your disk requirements and cost, etc.
fwiw.
Active logging (next article, should be tomorrow if I'm not mistaken), with some control over what gets stored, might be a better solution in that case. Either way, it's going to take disk space if you want logging.
- 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 #513662
Mike Dougherty
Mike Dougherty
Posted Monday, June 09, 2008 8:08 AM
SSC-Enthusiastic
Group: General Forum Members
Last Login: Thursday, July 22, 2010 8:59 AM
Points: 110,
Visits: 952
We implemented a passive log while trying to audit SQL injection attempts from our website. We couldn't even begin to consider server-based solutions because the command batch itself was potentially poisonous. I wrote an access interface for the ADO connection, then forced every web transaction to go through that interface. It give me a chance to do basic heuristics before sending the command to SQL (and dumping obvious attack attempts before it gets anywhere near the data). It also writes each transaction to a folder based on page request. After a week it was interesting to review which folders contained the most transactions - you don't realize how much conversation the webserver has with the SQL server until you have a several gigabyte folder to illustrate that fact for you.
In the post-mortem of an event, we tried searching the nearly one million <1k files. It never finished and we never found what we were searching for. Just like backups with untested restores, it's a good idea to test your audit strategy for realistic usability.
Ultimately we turned off most of the 'normal' logging due to the extreme space requirement, but we continue to record those transactions that fail the heuristic. This was useful to recover a false-positive match due to a too-aggressive regex.
Another useful point about auditing is to know your baseline so you can more quickly assess out-of-norm behaviors.
</soapbox> now back to work....
Post #513739
GSquared
GSquared
Posted Monday, June 09, 2008 9:57 AM
SSCoach
Group: General Forum Members
Last Login: 2 days ago @ 1:45 PM
Points: 15,442,
Visits: 9,572
Mike, I would actually classify what you're describing as a form of active auditing. You created a log other than the SQL server transaction log.
Yeah, in any sort of active logging, logging too much and not being able to use it for anything is a common problem. As is huge amounts of disk-use.
For what you were doing, preventing code from ever reaching the database in the first place, passive auditing definitely wouldn't do it. Neither would trigger-based SQL auditing. That's when you have to have the front-end or some other tier do the logging for you.
- 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 #513820
Mike Dougherty
Mike Dougherty
Posted Monday, June 09, 2008 10:20 AM
SSC-Enthusiastic
Group: General Forum Members
Last Login: Thursday, July 22, 2010 8:59 AM
Points: 110,
Visits: 952
GSquared (6/9/2008)
Mike, I would actually classify what you're describing as a form of active auditing. You created a log other than the SQL server transaction log.
yeah... I didn't want to wait for Part II, sorry. :)
Post #513832
« Prev Topic
|
Next Topic »
21 posts, Page 1 of 3
1
2
3
»
»»
Permissions
You
cannot
post new topics.
You
cannot
post topic replies.
You
cannot
post new polls.
You
cannot
post replies to polls.
You
cannot
edit your own topics.
You
cannot
delete your own topics.
You
cannot
edit other topics.
You
cannot
delete other topics.
You
cannot
edit your own posts.
You
cannot
edit other posts.
You
cannot
delete your own posts.
You
cannot
delete other posts.
You
cannot
post events.
You
cannot
edit your own events.
You
cannot
edit other events.
You
cannot
delete your own events.
You
cannot
delete other events.
You
cannot
send private messages.
You
cannot
send emails.
You
may
read topics.
You
cannot
rate topics.
You
cannot
vote within polls.
You
cannot
upload attachments.
You
may
download attachments.
You
cannot
post HTML code.
You
cannot
edit HTML code.
You
cannot
post IFCode.
You
cannot
post JavaScript.
You
cannot
post EmotIcons.
You
cannot
post or upload images.
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved.
Privacy Policy.
Terms of Use.
Report Abuse.
