system level trigger – Who is running risky DDL

Let me show how to kick DBA ass if he is rying to run risky DDL, i.e alter table struture in production database.
 
Oracle allow us to create system level trigger. So I can stop DBA doing that, don’t allow this happen, always.
 
Example
create or replace trigger tri_forbidden_ddl_on_schema before ddl on database
    begin
       if ora_login_user = ‘SYS’ then
               if ora_dict_obj_owner = ‘PROTECTED_SCHEMA’ then
                       insert into risky_DDL_history(ora_sysevent,ora_login_user,ora_dict_obj_type,ora_dict_obj_owner,ora_dict_obj_name) values (ora_sysevent,ora_login_user,ora_dict_obj_type,ora_dict_obj_owner,ora_dict_obj_name);
                       Raise_application_error(-20000, ‘SYSDBA DDL on PROTECTED_SCHEMA is not allowed’);
               end if;
       end if;
    end;
 /
 
alter table PROTECTED_SCHEMA.t1 add tt number;
alter table PROTECTED_SCHEMA.t1 add tt number
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-20000: SYSDBA DDL on PROTECTED_SCHEMA is not allowed
ORA-06512: at line 5

Advertisements

About Pang

Oracle DBA working in database, fusion middleware and identity management and cloud.
This entry was posted in Security. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s