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.