Tag Archives: Unified 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.

Audit Trail cleanup in Oracle Multitenant environments

A crucial aspect of any database audit concept is the management and maintenance of audit trails. Depending on the defined audit policies and the database activity, an audit trail can grow relatively quickly. Oracle Multitenant environments increase the operational effort because the root container and each PDB uses their own audit trail. Ok, for a simple CDB with 2-3 PDB this effort is manageable, but what about CDB’s with 40 or more PDB’s?

Let’s look into the different possibilities.

Common Purge Job for all PDB’s

Oracle allow’s to initiate an audit trail clean up for all PDB’s using dbms_audit_mgmt.clean_audit_trail procedure by specifying dbms_audit_mgmt.container_all. The following example initiate a clean up in the root container for all PDB’s without considering the last archive timestamp.

ALTER SESSION SET CONTAINER=cdb$root;
BEGIN
   dbms_audit_mgmt.clean_audit_trail(
      audit_trail_type => dbms_audit_mgmt.audit_trail_unified,
      container => dbms_audit_mgmt.container_all,
      use_last_arch_timestamp => false);
END;
/

The problem with this method are the closed PDB’s. First let’s check how much audit records we do have per PDB.

COLUMN con_id FORMAT 999999
COLUMN count FORMAT 99999
COLUMN name FORMAT a8
SELECT
   v.con_id, v.name, v.open_mode, COUNT(u.event_timestamp) count
FROM
   cdb_unified_audit_trail u
FULL OUTER JOIN v$containers v ON u.con_id = v.con_id
GROUP BY
   v.con_id, v.name, v.open_mode
ORDER BY
   v.con_id;

CON_ID NAME     OPEN_MODE   COUNT
====== ======== =========== =====
     1 CDB$ROOT READ WRITE    644
     2 PDB$SEED READ ONLY       0
     3 PDB1     MOUNTED         0
     4 PDB2     READ WRITE     36
     5 PDB3     MOUNTED         0
     6 PDBSEC   READ WRITE     27

Running the clean up on this container database will end with an ORA-46273: DBMS_AUDIT_MGMT operation failed in one of the PDB.

BEGIN
   dbms_audit_mgmt.clean_audit_trail(
      audit_trail_type => dbms_audit_mgmt.audit_trail_unified,
      container => dbms_audit_mgmt.container_all,
      use_last_arch_timestamp => false);
END;
/

BEGIN
*
ERROR at line 1:
ORA-46273: DBMS_AUDIT_MGMT operation failed in one of the PDB
ORA-06512: at "AUDSYS.DBMS_AUDIT_MGMT", line 204
ORA-06512: at "AUDSYS.DBMS_AUDIT_MGMT", line 2137
ORA-06512: at "AUDSYS.DBMS_AUDIT_MGMT", line 1409
ORA-06512: at line 2

The error ORA-46273 is confusing. When we check the audit records, we see that the entries have been effectively be deleted. Here it would be more understandable if Oracle simply issue a warning rather than an error.

COLUMN con_id FORMAT 999999
COLUMN count FORMAT 99999
COLUMN name FORMAT a8
SELECT
   v.con_id, v.name, v.open_mode, COUNT(u.event_timestamp) count
FROM
   cdb_unified_audit_trail u
FULL OUTER JOIN v$containers v ON u.con_id = v.con_id
GROUP BY
   v.con_id, v.name, v.open_mode
ORDER BY
   v.con_id;

CON_ID NAME     OPEN_MODE   COUNT
====== ======== =========== =====
     1 CDB$ROOT READ WRITE    5
     2 PDB$SEED READ ONLY     0
     3 PDB1     MOUNTED       0
     4 PDB2     READ WRITE    1
     5 PDB3     MOUNTED       0
     6 PDBSEC   READ WRITE    1

The same does apply when we run the clean up task as a job. The job will always fail if one PDB is in MOUNT stat. This is annoying when monitoring scheduler jobs.

SQL> BEGIN
2 DBMS_SCHEDULER.RUN_JOB(job_name => '"AUDSYS"."TVD_TEST"');
3 END;
4 /
BEGIN
*
ERROR at line 1:
ORA-46273: DBMS_AUDIT_MGMT operation failed in one of the PDB
ORA-06512: at "AUDSYS.DBMS_AUDIT_MGMT", line 204
ORA-06512: at "AUDSYS.DBMS_AUDIT_MGMT", line 2137
ORA-06512: at "AUDSYS.DBMS_AUDIT_MGMT", line 1409
ORA-06512: at line 1
ORA-06512: at "SYS.DBMS_ISCHED", line 242
ORA-06512: at "SYS.DBMS_SCHEDULER", line 566
ORA-06512: at line 2

There is an other issue related to dbms_audit_mgmt.clean_audit_trail in Oracle 12.1 to 18c. The procedure does create a wrong scheduler job. The container_all is not set for the scheduler job, which results in the job running only in the root container. The issue is addressed in Oracle bug 27527173 – ISSUE WITH CREATE PURGE JOB FOR UNIFIED_AUDIT_TRAIL WITH CONTAINER_ALL. A bugfix is available for Oracle 12.1.0.2, 12.2.0.1 and 18.0.0.0. Alternatively you can workaround this issue by manually create a scheduler job to purge the audit trail rather than using dbms_audit_mgmt.clean_audit_trail. The issue is permanently fixed in Oracle 19.0.0.0.

dbms_audit_mgmt or Scheduler Job

Considering the above problems, it seems better to directly create a regular scheduler job for deleting audit trails. All right, this does work as expected, but these kind of jobs will never be visible in cdb_audit_mgmt_cleanup_jobs view. Technically this is not a problem, but it is good practice when audit-related clean up jobs are visible where they should.

The following example creates a regular scheduler job.

BEGIN
   dbms_scheduler.create_job(
      job_name => 'TVD_AUDIT_PURGE_JOB',
      job_type => 'PLSQL_BLOCK',
      job_action => 'BEGIN dbms_audit_mgmt.clean_audit_trail(
         audit_trail_type => dbms_audit_mgmt.audit_trail_unified,
         container => dbms_audit_mgmt.container_current,
         use_last_arch_timestamp => false); END;',
      start_date => trunc(sysdate)+(2/24),
      repeat_interval => 'FREQ=HOURLY;INTERVAL=12',
      enabled => true,
      comments => 'Create an audit purge job for unified audit');
END;
/

Insights when creating audit purge job via dbms_scheduler:

  • Job is not visible in cdb_audit_mgmt_cleanup_jobs
  • Start date can be defined when the job is created
  • Job can be created in any schema, which has the appropriate rights.

The following example does create an audit trail clean up job for unified audit trail using dbms_audit_mgmt and adjusting the start time to 01:00 with dbms_scheduler.

BEGIN
   -- Create regular purge job
   dbms_audit_mgmt.create_purge_job(
      audit_trail_type => dbms_audit_mgmt.audit_trail_unified,
      audit_trail_purge_interval => 12,
      audit_trail_purge_name=> 'tvd_unified_audit_purge_job',
      container => dbms_audit_mgmt.container_current,
      use_last_arch_timestamp => true);
   -- Adapt start date of dbms_audit_mgmt purge job
   dbms_scheduler.set_attribute (
      name => 'tvd_unified_audit_purge_job',
      attribute => 'start_date',
      value => trunc(sysdate) + ( 1 / 24 ) );
END;
/

Insights when creating audit purge job via dbms_audit_mgmt:

  • Job is visible in cdb_audit_mgmt_cleanup_jobs
  • Start date can not be defined when the job is created with dbms_audit_mgmt. The start date has to be changed later using dbms_scheduler.set_attribute. This also applies to other adjustments to the job.
  • For Oracle 18c and 19c the job will be created in the AUDSYS schema. In Oracle 12c and earlier it will be created in the SYS schema.

Audit Window

In the above examples I did set use_last_arch_timestamp=>true. This means that only audit trails older than the archive timestamp will be deleted. The archive timestamp is usually set by the Oracle audit vault agent, when it reads the audit records. But what happens a third party tool like splunk collects the audit data? These tools will generally just read the data without setting a corresponding archive timestamp. Oracle will then never delete old audit records. Therefor it is crucial, that the archive timestamp is set by the tool as soon as the data as been collected. Additionally to this, it is good practice to define an audit window. This is nothing else than set up a job which regularly set an archive timestamp e.g. set the timestamp to sysdate-30. This way all audit records older than 30 days will be removed. The database is thus always nicely tidied up. :-The following example creates such an archive timestamp job. Every 12 hours the archive timestamp is set to sysdate-30.

BEGIN
   dbms_scheduler.create_job(
   job_name =>'tvd_unified_audit_timestamp_job',
   job_type => 'PLSQL_BLOCK',
   job_action => 'BEGIN dbms_audit_mgmt.set_last_archive_timestamp(
      audit_trail_type => dbms_audit_mgmt.audit_trail_unified,
      container => dbms_audit_mgmt.container_current,
      last_archive_time => sysdate - 30); END;',
   start_date => trunc(sysdate)+(2/24),
   repeat_interval => 'FREQ=HOURLY;INTERVAL=12',
   enabled => true,
   comments => 'Create an regularly archive timestamp for unified audit');
END;
/

Conclusion

The administration of audit records works well in Oracle Multitenant environments as well. dbms_audit_mgmt helps with the automatic and manual management of audit trails. Nevertheless, there are a few issues and best practices to consider:

  • Audit data should always be stored in a separate tablespace. However, depending on the Oracle version, there may be issues related to the relocation of the audi trail. For example, not all partitions are moved correctly, LOB and index partitions will continue to be created in the old tablespace. See also bug 27576342 – dbms_audit_mgmt.set_audit_trail_location does not move lob and index partitions or MOS note 2428624.1 Dbms_Audit_Mgmt.Set_Audit_Trail_Location Does Not Move Lob And Index Partitions
  • Adjusting the partition size or the interval when a new audit partition has to be created, has be done according to the audit quantity and database activity..
  • The different jobs for the clean up as well as the setting of the archive timestamp should run in the same scheme. This increases the transparency. As of Oracle 18c < dbms_audit_mgmt creates the jobs under the user AUDSYS. Older versions usually use the SYS user
  • In Oracle Multitenant environments a central clean up job is the easiest, but makes no sense, depending on the state of the PDB’s it always finishes with an error. Here it is recommended to define one job for each CDB / PDB. In this way, the frequency of the deletion can be individually adapted to the database activity and compliance requirements of the particular PDB.
  • So far we just discussed unified audit. If the database does run in pure unified mode there is no need to define a housekeeping for the legacy audit trails (AUD$, FGA$ etc). Although it is a good practice to define a minimal housekeeping for the legacy audit trail.
  • To define a time window in which audit records are keep in the database is usually a easy method to hold a certain level of audit information in the database. Third party tools like splunk do have enough time to collect the relevant information.
  • For successful database audit concept it is crucial to define the different users and roles who can access or maintain audit information.

References

Some links related to this blog post:

  • Oracle® Database PL/SQL Packages and Types Reference 19c DBMS_AUDIT_MGMT
  • Master Note For Oracle Database Auditing [1299033.1]
  • Dbms_Audit_Mgmt.Set_Audit_Trail_Location Does Not Move Lob And Index Partitions [2428624.1]
  • Bug 27576342 – dbms_audit_mgmt.set_audit_trail_location does not move lob and index partitions
  • Bug 22859443 – CONTAINER_ALL IS IGNORED IN CREATE_PURGE_JOB
  • Patch 27527173 – ISSUE WITH CREATE PURGE JOB FOR UNIFIED_AUDIT_TRAIL WITH CONTAINER_ALL (Patch)

DOAG SIG Security Mannheim 2016

Bit more than two weeks ago I finished my presentation about Security Probleme und deren Risikobewertung at the DOAG SIG Security in Mannheim. It is about Database and Data Classification, Risk Assessment and how Risks could be minimized. The slides are available for download  DOAG_SIG_Security_Security_Wieviel_darf_es_sein.pdf.

DOAG SIG Security Munich 2015

Just finished my presentation about Unified Audit at the DOAG SIG Security in München. It is about Oracle Unified Audit and a few considerations for migrating old standard audit to new policy based unified audit. The slides are available for download  DOAG_SIG_Security_Oracle_Unified_Audit.pdf.

Some impression for the event and my presentation.
DOAG_SIG_SEC_2015_1 DOAG_SIG_SEC_2015_2