How to use unified audit trail in oracle database19c with Splunk

September 25, 2020 admin oracle database19c, 0

How to use unified audit trail in oracle database19c with Splunk

Intoduction:
We will use the predefined unified audit policies provided by oracle for the current demo and will not configure unified audit trail or create any specific audit policies for our splunk demo. (The details of Unified audit trail are not part of this post)

Part1. We need to do some verifications before installing and configuring splunk so that our demo will succeed. In this topic, we will use the mixed auditing mode. When a new database is created, this mode is enabled by default. Unified auditing is not enabled by default.

Unified auditing integration with splunk.

• Database server – version

select banner from v$version;
BANNER 
--------------------------------------------------------------------------
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

• Verify that Unified Audit Trail is not enabled

select parameter , value from v$option
where PARAMETER = 'Unified Auditing';
PARAMETER        VALUE
----------------  ----------
Unified Auditing     FALSE

Auditing Activities with the Predefined Unified Audit Policies Oracle Database provides predefined unified audit policies that cover commonly used security-relevant audit settings. Verfiy thaty ORA_SECURECONFIG and ORA_LOGON_FAILURES unified audit policies

• Verify  the existence of the predefined ORA_SECURECONFIG audit policy.

col POLICY_NAME format A20
col AUDIT_OPTION format A40
set PAGES 100
select POLICY_NAME, AUDIT_OPTION
from AUDIT_UNIFIED_POLICIES
where policy_name = 'ORA_SECURECONFIG' order by 2 ;

POLICY_NAME          AUDIT_OPTION
-------------------- ----------------------------------------
ORA_SECURECONFIG     ADMINISTER KEY MANAGEMENT
ORA_SECURECONFIG     ALTER ANY PROCEDURE
ORA_SECURECONFIG     ALTER ANY SQL TRANSLATION PROFILE
ORA_SECURECONFIG     ALTER ANY TABLE
ORA_SECURECONFIG     ALTER DATABASE
ORA_SECURECONFIG     ALTER DATABASE DICTIONARY
ORA_SECURECONFIG     ALTER DATABASE LINK
ORA_SECURECONFIG     ALTER PLUGGABLE DATABASE
ORA_SECURECONFIG     ALTER PROFILE
ORA_SECURECONFIG     ALTER ROLE
ORA_SECURECONFIG     ALTER SYSTEM
ORA_SECURECONFIG     ALTER USER
ORA_SECURECONFIG     AUDIT SYSTEM
ORA_SECURECONFIG     BECOME USER
ORA_SECURECONFIG     CREATE ANY JOB
ORA_SECURECONFIG     CREATE ANY LIBRARY
ORA_SECURECONFIG     CREATE ANY PROCEDURE
ORA_SECURECONFIG     CREATE ANY SQL TRANSLATION PROFILE
ORA_SECURECONFIG     CREATE ANY TABLE
ORA_SECURECONFIG     CREATE DATABASE LINK
ORA_SECURECONFIG     CREATE DIRECTORY
ORA_SECURECONFIG     CREATE EXTERNAL JOB
ORA_SECURECONFIG     CREATE PLUGGABLE DATABASE
ORA_SECURECONFIG     CREATE PROFILE
ORA_SECURECONFIG     CREATE PUBLIC SYNONYM
ORA_SECURECONFIG     CREATE ROLE
ORA_SECURECONFIG     CREATE SQL TRANSLATION PROFILE
ORA_SECURECONFIG     CREATE USER
ORA_SECURECONFIG     DROP ANY PROCEDURE
ORA_SECURECONFIG     DROP ANY SQL TRANSLATION PROFILE
ORA_SECURECONFIG     DROP ANY TABLE
ORA_SECURECONFIG     DROP DATABASE LINK
ORA_SECURECONFIG     DROP DIRECTORY
ORA_SECURECONFIG     DROP PLUGGABLE DATABASE
ORA_SECURECONFIG     DROP PROFILE
ORA_SECURECONFIG     DROP PUBLIC SYNONYM
ORA_SECURECONFIG     DROP ROLE
ORA_SECURECONFIG     DROP USER
ORA_SECURECONFIG     EXECUTE
ORA_SECURECONFIG     EXECUTE
ORA_SECURECONFIG     EXEMPT ACCESS POLICY
ORA_SECURECONFIG     EXEMPT REDACTION POLICY
ORA_SECURECONFIG     GRANT ANY OBJECT PRIVILEGE
ORA_SECURECONFIG     GRANT ANY PRIVILEGE
ORA_SECURECONFIG     GRANT ANY ROLE
ORA_SECURECONFIG     LOGMINING
ORA_SECURECONFIG     PURGE DBA_RECYCLEBIN
ORA_SECURECONFIG     SET ROLE
ORA_SECURECONFIG     TRANSLATE ANY SQL

49 lignes sélectionnées.

• Verify  the existence of the predefined ORA_LOGON_FAILURES audit policy.

col POLICY_NAME format A20
col AUDIT_OPTION format A40
select POLICY_NAME, AUDIT_OPTION
from AUDIT_UNIFIED_POLICIES
where policy_name = 'ORA_LOGON_FAILURES';

POLICY_NAME                AUDIT_OPTION
--------------------       ----------------
ORA_LOGON_FAILURES         LOGON

• Verify that the predefined ORA_LOGON_FAILURES audit policy is enabled by default.

select POLICY_NAME
from AUDIT_UNIFIED_ENABLED_POLICIES
where policy_name = 'ORA_LOGON_FAILURES';

POLICY_NAME 
--------------------
ORA_LOGON_FAILURES

• Verify Audited Users

col action_name format A12
col dbusername format A12
col unified_audit_policies format A40

select  distinct dbusername, action_name, unified_audit_policies 
from unified_audit_trail
where dbusername in (
'HR','SALEM','SPLUNKUSER','ORAUSER','WEBUTIL'
);

DBUSERNAME         ACTION_NAME     UNIFIED_AUDIT_POLICIES
------------       ------------    ------------------------------
SALEM              LOGON           ORA_LOGON_FAILURES
HR                 LOGON           ORA_LOGON_FAILURES
WEBUTIL            LOGON           ORA_LOGON_FAILURES
SPLUNKUSER         LOGON           ORA_LOGON_FAILURES

Part 2. Install and configure Splunk

For more details, refer to https://docs.splunk.com/Documentation/Splunk

• First log into your Splunk entreprise – Dashboard

• From SplunkApps

• Search for the following applications:
  Splunk DB Connect
  Splunk Add-on for Oracle Database

• Click on DbConnect
  Create a new identity

For our demo: Salem is the identity used by Splunk to connect to our pluggable database orclpdb

Configure Inputs

The details of your Inputs.conf – file :

[AUDIT_DATA_INPUT]
batch_upload_size = 1000
connection = SPLUNKPDB_CNT
description = Collect Oracle Audit Unified.
.....
......

should be found on $SPLUNK_HOME/etc/apps/Splunk_TA_oracle/local.

Finally display the audit data in Splunk. (This ends our demo).

 

Ref.:

Splunk: https://docs.splunk.com/Documentation/Splunk

Unified Audit Trail: https://docs.oracle.com/en/database/oracle/oracle-database/19/refrn/UNIFIED_AUDIT_TRAIL.html#GUID-B7CE1C02-2FD4-47D6-80AA-CF74A60CDD1D

Posted on: 2020-11-29

Postfix SMTP Server installation and configuration on Centos8 Oracle WebLogic Server 12c: Configuring JMS Servers and Destinations