{"id":13000,"date":"2023-03-16T16:00:13","date_gmt":"2023-03-16T15:00:13","guid":{"rendered":"https:\/\/www.oradba.ch\/wordpress\/?p=13000"},"modified":"2023-03-16T16:00:13","modified_gmt":"2023-03-16T15:00:13","slug":"help-i-lost-my-brand-new-unified-audit-policy","status":"publish","type":"post","link":"https:\/\/www.oradba.ch\/wordpress\/2023\/03\/help-i-lost-my-brand-new-unified-audit-policy\/","title":{"rendered":"Help I lost my brand new Unified Audit Policy?"},"content":{"rendered":"\n

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.<\/p>\n\n\n\n

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. <\/p>\n\n\n\n

The Problem<\/h2>\n\n\n\n

My audit policy looks as follows:<\/p>\n\n\n\n

CREATE AUDIT POLICY tvdlm_dir_access\n  ACTIONS\n    READ DIRECTORY,\n    WRITE DIRECTORY,\n    EXECUTE DIRECTORY\n  ONLY TOPLEVEL;<\/pre>\n\n\n\n
Cheerfully we check once in the data dictionary view audit_unified_policies<\/em> our policy<\/p>\n\n\n\n
SET pagesize 200\nSET linesize 160\nCOL policy_name FOR A25\nSELECT policy_name FROM audit_unified_policies\nGROUP BY policy_name ORDER BY policy_name;<\/pre>\n\n\n\n
As you can see the new policy is not shown in the data dictionary view audit_unified_policies<\/em>.<\/p>\n\n\n\n
POLICY_NAME\n-------------------------\nORA_ACCOUNT_MGMT\nORA_CIS_RECOMMENDATIONS\nORA_DATABASE_PARAMETER\nORA_DV_AUDPOL\nORA_DV_AUDPOL2\nORA_LOGON_FAILURES\nORA_RAS_POLICY_MGMT\nORA_RAS_SESSION_MGMT\nORA_SECURECONFIG\n\n9 rows selected.<\/pre>\n\n\n\n
But when you try to create it once more you get an error ORA-46358.<\/p>\n\n\n\n
CREATE AUDIT POLICY tvdlm_dir_access\n  ACTIONS\n    READ DIRECTORY,\n    WRITE DIRECTORY,\n    EXECUTE DIRECTORY\n  ONLY TOPLEVEL;<\/pre>\n\n\n\n
CREATE AUDIT POLICY tvdlm_dir_access\n*\nERROR at line 1:\nORA-46358: Audit policy TVDLM_DIR_ACCESS already exists.<\/pre>\n\n\n\n
But where is it? Lets see if we found it in dba_objects<\/em>.<\/p>\n\n\n\n
SET pagesize 200\nSET linesize 160\nCOL object_name FOR A25\nCOL object_type FOR A25\nSELECT object_name, object_type FROM dba_objects\n  WHERE object_name LIKE 'TVD%' ORDER BY object_name;<\/pre>\n\n\n\n
OBJECT_NAME\t\t  OBJECT_TYPE\n------------------------- -------------------------\nTVDLM_DIR_ACCESS\t  UNIFIED AUDIT POLICY<\/pre>\n\n\n\n
OK in dba_objects<\/em> we can see the policy. Can we also use \/ enable it? <\/p>\n\n\n\n
Test Case<\/h2>\n\n\n\n
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<\/em>. As a first step we do enable the audit policy for all user.<\/p>\n\n\n\n
AUDIT POLICY tvdlm_dir_access;<\/pre>\n\n\n\n
Verify all active unified audit policies by quering audit_unified_enabled_policies<\/em>.<\/p>\n\n\n\n
SET linesize 160 pagesize 200\nCOL policy_name FOR A20\nCOL entity_name FOR A10\n\nSELECT * FROM audit_unified_enabled_policies;<\/pre>\n\n\n\n
POLICY_NAME          ENABLED_OPTION  ENTITY_NAM ENTITY_ SUC FAI\n-------------------- --------------- ---------- ------- --- ---\nORA_SECURECONFIG     BY USER         ALL USERS  USER    YES YES\nORA_LOGON_FAILURES   BY USER         ALL USERS  USER    NO  YES\nTVDLM_DIR_ACCESS     BY USER         ALL USERS  USER    YES YES\n\n3 rows selected.<\/pre>\n\n\n\n
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<\/em>.<\/p>\n\n\n\n
CREATE OR REPLACE DIRECTORY exttab AS '\/u01\/app\/oracle\/admin\/TSEC02\/adhoc';\nGRANT READ, WRITE ON DIRECTORY exttab TO scott;<\/pre>\n\n\n\n
In the adhoc folder we do create a csv file scott.emp.csv<\/em> with the following content.<\/p>\n\n\n\n
SELECT empno||','||ename||','||job csv_output FROM scott.emp;<\/pre>\n\n\n\n
CSV_OUTPUT\n-------------------------------------------------------------\n7369,SMITH,CLERK\n7499,ALLEN,SALESMAN\n7521,WARD,SALESMAN\n7566,JONES,MANAGER\n7654,MARTIN,SALESMAN\n7698,BLAKE,MANAGER\n7782,CLARK,MANAGER\n7788,SCOTT,ANALYST\n7839,KING,PRESIDENT\n7844,TURNER,SALESMAN\n7876,ADAMS,CLERK\n7900,JAMES,CLERK\n7902,FORD,ANALYST\n7934,MILLER,CLERK\n\n14 rows selected.<\/pre>\n\n\n\n
And finally we do create a simple external table on this csv file.<\/p>\n\n\n\n
CREATE TABLE scott.emp_external(\n    EMPNO NUMBER(4),\n    ename VARCHAR2(10),\n    job VARCHAR2(9)\n)\nORGANIZATION EXTERNAL(\n    TYPE oracle_loader\n    DEFAULT DIRECTORY exttab\n    ACCESS PARAMETERS \n    (FIELDS TERMINATED BY ',')\n    LOCATION ('scott.emp.csv'));<\/pre>\n\n\n\n
Before we query the external table, we purge the audit trail to have a clean trail \ud83d\ude0a<\/p>\n\n\n\n
EXEC dbms_audit_mgmt.clean_audit_trail( audit_trail_type => dbms_audit_mgmt.audit_trail_unified,use_last_arch_timestamp => FALSE);<\/pre>\n\n\n\n
Run the query on the external table scott.emp_external<\/em>.<\/p>\n\n\n\n
SELECT * FROM scott.emp_external;<\/pre>\n\n\n\n
     EMPNO ENAME      JOB\n---------- ---------- ---------\n      7369 SMITH      CLERK\n      7499 ALLEN      SALESMAN\n      7521 WARD       SALESMAN\n      7566 JONES      MANAGER\n      7654 MARTIN     SALESMAN\n      7698 BLAKE      MANAGER\n      7782 CLARK      MANAGER\n      7788 SCOTT      ANALYST\n      7839 KING       PRESIDENT\n      7844 TURNER     SALESMAN\n      7876 ADAMS      CLERK\n      7900 JAMES      CLERK\n      7902 FORD       ANALYST\n      7934 MILLER     CLERK\n\n14 rows selected.<\/pre>\n\n\n\n
Verify what we do have in the unified_audit_trail<\/em>. <\/p>\n\n\n\n
COL event_timestamp FOR A26\nCOL entry_id FOR 999\nCOL dbusername FOR A5\nCOL dbproxy_username FOR A10\nCOL action_name FOR A18\nCOL return_code FOR 999999\nCOL object_schema FOR A10\nCOL object_name FOR A16\nCOL unified_audit_policies FOR A20\nSET LINES 200\nSET PAGES 999\nSELECT\n   to_char(event_timestamp,'DD.MM.YY HH24:MI:SS') \"Timestamp\",\n   entry_id,\n   action_name,\n   object_name\n   --, unified_audit_policies\nFROM\n    unified_audit_trail\nORDER BY\n    event_timestamp ,\n    entry_id;<\/pre>\n\n\n\n
Timestamp         ENTRY_ID ACTION_NAME        OBJECT_NAME                                                                                                                                               \n----------------- -------- ------------------ ----------------                                                                                                                                          \n16.03.23 15:52:17       42 EXECUTE            DBMS_AUDIT_MGMT                                                                                                                                           \n16.03.23 15:52:36        1 READ DIRECTORY     EXTTAB                                                                                                                                                    \n16.03.23 15:52:36        2 EXECUTE DIRECTORY  EXTTAB                                                                                                                                                    \n16.03.23 15:52:36        3 WRITE DIRECTORY    EXTTAB                                                                                                                                                    \n16.03.23 15:52:36        4 READ DIRECTORY     EXTTAB                                                                                                                                                    \n16.03.23 15:52:36        5 EXECUTE DIRECTORY  EXTTAB                                                                                                                                                    \n16.03.23 15:52:36        6 WRITE DIRECTORY    EXTTAB                                                                                                                                                    \n16.03.23 15:52:36        7 READ DIRECTORY     EXTTAB                                                                                                                                                    \n16.03.23 15:52:36        8 EXECUTE DIRECTORY  EXTTAB                                                                                                                                                    \n16.03.23 15:52:36        9 WRITE DIRECTORY    EXTTAB                                                                                                                                                    \n16.03.23 15:52:36       10 READ DIRECTORY     EXTTAB                                                                                                                                                    \n16.03.23 15:52:36       11 EXECUTE DIRECTORY  EXTTAB                                                                                                                                                    \n16.03.23 15:52:36       12 WRITE DIRECTORY    EXTTAB                                                                                                                                                    \n16.03.23 15:52:36       13 READ DIRECTORY     EXTTAB                                                                                                                                                    \n16.03.23 15:52:36       14 EXECUTE DIRECTORY  EXTTAB                                                                                                                                                    \n16.03.23 15:52:36       15 WRITE DIRECTORY    EXTTAB                                                                                                                                                    \n16.03.23 15:52:36       16 READ DIRECTORY     EXTTAB                                                                                                                                                    \n16.03.23 15:52:36       17 EXECUTE DIRECTORY  EXTTAB                                                                                                                                                    \n16.03.23 15:52:36       18 WRITE DIRECTORY    EXTTAB                                                                                                                                                    \n\n19 rows selected.<\/pre>\n\n\n\n
Conclusion<\/h2>\n\n\n\n
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<\/em> to check if a policy really exists.<\/p>\n\n\n\n
A few final facts…<\/p>\n\n\n\n