Loading....

Introduction

In previous releases of Oracle Database prior to Oracle 12c Release 1, there were separate audit trails for individual components.

For example:

  • SYS.AUD$ for the database audit trail,
  • SYS.FGA_LOG$ for fine-grained auditing,
  • DVSYS.AUDIT_TRAIL$ for Oracle Database Vault, Oracle Label Security,

Starting with 12c Release 1, Oracle has unified all of the auditing types into one single unit called Unified Auditing where the AUD$ and FGA$ tables have been replaced with one single audit trail table.

We no longer need to set any auditing related parameters in the database like AUDIT_SYS_OPERATIONS and AUDIT_FILE_DEST. Once unified auditing is enabled, it does not depend on the initialization parameters that were used in previous releases.

By default, audit trail records are written to the AUDSYS schema in the SYSAUX tablespace. We can designate a different tablespace, including one that is encrypted, by using the DBMS_AUDIT_MGMT package.

In Oracle Database 12c release 1 (12.1), we had the option of queuing the audit records in memory (queued-write mode) which would be written then periodically to the AUDSYS schema audit table.

However, starting with Oracle Database 12c release 2 (12.2) the queued-write mode is deprecated and the unified audit records are written immediately to disk to a range-partitioned table in the AUDSYS schema called AUD$UNIFIED.

Access to the AUDSYS. AUD$UNIFIED table is provided via the view SYS.UNIFIED_AUDIT_TRAIL.

Audit administration duties have been separated with two audit administration roles: AUDIT_ADMIN and AUDIT_VIEWER.

Unified auditing can be implemented in one of the following ways:

 1) Mixed mode auditing:  allows both the new audit engine and the traditional pre-Oracle 12c audit engine to work simultaneously.  This is the default on upgrade to 12c.

2) Pure unified auditing:  allows only the unified auditing
 
  To enable unified auditing relink Oracle binaries with the new uniaud_on option after shutting down all Oracle processes running out of  the Oracle Home like database instance and listener. 

After unified auditing is enabled none of the old audit related database parameters like  audit_trail, audit_file_dest,audit_sys_operations,audit_syslog_level are applicable.


When the database is not open in read-write mode, then audit records are written to new format operating system files located in the $ORACLE_BASE/audit/$ORACLE_SID directory.

Enabling Unified Auditing

SQL> SELECT VALUE FROM V$OPTION WHERE PARAMETER = ‘Unified Auditing’;

VALUE

—————————————————————-

FALSE

SQL> show parameter AUDIT_TRAIL    

NAME                                                        TYPE VALUE

———————————— ———– ——————————

audit_trail                                                string               DB

SQL> alter system set audit_trail=’NONE’ scope=spfile;

System altered.

SQL> shutdown immediate;

$ cd $ORACLE_HOME/rdbms/lib

$ make -f ins_rdbms.mk uniaud_on ioracle

SQL> startup;

SQL> SELECT VALUE FROM V$OPTION WHERE PARAMETER = ‘Unified Auditing’;

VALUE

—————————————————————-

TRUE

Changing the default tablespace for the Unified Auditing

Create a new tablespace called AUDIT_DATA

SQL> BEGIN

DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_LOCATION(

 audit_trail_type => dbms_audit_mgmt.audit_trail_unified,

 audit_trail_location_value => ‘AUDIT_DATA’);

END;

/

PL/SQL procedure successfully completed.



SQL> col owner format a10
SQL> col table_name format a15
SQL> col tablespace_name format a12
SQL> col interval format a20     
SQL> set lines 200

          
SQL> select owner,table_name,interval,partitioning_type,partition_count,def_tablespace_name from dba_part_Tables where owner=’AUDSYS’;

OWNER     TABLE_NAME    INTERVAL         PARTITION PARTITION_COUNT DEF_TABLESPACE_NAME

———- ————— ——————– ——— ————— ——————————

AUDSYS    AUD$UNIFIED         INTERVAL ‘1’ MONTH      RANGE           1048575 AUDIT_DATA


No DDL or DML permitted on AUD$UNIFIED Table

SQL> truncate table audsys.AUD$UNIFIED;

truncate table audsys.AUD$UNIFIED

                      *

ERROR at line 1:

ORA-46385: DML and DDL operations are not allowed on table

