Click here to monitor SSC
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in
Home       Members    Calendar    Who's On

Add to briefcase ««12

The Future of Auditing Expand / Collapse
Posted Thursday, December 5, 2013 9:53 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, August 4, 2016 7:09 AM
Points: 252, 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.
Post #1520203
Posted Thursday, December 5, 2013 5:31 PM


Group: General Forum Members
Last Login: Tuesday, January 26, 2016 9:10 AM
Points: 2,602, Visits: 1,694
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.


Not all gray hairs are Dinosaurs!
Post #1520362
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse