Pete Finnigan's Oracle Security Weblog

If i want to create layered security in a database around something such as specific data or to protect access to a resource or perhaps control access to a privilege then we can create a security layer using standard features BUT the next thing we should think about if we create a solution is someone (a hacker) then turning off parts of that solution.

Therefore we need to then create a protection layer that protects the security we have just implemented. We can even then create another layer that protects the protection layer.

Finally we must also audit all of the layers to test if anyone is trying to access the thing we are protecting or trying to change the security layer to beat the protection.

So, as part of this goal we created DDL triggers as part of the security protection so I want to see if there is a way to detect if someone disables a DDL trigger.

Let us create a simple DDL trigger that can capture all DDL to see if we can catch the disabling of a trigger. Actually not all DDL is caught by a DDL trigger as we already know that ALTER SYSTEM whilst is described as DDL is not caught.

Lets create a DDL trigger that will debug out details of actions:

SQL> get ddl
  1  create or replace trigger atk_ddl
  2  after ddl on database
  3  declare
  4     lv_sql ora_name_list_t;
  5     lv_stmt varchar2(32767);
  6     lv_n pls_integer:=0;
  7  begin
  8     --
  9     lv_n:=ora_sql_txt(lv_sql);
 10     for i in 1 .. lv_n loop
 11             lv_stmt:=lv_stmt||lv_sql(i);
 12     end loop;
 13     --
 14     dbms_output.put_line('DDL>'||lv_stmt);
 15* end;
SQL> @ddl

Trigger created.

SQL>

Now run a DISABLE command on an other trigger and see if its caught:

SQL> sho user
USER is "SYS"
SQL> set serveroutput on
SQL> alter trigger atk_sec_logon disable;
DDL>alter trigger atk_sec_logon disable

Trigger altered.

SQL> alter trigger atk_sec_logon enable;
DDL>alter trigger atk_sec_logon enable

Trigger altered.

SQL>

Yes, fantastic, we can detect disabling of a system trigger, what about disabling our DML trigger. First find out the owner of our trigger:

SQL> col owner for a30
SQL> col trigger_name for a30
SQL> l
  1* select owner,trigger_name from dba_triggers where trigger_name like 'ATK%'
SQL> /

OWNER                          TRIGGER_NAME
------------------------------ ------------------------------
ATK_SEC                        ATK_DML
SYS                            ATK_DDL
SYS                            ATK_SEC_ALTER
SYS                            ATK_SEC_CREATE
SYS                            ATK_SEC_DROP
SYS                            ATK_SEC_GRANT
SYS                            ATK_SEC_LOGON
SYS                            ATK_SEC_TRUN

8 rows selected.

SQL>

Now we can try and disable and re-enable this trigger:

SQL> alter trigger atk_sec.atk_dml disable;
DDL>alter trigger atk_sec.atk_dml disable

Trigger altered.

SQL> alter trigger atk_sec.atk_dml enable;
DDL>alter trigger atk_sec.atk_dml enable

Trigger altered.

SQL>

Yes, we can detect system triggers and DML triggers being disabled. Now we can write a new DDL trigger that can block disable of a trigger such as the DML trigger used in out AUDSYS.AUD$UNIFIED experiment. Now create a new trigger that can detect the disabling of a trigger and block it. First create a version that will find a trigger and report it. We will extend it soon:

SQL> get disable
  1  create or replace trigger atk_disable
  2  after ddl on database
  3  declare
  4     lv_sql ora_name_list_t;
  5     lv_stmt varchar2(32767);
  6     lv_n pls_integer:=0;
  7     atk_disable exception;
  8     pragma exception_init(atk_disable,-20012);
  9  begin
 10     --
 11     lv_n:=ora_sql_txt(lv_sql);
 12     for i in 1 .. lv_n loop
 13             lv_stmt:=lv_stmt||lv_sql(i);
 14     end loop;
 15     if(ora_dict_obj_type = 'TRIGGER') then
 16             if(ora_dict_obj_name like 'ATK%') then
 17                     dbms_output.put_line('Found '||ora_dict_obj_type||'.'||ora_dict_obj_name);
 18             end if;
 19     end if;
 20     --
 21     dbms_output.put_line('DDL>'||lv_stmt);
 22* end;
SQL> @disable

Trigger created.

SQL>

Now test it to make sure we can find a trigger and also its our trigger:

SQL> set serveroutput on
SQL>  alter trigger atk_sec.atk_dml disable;
Found TRIGGER.ATK_DML
DDL>alter trigger atk_sec.atk_dml disable

Trigger altered.

SQL>

Fantastic, we captured the fact that the DML trigger was being disabled. Now lets change the code in the disable trigger to prevent this for our triggers:

SQL> get disable
  1  create or replace trigger atk_disable
  2  after ddl on database
  3  declare
  4     lv_sql ora_name_list_t;
  5     lv_stmt varchar2(32767):='*';
  6     lv_n pls_integer:=0;
  7     atk_disable exception;
  8     pragma exception_init(atk_disable,-20012);
  9  begin
 10     --
 11     lv_n:=ora_sql_txt(lv_sql);
 12     for i in 1 .. lv_n loop
 13             lv_stmt:=lv_stmt||lv_sql(i);
 14     end loop;
 15     if(ora_dict_obj_type = 'TRIGGER') then
 16             if(ora_dict_obj_name like 'ATK%') then
 17                     dbms_output.put_line('Found '||ora_dict_obj_type||'.'||ora_dict_obj_name);
 18                     if(instr(upper(lv_stmt),'DISABLE')<>0) then
 19                             raise atk_disable;
 20                     end if;
 21             end if;
 22     end if;
 23     --
 24     dbms_output.put_line('DDL>'||lv_stmt);
 25* end;
SQL> @disable

Trigger created.

SQL>

Note that we initialise the SQL string to * to prevent a NULL search. It does not matter if the SQL string now starts with *. Now run a DISABLE on our trigger:

SQL> set serveroutput on
SQL> alter trigger atk_sec.atk_dml disable;
Found TRIGGER.ATK_DML
alter trigger atk_sec.atk_dml disable
              *
ERROR at line 1:
ORA-04088: error during execution of trigger 'SYS.ATK_DISABLE'
ORA-00604: error occurred at recursive SQL level 1
ORA-20012:
ORA-06512: at line 17


SQL>

Fantastic, this works, we can now block the disabling of our DML and system triggers. This is one further layer of protection that we can add to protect our security interface on our table.

#oracleace #sym_42 #oracle #database #security #grants #ddl #trigger #protect #hacking #databreach