DDL Event Security in Oracle Database - Page 2

Setup

1. One SYS owned tables are created to hold information regarding the objects that should be prevented from structural changes. Please note that if you implement this security globally, you may not require the table below, but if you intend to do it for only the application tables and allow other temporary objects in the database to be changed, you will have to maintain a master such as the one below to differentiate such tables.

For example, my site runs Oracle Applications 11i. There are times when reporting or custom objects have to be modified for urgent implementation of a change (e.g.: new adhoc report requirement). As these are custom objects and are meant for data extraction for reporting purpose, I allow some room for structure changes here. However, nocompromise can be done with the core application tables, so these are permanently locked against all structural changes. I maintain a table such as the one below to hold application related objects and important custom objects.

create table az_secure_obj
(owner        varchar2(30) not null,                  --Owner of the object.
 obj_name     varchar2(30) not null,                  --Object name
 obj_type     varchar2(30) not null,                  --Object type  
 event_create varchar2(1) default 'N' not null,       --Allow CREATE command
 event_alter  varchar2(1) default 'N' not null,       --Allow ALTER command
 event_drop   varchar2(1) default 'N' not null,       --Allow DROP command
 event_trunc  varchar2(1) default 'N' not null,       --Allow TRUNCATE command
 status       varchar2(10) default 'Active' not null) --Enable/Disable the security.
/

Here, the object type can be a PROCEDURE, FUNCTION, PACKAGE, PACKAGE BODY, SYNONYM, TABLE, TRIGGER, INDEX or a VIEW.

The columns starting with EVENT_ are meant to track if CREATE, ALTER, DROP or TRUNCATE are allowed. There may be a need to allow truncating of a table but prevent other changes; the EVENT_TRUNC can be set to 'Y' and the rest of the events can be set to 'N'.

Similarly there may be a need for a routine that should be allowed to be re-compiled (ALTER) while re-creating is prevented (CREATE OR REPLACE ..). The EVENT_ALTER can be set to 'Y' and other events prevented.

The STATUS column states whether the security is 'Active' for an object or not. This can be set to 'Inactive' to disable the security.

2. The following system event trigger is created as SYS user.

create or replace trigger az_secure_obj_trg
before create or alter or drop or truncate on database
declare
  l_errmsg varchar2(100):= 'enabled for DDL Event Security: ' 
                           || 'You cannot do structural changes to this object.';
  l_chk    pls_integer;
begin
  if ora_sysevent = 'CREATE' then
    select 1
    into   l_chk  
    from   az_secure_obj
    where  owner = ora_dict_obj_owner
    and    obj_name = ora_dict_obj_name
    and    obj_type = ora_dict_obj_type
    and    event_create = 'N'
    and    status = 'Active'
    and    rownum = 1;

    raise_application_error(-20001, ora_dict_obj_owner || '.' || ora_dict_obj_name || ' ' || l_errmsg);

  elsif ora_sysevent = 'ALTER' then
    select 1
    into   l_chk  
    from   az_secure_obj
    where  owner = ora_dict_obj_owner
    and    obj_name = ora_dict_obj_name
    and    obj_type = ora_dict_obj_type
    and    event_alter = 'N'
    and    status = 'Active'
    and    rownum = 1;

    raise_application_error(-20001, ora_dict_obj_owner || '.' || ora_dict_obj_name || ' ' || l_errmsg);

  elsif ora_sysevent = 'DROP' then
    select 1
    into   l_chk  
    from   az_secure_obj
    where  owner = ora_dict_obj_owner
    and    obj_name = ora_dict_obj_name
    and    obj_type = ora_dict_obj_type
    and    event_drop = 'N'
    and    status = 'Active'
    and    rownum = 1;

    raise_application_error(-20001, ora_dict_obj_owner || '.' || ora_dict_obj_name || ' ' || l_errmsg);

  elsif ora_sysevent = 'TRUNCATE' then
    select 1
    into   l_chk  
    from   az_secure_obj
    where  owner = ora_dict_obj_owner
    and    obj_name = ora_dict_obj_name
    and    obj_type = ora_dict_obj_type
    and    event_trunc = 'N'
    and    status = 'Active'
    and    rownum = 1;

    raise_application_error(-20001, ora_dict_obj_owner || '.' || ora_dict_obj_name || ' ' || l_errmsg);
  end if;

exception
  when no_data_found then
    null;
end;
/

3. If there is a need to disable DDL event security for a particular object or for all objects, the master table STATUS column should be updated as 'Inactive'. As the System trigger looks at this table to enable security, it will skip all objects with an inactive status.

How this setup works

For example, I enable this security for a custom table called AZ_CATENT. The following insert will add this object to the master.

insert into az_secure_obj
          (owner, obj_name, obj_type, event_create, event_alter, event_drop, event_trunc, status) 
    values('APPS', 'AZ_CATENT', 'TABLE', 'N', 'N', 'N', 'N', 'Active');

commit;

Now structural changes to this table fail.

SQL> drop table az_catent;
drop table az_catent;
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-20001: APPS.AZ_CATENT enabled for DDL Event Security: You cannot do structural changes to this object.
ORA-06512: at line 43

SQL> alter table az_catent add flg varchar2(1);
alter table az_catent add flg varchar2(1)
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-20001: APPS.AZ_CATENT enabled for DDL Event Security: You cannot do structural changes to this object.
ORA-06512: at line 30


orAP>truncate table az_catent;
truncate table az_catent
               *
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-20001: APPS.AZ_CATENT enabled for DDL Event Security: You cannot do structural changes to this object.
ORA-06512: at line 56

The security is in place now.

Conclusion

Please be careful when working with system triggers. Do not leave an event trigger with compilation errors. This would throw errors for all structural changes being carried out.

SQL> truncate table abc;
truncate table abc
               *
ERROR at line 1:
ORA-04098: trigger 'SYS.AZ_SECURE_OBJ_TRG' is invalid and failed re-validation

DDL Event Security is one of the many alternatives that can be used to prevent mishaps, but this does not necessary mean that the basic security established by password, roles and privileges take a back seat; these should not be compromised for any other alternatives. Use the above feature to suit your setup requirements.

» See All Articles by Columnist Amar Kumar Padhi