Tuesday, April 7, 2009

Poor Man's Data Vault - Update

It's been a little over a month since I've written anything about it and seeing as how I have not posted anything Oracle related in what seems like years.

A quick reminder, Poor Man's Data Vault, PMDV for short, is a very basic tool to allow you lockdown your production environment. In essence, you are required to "submit" a ticket before any DDL is allowed in your specified schemas. When you "submit" or run a procedure you must supply a ticket number or description. Obviously there is no way to verify (yet) that this is an actual ticket, but it is a start. The ideal in the regard would be to query your ticketing/bug tracking database to validate. Currently we have a pretty sweet integration of Subversion, Fisheye, Bamboo and Jira. If we add the ticket number to the Subversion comments, we can then see (via Fisheye) what code is attached and even better do a diff right in the browser.

So, using Jira would be nice, but it's out of scope at this time.

Here's the final model I came up with:



PMDV_WORK is the driving table. It stores the records on the deployment or fix. PMDV_INVALID_OBJECTS takes a snapshot before, during and after the deployment of objects in an INVALID state. PMDV_CHANGED_OBJECTS will capture all the objects that were affected by the deployment (new and altered).

I'm starting with those 3 (because I forgot what I was going to do with PMDV_PRIVILEGES). I have created those 3 tables and checked them into source control (Google Code). You can find the project here. If you would like to join the "project" (yes, it's in quotes because I haven't done a whole lot but would love to actually finish one. Besides, I need all the help I can get.) drop me a line chet at oraclenerd dot com (does that really work? spelling out the email address I mean).

No comments: