Oct-15

23-Oct

You can enable unified audit for a specific user or conditional..

To audit all users

SQL> AUDIT POLICY audit_login;

To audit a specific user

SQL> AUDIT POLICY audit_login BY scott;

To audit all users except THOMAS

SQL> AUDIT POLICY audit_login EXCEPT thomas;

22-Oct

Oracle Unified Auditing:  Oracle 12c consolidates all kinds of audit records into a single location under AUDSYS schema.  The audit records are visible through a view UNIFIED_AUDIT_TRIAL for the database wide users.  The user granted with the privilege AUDIT_ADMIN can manage the audit logs – can archive or purge the audit records.

Audit records can be captured in two ways

  • Immediate write-mode: records are written to the AUDSYS schema real time as it is created. This will impact the performance of the database due to the real time writing.  You can enable the immediate write mode using
BEGIN
DBMS_AUDIT_MGMT.SET_AUDIT_TRIAL_PROPERTY (
DBMS_AUDIT_MGMT.AUDIT_TRIAL_UNIFIED,
DBMS_AUDIT_MGMT.AUDIT_TRIAL_WRITE_MODE,
DBMS_AUDIT_MGMT.AUDIT_TRIAL_QUEUED_WRITE )
END;

 

  • Queued Write Mode: In queued write mode, the audit records are buffered in the SGA and later written to the disk by a background process (GEN0 – General Task Execution Process).  The memory is allocated in the SGA is based on the parameter UNIFIED_AUDIT_SGA_QUEUE_SIZE, can have the values ranging from 1MB to 30MB.  By default, the value is set to 1MB.    There will be 2 queues in the SGA, so that audit trails can be buffered one queue while the other queue being written the persistent tables in the AUDSYS.

 

The GEN0 process writes the audits records to the AUDSYS schema in every 3 seconds or longer based on the database activity.  However, administrator can flush the audit records to the SYSAUDIT schema using DBMS_AUDIT_MGMT package.

 

To flush the records in all RAC nodes and all PDBs


 exec DBMS_AUDIT_MGMT.FLUSH_UNIFIED_AUDIT_TRIAL (DBMS_AUDIT_MGMT.FLUSH_ALL_INSTANCES);

To flush all PDBs in a single instance


exec DBMS_AUDIT_MGMT.FLUSH_UNIFIED_AUDIT_TRIAL (CONTAINER => DBMS_AUDIT_MGMT.CONTAINER_ALL);

To flush the current container


exec DBMS_AUDIT_MGMT.FLUSH_UNIFIED_AUDIT_TRIAL (CONTAINER => DBMS_AUDIT_MGMT.CONTAINER_CURRENT);

There are two new privileges introduced in 12c associated with unified auditing

AUDIT_ADMIN : is responsible for managing the audit logs like purging or archiving.  No SYSDBA privilege is required to create or mange policies, purge or archive audit records.  Audit administrator role separated from the SYSDBA role and can handle any audit related operations.  Audit administrator must use the DBMA_UNIFIED_MGMT package to administer the unified audit.

AUDIT_VIEWER:  can view and access the audit records

 

Unified Audit policies can be defined for a specific action and component as


SQL> CREATE AUDIT POLICY datapump_policy1 ACTIONS COMPONENT= datapump export;

SQL> AUDIT POLICY datapump_policy1

 

Below example is auditing trigger creation by SYS and SYSTEM


SQL> CREATE AUDIT POLICY ora_trigger_policy  PRIVILAGES CREATE ANY TRIGGER;

SQL> AUDIT POLICY ora_trigger_policy  by SYS, SYSTEM;

 

You can drop a policy


SQL> DROP POLICY ora_trigger_policy;

 

By default the audit tables are located in SYSAUX tablespace and can be re-located to any tablespace using DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_LOCATION,


 BEGIN
 DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_LOCATION (
 AUDIT_TRAIL_TYPE  => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,
AUDIT_TRAIL_LOCATION_VALUE  => ‘AUDIT_TBS’;
END;

 

You can move the audit trail table to a different tablespace as follows


SQL> exec DBMS_UNIFIED_MGMT.MOVE_DBAUDIT_TABLES (‘audit_data’);

 

By default the unified policy option is not enabled for a database, and you can enable it by

  • Shutdown all the instances
  • Cd $ORACLE_HOME/rdbms/lib
  • Link the binaries with i=uniaud_on option as make –f ins_rdbms.mk uniaud_on ioracle ORACLE_HOME=$ORACLE_HOME

Note:  you can disable the unified audit using : make –f ins_rdbms.mk uniaud_off  ioracle ORACLE_HOME=$ORACLE_HOME

  • Start the databases instances
  • Define the default tablespaces for the audit tables

There are following pre-defined polices are available:

  • ORA_SECURECONFIG
  • ORA_ACCOUNT_MGMT
  • ORA_DATABASE_PARAMETER

Views associated with Unified Auditing

  • AUDIT_UNIFIED_POLICIES : lists the system policies configured for auditing
  • AUDIT_UNIFIED_ENABLED_POLICIES: List of all policies enabled in the database

When the database is not open, the audit records are written to the location $ORACLE_BASE/audit/<db_name>/.  Once the database is open, you can use DBMS_UNIFIED_MGMT.LOAD_UNIFIED_AUDIT_FILES to load the files to the unified audit table and the files will be deleted.


 BEGIN

 DBMS_UNIFIED_MGMT.LOAD_UNIFIED_AUDIT_FILES ( DBMS_AUDIT_MGMT.CONTAINER_CURRENT);

END;

12-Oct

 

Unified audit

Privileged Analysis

Data redaction

Temporal archive

Real Time ADDM

Emergency monitoring

Monitoring DB operations

DBRM – CDB Plans and PDB plans

 

Data dictionary for PDB Parameter changes: pdb_spfile$