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...

PMDV: Lessons Learned

On Monday I "released" Version 0.1. Not only did I blog about it, I sent private emails to people I know asking them to take a look. I'm going to summarize the good and the bad here.

SYS
I had planned all along to put the objects in the SYS schema as Oracle treats it differently. By differently I mean that it's more secure than any other user/schema. You cannot create objects in the schema as another user, even with DBA privileges. Same goes for executing code, you need an explicit GRANT from SYS in order to run a package. I can't think of one off the top of my head, but around on or after 9i, DBMS_SESSION needed to be granted explicitly to users.

What's the lesson learned? Well, if you create objects in the SYS schema you now have an unsupported database. Yikes. I don't want that to happen and I will be updating the instructions tonight to reflect that (not that anyone has gone and thrown it in production yet).

What's in a Name?
Poor Man's Data Vault was not the best choice of names. I knew that all along really, but stuck with it just because. Someone suggested that the general concept might be related a bit more to Audit Vault; my only exposure to it was at COLLABORATE.

If there are any good ideas out there, let them fly. The only constraint? You have to use the letters P, M, D and V. Why? Because I've prefixed everything with that. :)

Documentation
Documentation is hard, but I tried to get down as much as possible on the wiki. A friend and former colleague (twice), told me it sucked. It wasn't very descriptive or clear as to the purpose. I don't think it sucked, but it wasn't good or even great. Mediocre maybe?

Reason for Being
Maybe writing software is hard? Not the coding part necessarily, all the "extraneous" stuff. Documentation. User Expectations. Documentation. Testing (what?). Documentation. Critiques.

Despite the "smallness" of the project, I have a better appreciation for what Jake talks about over at the AppsLab. I can only imagine what it's like doing it at that scale.

Final Thoughts
I think I will continue on with the project.

I believe the goals have changed a bit so I'll need to adjust. Actually, I'll need to refactor my goals.

I will try to use the criticism wisely and allow that the help shape the new goals.

I'll have to change the target audience...well, define one. I had bigger shops (multiple DBAs) in mind initially or a place that needed to be SOX compliant. Maybe it should be small or medium size shops? I don't know.

Don't be afraid to leave comments, good and bad.

DBA: Poor Man's Data Vault

Nothing against women, so don't start with me. By "Man" I mean (hu)Man. Though there are plenty out there that would argue against any DBA, in any way, resembling a human.

Back in March I started this project on Google Code. Here I am 4 months later and I've finally gotten around to it.

What is it?
Version 1.0, or better, Version 0.1, will simply require you to set a context prior to deployment. All that means is this:

CJUSTICE@TESTING>BEGIN
  pmdv_work_pkg.create_work
    ( p_name => 'TICKET #44', 
      p_description => 'CLEANING UP CJUSTICE SCHEMA' );
END;
/

What's the big deal with that?
Well, it depends. The idea was originally sparked by watching my DBAs deploy my projects at a previous job. After deployment, they would run this script, enter the ticket number, start and stop time and it would spool off this report. I never looked at the report, but guessed that it had something to do with Security or Auditing. So I made the grand leap to assume it was all the objects that were affected by the deployment. It was all an assumption though as I was, on occasion, asked to provide details on objects that I deployed by the Security team.

What does it do?
It's pretty simple actually. One trigger, ON DATABASE BEFORE DDL is set up. That calls a package with the relevant exceptions, and determines whether the context has been set (if the other exceptions are not met). If all the exceptions are met and the context is set, you can deploy your changes (DDL). If not, you get a pretty error message like this:

CJUSTICE@TESTING>@packages/pmdv_work_pkg.pkb
CREATE OR REPLACE
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-20001: PMDV:  DEPLOYMENTS NOT ALLOWED!
ORA-06512: at line 14

When you first create your work (order), the start date and end date are set (end_date defaults to 30 minutes, but time can be added if necessary). Also, all INVALID objects are captured prior to deployment. Once you are finished, you issue the following commands:

CJUSTICE@TESTING>EXEC pmdv_work_pkg.end_work;

COMMIT;

When you issue the END_WORK command, the INVALID objects are captured again (DURING), the work record updated, then any changed objects during the deployment window are captured (from DBA_ALL_OBJECTS.LAST_DDL_TIME).

For those of you worried about putting such a thing in production, I am also providing a trigger (AFTER DDL) and a table to capture the DDL in your environment. That way you can note the exceptions and add them to the code before ever throwing such a thing in production. Not that I think many of you would do that...especially without more rigorous testing at the very least.

Testing is my next step. I know, it's backwards. I just want this out the door. I want someone to look at it and say, "Hey Chet, that's a piece of crap." or "You're nuts if you think I would ever put this on a production system!"

