Wednesday, October 20, 2010

DDL Auditing - Revisited

Of course I can't find the post(s) that I read recently, but they were all concerning auditing DDL. The most desirable solution is to use the built in auditing providing by Oracle...the only problem with auditing, in general, is that no one really monitors it. I'm sure there are numbers out there (read about that too, but can't seem to find the post). Oh wait, I found it, it was a video that I got via the Database Insider newsletter. You can see it here.

The gist of that video (as cheesy as it may be), is that some broker figures out a way to submit trades with his account that don't really exist. He's paid the commission, automatically, some $10 million. The Risk/Security Officer talks about the amount of time (months) they spent trying to track down how it was done. They were at least smart enough to have auditing turned on, however, they never monitored it. Somehow, all this led me to DDL auditing. No idea how, it just did.

After thinking about it for a few days, I realized the similarity between that and my PMDV project I did last year. It only made it to version 0.1, but this gave me the idea to revisit it. Maybe it was just poor naming/branding (PMDV stands for Poor Man's Data Vault)? Maybe it did just suck eggs? Tough to say for sure.

To sum it up quickly, it required a "ticket number" or "work order" to set an application context. Without that application context set, you could not perform any DDL. On top of that, it would record all the DDL changes performed during that deployment window and give you decent reporting through an APEX front end. Like I said in one of those other posts, a former company of mine had to provide the security team with audit reports for each deployment. While the built-in auditing would capture all this, what I created will at least capture that in one place and provide the objects that change.

So perhaps this falls into the Change Management landscape (which is on my radar again...big, big topic).

Guess what, I just remembered how I came to remember this...a conversation on Twitter with @hillbillytoad and @krisrice...that's what led me to the DDL trigger and auditing. I think Kris suggested that a DDL trigger should be installed to prevent little old me from doing damage. :)

That link led to here and then a few other places...

No comments: