Infrastructure at your Service

Mouhamadou Diaw

Oracle 12C Unified Auditing Feature in a multitenant environment

In oracle 12c we have a new feature called Unified Auditing.

What is it, how to implement it, how it works in a multitenant environment thatā€™s what we will try to explain briefly in this article.

Traditional oracle audit involves many audit-trail locations and tables and does not follow a standard.

To help with this oracle 12c has introduced Unified Auditing

Ā 1- Enabling Unified Auditing

By default the feature is disabled

SQL> select value from v$option where lower(parameter)='unified auditing';

VALUE
----------------------------------------------------------------
FALSE

To enable we have to relink in the $ORACLE_HOME/rdbms/lib (listeners and databases should be stopped)

[email protected]:/u00/app/oracle/product/12.1.0/db_2_0/rdbms/lib/ [CDB1P] pwd
/u00/app/oracle/product/12.1.0/db_2_0/rdbms/lib
[email protected] $ make -f ins_rdbms.mk uniaud_on ioracle ORACLE_HOME=$ORACLE_HOME

And now the unified auditing is enabled

SQL> select value from v$option where lower(parameter)='unified auditing';

VALUE
----------------------------------------------------------------
TRUE

2- Create audit policies

The first step is to create audit policies.

In a multitenant environment we have two types of audit policies: local audit policy and common audit policy

2.1- Local audit policies

Apply only to the current PDB (it is the default, CURRENT is optional)

CREATE AUDIT POLICY policy_name
 action1 [,action2 ]
 CONTAINER = CURRENT;
2.1.1- System privilege policies

TheseĀ Ā policies are based on system privileges

SQL> show con_name

CON_NAME
------------------------------
PDB1P

SQL> create audit policy my_sysprivs_audit_pdbp1 PRIVILEGES SELECT ANY TABLE, ALTER ANY TABLE;

Audit policy created.

SQL> select distinct POLICY_NAME, COMMON from audit_unified_policies where lower(policy_name) like 'my%';

POLICY_NAME               COM
------------------------- ---
MY_SYSPRIVS_AUDIT_PDBP1   NO
2.1.2- Object privilege policies

TheseĀ policies are based on object privileges

SQL> show con_name

CON_NAME
------------------------------
PDB1P

SQL> create audit policy my_objprivs_audit_pdbp1 ACTIONS SELECT, DELETE, UPDATE ON SCOTT.DEPT_BIS;

Audit policy created.
2.1.3- Mix audit policies

Based on both object and system privileges

SQL> show con_name

CON_NAME
------------------------------
PDB1P

SQL> create audit policy my_mix_audit_pdbp1 PRIVILEGES CREATE ANY TABLE, ALTER USER ACTIONS SELECT ON SCOTT.EMP, ALTER DATABASE LINK;

Audit policy created.
2.1.4- Function based policies

A very interesting thing is that we can haveĀ more complex policies using some oracle functions (note that all functions are not supported: see oracle doc for more info).

For example, we can create an audit policy for users querying SCOTT EMP table using SQL Developer

SQL> show con_name

CON_NAME
------------------------------
PDB1P

SQL> create audit policy my_sqldvper_audit_pdbp1 ACTIONS SELECT ON scott.emp WHEN 'upper(SYS_CONTEXT(''USERENV'',''MODULE''))=''SQL DEVELOPER''' EVALUATE PER STATEMENT;

2.2- common audit policies

Apply to all pluggable databases and concern only common objects. Can be defined only when connecting to the root container.

CREATE AUDIT POLICY policy name
 action1 [,action2 ]
 CONTAINER = ALL;

Let’s consider the following example

c##scott_common_role is a common role

PDB1P and PDB2P are 2 pluggable databases

audit3

I can create a global audit policy for both pluggable databases to audit SELECT on SCOTT tables (in the 2 pluggable databases).
For this I can do the following steps

2.2.1- Create a common role in the root container
SQL> show con_name

CON_NAME
------------------------------
CDB$ROOT

SQL> create role c##scott_common_role container=all;

Role created.
2.2.2- Grant SELECT to the common role and grant the role to users
SQL> show con_name

CON_NAME
------------------------------
PDB1P

SQL> grant select on scott.salgrade to c##scott_common_role;

Grant succeeded.

SQL> grant c##scott_common_role to locuser;

Grant succeeded.
2.2.3- create a global audit policy using the common role
SQL> create audit policy my_common_policy ROLES c##scott_common_role container=all;

Audit policy created.

And then every time that the user locuser will query the SALGRADE table, an audit record will be added (policy should be enabled, please see next section).

3- Enable Policies

After creating policies, just enable them by using AUDIT (NOAUDIT to disable)

SQL> audit policy MY_OBJPRIVS_AUDIT_PDBP1;

Audit succeeded.

Querying AUDIT_UNIFIED_ENABLED_POLICIES, we can see that there are also 2 predefined policies by oracle

SQL> show con_name

CON_NAME
------------------------------
PDB1P

SQL> select * from audit_unified_enabled_policies;

USER_NAME            POLICY_NAME               ENABLED_ SUC FAI
-------------------- ------------------------- -------- --- ---
ALL USERS            ORA_SECURECONFIG          BY       YES YES
ALL USERS            ORA_LOGON_FAILURES        BY       NO  YES
ALL USERS            MY_OBJPRIVS_AUDIT_PDBP1   BY       YES YES
ALL USERS            MY_MIX_AUDIT_PDBP1        BY       YES YES
ALL USERS            MY_SYSPRIVS_AUDIT_PDBP1   BY       YES YES
ALL USERS            MY_SQLDVPER_AUDIT_PDBP1   BY       YES YES

4- Audit results

All results of Unified auditing are stored in one table UNIFIED_AUDIT_TRAIL and should be queried from the concerning database

SQL> show con_name

CON_NAME
------------------------------
PDB1P

SQL> select dbusername,event_timestamp,sql_text,CLIENT_PROGRAM_NAME
from unified_audit_trail where upper(sql_text) like '%EMP%'
and upper(CLIENT_PROGRAM_NAME) like '%DEVELOPER%';

DBUSERNAME EVENT_TIMESTAMP SQL_TEXT CLIENT_PROGRAM_NAME
---------- ------------------------- ------------------------- -------------------------
LOCUSER 21.03.16 14:17:10.092721 select * from scott.emp SQL Developer
SCOTT 21.03.16 14:36:47.873091 select * from scott.emp SQL Developer

5-Audit purge

To purge the UNIFIED_AUDIT_TRAIL, the traditional delete will not work

SQL> show con_name

CON_NAME
------------------------------
PDB1P

SQL> delete from unified_audit_trail;
delete from unified_audit_trail
            *
ERROR at line 1:
ORA-02030: can only select from fixed tables/views

We have to use DBMS_AUDIT_MGMT.CLEAN_AUDIT_TRAIL

Conclusion:

Oracle unified auditing in a multitenant environment is managed in the same way that in a traditional database except the notion of common global policy

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Mouhamadou Diaw
Mouhamadou Diaw

Consultant