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
SSCommitted
SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)

Group: General Forum Members
Points: 1531 Visits: 438
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
SSCarpal Tunnel
SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)

Group: General Forum Members
Points: 4264 Visits: 1695
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
SSC-Insane
SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)

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

Group: General Forum Members
Points: 197 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
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1057 Visits: 1246
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
One Orange Chip
One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)

Group: General Forum Members
Points: 27460 Visits: 6552
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