Monday, December 15, 2008

DDL Triggers and Security

I've mentioned in the past that I don't particularly care for trigger. That's a shocker, I know, because Tom Kyte loves them so much (here, here, and here).

We're trying to move to a named account approach (for developers too) and the only way to do that is to grant system level privileges. That's not good.

I had read about DDL triggers in the past but wasn't sure exactly how they could be used. Well, let's just say you can do quite a lot. There are database level events and client level events. The database level events include: STARTUP, SHUTDOWN, DB_ROLE_CHANT and SERVERERROR. A few of the client level events include (BEFORE and AFTER): DDL, LOGOFF, LOGON, GRANT, DROP, CREATE, ALTER, etc.

Being a developer my entire career (with certain DBA skills), I had never quite wandered into this territory. I was rather impressed.

The three I thought would make the most sense were ALTER, DROP and CREATE.

If you have system level privileges my biggest concern would be CREATE ANY PROCEDURE and EXECUTE ANY PROCEDURE (though the latter is not one that is currently granted). I've used those 2 privileges to get the DBA role in the past.

Here's what I need to do:
1. Does the user have the DBA role?
SELECT grantee
INTO l_grantee
FROM dba_role_privs
WHERE grantee = ora_login_user
AND granted_role = 'DBA';

If they do have the role, it exits right then.
If not,
2. Where is the user trying to create/drop/alter an object? Is it in an approved schema? Their own?
3. raise_application_error if it's not approved or not their own object

Easy enough:
TRIGGER system.no_create_in_dba_schema
l_user VARCHAR2(30) := ora_login_user;
l_owner VARCHAR2(30) := ora_dict_obj_owner;
l_object_name VARCHAR2(128) := ora_dict_obj_name;
l_dba VARCHAR2(30);
l_can_create BOOLEAN;
--is DBA?
SELECT grantee
INTO l_dba
FROM dba_role_privs
WHERE granted_role = 'DBA'
AND grantee = l_user;

WHEN no_data_found THEN
l_can_create := can_user_create_alter_drop
( p_user => l_user,
p_owner => l_owner );

IF l_can_create = FALSE THEN
raise_application_error( -20001, 'cannot CREATE objects in ' || l_owner );
END no_create_in_dba_schema;
show errors
In preparation for this post I found two very similar articles by Arup Nanda and Tom Kyte. I think they've trained me well.


SydOracle said...

I'd argue CREATE ANY TRIGGER is worse than CREATE AND PROCEDURE. With the latter you still have to figure out a way to get the procedure executed.
CREATE ANY TRIGGER could be attached to fire code on an insert/update to a table or, as you see, on logon.
Of course SELECT ANY DICTIONARY (pre 11g) lets you see the password hashes which may be a soft target....
Have you looked into proxy, so users log into the schema accounts with their own identities

oraclenerd said...

Good point. I remember that part about CREATE ANY TRIGGERS from David Litchfield's book (always good to know the ways around so you can prevent skullduggery).

I'll have to check out the proxy thing, thanks Gary.