Oracle Auditing in RAC & Standalone: Detailed Implementation.

Oracle database auditing :-

Oracle database auditing is the process of tracking and monitoring database activities to ensure security and compliance.
It keeps the record and action by users and database administrators like Logins, Database access, modification or changes and configuration changes of the database.
Auditing can help to identify unauthorized activities and enforce security policies.
All the SYS operations audit records goes to OS level into AUDIT_FILE_DEST location.
Audit_file_dest default Location is $ORACLE_HOME/rdbms/audit. The default value is: ORACLE_BASE / admin/ ORACLE_SID/adump.
This create files with the .aud extension to audit operations at os level.

Types of Auditing :-

Standard Auditing :- It tracks specific SQL statements, privileges, or schema objects.
Fine-Grained Auditing :- Monitors data access at a more granular level, such as specific rows or columns .
Unified Auditing :- A newer feature that consolidates auditing data into a single repository, simplifying the management of audit policies.

To check database auditing is enabled :- If AUDIT_TRAIL parameter is set NONE it means it was Disabled.

SQL> show parameter audit
NAME TYPE VALUE
----------------------- ----------- --------------
audit_file_dest string /u01/app/oracle/admin/prim/audit
audit_syslog_level string
audit_sys_operations boolean TRUE
audit_trail string NONE
unified_audit_common_systemlog string
unified_audit_sga__queue_size integer 12321
unified_audit_systemlog

If you set audit_sys_operations=true. There is a separate file for every session that connects as SYSDBA in oracle database and by default this parameter is TRUE.

The AUDIT_TRAIL parameter status :-

NONE : – Database auditing is not Enabled .
OS : – Audit logs are stored at OS level, not inside the database.
DB :– Audit records are stored inside database.(If we are creating database by DBCA Utility , then default it is DB else it is none.)
DB,EXTENDED :– Same as DB but populates SQL_BIND & SQL_TEXT output.
XML :– Audit records are stored at OS level in XML format.
XML,EXTENDED :– Same as XML but populates the SQL_BIND & SQL_TEXT output.

Implementation Step By Step of UNIFIED AUDITING :-

Prerequisites :-

Ensure Oracle database version should more than 12c or higher for unified auditing policy.
License Check the oracle licenses that includes unified auditing or not.
Database compatibility: Should more than oracle 12.0.0 or higher.

SELECT NAME,VAULE from v$parameter WHERE NAME='compatible';

for that SYSDBA privilege is required to enable or disable.
OS Space Requirement: ensure the available space maintained in for the unified auditing. to store the database and OS logs.
You can create separate User for Audit management of your database.
For different Users specification you can create different POLICY, According to that you can provide the different privilege's.

STEP 01 :- [ Policy name --- ddl_actions ]

CREATE AUDIT POLICY <Policy_Name> ACTIONS CREATE TABLE, DROP TABLE, ALTER TABLE, TRUNCATE TABLE, CREATE INDEX, DROP INDEX, ALTER INDEX, CREATE SEQUENCE, DROP TABLESPACE,SELECT;

e,g. :-

CREATE AUDIT POLICY ddl_actions ACTIONS CREATE TABLE, DROP TABLE, ALTER TABLE,TRUNCATE TABLE,CREATE INDEX,DROP INDEX,ALTER INDEX,CREATE SEQUENCE,DROP TABLESPACE,SELECT;

According to you can customize the Audit Policy and for different user. (You can create different policy )

STEP 02 :- Ensure that enough space maintained in OS mount point level at least 2GB above.

ALTER SYSTEM SET audit_file_dest ='<AUDIT_LOG_LOCATION>' scope=spfile;

e.g.

ALTER SYSTEM SET audit_file_dest ='/oracle_audit/Audit_data/Ganesh' scope=spfile;

According to your enviroment dependent you can set AUDIT_TRAIL parameter. Like :-

ALTER SYSTEM SET audit_trail= '<AUDIT_TRAIL >' SCOPE=spfile;

e.g.

ALTER SYSTEM SET audit_trail= 'DB','EXTENDED' SCOPE=spfile;

Ensure that enough space maintained in OS mount point level at least 2GB above.

STEP 03:- USER_NAME --- Those who user are present in database. ( SELECT USERNAME FROM DBA_USERS ).

AUDIT POLICY <Policy_Name> by <USER_NAME>;

