.::: Enable Oracle 19C Unified Auditing or Oracle Audit like SQL Audit, MariaDB Audit, pgaudit & disable Permanent :::.

 

Unified Auditing in Oracle Database 19c is a security feature designed to streamline and enhance auditing capabilities by consolidating all types of audits (traditional and unified) into a centralized and efficient architecture. This approach simplifies audit management, storage, and analysis

A. Setup Oracle Audit

1. Check Unified Auditing
SELECT VALUE FROM V$OPTION WHERE PARAMETER = 'Unified Auditing';

2. Activate Unified Auditing

SHUTDOWN IMMEDIATE;

cd $ORACLE_HOME/rdbms/lib
make -f ins_rdbms.mk uniaud_on ioracle

startup

or

/etc/ini.d/dboracle start
  

3. Create audit

3.1  Audit Aktivitas Login User

CREATE AUDIT POLICY login_policy
ACTIONS LOGON;

SELECT *  
FROM AUDIT_UNIFIED_POLICIES
WHERE POLICY_NAME in ('LOGIN_POLICY','TABLE_CHANGES_POLICY');

# for all user

AUDIT POLICY login_policy;

3.2 Audit Perubahan pada Objek Tertentu
Buat Kebijakan untuk Audit DDL (CREATE, ALTER, DROP) pada Tabel:


CREATE AUDIT POLICY table_changes_policy
ACTIONS CREATE TABLE, ALTER TABLE, DROP TABLE, TRUNCATE TABLE,INSERT,UPDATE,DELETE;

## just testing
CREATE AUDIT POLICY table_changes_policy
ACTIONS CREATE TABLE, ALTER TABLE, DROP TABLE, TRUNCATE TABLE,SELECT,INSERT,UPDATE,DELETE;

AUDIT POLICY table_changes_policy;
Aktifkan untuk User Tertentu (Opsional):

AUDIT POLICY table_changes_policy BY AISYAH;

3.3 check

SELECT *  
FROM AUDIT_UNIFIED_POLICIES
WHERE POLICY_NAME in ('LOGIN_POLICY','TABLE_CHANGES_POLICY');
 

4. Try delete, insert,update or other

UPDATE pasok
SET JUMLAH_PASOK = 22
WHERE KODE_PASOK = 'PAS-E003';

5. check status

SELECT AUDIT_TYPE,EVENT_TIMESTAMP, DBUSERNAME, ACTION_NAME, OBJECT_NAME,CLIENT_PROGRAM_NAME,UNIFIED_AUDIT_POLICIES,SQL_TEXT
WHERE DBUSERNAME = 'AISYAH' and OBJECT_NAME in ('BARANG','CUSTOMER','SUPLIER','PASOK','PEMBELIAN') order by EVENT_TIMESTAMP desc ;
 

SELECT AUDIT_TYPE,EVENT_TIMESTAMP, DBUSERNAME, ACTION_NAME, OBJECT_NAME,CLIENT_PROGRAM_NAME,UNIFIED_AUDIT_POLICIES,SQL_TEXT,
       (SELECT i.host_name FROM V$INSTANCE i) AS HOST_NAME,
       (SELECT i.version FROM V$INSTANCE i) AS ORACLE_VERSION,
       (SELECT v.banner FROM V$VERSION v WHERE ROWNUM = 1) AS BANNER
FROM UNIFIED_AUDIT_TRAIL
WHERE DBUSERNAME = 'AISYAH'
  AND OBJECT_NAME IN ('BARANG', 'CUSTOMER', 'SUPLIER', 'PASOK','pasok','PEMBELIAN','ORDERS')
ORDER BY EVENT_TIMESTAMP DESC;
 

6. check status withoutfilter

SELECT AUDIT_TYPE,EVENT_TIMESTAMP, DBUSERNAME, ACTION_NAME, OBJECT_NAME,CLIENT_PROGRAM_NAME,UNIFIED_AUDIT_POLICIES,SQL_TEXT
FROM UNIFIED_AUDIT_TRAIL
WHERE DBUSERNAME = 'AISYAH' order by EVENT_TIMESTAMP desc
 

B. Drop Oracle Audit

1. delete oracle audit

-- inactive Oracle Audit
NOAUDIT POLICY login_policy;
NOAUDIT POLICY table_changes_policy;

-- drop Oracle Audit
DROP AUDIT POLICY login_policy;
DROP AUDIT POLICY table_changes_policy;

C. Permanen disable oracle audit

1. Check the Status of Unified Auditing
SELECT VALUE FROM V$OPTION WHERE PARAMETER = 'Unified Auditing';

2. Shutdown the Database

sqlplus / as sysdba
SHUTDOWN IMMEDIATE;

3. Relink Oracle Binaries to Disable Unified Auditing
cd $ORACLE_HOME/rdbms/lib
make -f ins_rdbms.mk uniaud_off ioracle

4. Start the Database

sqlplus / as sysdba
STARTUP;

5. Verify Unified Auditing is Disabled
SELECT VALUE FROM V$OPTION WHERE PARAMETER = 'Unified Auditing';