“AUDSYS”.”AUD$UNIFIED”.

Create an Auditing user with the AUDIT_VIEWER Role

SQL> create user auditor identified by Dreamliner787##;

User created.

SQL> grant create session, select any dictionary, select any table to auditor;

Grant succeeded.

SQL> conn auditor/Dreamliner787##@pdb1

Connected.

SQL> select count(*) from audsys.unified_audit_trail;

select count(*) from audsys.unified_audit_trail

                            *

ERROR at line 1:

ORA-01031: insufficient privileges

SQL> conn / as sysdba

Connected.

SQL> alter session set container=pdb1;

Session altered.

SQL> grant audit_viewer to auditor;

Grant succeeded.

SQL> conn auditor/Dreamliner787##@pdb1

Connected.

SQL> select count(*) from audsys.unified_audit_trail;

  COUNT(*)

———-

       278

Purge Audit Trail is not possible using AUDIT_VIEWER role – requires AUDIT_ADMIN role

SQL> BEGIN

  DBMS_AUDIT_MGMT.CLEAN_AUDIT_TRAIL(

   AUDIT_TRAIL_TYPE           =>  DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED,

   USE_LAST_ARCH_TIMESTAMP    =>  TRUE,

   CONTAINER                  =>  DBMS_AUDIT_MGMT.CONTAINER_CURRENT );

END;

   AUDIT_TRAIL_TYPE           =>  DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED,

                                  *

ERROR at line 3:

ORA-06550: line 3, column 35:

PLS-00904: insufficient privilege to access object AUDSYS.DBMS_AUDIT_MGMT

ORA-06550: line 2, column 3:

PL/SQL: Statement ignored

Manually Purging the  Unified Audit Trail

SQL> select count(*) from audsys.unified_audit_trail;

  COUNT(*)

———-

       289

SQL> BEGIN

  DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP(

   AUDIT_TRAIL_TYPE     =>  DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED,

   LAST_ARCHIVE_TIME    =>  ’13-APR-2020 06:30:00.00′,

   RAC_INSTANCE_NUMBER  =>  1,

   CONTAINER            => DBMS_AUDIT_MGMT.CONTAINER_CURRENT);

END;

PL/SQL procedure successfully completed.

SQL> BEGIN

  DBMS_AUDIT_MGMT.CLEAN_AUDIT_TRAIL(

   AUDIT_TRAIL_TYPE           =>  DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED,

   USE_LAST_ARCH_TIMESTAMP    =>  TRUE,

   CONTAINER                  =>  DBMS_AUDIT_MGMT.CONTAINER_CURRENT );

END;

/

PL/SQL procedure successfully completed.

SQL> select count(*) from audsys.unified_audit_trail;

  COUNT(*)

———-

                18

Purging the  Unified Audit Trail via a job

SQL> BEGIN

  DBMS_AUDIT_MGMT.CREATE_PURGE_JOB (

   AUDIT_TRAIL_TYPE            => DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED,

   AUDIT_TRAIL_PURGE_INTERVAL  => 24,

   AUDIT_TRAIL_PURGE_NAME      => ‘Unified_Audit_Trail_Purge_Job’,

   USE_LAST_ARCH_TIMESTAMP     => TRUE,

   CONTAINER                   => DBMS_AUDIT_MGMT.CONTAINER_CURRENT);

END;

PL/SQL procedure successfully completed.

**View Purge Job Details – DBA_AUDIT_MGMT_CLEANUP_JOBS

Create an Unified Audit Policy – audit CREATE TABLE for the user AUDITOR

SQL> CREATE AUDIT POLICY policy_for_auditor

PRIVILEGES CREATE TABLE

WHEN    ‘SYS_CONTEXT(”USERENV”, ”SESSION_USER”) = ”AUDITOR”’

EVALUATE PER SESSION

CONTAINER = CURRENT; 

Audit policy created.

SQL> AUDIT POLICY policy_for_auditor;

Audit succeeded.

SQL> conn auditor/Dreamliner787##@pdb1

Connected.

SQL> create table auditors_table as select * from dba_objects;

Table created.

View the Unified Audit Trail

SQL> conn / as sysdba

Connected.

SQL> alter session set container=PDB1;

Session altered.

SQL> COLUMN event_timestamp FORMAT A30

