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/liboracle@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_nameCON_NAME------------------------------PDB1PSQL> 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_nameCON_NAME------------------------------PDB1PSQL> 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_nameCON_NAME------------------------------PDB1PSQL> 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_nameCON_NAME------------------------------PDB1PSQL> 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_nameCON_NAME------------------------------CDB$ROOTSQL> 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_nameCON_NAME------------------------------PDB1PSQL> 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_nameCON_NAME------------------------------PDB1PSQL> select * from audit_unified_enabled_policies;USER_NAME POLICY_NAME ENABLED_ SUC FAI-------------------- ------------------------- -------- --- ---ALL USERS ORA_SECURECONFIG BY YES YESALL USERS ORA_LOGON_FAILURES BY NO YESALL USERS MY_OBJPRIVS_AUDIT_PDBP1 BY YES YESALL USERS MY_MIX_AUDIT_PDBP1 BY YES YESALL USERS MY_SYSPRIVS_AUDIT_PDBP1 BY YES YESALL 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_nameCON_NAME------------------------------PDB1PSQL> select dbusername,event_timestamp,sql_text,CLIENT_PROGRAM_NAMEfrom 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 DeveloperSCOTT 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_nameCON_NAME------------------------------PDB1PSQL> 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
![Thumbnail [60x60]](https://www.dbi-services.com/blog/wp-content/uploads/2022/12/oracle-square.png)
![Thumbnail [90x90]](https://www.dbi-services.com/blog/wp-content/uploads/2022/08/DWE_web-min-scaled.jpg)
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)