Pete Finnigan's Oracle Security Weblog

This is the next part of the series looking at the AUDSYS schema and AUD$UNIFIED table that Oracle has created and protected. In the first part we explored what AUDSYS and AUD$UNIFIED looks like in terms of security; in part 2 we explored how Oracle might have created this security and in part 3 we designed at a high level how we might implement similar security in the database. In the last part 4 I implemented a security design to match as best we could the AUDSYS and AUD$UNIFIED set up functionally from Oracle.

In this short post I want to now see if I can abuse abuse my set up and see how it performs.

First lets do the basics and add data to the table and delete from it via the API as designed:

SQL> exec atk_sec.atk_sec_pack.del;

PL/SQL procedure successfully completed.

SQL> exec atk_sec.atk_sec_pack.ins(3,4);

PL/SQL procedure successfully completed.

SQL> exec atk_sec.atk_sec_pack.ins(5,6);

PL/SQL procedure successfully completed.

SQL>

This works as planned but what about trying direct deletes, inserts and updates on the table as SYSDBA:

SQL> delete from atk_sec.my_tab;
delete from atk_sec.my_tab
                    *
ERROR at line 1:
ORA-20006:
ORA-06512: at "ATK_SEC.ATK_DML", line 20
ORA-04088: error during execution of trigger 'ATK_SEC.ATK_DML'


SQL>
SQL> insert into atk_sec.my_tab(col01,col02) values (1,2);
insert into atk_sec.my_tab(col01,col02) values (1,2)
                    *
ERROR at line 1:
ORA-20006:
ORA-06512: at "ATK_SEC.ATK_DML", line 20
ORA-04088: error during execution of trigger 'ATK_SEC.ATK_DML'


SQL>
SQL> update atk_sec.my_tab set col01=3;
update atk_sec.my_tab set col01=3
               *
ERROR at line 1:
ORA-20006:
ORA-06512: at "ATK_SEC.ATK_DML", line 20
ORA-04088: error during execution of trigger 'ATK_SEC.ATK_DML'


SQL>

So, these all work as planned. We can insert records into the table only via the API and delete only via the API. Also I was logged on as SYSDBA and using UPDATE ANY TABLE and DELETE ANY TABLE and INSERT ANY TABLE and these were blocked so we fixed the issue of ATK_SEC not being a dictionary protected user.

Now, what if we try and truncate the table:

SQL> truncate table atk_sec.my_tab;

Table truncated.

SQL>

Hmm, that is not what we want. Lets fix that with a TRUNCATE DDL trigger:

SQL> get trunc
  1  create or replace trigger atk_sec_trun
  2  before truncate on database
  3  declare
  4     atk_trunc exception;
  5     pragma exception_init(atk_trunc,-20009);
  6  begin
  7     if(ora_dict_obj_owner = 'ATK_SEC' and ora_sysevent='TRUNCATE') then
  8             raise atk_trunc;
  9     end if;
 10* end;
SQL> @trunc
SQL> create or replace trigger atk_sec_trun
  2  before truncate on database
  3  declare
  4          atk_trunc exception;
  5          pragma exception_init(atk_trunc,-20009);
  6  begin
  7          if(ora_dict_obj_owner = 'ATK_SEC' and ora_sysevent='TRUNCATE') then
  8                  raise atk_trunc;
  9          end if;
 10  end;
 11  /

Trigger created.

SQL> 

And if try and truncate the table again:

SQL> truncate table atk_sec.my_tab;
truncate table atk_sec.my_tab
                       *
ERROR at line 1:
ORA-04088: error during execution of trigger 'SYS.ATK_SEC_TRUN'
ORA-00604: error occurred at recursive SQL level 1
ORA-20009:
ORA-06512: at line 6


SQL>

Fixed!

What if we try and create a table or procedure in ATK_SEC:

SQL> create table atk_sec.tab2(col1 number);
create table atk_sec.tab2(col1 number)
*
ERROR at line 1:
ORA-04088: error during execution of trigger 'SYS.ATK_SEC_ALTER'
ORA-00604: error occurred at recursive SQL level 1
ORA-20004:
ORA-06512: at line 6


SQL> create procedure atk_sec.my_proc is begin null; end;
  2  /
create procedure atk_sec.my_proc is begin null; end;
*
ERROR at line 1:
ORA-04088: error during execution of trigger 'SYS.ATK_SEC_ALTER'
ORA-00604: error occurred at recursive SQL level 1
ORA-20004:
ORA-06512: at line 6


SQL>

Also covered, if an attacker is able to create objects in the schema we protected then maybe they can find a way to bypass security.

Lets try and login as ATK_SEC:

SQL> connect atk_sec/atk_sec@//192.168.56.33:1539/xepdb1
ERROR:
ORA-28000: The account is locked.


Warning: You are no longer connected to ORACLE.
SQL>

OK, lets try and add a password and unlock:

SQL> alter user atk_sec identified by atk_sec;
alter user atk_sec identified by atk_sec
*
ERROR at line 1:
ORA-04088: error during execution of trigger 'SYS.ATK_SEC_ALTER'
ORA-00604: error occurred at recursive SQL level 1
ORA-20002:
ORA-06512: at line 7


SQL>

Or can we unlock the account or make it proxiable:

SQL> alter user atk_sec account unlock;
alter user atk_sec account unlock
*
ERROR at line 1:
ORA-04088: error during execution of trigger 'SYS.ATK_SEC_ALTER'
ORA-00604: error occurred at recursive SQL level 1
ORA-20002:
ORA-06512: at line 7


SQL> alter user atk_sec grant connect through aud4;
alter user atk_sec grant connect through aud4
*
ERROR at line 1:
ORA-04088: error during execution of trigger 'SYS.ATK_SEC_ALTER'
ORA-00604: error occurred at recursive SQL level 1
ORA-20002:
ORA-06512: at line 7


SQL>

NO, what about trying to grant CREATE SESSION?

SQL> grant create session to atk_sec;
grant create session to atk_sec
*
ERROR at line 1:
ORA-04088: error during execution of trigger 'SYS.ATK_SEC_GRANT'
ORA-00604: error occurred at recursive SQL level 1
ORA-20003:
ORA-06512: at line 16


SQL>

We did capture some audit events with the standard events of PFCLATK

   ID TIMESTAMP                  DBNAME PAYLOAD                          ERRORTEXT                                                                                            P
----- -------------------------- ------ -------------------------------- ------------------------------------------------------------------------------------------------------------------------ -
   36 22-JUL-2025 15:16:50:21385 XE     EVE_1_9:CHANGES-TO-EXTERNALS     System Privilege {CREATE TRIGGER} used by {SYS} on {SYS.ATK_SEC_CREATE} using IP {192.168.56.1} with error code {0}   N
   36 22-JUL-2025 15:16:50:21385 XE     EVE_1_9:CHANGES-TO-EXTERNALS     System Privilege {CREATE TRIGGER} used by {SYS} on {SYS.ATK_SEC_DROP} using IP {192.168.56.1} with error code {0}     N
   36 22-JUL-2025 15:16:50:21385 XE     EVE_1_9:CHANGES-TO-EXTERNALS     System Privilege {CREATE TRIGGER} used by {SYS} on {SYS.ATK_SEC_GRANT} using IP {192.168.56.1} with error code {0}    N
   37 22-JUL-2025 15:40:43:12690 XE     EVE_1_24:ERROR-LIMIT             Someone on IP Adress {192.168.56.1} has has generated {9} errors for the user {SYS} in the last 30 minutes            N
   38 22-JUL-2025 15:40:54:44356 XE     EVE_1_24:ERROR-LIMIT             Someone on IP Adress {192.168.56.1} has has generated {10} errors for the user {SYS} in the last 30 minutes           N
   39 22-JUL-2025 15:42:02:70611 XE     EVE_1_24:ERROR-LIMIT             Someone on IP Adress {192.168.56.1} has has generated {11} errors for the user {SYS} in the last 30 minutes           N
   40 22-JUL-2025 15:46:50:11734 XE     EVE_1_9:CHANGES-TO-EXTERNALS     System Privilege {ALTER TABLE} used by {SYS} on {ATK_SEC.MY_TAB} using IP {192.168.56.1} with error code {4088}       N
   40 22-JUL-2025 15:46:50:11734 XE     EVE_1_9:CHANGES-TO-EXTERNALS     System Privilege {CREATE TRIGGER} used by {SYS} on {SYS.ATK_SEC_TRUN} using IP {192.168.56.1} with error code {0}     N
SQL>

This is a pretty good version of the same type of security as Oracles AUDSYS and AUD$UNIFIED.

YES, I know it can be bypassed by turning off system triggers or disabling triggers but if we have a good audit trail and can detect very quickly that this has occurred then the danger is that an attacker has update or deleted records from the table. If we know it happened quickly we can get those records back via flashback or Log Miner so yes its possible to bypass but the damage does by the attacker can be found and fixed quickly.

We can also quickly test the structure of our security every time its used and react if it has changed.

The solution is not perfect BUT we can do it with non additional cost options

#oracleace #sym_42 #oracle #security #audit #trail #audittrail #databreach #hacking #grants #protect #readonly #table