I hear you. Testing will be done. Test cases will also be provided so that you can verify the results as well. Hopefully, if enough of you nit-picking, anal-retentive DBAs take a look at it and critique it (good and bad), I can make something of it. Something that would be useful to everyone. So go crazy on it please.

Here's the package spec for PMDV_WORK_PKG:

FUNCTION CAN_DEPLOY_DDL RETURNS BOOLEAN
 Argument Name   Type   In/Out Default?
 ------------------------------ ----------------------- ------ --------
 P_ORA_SYSEVENT   VARCHAR2  IN
 P_ORA_LOGIN_USER  VARCHAR2  IN
 P_ORA_INSTANCE_NUM  NUMBER   IN
 P_ORA_DATABASE_NAME  VARCHAR2  IN
 P_ORA_DICT_OBJ_NAME  VARCHAR2  IN
 P_ORA_DICT_OBJ_TYPE  VARCHAR2  IN
 P_ORA_DICT_OBJ_OWNER  VARCHAR2  IN

CAN_DEPLOY_DDL is used by the trigger, PMDV_BEFORE_DDL to determine whether or not the DDL is allowed. Currently, I have 3 exceptions:
1.  If the ORA_LOGIN_USER = SYS. DDL OK.
2.  If the ORA_LOGIN_USER IS NULL. I noticed this behavior on a 10g instance. DDL OK.
3.  By default Oracle does not allow objects to be created in the SYS schema. Unless your system is compromised in some way, it is reasonably safe to assume that if the ORA_DICT_OBJ_OWNER = SYS, DDL is OK.
4.  PMDV_CONTEXT is set. DDL is OK.

If you see anything in that list that jumps out at you, please let me know. I absolutely do not want to introduce something into the wild that could be harmful.

FUNCTION CREATE_WORK RETURNS NUMBER
 Argument Name   Type   In/Out Default?
 ------------------------------ ----------------------- ------ --------
 P_NAME    VARCHAR2  IN
 P_DESCRIPTION   VARCHAR2  IN
 P_ESTIMATED_TIME  DATE   IN     DEFAULT

This gets it all started. It accepts 2 strings, they can be anything, there is no validation check on them. P_ESTIMATE_TIME defaults to 30 minutes. You can either create the work (order) with the default, it give yourself an hour, or however much time you need really.

PROCEDURE END_WORK

This ends your session, captures INVALID and CHANGED objects and clears the context.

PROCEDURE EXTEND_DEPLOYMENT_WINDOW
 Argument Name   Type   In/Out Default?
 ------------------------------ ----------------------- ------ --------
 P_TIME_TO_ADD   NUMBER   IN

Kind of self explanatory. Run this in the same (SQL*PLus) session to extend your window.

PROCEDURE JOB_LISTENER

When you issue the CREATE_WORK statement, the PMDV_LISTENER job is enabled and runs once a minute to check if your session has expired. If you finish in 5 minutes, you don't necessarily have to issue the END_WORK call. The job will expire the work (order) after the allotted time.

PROCEDURE RECONNECT
 Argument Name   Type   In/Out Default?
 ------------------------------ ----------------------- ------ --------
 P_CLIENT_IDENTIFIER  VARCHAR2  IN

RECONNECT allows you to open up another session and re-enable the context (i.e. allow you to deploy). You can find this information in PMDV_WORK.CLIENT_IDENTIFIER column.

Since I'm using DBMS_SESSION.UNIQUE_SESSION_ID, you can only have 1 deployment per SQL*Plus session. All you need to do to get around that is DISCONNECT and login again.

For the love of Pete, give me some feedback. Is this something you could ever use? Am I silly (well, you don't have to answer that specifically) for thinking this up? Any and all comments will be most appreciated. I've spent a significant amount of time over the last couple of weeks getting this together...hopefully to some end.

Where do I find this?
The code can be found here. I'll be wrapping it up all nice and pretty over the next couple of days. I'll create a download package as well. The wiki page can be found here.

Oh yeah, did I mention, I built an Apex front end for it?

Here's what that looks like.

The Work (Order) Screen:


And the Work (Order) Details Screen:

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).

Poor Man's Data Vault

That's what I'm calling it anyway.

Part I.

Part II.

Part III.

I've started the project on Google Code

The goal is simple, have a simple, easy to use version of Oracle's Data Vault. By no means is this trying to replace their product, it's just a simple solution to helping lockdown your Oracle database.

I've read through a bit of Oracle's documentation on Data Vault and it seems like pretty cool stuff. I started this as something simple for our environment and was told it resembles (loosely) Data Vault by a friend.

So check it out if you want. Feedback, both good and bad, is welcome and appreciated. As the Generalissimo stated, I'm a big boy.