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 Sunday, June 8, 2008 10:13 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Thursday, December 18, 2014 9:58 AM
Points: 13,872, Visits: 9,600
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
Posted Monday, June 9, 2008 12:19 AM


SSC-Dedicated

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

Group: General Forum Members
Last Login: Today @ 5:57 PM
Points: 35,779, Visits: 32,451
Nicely done, Gus... Can't wait for the others!

--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 #513541
Posted Monday, June 9, 2008 12:35 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Monday, December 15, 2014 4:15 AM
Points: 5,478, Visits: 1,402
Excellent one... nicely explained. :)


Post #513545
Posted Monday, June 9, 2008 4:23 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

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
Posted Monday, June 9, 2008 5:19 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, December 19, 2014 2:13 PM
Points: 42, Visits: 129
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
Posted Monday, June 9, 2008 6:05 AM


UDP Broadcaster

UDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP Broadcaster

Group: General Forum Members
Last Login: Wednesday, January 2, 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
Posted Monday, June 9, 2008 6:37 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Thursday, December 18, 2014 9:58 AM
Points: 13,872, Visits: 9,600
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
Posted Monday, June 9, 2008 8:08 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-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
Posted Monday, June 9, 2008 9:57 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Thursday, December 18, 2014 9:58 AM
Points: 13,872, Visits: 9,600
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
Posted Monday, June 9, 2008 10:20 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-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 »

Add to briefcase 123»»»

Permissions Expand / Collapse