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?
WHERE grantee = ora_login_user
AND granted_role = 'DBA';
If they do have the role, it exits right then.
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
CREATE OR REPLACEIn preparation for this post I found two very similar articles by Arup Nanda and Tom Kyte. I think they've trained me well.
l_user VARCHAR2(30) := ora_login_user;
l_owner VARCHAR2(30) := ora_dict_obj_owner;
l_object_name VARCHAR2(128) := ora_dict_obj_name;
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 );