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.
Applies to one or more Users:
One or More PDB's;
Complete database environment.
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.