Thursday, February 19, 2009

No DDL in Production Environment - III

Part I.

Part II.

Alright, I've officially hit scope creep.

We've recently implemented VPD to further protect senstive data. We're using roles to manage the security as opposed to user level privileges. We as DBAs don't have (nor want) access to that sensitive data, but we're DBAs, we can do whatever we want right?

Anyway, by default we will NOT have these roles. They will only be enabled when needed. How do you manage that though? Auditing is already turned on so that's a good thing. But it would also be nice to know when someone granted one of these secure roles to a user or role. What better way than event based triggers?

I've come to appreciate triggers of late, at least the database or event level triggers. I still think application triggers are a means of last resort.

How does this tie in with No DDL in Production? Of course a GRANT statement is DDL but it's more of an access or security type. It's outside of the original intent of this "project." It would nice to be able to track it though. And shouldn't you be required to have a ticket prior to giving someone one of these secure roles?

So I've added 2 new tables, SECURE_ROLES and SECURE_GRANTS.

SECURE_ROLES
Stores the role name of...you guessed it, your secure roles.

    SECURE_GRANTS
    Tracks the history of GRANTs on those secure roles. If temporary, it would be easy enough to create a job to scan the table and send out alerts. Which just made me think of another attribute, amount of time needed. Perhaps to default at 1 hour or something.

    Here's the model so far:



    I've actually left off 3 other tables I just added. Serious scope creep. The good thing is I haven't written a lick of code yet. Let's call this the requirements/design phase.

    I think I need a better name for this project too. Any suggestions out there?

    4 comments:

    Tom said...

    Hmmm. A Free Version of Database Vault?

    oraclenerd said...

    shut up! really? is that what database vault does?

    great, now I'm going to have to read up on it.

    Thanks Tom.

    Anonymous said...

    Ah, that's what I was going to say ;-)

    It would be fair to say that DV is a little more rich in features, too.

    Added to that, Database Vault is going to do a much better job of stopping DBAs from seeing and doing things they don't want to. Yes, the owner of the oracle account can always do anything *but* they would have to shut down the instance, relink the code and restart it and that would be much trickier than working around what you're proposing here.

    oraclenerd said...

    barnacles...here i was thinking (bad news in itself) that i was on to something cool. ah well.

    I'll continue down the path but scale back. Keep is simple right?