Tag Archives: Oracle Audit

Help I lost my brand new Unified Audit Policy?

I am currently working on audit concepts as well as corresponding Unified Audit Policies for various customer projects. That’s why today I once again had a closer look at Oracle Unified Audit.

One requirement in the security concept is to be able to track if someone accesses the operating system via Oracle DIRECTORY OBJECTS. To do this, you can either work with an explicit object action on a specific directory or you can generally audit the object actions on directories. An audit policy with one or more explicit object actions must of course be managed accordingly. Therefore I decided to monitor actions on Oracle directories in general. I.e. READ, WRITE and EXECUTE.

The Problem

My audit policy looks as follows:

CREATE AUDIT POLICY tvdlm_dir_access
  ACTIONS
    READ DIRECTORY,
    WRITE DIRECTORY,
    EXECUTE DIRECTORY
  ONLY TOPLEVEL;

Cheerfully we check once in the data dictionary view audit_unified_policies our policy

SET pagesize 200
SET linesize 160
COL policy_name FOR A25
SELECT policy_name FROM audit_unified_policies
GROUP BY policy_name ORDER BY policy_name;

As you can see the new policy is not shown in the data dictionary view audit_unified_policies.

POLICY_NAME
-------------------------
ORA_ACCOUNT_MGMT
ORA_CIS_RECOMMENDATIONS
ORA_DATABASE_PARAMETER
ORA_DV_AUDPOL
ORA_DV_AUDPOL2
ORA_LOGON_FAILURES
ORA_RAS_POLICY_MGMT
ORA_RAS_SESSION_MGMT
ORA_SECURECONFIG

9 rows selected.

But when you try to create it once more you get an error ORA-46358.

CREATE AUDIT POLICY tvdlm_dir_access
  ACTIONS
    READ DIRECTORY,
    WRITE DIRECTORY,
    EXECUTE DIRECTORY
  ONLY TOPLEVEL;
CREATE AUDIT POLICY tvdlm_dir_access
*
ERROR at line 1:
ORA-46358: Audit policy TVDLM_DIR_ACCESS already exists.

But where is it? Lets see if we found it in dba_objects.

SET pagesize 200
SET linesize 160
COL object_name FOR A25
COL object_type FOR A25
SELECT object_name, object_type FROM dba_objects
  WHERE object_name LIKE 'TVD%' ORDER BY object_name;
OBJECT_NAME		  OBJECT_TYPE
------------------------- -------------------------
TVDLM_DIR_ACCESS	  UNIFIED AUDIT POLICY

OK in dba_objects we can see the policy. Can we also use / enable it?

Test Case

Let’s setup a simple test case to see if this audit policy does work even when it is not shown as valid audit policy in audit_unified_policies. As a first step we do enable the audit policy for all user.

AUDIT POLICY tvdlm_dir_access;

Verify all active unified audit policies by quering audit_unified_enabled_policies.

SET linesize 160 pagesize 200
COL policy_name FOR A20
COL entity_name FOR A10

SELECT * FROM audit_unified_enabled_policies;
POLICY_NAME          ENABLED_OPTION  ENTITY_NAM ENTITY_ SUC FAI
-------------------- --------------- ---------- ------- --- ---
ORA_SECURECONFIG     BY USER         ALL USERS  USER    YES YES
ORA_LOGON_FAILURES   BY USER         ALL USERS  USER    NO  YES
TVDLM_DIR_ACCESS     BY USER         ALL USERS  USER    YES YES

3 rows selected.

This looks promising. At least the active audit policy is shown correctly. Now, to test access, we create an Oracle directory object. For the test I use the user scott.

CREATE OR REPLACE DIRECTORY exttab AS '/u01/app/oracle/admin/TSEC02/adhoc';
GRANT READ, WRITE ON DIRECTORY exttab TO scott;

In the adhoc folder we do create a csv file scott.emp.csv with the following content.

SELECT empno||','||ename||','||job csv_output FROM scott.emp;
CSV_OUTPUT
-------------------------------------------------------------
7369,SMITH,CLERK
7499,ALLEN,SALESMAN
7521,WARD,SALESMAN
7566,JONES,MANAGER
7654,MARTIN,SALESMAN
7698,BLAKE,MANAGER
7782,CLARK,MANAGER
7788,SCOTT,ANALYST
7839,KING,PRESIDENT
7844,TURNER,SALESMAN
7876,ADAMS,CLERK
7900,JAMES,CLERK
7902,FORD,ANALYST
7934,MILLER,CLERK

14 rows selected.

And finally we do create a simple external table on this csv file.

CREATE TABLE scott.emp_external(
    EMPNO NUMBER(4),
    ename VARCHAR2(10),
    job VARCHAR2(9)
)
ORGANIZATION EXTERNAL(
    TYPE oracle_loader
    DEFAULT DIRECTORY exttab
    ACCESS PARAMETERS 
    (FIELDS TERMINATED BY ',')
    LOCATION ('scott.emp.csv'));

Before we query the external table, we purge the audit trail to have a clean trail 😊

EXEC dbms_audit_mgmt.clean_audit_trail( audit_trail_type => dbms_audit_mgmt.audit_trail_unified,use_last_arch_timestamp => FALSE);

Run the query on the external table scott.emp_external.

SELECT * FROM scott.emp_external;
     EMPNO ENAME      JOB
---------- ---------- ---------
      7369 SMITH      CLERK
      7499 ALLEN      SALESMAN
      7521 WARD       SALESMAN
      7566 JONES      MANAGER
      7654 MARTIN     SALESMAN
      7698 BLAKE      MANAGER
      7782 CLARK      MANAGER
      7788 SCOTT      ANALYST
      7839 KING       PRESIDENT
      7844 TURNER     SALESMAN
      7876 ADAMS      CLERK
      7900 JAMES      CLERK
      7902 FORD       ANALYST
      7934 MILLER     CLERK

14 rows selected.

Verify what we do have in the unified_audit_trail.

COL event_timestamp FOR A26
COL entry_id FOR 999
COL dbusername FOR A5
COL dbproxy_username FOR A10
COL action_name FOR A18
COL return_code FOR 999999
COL object_schema FOR A10
COL object_name FOR A16
COL unified_audit_policies FOR A20
SET LINES 200
SET PAGES 999
SELECT
   to_char(event_timestamp,'DD.MM.YY HH24:MI:SS') "Timestamp",
   entry_id,
   action_name,
   object_name
   --, unified_audit_policies
FROM
    unified_audit_trail
ORDER BY
    event_timestamp ,
    entry_id;
Timestamp         ENTRY_ID ACTION_NAME        OBJECT_NAME                                                                                                                                               
----------------- -------- ------------------ ----------------                                                                                                                                          
16.03.23 15:52:17       42 EXECUTE            DBMS_AUDIT_MGMT                                                                                                                                           
16.03.23 15:52:36        1 READ DIRECTORY     EXTTAB                                                                                                                                                    
16.03.23 15:52:36        2 EXECUTE DIRECTORY  EXTTAB                                                                                                                                                    
16.03.23 15:52:36        3 WRITE DIRECTORY    EXTTAB                                                                                                                                                    
16.03.23 15:52:36        4 READ DIRECTORY     EXTTAB                                                                                                                                                    
16.03.23 15:52:36        5 EXECUTE DIRECTORY  EXTTAB                                                                                                                                                    
16.03.23 15:52:36        6 WRITE DIRECTORY    EXTTAB                                                                                                                                                    
16.03.23 15:52:36        7 READ DIRECTORY     EXTTAB                                                                                                                                                    
16.03.23 15:52:36        8 EXECUTE DIRECTORY  EXTTAB                                                                                                                                                    
16.03.23 15:52:36        9 WRITE DIRECTORY    EXTTAB                                                                                                                                                    
16.03.23 15:52:36       10 READ DIRECTORY     EXTTAB                                                                                                                                                    
16.03.23 15:52:36       11 EXECUTE DIRECTORY  EXTTAB                                                                                                                                                    
16.03.23 15:52:36       12 WRITE DIRECTORY    EXTTAB                                                                                                                                                    
16.03.23 15:52:36       13 READ DIRECTORY     EXTTAB                                                                                                                                                    
16.03.23 15:52:36       14 EXECUTE DIRECTORY  EXTTAB                                                                                                                                                    
16.03.23 15:52:36       15 WRITE DIRECTORY    EXTTAB                                                                                                                                                    
16.03.23 15:52:36       16 READ DIRECTORY     EXTTAB                                                                                                                                                    
16.03.23 15:52:36       17 EXECUTE DIRECTORY  EXTTAB                                                                                                                                                    
16.03.23 15:52:36       18 WRITE DIRECTORY    EXTTAB                                                                                                                                                    

19 rows selected.

Conclusion

So the whole thing is not as bad as it looked at the beginning. The audit policy is created, can be activated and the corresponding audit records are created. Nevertheless, not seeing the audit policy in audit_unified_policies is a bit confusing. Especially since according to Oracle documentation this view should list all available audit policies. After a bit of research and a service request, it sure turned out that this is a known issue. A corresponding bugfix seems to be in the works. Until then you have to query dba_objects to check if a policy really exists.

A few final facts…

  • Oracle Support Document 30769454.8 Bug 30769454 – Policy Created For Some Actions Are Not Showing In Audit_Unified_Policies.
  • Oracle Support Document 2419064.1 Audit Policy is Not Seen in AUDIT_UNIFIED_POLICIES After Dropping Objects.
  • Oracle Database Enterprise Edition up to 21.3 is affected. Testing with 21c RU January 2023 (21.9.0.0) showed that in this version the problem is fixed.
  • Audit policies on directory action do create a couple of audit records. It seems that this is related to how external tables are accessed. This can be reduced by explicitly set NOLOGFILE, NOBADFILE or NODISCARDFILE. But still then there will always be more than just one single entry.