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


The Future of Auditing


The Future of Auditing

Author
Message
lshanahan
lshanahan
SSChasing Mays
SSChasing Mays (616 reputation)SSChasing Mays (616 reputation)SSChasing Mays (616 reputation)SSChasing Mays (616 reputation)SSChasing Mays (616 reputation)SSChasing Mays (616 reputation)SSChasing Mays (616 reputation)SSChasing Mays (616 reputation)

Group: General Forum Members
Points: 616 Visits: 436
A cautionary real-world tale:

Our company uses a mission critical system from a vendor. I'm not sure of the exact architecture, but in general there is a production database that is copied to and overwrites a "mirror" testing database every night. Effectively the mirror is a day-old copy of production intended for testing and training purposes. The two databases are accessed from similar but very distinct Java clients.

Anyway, we were informed the other day that somehow their server that handles the "mirror" transactions inadvertantly got pointed at the production data(!). Fortunately it was early in the morning and only a few people were using it, however one of our staff was doing a competency evaluation on the mirror involving data for a 29 CFR 11-compliant process.

To their credit, the vendor did inform us, but then proceeded to resolve the issue by rolling back the erroneous transactions that were made as if they never happened. BIG NO-NO. 29 CFR 11 regulations require that data entry errors be handled in a manner that shows the erroneous data AND the corrections. You don't simply "white-out" or overwrite/roll back errors.

For reasons I can't divulge, there was a remote but very real possibility people's health or even their lives could have been put at risk by this situation.

Not all applications and databases necessarily rise to this level of impact, but it is certainly the case that the time gap between when data is collected and when it is used has fallen to near zero, so the accuracy and integrity of data - which includes auditability - has become much more important because the real-world consequences can in many cases be substantial.

____________
Just my $0.02 from over here in the cheap seats of the peanut gallery - please adjust for inflation and/or your local currency.
Miles Neale
Miles Neale
Hall of Fame
Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)

Group: General Forum Members
Points: 3026 Visits: 1694
The only secure provable way we have been able to provide auditable information is to include the audit function as part of the development cycle. This has been driven by federal and state legal requirements of types of data and processes we must perform. We log, keep audit trails, and have write/update triggers that cut before and after images of parts of many transactions. This is the only provable way we are able to prove the transactions.

Windows logs and server logs help but in the end if the law requires you to keep the minutia then the minutia is what you collect.

M...

Not all gray hairs are Dinosaurs!
Iwas Bornready
Iwas Bornready
SSChampion
SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)

Group: General Forum Members
Points: 13712 Visits: 885
Haven't been able to make it work with any real value for us.
kiwood
kiwood
SSC-Enthusiastic
SSC-Enthusiastic (125 reputation)SSC-Enthusiastic (125 reputation)SSC-Enthusiastic (125 reputation)SSC-Enthusiastic (125 reputation)SSC-Enthusiastic (125 reputation)SSC-Enthusiastic (125 reputation)SSC-Enthusiastic (125 reputation)SSC-Enthusiastic (125 reputation)

Group: General Forum Members
Points: 125 Visits: 88
While I agree that sys admin actions on production machines should be tracked, as a developer I wish there was an easier solution to logging user behavior. It has been a few years, but shortly after Change Data Capture became available I was building an application where we wanted to track who changed what. The DBA thought this would be a great place to try out CDC. It all seemed great -- until I needed modify the schema. Then all that goodness silently broke leaving no tracking. This to me is the kind of story that just shouldn't happen.
Yet Another DBA
Yet Another DBA
SSC-Addicted
SSC-Addicted (475 reputation)SSC-Addicted (475 reputation)SSC-Addicted (475 reputation)SSC-Addicted (475 reputation)SSC-Addicted (475 reputation)SSC-Addicted (475 reputation)SSC-Addicted (475 reputation)SSC-Addicted (475 reputation)

Group: General Forum Members
Points: 475 Visits: 1234
Still amazes me that developers want to do all the auditing via the application.
Tend to get a cross-eyed response when I ask them how they want to work auditing out in a CQS applications. :-P
Gary Varga
Gary Varga
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16162 Visits: 6532
Feel free to correct me if I am wrong (please!!!) but I see little or no movement in this in the last 3 years.

Gaz

-- Stop your grinnin' and drop your linen...they're everywhere!!!
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