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:

CREATE OR REPLACE 
TRIGGER system.no_create_in_dba_schema
  BEFORE CREATE
  ON DATABASE
DECLARE
  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;
BEGIN
  --is DBA?
  SELECT grantee
  INTO l_dba
  FROM dba_role_privs
  WHERE granted_role = 'DBA'
    AND grantee = l_user;

EXCEPTION
  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 IF;
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.