SQL> COLUMN dbusername FORMAT A10

SQL> COLUMN action_name FORMAT A20

SQL> COLUMN object_schema FORMAT A10

SQL>COLUMN object_name FORMAT A20

SQL> SELECT event_timestamp,

       dbusername,

       action_name,

       object_schema,

       object_name

FROM   unified_audit_trail

WHERE  dbusername = ‘AUDITOR’;

EVENT_TIMESTAMP        DBUSERNAME ACTION_NAME           OBJECT_SCH OBJECT_NAME

------------------------------ ---------- -------------------- ---------- --------------------

13-APR-20 04.27.26.721127 PM   AUDITOR   CREATE TABLE         AUDITOR    AUDITORS_TABLE

13-APR-20 04.27.26.741823 PM   AUDITOR   SELECT          SYS   DBA_OBJECTS

13-APR-20 04.03.54.117862 PM   AUDITOR   EXECUTE         AUDSYS     DBMS_AUDIT_MGMT

**Note: EXECUTE of the DBMS_AUDIT_MGMT PL/SQL package is audited by default –  the attempt to execute   DBMS_AUDIT_MGMT.CLEAN_AUDIT_TRAIL by the AUDITOR user was audited

Using the predefined ORA_* Unified Audit Policies

SQL> select distinct policy_name from audit_unified_policies order by 1; 

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

POLICY_FOR_AUDITOR

**Note: For new databases, all the ORA_*  policy is enabled by default for both pure unified auditing and mixed-mode auditing environments.

SQL> conn auditor/DReamliner787##@pdb1

ERROR:

ORA-01017: invalid username/password; logon denied

SQL> SELECT event_timestamp,

       dbusername,

       action_name,

       object_schema,

       object_name

       FROM   unified_audit_trail 

WHERE  dbusername = ‘AUDITOR’;

EVENT_TIMESTAMP        DBUSERNAME ACTION_NAME           OBJECT_SCH OBJECT_NAME

------------------------------ ---------- -------------------- ---------- --------------------

13-APR-20 04.27.26.721127 PM   AUDITOR   CREATE TABLE         AUDITOR    AUDITORS_TABLE

13-APR-20 04.27.26.741823 PM   AUDITOR   SELECT          SYS   DBA_OBJECTS

13-APR-20 04.58.25.980623 PM   AUDITOR   LOGON

13-APR-20 04.03.54.117862 PM   AUDITOR   EXECUTE         AUDSYS     DBMS_AUDIT_MGMT



SQL> SELECT

event_timestamp,

dbusername,

object_schema,

object_name

FROM   unified_audit_trail 

WHERE  action_name=’CREATE USER’;


EVENT_TIMESTAMP        DBUSERNAME OBJECT_SCH OBJECT_NAME

------------------------------ ---------- ---------- --------------------

13-APR-20 03.41.02.108624 PM   SYS              AUDITOR



Disabling a  predefined ORA_* Unified Audit Policy

Privileges like ALTER ANY TABLE, CREATE ANY TABLE, DROP ANY TABLE, ALTER USER, CREATE USER, DROP USER etc are part of predefined ORA_SECURECONFIG unified audit policy definition.

**Note:The CREATE USER statement  we had issued earlier has been audited by default.

SQL> SELECT

event_timestamp,

dbusername,

object_schema,

object_name

FROM   unified_audit_trail 

WHERE  action_name=’CREATE USER’;



EVENT_TIMESTAMP        DBUSERNAME OBJECT_SCH OBJECT_NAME

------------------------------ ---------- ---------- --------------------

13-APR-20 03.41.02.108624 PM   SYS              AUDITOR

SQL> noaudit policy ORA_SECURECONFIG;

Noaudit succeeded.

Please follow and like us:

Last Update: June 8, 2020  

June 8, 2020 125 Gavin Soorma
Total 0 Votes:
0

Tell us how can we improve this post?

+ = Verify Human or Spambot ?

Add A Knowledge Base Question !

You will receive an email when your question will be answered.

+ = Verify Human or Spambot ?

, , , , ,

Leave a Comment

Your email address will not be published. Required fields are marked *

*
*

Social media & sharing icons powered by UltimatelySocial
Back To Top

Add A Knowledge Base Question !

You will receive an email when your question will be answered.

+ = Verify Human or Spambot ?