Intro
Database auditing is a critical component of data security, providing vital
insights into user activity, changes made to the database, and potential
threats lurking within the system. By monitoring user actions and tracking
modifications, auditing ensures regulatory compliance and helps identify
anomalies that may signify malicious or unauthorized activities.
Through meticulous auditing, organizations can detect suspicious behavior and
promptly intervene to mitigate risks and prevent potential damage. This
proactive approach not only safeguards sensitive data but also enhances
overall security posture by addressing vulnerabilities and enforcing access
controls effectively.
-
Security:
- Forensic Analysis:
Auditing helps ensure the security of sensitive data stored within
databases. By monitoring and tracking access to databases, organizations can
detect and prevent unauthorized access, potential data breaches, or insider
threats. It helps in identifying suspicious activities and potential
security vulnerabilities that need to be addressed promptly.
Compliance:
Many industries and organizations are subject to regulatory requirements and
compliance standards (such as GDPR, HIPAA, PCI DSS, etc.) that mandate the
auditing and monitoring of database activities. Auditing helps organizations
demonstrate compliance with these regulations by providing detailed records
of who accessed what data, when, and for what purpose.
Risk Management:
Auditing assists in risk management by providing insights into potential
risks associated with data handling and access. By identifying patterns of
unauthorized access or unusual activities, organizations can take proactive
measures to mitigate risks and strengthen their overall security posture.
Data Integrity:
Auditing ensures the integrity of data stored in databases by tracking
changes made to the data. It helps maintain data accuracy and
reliability by providing a trail of modifications, updates, and deletions,
which is crucial for data quality assurance and maintaining trust in the
information stored within the database.
-
In the event of a security incident or data breach, auditing provides a
valuable source of information for forensic analysis. Detailed audit logs can
be used to reconstruct events leading up to the incident, identify the root
cause, and facilitate incident response and recovery efforts.
- Performance Monitoring:
Auditing can also contribute to performance monitoring and optimization of
database systems. By analyzing audit logs, organizations can identify
bottlenecks, optimize queries, and improve overall database performance.
Auditing promotes accountability and transparency within organizations by
holding individuals accountable for their actions regarding database access
and usage. It creates a culture of responsibility and ensures that employees
understand the importance of adhering to data access policies and
procedures.
Oracle Database Auditing is a comprehensive feature that allows organizations
to monitor and record activities occurring within the Oracle database
environment. It provides detailed information about user actions, system
changes, and access to sensitive data, enabling organizations to maintain
security, ensure compliance with regulatory requirements, and detect and
respond to potential threats effectively.
What is Oracle Unified Auditing?
Oracle Unified Auditing is a comprehensive auditing solution introduced in
Oracle Database 12c that consolidates database auditing into a single,
unified framework. It replaces the traditional auditing mechanisms, such as
the AUDIT_TRAIL parameter and the AUDIT command, with a more streamlined and
efficient approach to auditing.
Unified Auditing comes standard with Oracle Enterprise Edition; no additional license is required. It is installed by default, but not fully enabled by default.
There are two modes of operation to allow for a transition from pre-12c auditing
- Mixed Mode: default 12c option. All pre-12c log and audit functionality and configurations work as before. New Unified Auditing functionality is also available. Log data is available in both the traditional locations as well as a new view SYS.UNIFIED_AUDIT_TRAIL. Also, log data continues to be written in clear text when Syslog is used.
Figure 1: Oracle unified auditing mix mode.
- Full Mode or PURE mode: enabled only by stopping the database and relinking the Oracle kernel. Once enabled, pre-12c log and audit configurations are ignored, and audit data is saved using the Oracle SecureFiles, which is a proprietary file format. Because of this, Syslog is not supported. All audit data can be found in the view SYS.UNIFIED_AUDIT_TRAIL.
In this article, I will elaborate on how we can enable unified auditing in Oracle E-Businees Suite.
This is the Oracle meta link note to enable unified auditing in EBS :
Enabling Unified Auditing in Oracle E-Business Suite Release 12.2 with Oracle Database 19c or 12c (Doc ID 2777404.1)Pre-Requisites:
EBS application-level patches:
Validate the patches are applied before enabling the unified patching:
· At a minimum, R12.AD.C.delta.13 Patch 32394134 and R12.TXK.C.delta.13 Patch 32392507 is needed. See Document 1617461.1, Applying the Latest AD and TXK Release Update Packs to Oracle E-Business Suite Release 12.2
One of the following
- Oracle E-Business Suite Release 12.2.11 Release Update Patch 31856789. See Document 2758997.1, Oracle E-Business Suite Release 12.2.11 Readme.
or
- Oracle E-Business Suite Release 12.2.3 through Release 12.2.10 and the EBS System Schema Migration Consolidated Patch (Patch 31817501:12.2.0). See Document 2774309.1, Applying the Oracle E-Business Suite Consolidated Patch for EBS System Schema Migration.
Database level patches
- Validate the database patches for known issues. For the 19c instance, we need to make sure 32969863 is applied.
How to validate patches have been applied to the EBS instance:
Apply patches:
Oracle Application Manager Patch Wizard Does Not Show Patches Applied
Via Downtime Mode Or Hotpatch Mode (Doc ID 2061618.1)
select ad_patch.is_patch_applied('R12',-1,32394134) from dual;
select ad_patch.is_patch_applied('R12',-1,32392507) from dual;
select ad_patch.is_patch_applied('R12',-1,31856789) from dual;
EXPLICIT = applied
NOT APPLIED = not applied/aborted)
SQL> select ad_patch.is_patch_applied('R12',-1,32394134) from dual;
AD_PATCH.IS_PATCH_APPLIED('R12',-1,32394134)
--------------------------------------------------------------------------------
EXPLICIT
SQL> select ad_patch.is_patch_applied('R12',-1,32392507) from dual;
AD_PATCH.IS_PATCH_APPLIED('R12',-1,32392507)
--------------------------------------------------------------------------------
EXPLICIT
SQL> select ad_patch.is_patch_applied('R12',-1,31856789) from dual;
AD_PATCH.IS_PATCH_APPLIED('R12',-1,31856789)
--------------------------------------------------------------------------------
EXPLICIT
Validate database patches.
Check the current database patch level.
[oracle@ebs-12-2-12 ~]$ /u01/install/APPS/19.0.0/OPatch/opatch lspatches
35638318;JDK BUNDLE PATCH 19.0.0.0.231017
35239280;DSTV42 UPDATE - TZDATA2023C - NEED OJVM FIX
35648110;OJVM RELEASE UPDATE: 19.21.0.0.231017 (35648110)
35643107;Database Release Update : 19.21.0.0.231017 (35643107)
29585399;OCW RELEASE UPDATE 19.3.0.0.0 (29585399)
OPatch succeeded.
Check 32969863 patches are applied to the database
Note:
Make sure this patch is applied on the database home binary.
/u01/install/APPS/19.0.0/OPatch/opatch lsinventory -oh $OH | grep ^Patch | grep 32969863
[oracle@ebs-12-2-12 ~]$ /u01/install/APPS/19.0.0/OPatch/opatch lsinventory -oh $OH | grep 32969863
32969466, 32969863, 32970478, 32972633, 32972716, 32973035, 32973406
[oracle@ebs-12-2-12 ~]$
How to apply Application level unified patch.
This Patch 33339495:R12.FND.C is available to assist you with creating and enabling recommended unified auditing policies in your Oracle E-Business.Patch 33339495:R12.FND.C
includes the recommended guidelines for enabling unified auditing policies in
your Oracle E-Business Suite database. The creation and enablement of
predefined Oracle E-Business Suite unified auditing policies and predefined
Oracle Database unified auditing policies as defined in this section and as
provided in Patch 33339495:R12.FND.C is not mandatory.
Applying this patch will generate scripts to create pre-defined
policies.
Patch applying commands :
This is the hot patch and can be apply this to directly to run the file system.
cd /u01/install/APPS/fs_ne/EBSapps/patch
cp ~/PATCHES/p33339495_R12.FND.C_R12_GENERIC.zip .
unzip p33339495_R12.FND.C_R12_GENERIC.zip
adop phase=apply patches=33339495 hotpatch=yes
######## Apply patch
Patch 33339495: PATCH REQUEST FOR EBS 12.2 UNIFIED AUDITING POLICY SCRIPTS
. /u01/install/APPS/EBSapps.env run
adop phase=apply patches=33339495 hotpatch=yes
Expected output
[oracle@ebs-12-2-12 patch]$ adop phase=apply patches=33339495 hotpatch=yes
Enter the APPS password:
Enter the EBS_SYSTEM password:
Enter the WLSADMIN password:
Validating credentials.
Initializing.
Run Edition context : /u01/install/APPS/fs1/inst/apps/ebsdb_apps/appl/admin/ebsdb_apps.xml
Patch edition context: /u01/install/APPS/fs2/inst/apps/ebsdb_apps/appl/admin/ebsdb_apps.xml
Patch file system free space: 107.90 GB
Validating system setup.
Node registry is valid.
Checking for existing adop sessions.
[INFO] ICM is not down
[INFO] Connection to http://ebs-12-2-12.sub10241841160.oemvcn.oraclevcn.com:8000 successful
[WARNING] You should only specify hotpatch mode when directed to by the patch readme.
No pending session exists.
Starting new adop session.
===========================================================================
ADOP (C.Delta.14)
Session ID: 118
Node: apps
Phase: apply
Log: /u01/install/APPS/fs_ne/EBSapps/log/adop/118/20240125_163513/adop.log
===========================================================================
Applying patch 33339495.
Log: /u01/install/APPS/fs_ne/EBSapps/log/adop/118/20240125_163513/apply/apps/33339495/log/u33339495.log
Running finalize actions for the patches being applied.
Log: @ADZDSHOWLOG.sql "2024/01/25 16:36:14"
Running cutover actions for the patches being applied.
Creating workers to process cutover DDL in parallel
Log: /u01/install/APPS/fs_ne/EBSapps/log/adop/118/20240125_163513/apply/apps/log/cutover.log
Performing database cutover in Quick mode
Generating post apply reports.
Generating log report.
Output: /u01/install/APPS/fs_ne/EBSapps/log/adop/118/20240125_163513/apply/apps/adzdshowlog.out
The apply phase completed successfully.
adop exiting with status = 0 (Success)
[oracle@ebs-12-2-12 patch]$
Unified Auditing Policies for Oracle E-Business Suite Databases
- Policy Name: This column provides the name of the predefined Oracle Database unified auditing policy or the recommended name for the Oracle E-Business Suite recommended unified auditing policy.
- Policy Type - This column states whether the policy is a predefined Oracle Database unified auditing policy or one recommended by Oracle E-Business Suite.
- Description - This column provides a brief description of the unified auditing policy.
- Policy Location - This column describes whether the policy is created, enabled, or both, in an Oracle E-Business Suite multitenant database in a PDB, CDB, or in a non-multitenant database.
Enable Pure Unified Auditing
Steps to enable unified auditing parameter
- Shut down the EBS application Tire.
- Disable the audit trail parameter.
- Shut down the EBS database Tire.
- Relink the unified auditing binary.
- Startup the database Tier.
- Startup the application Tire
Shutdown the application
We need to stop the application before making any changes at the database level. For this unified auditing change need to take downtime.
[oracle@ebs-12-2-12 sql]$ $ADMIN_SCRIPTS_HOME/adstpall.sh
You are running adstpall.sh version 120.22.12020000.7
Enter the APPS username: apps
Enter the APPS password:
Enter the WebLogic Server password:
The logfile for this session is located at /u01/install/APPS/fs1/inst/apps/ebsdb_apps/logs/appl/admin/log/adstpall.log
Disable the audit trail parameter
We are disabling the audit trail parameter to use pure unified auditing mode.
SQL> alter system set audit_trail=none scope=spfile;
System altered.
Shutdown the EBS database Tire
As we need to perform a relinking to enable unified auditing, we need to shut down the database.
[oracle@ebs-12-2-12 ~]$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Thu Jan 25 16:54:42 2024
Version 19.21.0.0.0
Copyright (c) 1982, 2022, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.21.0.0.0
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 ebsdb READ WRITE NO
SQL> shut immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> exit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.21.0.0.0
Relinked the unified auditing binary.
Relinking commands:
$ cd $ORACLE_HOME/rdbms/lib$ make -f ins_rdbms.mk uniaud_on ioracle ORACLE_HOME=$ORACLE_HOME
Expected output for relinking :
[oracle@ebs-12-2-12 ~]$ cd $ORACLE_HOME/rdbms/lib
[oracle@ebs-12-2-12 lib]$ make -f ins_rdbms.mk uniaud_on ioracle ORACLE_HOME=$ORACLE_HOME
/usr/bin/ar d /u01/install/APPS/19.0.0/rdbms/lib/libknlopt.a kzanang.o
/usr/bin/ar cr /u01/install/APPS/19.0.0/rdbms/lib/libknlopt.a /u01/install/APPS/19.0.0/rdbms/lib/kzaiang.o
chmod 755 /u01/install/APPS/19.0.0/bin
cd /u01/install/APPS/19.0.0/rdbms/lib/;\
/usr/bin/ar r /u01/install/APPS/19.0.0/rdbms/lib/libknlopt.a `/usr/bin/ar t /u01/install/APPS/19.0.0/rdbms/lib/libknlopt.a` ;
- Linking Oracle
rm -f /u01/install/APPS/19.0.0/rdbms/lib/oracle
/u01/install/APPS/19.0.0/bin/orald -o /u01/install/APPS/19.0.0/rdbms/lib/oracle -m64 -z noexecstack -Wl,--disable-new-dtags -L/u01/install/APPS/19.0.0/rdbms/lib/ -L/u01/install/APPS/19.0.0/lib/ -L/u01/install/APPS/19.0.0/lib/stubs/ -Wl,-E /u01/install/APPS/19.0.0/rdbms/lib/opimai.o /u01/install/APPS/19.0.0/rdbms/lib/ssoraed.o /u01/install/APPS/19.0.0/rdbms/lib/ttcsoi.o -Wl,--whole-archive -lperfsrv19 -Wl,--no-whole-archive /u01/install/APPS/19.0.0/lib/nautab.o /u01/install/APPS/19.0.0/lib/naeet.o /u01/install/APPS/19.0.0/lib/naect.o /u01/install/APPS/19.0.0/lib/naedhs.o /u01/install/APPS/19.0.0/rdbms/lib/config.o -ldmext -lserver19 -lodm19 -lofs -lcell19 -lnnet19 -lskgxp19 -lsnls19 -lnls19 -lcore19 -lsnls19 -lnls19 -lcore19 -lsnls19 -lnls19 -lxml19 -lcore19 -lunls19 -lsnls19 -lnls19 -lcore19 -lnls19 -lclient19 -lvsnst19 -lcommon19 -lgeneric19 -lknlopt -loraolap19 -lskjcx19 -lslax19 -lpls19 -lrt -lplp19 -ldmext -lserver19 -lclient19 -lvsnst19 -lcommon19 -lgeneric19 `if [ -f /u01/install/APPS/19.0.0/lib/libavserver19.a ] ; then echo "-lavserver19" ; else echo "-lavstub19"; fi` `if [ -f /u01/install/APPS/19.0.0/lib/libavclient19.a ] ; then echo "-lavclient19" ; fi` -lknlopt -lslax19 -lpls19 -lrt -lplp19 -ljavavm19 -lserver19 -lwwg `cat /u01/install/APPS/19.0.0/lib/ldflags` -lncrypt19 -lnsgr19 -lnzjs19 -ln19 -lnl19 -lngsmshd19 -lnro19 `cat /u01/install/APPS/19.0.0/lib/ldflags` -lncrypt19 -lnsgr19 -lnzjs19 -ln19 -lnl19 -lngsmshd19 -lnnzst19 -lzt19 -lztkg19 -lmm -lsnls19 -lnls19 -lcore19 -lsnls19 -lnls19 -lcore19 -lsnls19 -lnls19 -lxml19 -lcore19 -lunls19 -lsnls19 -lnls19 -lcore19 -lnls19 -lztkg19 `cat /u01/install/APPS/19.0.0/lib/ldflags` -lncrypt19 -lnsgr19 -lnzjs19 -ln19 -lnl19 -lngsmshd19 -lnro19 `cat /u01/install/APPS/19.0.0/lib/ldflags` -lncrypt19 -lnsgr19 -lnzjs19 -ln19 -lnl19 -lngsmshd19 -lnnzst19 -lzt19 -lztkg19 -lsnls19 -lnls19 -lcore19 -lsnls19 -lnls19 -lcore19 -lsnls19 -lnls19 -lxml19 -lcore19 -lunls19 -lsnls19 -lnls19 -lcore19 -lnls19 `if /usr/bin/ar tv /u01/install/APPS/19.0.0/rdbms/lib/libknlopt.a | grep "kxmnsd.o" > /dev/null 2>&1 ; then echo " " ; else echo "-lordsdo19 -lserver19"; fi` -L/u01/install/APPS/19.0.0/ctx/lib/ -lctxc19 -lctx19 -lzx19 -lgx19 -lctx19 -lzx19 -lgx19 -lclscest19 -loevm -lclsra19 -ldbcfg19 -lhasgen19 -lskgxn2 -lnnzst19 -lzt19 -lxml19 -lgeneric19 -locr19 -locrb19 -locrutl19 -lhasgen19 -lskgxn2 -lnnzst19 -lzt19 -lxml19 -lgeneric19 -lgeneric19 -lorazip -loraz -llzopro5 -lorabz2 -lorazstd -loralz4 -lipp_z -lipp_bz2 -lippdc -lipps -lippcore -lippcp -lsnls19 -lnls19 -lcore19 -lsnls19 -lnls19 -lcore19 -lsnls19 -lnls19 -lxml19 -lcore19 -lunls19 -lsnls19 -lnls19 -lcore19 -lnls19 -lsnls19 -lunls19 -lsnls19 -lnls19 -lcore19 -lsnls19 -lnls19 -lcore19 -lsnls19 -lnls19 -lxml19 -lcore19 -lunls19 -lsnls19 -lnls19 -lcore19 -lnls19 -lasmclnt19 -lcommon19 -lcore19 -ledtn19 -laio -lons -lmql1 -lipc1 -lfthread19 `cat /u01/install/APPS/19.0.0/lib/sysliblist` -Wl,-rpath,/u01/install/APPS/19.0.0/lib -lm `cat /u01/install/APPS/19.0.0/lib/sysliblist` -ldl -lm -L/u01/install/APPS/19.0.0/lib `test -x /usr/bin/hugeedit -a -r /usr/lib64/libhugetlbfs.so && test -r /u01/install/APPS/19.0.0/rdbms/lib/shugetlbfs.o && echo -Wl,-zcommon-page-size=2097152 -Wl,-zmax-page-size=2097152 -lhugetlbfs`
rm -f /u01/install/APPS/19.0.0/bin/oracle
mv /u01/install/APPS/19.0.0/rdbms/lib/oracle /u01/install/APPS/19.0.0/bin/oracle
chmod 6751 /u01/install/APPS/19.0.0/bin/oracle
(if [ ! -f /u01/install/APPS/19.0.0/bin/crsd.bin ]; then \
getcrshome="/u01/install/APPS/19.0.0/srvm/admin/getcrshome" ; \
if [ -f "$getcrshome" ]; then \
crshome="`$getcrshome`"; \
if [ -n "$crshome" ]; then \
if [ $crshome != /u01/install/APPS/19.0.0 ]; then \
oracle="/u01/install/APPS/19.0.0/bin/oracle"; \
$crshome/bin/setasmgidwrap oracle_binary_path=$oracle; \
fi \
fi \
fi \
fi\
);
Startup database and validate the unified auditing parameter:
As the relinking is complete, we validate the unified auditing parameter.
[oracle@ebs-12-2-12 lib]$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Thu Jan 25 16:56:16 2024
Version 19.21.0.0.0
Copyright (c) 1982, 2022, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup;
ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
ORACLE instance started.
Total System Global Area 6442448952 bytes
Fixed Size 9181240 bytes
Variable Size 3640655872 bytes
Database Buffers 2566914048 bytes
Redo Buffers 225697792 bytes
Database mounted.
Database opened.
SQL>
SQL> show parameter audit
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
audit_file_dest string /u01/install/APPS/diag/admin/e
bscdb/adump
audit_sys_operations boolean TRUE
audit_syslog_level string
audit_trail string NONE
unified_audit_common_systemlog string
unified_audit_sga_queue_size integer 1048576
unified_audit_systemlog string
SQL> SELECT VALUE FROM V$OPTION WHERE PARAMETER = 'Unified Auditing';
VALUE
----------------------------------------------------------------
TRUE
SQL>
We need to create two users for creating policies. One common user c##audit and pdb local user ebsuaad .
For EBS it's better to have this user segregate roles. For multitenant databases create common users as mentioned below.
CDB/PDB level common user:
create user c##audit identified by manager;
grant CREATE SESSION to c##audit container=all;
grant AUDIT_ADMIN to c##audit container=all;
grant SELECT_CATALOG_ROLE to c##audit container=all;
grant CREATE PROCEDURE to c##audit container=all;
grant AUDIT SYSTEM to c##audit container=all;
grant AUDIT ANY to c##audit container=all;
grant SELECT on SYS.audit_unified_contexts to c##audit container=all;
grant SELECT on SYS.audit_unified_policies to c##audit container=all;
grant SELECT on SYS.audit_unified_enabled_policies to c##audit container=all;
PDB Level local user :
create user ebsuaad identified by Welcome1;
grant CREATE SESSION to ebsuaad;
grant AUDIT_ADMIN to ebsuaad;
grant SELECT_CATALOG_ROLE to ebsuaad;
grant CREATE PROCEDURE to ebsuaad;
grant AUDIT SYSTEM to ebsuaad;
grant AUDIT ANY to ebsuaad;
grant SELECT on SYS.audit_unified_contexts to ebsuaad;
grant SELECT on SYS.audit_unified_policies to ebsuaad;
grant SELECT on SYS.audit_unified_enabled_policies to ebsuaad;
Execute scripts to create policies
Once you apply the EBS Patch scripts are located under $FND_TOP.
cd $FND_TOP/patch/115/sql/
/u01/install/APPS/fs1/EBSapps/appl/fnd/12.0.0/patch/115/sql
-rwxr-xr-x. 1 oracle oinstall 19K Jan 25 16:35 fnd_UA_queries.sql
-rwxr-xr-x. 1 oracle oinstall 16K Jan 25 16:35 fnd_drop_UA_policies.sql
-rwxr-xr-x. 1 oracle oinstall 18K Jan 25 16:35 fnd_disable_UA_policies.sql
-rwxr-xr-x. 1 oracle oinstall 50K Jan 25 16:35 fnd_create_UA_policies.sql
Note: First create in CDB level as C##AUDIT user and then PDB level create policies as ebsuaad user.
SQL> show parameter db_uni
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_unique_name string ebscdb
SQL> show pdbs
SP2-0382: The SHOW PDBS command is not available
SQL> show pdbs
SP2-0382: The SHOW PDBS command is not available
SQL> @fnd_create_UA_policies.sql
Start Time: JAN-25-2024 17:37:48
Spool File: [ fnd_create_UA_policies_CDB.txt ]
Script information: $Header: fnd_create_UA_policies.sql 120.0.12020000.9 2021/12/09 15:21:20 emiranda noship $
----------------------------------------------
User : C##AUDIT
DB-version : 19.0
Instance Name : ebscdb
Multitenant instance : YES
CDB Name : ebscdb
Container Name : CDB$ROOT
OS User : oracle
Client IP Address : 10.1.1.131
Server Hostname : ebs-12-2-12
Client Hostname : ebs-12-2-12
----------------------------------------------
.
Procedure created.
.
.. Execution start => Date/time: JAN-25-2024 / 17:37:48
.. .. SECTION-01
-- Command: [ 2 ]
.. .. SECTION-02
-- Command: [ 8 ]
.. .. SECTION-03
-- Command: [ 10 ]
.. .. SECTION-04
"ORA_SECURECONFIG" : auditing policy
"ORA_ACCOUNT_MGMT" : auditing policy
.. .. SECTION-05
.. .. SECTION-06
-- Command: [ 19 ]
-- Command: [ 21 ]
.. .. SECTION-07
-- Command: [ 25 ]
.. .. SECTION-08
-- Command: [ 29 ]
-- Command: [ 31 ]
.. .. SECTION-09
-- Command: [ 32 ]
-- Command: [ 33 ]
"ORA_DATABASE_PARAMETER" : auditing policy
.. Execution end => Date/time: JAN-25-2024 / 17:37:50
.
PL/SQL procedure successfully completed.
. Enable policies - table audit_unified_policies:
----------------------------------------
EBS_CDB_AUDIT_DATAPUMP
EBS_CDB_TABLESPACE_CHANGES
ORA_ACCOUNT_MGMT
ORA_DATABASE_PARAMETER
ORA_LOGON_FAILURES
ORA_SECURECONFIG
----------------------------------------
.
. ------------------------------------------------
. Created policies - table audit_unified_policies:
.
Policy_name Audit_condition Common Inherited Object_type Audit_only_toplevel Total
---------------------------------------- -------------------- -------------------- -------------------- -------------------- -------------------- ------------
EBS_CDB_ACTIONS_BY_NAMED_USERS NONE NO NO NONE NO 1
EBS_CDB_ACTIONS_BY_PRIV_USERS NONE NO NO NONE YES 1
EBS_CDB_AUDIT_DATAPUMP NONE NO NO NONE NO 2
EBS_CDB_AUDIT_SCHEMA_CHANGES NONE NO NO NONE NO 85
EBS_CDB_DIRECT_DB_ACCESS (SYS_CONTEXT ('USERE NO NO NONE YES 1
EBS_CDB_TABLESPACE_CHANGES NONE NO NO NONE NO 3
ORA_ACCOUNT_MGMT NONE NO NO NONE NO 9
ORA_CIS_RECOMMENDATIONS NONE NO NO NONE NO 35
ORA_DATABASE_PARAMETER NONE NO NO NONE NO 3
ORA_DV_AUDPOL NONE NO NO FUNCTION NO 174
ORA_DV_AUDPOL NONE NO NO LIBRARY NO 16
ORA_DV_AUDPOL NONE NO NO PACKAGE NO 105
ORA_DV_AUDPOL NONE NO NO PROCEDURE NO 33
ORA_DV_AUDPOL NONE NO NO SEQUENCE NO 75
ORA_DV_AUDPOL NONE NO NO TABLE NO 594
ORA_DV_AUDPOL NONE NO NO VIEW NO 1183
ORA_DV_AUDPOL2 NONE NO NO REALM NO 6
ORA_DV_AUDPOL2 NONE NO NO RULE_SET NO 13
ORA_LOGON_FAILURES NONE NO NO NONE NO 1
ORA_RAS_POLICY_MGMT NONE NO NO NONE NO 35
ORA_RAS_SESSION_MGMT NONE NO NO NONE NO 14
ORA_SECURECONFIG NONE NO NO NONE NO 47
ORA_SECURECONFIG NONE NO NO PACKAGE NO 1
ORA_SECURECONFIG NONE NO NO PROCEDURE NO 1
. -----------------------------------------
.
PL/SQL procedure successfully completed.
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.21.0.0.0
[oracle@ebs-12-2-12 sql]$
Once you create the policies you can validate which policies are enabled by default.
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
3 ebsdb READ WRITE NO
SQL> SELECT policy_name FROM audit_unified_enabled_policies order by policy_name;
POLICY_NAME
--------------------------------------------------------------------------------------------------------------------------------
EBS_ACTIONS_ON_SEC_OBJ
EBS_ACTIONS_ON_SENSITIVE_OBJ
EBS_AUDIT_DATAPUMP
EBS_OLS_POLICY_CHANGES
EBS_REDACTION_POLICY_CHANGES
EBS_TABLESPACE_CHANGES
EBS_VPD_POLICY_CHANGES
ORA_ACCOUNT_MGMT
ORA_DATABASE_PARAMETER
ORA_LOGON_FAILURES
10 rows selected.
How to query the unified auditing records
You can use the below-mentioned queries to get the audit records.
SELECT *
FROM (SELECT unified_audit_policies policy,
statement_id,
to_char(event_timestamp, 'mm/dd/yy hh24:mi') audit_time,
userhost,
action_name,
return_code,
object_schema,
object_name,
-- OS_PROCESS,
dbusername,
client_identifier fnd_user,
sql_text
FROM unified_audit_trail
WHERE 1 = 1
--and event_timestamp > SYSDATE - 1
--and action_name='UPDATE'
--and unified_audit_policies like '%EBS_ACTIONS_ON%'
--and unified_audit_policies like 'EBS_AUDIT_DB_SCHEMA_CHANGES'
--and object_name='FND_PROFILE_OPTIONS'
--and dbusername='SYS'
AND unified_audit_policies IS NOT NULL
ORDER BY audit_time DESC,
statement_id)
WHERE rownum 50;
Sample output :
Conclusion
In summary, database auditing plays a critical role in maintaining the security, compliance, integrity, and performance of database systems, while also facilitating forensic analysis and promoting accountability within organizations.
