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.
Labels: database, ddl, oracle, security
