- Introduction : Oracle Database Auditing is a crucial tool that helps database administrators monitor and record activities occurring within the database environment. By tracking actions such as data modifications, user access, and system changes, auditing provides a detailed record of who did what and when.
- Types of Auditing in Oracle:
-
Mandatory Auditing :
Certain critical actions or users (like SYSDBA) are audited by default without configuration, ensuring that highly sensitive operations are always tracked.
Mandatory Auditing refers to the automatic auditing of specific database actions related to highly privileged users and system-level operations. These include:
- Logons by privileged users such as those connecting with the SYSDBA, SYSOPER, or similar administrative roles.
- Startup and shutdown operations of the Oracle instance.
- DDL statements and DML operations affecting the data dictionary and audit infrastructure.
- Direct interactions with audit-related system tables, such as the unified audit trail tables.
- Standard Auditing : This traditional auditing method lets you track specific SQL statements and privileges. You can audit actions like SELECT, INSERT, UPDATE, DELETE, DDL commands, and user logins by enabling audit policies using the AUDIT SQL statement.
- Fine-Grained Auditing (FGA): FGA provides detailed auditing by allowing you to define specific conditions on which to audit access. For example, you can audit access to particular columns or rows based on user-defined predicates, making it useful for monitoring sensitive data access.
- Unified Auditing : Introduced in Oracle 12c, Unified Auditing consolidates all audit records from different sources into a single, centralized audit trail. It simplifies audit management and improves performance by unifying standard, fine-grained, and other audit records.
- AUDIT_TRAIL Parameter : The AUDIT_TRAIL parameter controls whether and where audit records are stored. It's essential for enabling auditing. Below table will give the view of this parameter value.
- Environment:
- Prerequisites:
- 30 min downtime.
- Sufficient space at tablespace & OS level for storing aduit records.
- Now proceed for enabling standard auditing step by step:
| Feature | Mandatory Auditing | Standard Auditing | Fine-Grained Auditing (FGA) | Unified Auditing |
|---|---|---|---|---|
| Purpose | Audit critical system activities and SYSDBA/SYSOPER actions | Audit general actions (DML, DDL, logins) | Audit access to specific rows/columns based on conditions | Consolidates all audit types into one unified audit trail |
| Configuration | Auto-enabled | AUDIT / NOAUDIT |
DBMS_FGA package |
CREATE AUDIT POLICY + AUDIT statements |
| Granularity | System-level | Statement-level | Row & column with conditions | All levels unified |
| Where Records Are Stored | UNIFIED_AUDIT_TRAIL or OS |
DBA_AUDIT_TRAIL or OS files |
DBA_FGA_AUDIT_TRAIL |
UNIFIED_AUDIT_TRAIL view (secure internal table) |
| Available In | Both Editions | Standard & Enterprise | Enterprise Only | Enterprise Only |
| Disablable | No | Yes | Yes | Yes (via policy management) |
| Option | Description |
|---|---|
| NONE | Disables auditing. No audit records are generated. |
| OS | Audit records are written to operating system files. |
| DB | Audit records are written to the database table AUD$ in the SYS schema. |
| DB,EXTENDED | Same as DB, but includes SQL statements and bind variables. |
| XML | Audit records are written to XML files in the location defined by AUDIT_FILE_DEST. |
| XML,EXTENDED | Same as XML, but includes SQL text and bind values. |
For mandatory Auditing there is no practical steps as it is enabled by default. So, in this article we will configure the Standard Auditing.
| Hostname | orcl.oraeasy.com |
|---|---|
| Database Name | orcldb |
| Database Version | 19.27 |
| Database Edition | Enterprise Edition |
1. First we need to set the AUDIT_TRAIL parameter, here we will set this DB,EXTENDED.
==>First Create a pfile:
SQL> SHOW PDBS
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 ORCLPDB READ WRITE NO
SQL>
SQL> DEF
DEFINE _DATE = "30-JUL-25" (CHAR)
DEFINE _CONNECT_IDENTIFIER = "orcldc" (CHAR)
DEFINE _USER = "SYS" (CHAR)
DEFINE _PRIVILEGE = "AS SYSDBA" (CHAR)
DEFINE _SQLPLUS_RELEASE = "1927000000" (CHAR)
DEFINE _EDITOR = "vi" (CHAR)
DEFINE _O_VERSION = "Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.27.0.0.0" (CHAR)
DEFINE _O_RELEASE = "1927000000" (CHAR)
SQL>
SQL> CREATE PFILE='/home/oracle/pfileorcl.ora' FROM SPFILE;
File created.
==> Now set the AUDIT_TRAIL parameter. It requires Database bounce.
SQL> SHOW PARAMETER audit
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
audit_file_dest string /u01/app/oracle/admin/orcl/adu
mp
audit_sys_operations boolean TRUE
audit_syslog_level string
audit_trail string DB
unified_audit_common_systemlog string
unified_audit_sga_queue_size integer 1048576
unified_audit_systemlog string
SQL>
SQL> ALTER SYSTEM SET audit_trail=DB, EXTENDED SCOPE=SPFILE;
System altered.
SQL>
SQL> SHUTDOWN IMMEDIATE;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>
SQL> STARTUP
ORACLE instance started.
Total System Global Area 524284552 bytes
Fixed Size 9179784 bytes
Variable Size 432013312 bytes
Database Buffers 75497472 bytes
Redo Buffers 7593984 bytes
Database mounted.
Database opened.
SQL>
SQL> SHOW PARAMETER audit
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
audit_file_dest string /u01/app/oracle/admin/orcl/adu
mp
audit_sys_operations boolean TRUE
audit_syslog_level string
audit_trail string DB, EXTENDED
unified_audit_common_systemlog string
unified_audit_sga_queue_size integer 1048576
unified_audit_systemlog string
SQL>
SQL> SHOW PDBS
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 ORCLPDB READ WRITE NO
SQL>
SQL> ALTER SESSION SET CONTAINER=ORCLPDB;
Session altered.
SQL>
SQL> SHOW PARAMETER audit
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
audit_file_dest string /u01/app/oracle/admin/orcl/adu
mp
audit_sys_operations boolean TRUE
audit_syslog_level string
audit_trail string DB, EXTENDED
unified_audit_common_systemlog string
unified_audit_sga_queue_size integer 1048576
unified_audit_systemlog string
SQL>
SQL> SET LINESIZE 333 PAGESIZE 333
SQL> COLUMN SEGMENT_NAME FORMAT A20
SQL> SELECT segment_name, tablespace_name, blocks, bytes/1024/1024 "Size Mb"
FROM dba_segments
WHERE segment_name IN ('AUD$');
SEGMENT_NAME TABLESPACE_NAME BLOCKS Size Mb
-------------------- ----------------- -------- ----------
AUD$ SYSTEM 8 .0625
SQL> COLUMN FILE_NAME FORMAT A80
SQL> SELECT file_name, tablespace_name, bytes/1024/1024, status, autoextensible
FROM dba_data_files
WHERE tablespace_name IN ('SYSTEM');
FILE_NAME TABLESPACE_NAME BYTES/1024/1024 STATUS AUT
---------------------------------------------------- ------------------ --------------- --------- ---
/u01/app/oracle/oradata/ORCL/orclpdb/system01.dbf SYSTEM 680 AVAILABLE YES
SQL> CREATE TABLESPACE audit_data
DATAFILE '/u01/app/oracle/oradata/ORCL/orclpdb/audit_data01.dbf'
SIZE 1G AUTOEXTEND ON;
Tablespace created.
SQL> SELECT file_name, tablespace_name, bytes/1024/1024, status, autoextensible
FROM dba_data_files
WHERE tablespace_name IN ('AUDIT_DATA');
FILE_NAME TABLESPACE_NAME BYTES/1024/1024 STATUS AUT
-------------------------------------------------------- ----------------- --------------- --------- ---
/u01/app/oracle/oradata/ORCL/orclpdb/audit_data01.dbf AUDIT_DATA 1024 AVAILABLE YES
SQL>
SQL> BEGIN
dbms_audit_mgmt.set_audit_trail_location(
audit_trail_type => dbms_audit_mgmt.audit_trail_aud_std,
audit_trail_location_value => 'AUDIT_DATA');
END;
/
PL/SQL procedure successfully completed.
SQL> SELECT segment_name, tablespace_name, blocks, bytes/1024/1024 "Size Mb"
FROM dba_segments
WHERE segment_name IN ('AUD$');
SEGMENT_NAME TABLESPACE_NAME BLOCKS Size Mb
-------------------- ----------------- ---------- ----------
AUD$ AUDIT_DATA 8 .0625
SQL> SHOW PDBS
CON_ID CON_NAME OPEN MODE RESTRICTED
--- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 ORCLPDB READ WRITE NO
SQL>
SQL> ALTER SESSION SET CONTAINER=ORCLPDB;
Session altered.
SQL> SELECT COUNT(*) FROM dba_audit_trail;
COUNT(*)
----------
0
SQL> AUDIT TABLE BY ACCESS;
Audit succeeded.
SQL> AUDIT ALTER TABLE BY ACCESS;
Audit succeeded.
SQL> AUDIT INDEX BY ACCESS;
Audit succeeded.
SQL> AUDIT PROCEDURE BY ACCESS;
Audit succeeded.
SQL> AUDIT VIEW BY ACCESS;
Audit succeeded.
SQL> AUDIT TRIGGER BY ACCESS;
Audit succeeded.
SQL>
SQL> SET LINESIZE 333 PAGESIZE 333
SQL> SELECT audit_option, success, failure
FROM dba_stmt_audit_opts;
AUDIT_OPTION SUCCESS FAILURE
---------------------------------------- ---------- ----------
TABLE BY ACCESS BY ACCESS
VIEW BY ACCESS BY ACCESS
PROCEDURE BY ACCESS BY ACCESS
TRIGGER BY ACCESS BY ACCESS
ALTER ANY TABLE BY ACCESS BY ACCESS
INDEX BY ACCESS BY ACCESS
ALTER TABLE BY ACCESS BY ACCESS
CREATE ANY INDEX BY ACCESS BY ACCESS
DROP ANY INDEX BY ACCESS BY ACCESS
9 rows selected.
SQL>
SQL> CREATE USER testaudit IDENTIFIED BY TesT##123;
User created.
SQL> GRANT connect, resource TO testaudit;
Grant succeeded.
SQL> GRANT unlimited tablespace TO testaudit;
Grant succeeded.
SQL> GRANT create view TO testaudit;;
Grant succeeded
SQL> DEF
DEFINE _DATE = "30-JUL-25" (CHAR)
DEFINE _CONNECT_IDENTIFIER = "orclpdb" (CHAR)
DEFINE _USER = "TESTAUDIT" (CHAR)
DEFINE _PRIVILEGE = "" (CHAR)
DEFINE _SQLPLUS_RELEASE = "1927000000" (CHAR)
DEFINE _EDITOR = "vi" (CHAR)
DEFINE _O_VERSION = "Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.27.0.0.0" (CHAR)
DEFINE _O_RELEASE = "1927000000" (CHAR)
SQL>
SQL> CREATE TABLE company (emp_id INT, name VARCHAR2(255));
Table created.
SQL> DROP TABLE company;
Table dropped.
SQL> CREATE TABLE company (emp_id INT, name VARCHAR2(255));
Table created.
SQL> ALTER TABLE COMPANY DROP COLUMN city;
Table altered.
SQL> ALTER TABLE company ADD city VARCHAR2(20);
Table altered.
SQL> CREATE INDEX testidx ON company(city);
Index created.
SQL> DROP INDEX testidx;
Index dropped.
SQL> CREATE INDEX testidx ON company (city);
Index created.
SQL> ALTER INDEX testidx REBUILD;
Index altered.
SQL> ALTER INDEX testidx RENAME TO testidx_renamed;
Index altered.
SQL> CREATE VIEW comview AS SELECT * FROM company;
View created.
SQL> CREATE OR REPLACE TRIGGER trg_before_insert_company
BEFORE INSERT ON company
FOR EACH ROW
BEGIN
:NEW.name := UPPER(:NEW.name);
END;
/
Trigger created.
SQL> CREATE OR REPLACE PROCEDURE add_employee (
p_emp_id IN NUMBER,
p_name IN VARCHAR2
)
IS
BEGIN
INSERT INTO company (emp_id, name)
VALUES (p_emp_id, p_name);
DBMS_OUTPUT.PUT_LINE('Employee added: ' || p_emp_id || ' - ' || p_name);
EXCEPTION
WHEN DUP_VAL_ON_INDEX THEN
DBMS_OUTPUT.PUT_LINE('Error: Employee with ID ' 3 || p_emp_id || ' already exists.');
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Unexpected error: ' || SQLERRM);
END;
/
Procedure created.
SQL> SET LINESIZE 150 PAGESIZE 100
SQL> COLUMN username FORMAT A15
SQL> COLUMN owner FORMAT A15
SQL> COLUMN obj_name FORMAT A25
SQL> COLUMN action_name FORMAT A20
SQL> COLUMN extended_timestamp FORMAT A35
SQL> SELECT username, extended_timestamp, owner, obj_name, action_name
FROM dba_audit_trail
ORDER BY extended_timestamp;
USERNAME EXTENDED_TIMESTAMP OWNER OBJ_NAME ACTION_NAME
-------------------- ---------------------------------------- -------------------- ---------------------------------------- ----------------------------
TESTAUDIT 30-JUL-25 09.20.09.495217 PM +05:30 TESTAUDIT COMPANY CREATE TABLE
TESTAUDIT 30-JUL-25 09.22.34.542501 PM +05:30 TESTAUDIT COMPANY DROP TABLE
TESTAUDIT 30-JUL-25 09.22.51.495226 PM +05:30 TESTAUDIT COMPANY CREATE TABLE
TESTAUDIT 30-JUL-25 09.32.34.970492 PM +05:30 TESTAUDIT COMPANY ALTER TABLE
TESTAUDIT 30-JUL-25 09.35.11.064356 PM +05:30 TESTAUDIT COMPANY ALTER TABLE
TESTAUDIT 30-JUL-25 09.39.03.514319 PM +05:30 TESTAUDIT TESTIDX DROP INDEX
TESTAUDIT 30-JUL-25 09.39.29.215912 PM +05:30 TESTAUDIT TESTIDX CREATE INDEX
TESTAUDIT 30-JUL-25 09.42.37.492291 PM +05:30 TESTAUDIT TESTIDX CREATE INDEX
TESTAUDIT 30-JUL-25 09.43.00.302514 PM +05:30 TESTAUDIT TESTIDX ALTER INDEX
TESTAUDIT 30-JUL-25 10.09.23.738087 PM +05:30 TESTAUDIT COMVIEW CREATE VIEW
TESTAUDIT 30-JUL-25 10.09.42.569093 PM +05:30 TESTAUDIT COMVIEW CREATE VIEW
TESTAUDIT 30-JUL-25 10.12.37.288751 PM +05:30 TESTAUDIT TRG_BEFORE_INSERT_COMPANY CREATE TRIGGER
TESTAUDIT 30-JUL-25 10.13.21.758069 PM +05:30 TESTAUDIT ADD_EMPLOYEE CREATE PROCEDURE
13 rows selected.
SQL> AUDIT INSERT, UPDATE, DELETE ON testaudit.company BY ACCESS;
Audit succeeded.
==> Now check the DML audit policy.
SQL> SELECT owner, object_name, ins, upd, del FROM dba_obj_audit_opts
WHERE owner = 'TESTAUDIT' AND object_name = 'COMPANY';
OWNER OBJECT_NAME INS UPD DEL
-------------------- -------------------- --------- --------- ---------
TESTAUDIT COMPANY A/A A/A A/A
SQL>
SQL> NOAUDIT INSERT, UPDATE, DELETE ON testaudit.company;
Noaudit succeeded.
SQL>
SQL> NOAUDIT VIEW;
Noaudit succeeded.
SQL>
Now If we need to purge the audit data. We can follow the below steps and by using DBMS_AUDIT_MGMT package.
1. Now first check the current audit records.
SQL> SELECT COUNT(*) FROM dba_audit_trail;
COUNT(*)
----------
15
SQL> SELECT COUNT(*) FROM dba_audit_trail WHERE timestamp < SYSTIMESTAMP - 10;
COUNT(*)
----------
13
SQL> BEGIN
DBMS_AUDIT_MGMT.set_last_archive_timestamp(
audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,
last_archive_time => SYSTIMESTAMP-10);
END;
/
PL/SQL procedure successfully completed.
SQL>
SQL> COLUMN audit_trail FORMAT A20
SQL> COLUMN last_archive_ts FORMAT A40
SQL> SELECT * FROM dba_audit_mgmt_last_arch_ts;
AUDIT_TRAIL RAC_INSTANCE LAST_ARCHIVE_TS DATABASE_ID CONTAINER_GUID
-------------------- ------------ ---------------------------------------- ----------- ---------------------------------
FGA AUDIT TRAIL 0 31-JUL-25 10.56.47.000000 PM +00:00 1593504508 2C3D6BACF692345BE0633350A8C050ED
STANDARD AUDIT TRAIL 0 03-AUG-25 07.46.26.000000 PM +00:00 1593504508 2C3D6BACF692345BE0633350A8C050ED
SQL> BEGIN
DBMS_AUDIT_MGMT.clean_audit_trail(
audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,
use_last_arch_timestamp => TRUE);
END;
/
PL/SQL procedure successfully completed.
SQL>
SQL> SELECT COUNT(*) FROM dba_audit_trail;
COUNT(*)
----------
2
SQL> SELECT COUNT(*) FROM dba_audit_trail WHERE timestamp < SYSTIMESTAMP - 10;
COUNT(*)
----------
0
SQL>
==> To purge 30 days older records.
SQL> DELETE FROM sys.aud$ WHERE ntimestamp# < SYSTIMESTAMP - INTERVAL '30' DAY;
SQL> COMMIT;
==> To empty the audit record table.
SQL> TRUNCATE TABLE sys.aud$;
Thank you for reading!
I hope this content has been helpful to you. Your feedback and suggestions are always welcome — feel free to leave a comment or reach out with any queries.
Abhishek Shrivastava
0 comments:
Post a Comment