e.g :-

AUDIT POLICY ddl_actions by GANESH,RUSHIKESH,AKHIL,SAGAR,YASH;

STEP 04 :- Required for oracle Database bounce after that auditing reflected, Once the who users are added in audit policy, if that user perform any kind of action the action automatically capture in database and os level.

STEP 05 : - Shut down the database.

SHUTDOWN IMMEDIATE;

STEP 06 : - Start the database

STARTUP NOMOUNT;
ALTER DATABASE MOUNT;
ALTER DATABASE OPEN;

RAC & Standalone Database :-

Both RAC & Standalone Database are above STEP are same to Implementation of oracle database auditing.
In RAC environment you have to ensure that in every INSTANCE/NODE has same mount point (AUDIT_FILE_DEST) location.
After that you can restart/bounce your INSTANCE one by one or at-a-time.

Once it has been done then audit can enabled & Logs are generated in OS level(You can read also audit file in OS level.).

To check audit is Enabled or not .

select name || '=' || value PARAMETER from sys.v_$parameter where name like '%audit%';

SQL> show parameter audit
NAME TYPE VALUE
----------------------- ----------- --------------
audit_file_dest string /u01/app/oracle/admin/prim/audit
audit_syslog_level string
audit_sys_operations boolean TRUE
audit_trail string 'DB,EXTENDED'
unified_audit_common_systemlog string
unified_audit_sga__queue_size integer 12321
unified_audit_systemlog

-----------------------------------------------------------------------------------
How to find the Audit LOGS :-

SET PAGES 200 LINES 200
column event_timestamp format a30
column action_name format a13
column object_schema format a15
column object_name format a25
select event_timestamp, action_name, object_schema, object_name
from unified_audit_trail
where dbusername = '<USER_NAME>' order by event_timestamp;

STATEMENT for AUDIT :-

SET PAGES 200 LINES 200
column user_name format a10
column audit_option format a40
select * from sys.dba_stmt_audit_opts;

Privilege Audits :-

select * from dba_priv_audit_opts;

How to Disable auditing in database :-

NOAUDIT;

How to Disable audit Policy :-

NOAUDIT POLICY <Policy_Name> ;

e,g.

NOAUDIT POLICY ddl_actions ;

How to Disable auditing in database :-

NOAUDIT POLICY <Policy_Name> BY <USER_NAME>;

e.g.

NOAUDIT POLICY ddl_actions BY GANESH;

-----------------------------------------------------------------------------------------------

Create and schedule the purge jobs PURGE_ALL_AUDIT_TRAILS in Every day :-

RAC / Standalone :-

BEGIN
DBMS_AUDIT_MGMT.create_purge_job(
audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_ALL,
audit_trail_purge_interval => 48 /* hours */,
audit_trail_purge_name => 'PURGE_ALL_AUDIT_TRAILS',
use_last_arch_timestamp => TRUE );
END;
/

According to you audit_trail_purge_interval you can set. To verify at database level using below command...

SELECT job_action
FROM dba_scheduler_jobs
WHERE job_name = 'PURGE_ALL_AUDIT_TRAILS';

Remove/Purge unified_audit_trail using timestamp :-

RAC :-

BEGIN
DBMS_AUDIT_MGMT.set_last_archive_timestamp(
audit_trail_type => DBMS_AUDIT_MGMT.audit_trail_unified,
last_archive_time => SYSTIMESTAMP-7,
rac_instance_number => 1,
container => DBMS_AUDIT_MGMT.container_current
);
END;
/

It means it remove all rows with timestamp > 7 days in RAC enviroment.

STANDALONE :-

BEGIN
DBMS_AUDIT_MGMT.set_last_archive_timestamp(
audit_trail_type => DBMS_AUDIT_MGMT.audit_trail_unified,
last_archive_time => SYSTIMESTAMP-7,
container => DBMS_AUDIT_MGMT.container_current
);
END;
/

It means it remove all rows with timestamp > 7 days in STANDALONE environment.

For more details Please How to Disable Oracle Database Auditing (Doc ID 1528170.1)

--------------------------------- Thanks for Reading ----------------------------------

If you face any issues related to database administration, feel free to write down your queries in the comments. I will do my best to resolve your issues and get back to you as soon as possible.

Oracle Auditing in RAC & Standalone: Detailed Implementation