By Mouhamadou Diaw
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
1
2
3
4
5
|
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)
1
2
3
|
oracle@vmoratest1: /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 oracle@vmoratest1 $ make -f ins_rdbms.mk uniaud_on ioracle ORACLE_HOME=$ORACLE_HOME |
And now the unified auditing is enabled
1
2
3
4
5
|
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)
1
2
3
|
CREATE AUDIT POLICY policy_name action1 [,action2 ] CONTAINER = CURRENT ; |
2.1.1- System privilege policies
These policies are based on system privileges
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
|
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
1
2
3
4
5
6
7
8
9
|
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
1
2
3
4
5
6
7
8
9
|
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
1
2
3
4
5
6
7
|
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.
1
2
3
|
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
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
1
2
3
4
5
6
7
8
9
|
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
1
2
3
4
5
6
7
8
9
10
11
12
13
|
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
1
2
3
|
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)
1
2
3
|
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
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
|
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
1
2
3
4
5
6
7
8
9
10
11
12
13
14
|
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
1
2
3
4
5
6
7
8
9
10
11
|
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
Marcelo
04.01.2024Hi, good article!
i was wondering if theres a view like audit_unified_policies and audit_unified_enabled_policies that you can query from root container and can see all policies defined and enabled in all pdbs? i realized that when you query those views, they only show information for current container (theres no con_id column on them)