Auditing CDB and PDB level in Oracle Multitenant

Updated: Nov 16, 2020

Auditing is very important to ensure the record monitoring to the DBA with it is possible to know the actions performed by the user created in the Database or in the applications that have some type of access to the database, also some type of external access at the level Operating System. A well-planned audit allows you to verify individual actions, such as the type of SQL statement executed or in combinations of data that can include the user name, application, time, actions performed DML, in the case of a multi-tenant environment, you can audit actions both in the Pluggable Database (PDB) as a multi-tenant container level (CDB). All audit records are recorded in the UNIFIED_AUDIT_TRAIL view.


Audit settings

  • Applies to one or more Users:

  • One or More PDB's;

  • CDB level;

  • Complete database environment.

Audit Policy Oracle CDB PDB

Starting the audit procedure


Let's create some users to be audited:

--#USER OF THE AUDIT
CONN / AS SYSDBA
 show con_name;
CON_NAME
------------------------------
CDB$ROOT

CREATE USER C##AUD_SEC_ADMIN IDENTIFIED BY audoracle;
GRANT CREATE SESSION TO C##AUD_SEC_ADMIN CONTAINER=ALL;
GRANT DBA TO C##AUD_SEC_ADMIN CONTAINER=ALL;

--#USER TO BE AUDITED
CONN / AS SYSDBA

CREATE USER C##USR_HR_ADMIN IDENTIFIED BY hroracle;

Local Unified Audit Policy


We will connect with the user who is responsible for administering the audits.


CONN C##AUD_SEC_ADMIN/audoracle@ORADES_PDB01
Connected.
show con_name;

CON_NAME
------------------------------
ORADES_PDB01




The audit policy was created with the following characteristics: the name of the TB_PREVIS table is who will receive all the information and audit all the privileges of CREATE TABLE, DROP TABLE in the container called ORADES_PDB01.

Command to create an audit policy at PDB level

CREATE AUDIT POLICY TB_PREVIS PRIVILEGES CREATE ANY TABLE, DROP ANY TABLE CONTAINER = CURRENT;
Audit policy created.

After creating the Audit Table "TB_AUDIT, we will associate the user" C ## USR_HR_ADMIN "already created above.

AUDIT POLICY TB_PREVIS BY C##USR_HR_ADMIN;
Audit succeeded.

We will connect with the user "C ## USR_HR_ADMIN" being audited and we will create a Table called "TB_REGISTER".

CONN / AS SYSDBA
ALTER SESSION SET CONTAINER=ORADES_PDB01;
Session altered.
GRANT CREATE SESSION TO C##USR_HR_ADMIN CONTAINER=CURRENT;
Grant succeeded.
GRANT DBA TO C##USR_HR_ADMIN CONTAINER=CURRENT;
Grant succeeded.
CONN C##USR_HR_ADMIN/hroracle@ORADES_PDB01
Connected.
CREATE TABLE HR.TB_REGISTER ( NAME varchar2(30), REGISTER number(10), ADDRESS varchar2(20));
Table created.

Let's connect to the user we do to verify the actions of the audited user.

CONN C##AUD_SEC_ADMIN/audoracle@ORADES_PDB01
Connected.

Through Query in the view called "CDB UNIFIED_AUDIT_TRAIL ', we can see all the actions performed by the audited user C## USR_HR_ADMIN.

SET LINESIZE 150;
SET PAGESIZE 150;
COL OBJECT_NAME FORM A30;
COL OBJECT_SCHEMA FORM A30 
SELECT DBID, ACTION_NAME, OBJECT_SCHEMA,OBJECT_NAME FROM CDB_UNIFIED_AUDIT_TRAIL WHERE DBUSERNAME ='C##USR_HR_ADMIN';

Auditing common application SYSTEM users


In the command below we will have an example of creating a common user audit policy, the query will check all Table actions in all PDB's including the root of the CDB. The policy will audit the use of the DROP TABLE, SELECT ANY TABLE of privilege on containers.


CONN C##AUD_SEC_ADMIN/audoracle@ORADES_PDB01
Connected.

CREATE AUDIT POLICY aud_app_pdb_admin
 ACTIONS SELECT ON hr.rep_tab, DROP TABLE
 PRIVILEGES SELECT ANY TABLE
 CONTAINER = ALL;

AUDIT POLICY aud_app_pdb_admin by SYSTEM, C##AUD_SEC_ADMIN

We finished our audit study.