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?