- 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 FGA 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. |
In the previous article we have configured the Standard Auditing.
Please visit Standard Auditing. So, in this article we will configure the Fine-Grained Auditing (FGA).
| 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>
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 = "14-AUG-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 ('FGA_LOG$');
SEGMENT_NAME TABLESPACE_NAME BLOCKS Size Mb
-------------------- ------------------------------ ---------- ----------
FGA_LOG$ 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_fga_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 ('FGA_LOG$');
SEGMENT_NAME TABLESPACE_NAME BLOCKS Size Mb
-------------------- ------------------------------ ---------- ----------
FGA_LOG$ AUDIT_DATA 8 .0625
SQL> DEF
DEFINE _DATE = "14-AUG-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 employee (
emp_id NUMBER,
emp_name VARCHAR2(50),
salary NUMBER
);
Table created.
SQL> INSERT INTO employee VALUES (1, 'Alice', 9000);
1 row created.
SQL> INSERT INTO employee VALUES (2, 'Bob', 15000);
1 row created.
SQL> INSERT INTO employee VALUES (3, 'Carol', 20000);
1 row created.
SQL> COMMIT;
Commit complete.
SQL> SELECT * from employee;
EMP_ID EMP_NAME SALARY
---------- -------------------------------------------------- ----------
1 Alice 9000
2 Bob 15000
3 Carol 20000
SQL>
SQL> BEGIN
dbms_fga.add_policy(
object_schema => 'TESTAUDIT',
object_name => 'EMPLOYEE',
policy_name => 'DML_AUDIT',
audit_condition => NULL, -- NULL means all rows
audit_column => NULL, -- NULL means all columns
statement_types => 'INSERT,UPDATE,DELETE',
enable => TRUE
);
END;
/
PL/SQL procedure successfully completed.
SQL>
SQL> SET LINESIZE 333 PAGESIZE 333
SQL> COLUMN policy_owner FORMAT A20
SQL> COLUMN policy_column FORMAT A10
SQL> COLUMN object_name FORMAT A15
SQL> COLUMN object_schema FORMAT A20
SQL> COLUMN policy_name FORMAT A30
SQL> SELECT object_schema,
object_name,
policy_owner,
policy_name,
policy_column,
sel,
ins,
upd,
del
FROM dba_audit_policies;
OBJECT_SCHEMA OBJECT_NAME POLICY_OWNER POLICY_NAME POLICY_COL SEL INS UPD DEL
-------------------- --------------- -------------------- ------------------------------ ---------- --- --- --- ---
TESTAUDIT EMPLOYEE SYS DML_AUDIT NO YES YES YES
SQL> DEF
DEFINE _DATE = "14-AUG-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> INSERT INTO employee VALUES (4, 'Dave', 5000);
1 row created.
SQL> UPDATE employee SET salary = 1800 WHERE emp_id = 1;
1 row updated.
SQL> DELETE FROM employee WHERE emp_id = 2;
1 row deleted.
SQL> SELECT *
FROM employee
WHERE emp_id = 3;
EMP_ID EMP_NAME SALARY
---------- ---------- ----------
3 Carol 20000
SQL> DEF
DEFINE _DATE = "14-AUG-25" (CHAR)
DEFINE _CONNECT_IDENTIFIER = "orclpdb" (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> SET LINESIZE 333 PAGESIZE 333
SQL> COLUMN db_user FORMAT A20
SQL> COLUMN sql_text FORMAT A50
SQL> COLUMN extended_timestamp FORMAT A50
SQL> SELECT
db_user,
object_name,
statement_type,
sql_text,
extended_timestamp
FROM
dba_fga_audit_trail
WHERE
object_name = 'EMPLOYEE'
ORDER BY extended_timestamp DESC;
DB_USER OBJECT_NAME STATEME SQL_TEXT EXTENDED_TIMESTAMP
-------------------- --------------- ------- -------------------------------------------------- -------------------------------------
TESTAUDIT EMPLOYEE DELETE DELETE FROM EMPLOYEE WHERE emp_id = 2 14-AUG-25 04.33.50.107692 PM +05:30
TESTAUDIT EMPLOYEE UPDATE UPDATE EMPLOYEE SET salary = 1800 WHERE emp_id = 1 14-AUG-25 04.33.35.071285 PM +05:30
TESTAUDIT EMPLOYEE INSERT INSERT INTO EMPLOYEE VALUES (4, 'Dave', 5000) 14-AUG-25 04.33.26.467118 PM +05:30
==> To enable FGA policy.
BEGIN
DBMS_FGA.ENABLE_POLICY(
object_schema => 'TESTAUDIT',
object_name => 'EMPLOYEE',
policy_name => 'DML_AUDIT'
);
END;
/
==> To disable FGA policy.
BEGIN
DBMS_FGA.DISABLE_POLICY(
object_schema => 'TESTAUDIT',
object_name => 'EMPLOYEE',
policy_name => 'DML_AUDIT'
);
END;
/
==> To drop FGA policy.
BEGIN
DBMS_FGA.DROP_POLICY(
object_schema => 'TESTAUDIT',
object_name => 'EMPLOYEE',
policy_name => 'DML_AUDIT'
);
END;
/
Now If we need to purge the FGA 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_fga_audit_trail;
COUNT(*)
----------
5
SQL> SELECT COUNT(*) FROM dba_fga_audit_trail WHERE timestamp < SYSTIMESTAMP -5;
COUNT(*)
----------
3
SQL> BEGIN
DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP(
audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_FGA_STD,
last_archive_time => SYSTIMESTAMP - 5
);
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 14-AUG-25 07.47.58.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_FGA_STD,
use_last_arch_timestamp => TRUE
);
END;
/
PL/SQL procedure successfully completed.
SQL>
SQL> SELECT COUNT(*) FROM dba_fga_audit_trail;
COUNT(*)
----------
2
SQL> SELECT COUNT(*) FROM dba_fga_audit_trail WHERE timestamp < SYSTIMESTAMP -5;
COUNT(*)
----------
0
SQL>
SQL>
==> To purge 30 days older records.
SQL> DELETE FROM sys.fga_log$ WHERE ntimestamp# < SYSTIMESTAMP - INTERVAL '30' DAY;
SQL> COMMIT;
==> To empty the audit record table.
SQL> TRUNCATE TABLE sys.fga_log$;
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
Comments
Post a Comment