Introduction
This is the second post in our Oracle auditing series. We previously covered Standard Auditing, including a full breakdown of the four auditing types Oracle supports and how they compare. This time we'll configure Fine-Grained Auditing (FGA), which goes a step further than Standard Auditing by letting you audit access to specific columns or rows based on a condition you define, rather than auditing every statement against a table regardless of what it touches.
The AUDIT_TRAIL Parameter
FGA still relies on the AUDIT_TRAIL parameter to control whether and where records are stored, same as Standard Auditing. The table below is a quick reference for its values.
| 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. |
Prerequisites
- 30 minutes of downtime.
- Sufficient space at the tablespace and OS level for storing audit records.
Environment Used in This Guide
| Hostname | orcl.oraeasy.com |
|---|---|
| Database Name | orcldb |
| Database Version | 19.27 |
| Database Edition | Enterprise Edition |
Step 1: Set the AUDIT_TRAIL Parameter
We'll set AUDIT_TRAIL to DB,EXTENDED, same as in the Standard Auditing post. This requires a database bounce.
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.
Set the AUDIT_TRAIL Parameter (Requires a Database Bounce):
SQL> SHOW PARAMETER audit
NAME TYPE VALUE
------------------------------------ ----------- --------------------------------
audit_file_dest string /u01/app/oracle/admin/orcl/adump
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/adump
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/adump
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>
Step 2: Assign a Tablespace for FGA Audit Data
FGA records live in the FGA_LOG$ table, and by default it sits in SYSTEM. As with the standard audit trail, move it to a dedicated tablespace using DBMS_AUDIT_MGMT.
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
Step 3: Create a Test Table
FGA lets you scope auditing to particular columns of a table, so we need a table with a column worth watching, such as salary.
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>
Step 4: Enable an FGA Policy on the Table
Create the policy with DBMS_FGA. Setting audit_condition and audit_column to NULL here audits every row and column, but this is exactly where you'd narrow things down, for example by pointing audit_column at just salary.
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
Step 5: Perform DML Operations to Test the Policy
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
Step 6: Review the Captured FGA Audit Data
Unlike the standard audit trail, FGA records the exact SQL text that triggered the policy, which makes it easier to see precisely what changed.
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
Step 7: Enable, Disable, and Drop an FGA Policy
These are the three lifecycle operations for a policy once it exists.
To Enable an FGA Policy:
BEGIN
DBMS_FGA.ENABLE_POLICY(
object_schema => 'TESTAUDIT',
object_name => 'EMPLOYEE',
policy_name => 'DML_AUDIT'
);
END;
/
To Disable an FGA Policy:
BEGIN
DBMS_FGA.DISABLE_POLICY(
object_schema => 'TESTAUDIT',
object_name => 'EMPLOYEE',
policy_name => 'DML_AUDIT'
);
END;
/
To Drop an FGA Policy:
BEGIN
DBMS_FGA.DROP_POLICY(
object_schema => 'TESTAUDIT',
object_name => 'EMPLOYEE',
policy_name => 'DML_AUDIT'
);
END;
/
Purging FGA Audit Data
Same as the standard audit trail, the FGA log grows without bound unless it's periodically purged. DBMS_AUDIT_MGMT handles this the same way, just pointed at the FGA trail type instead.
Step 1: Check the Current Audit Record Count
SQL> SELECT COUNT(*) FROM dba_fga_audit_trail;
COUNT(*)
----------
5
SQL> SELECT COUNT(*) FROM dba_fga_audit_trail WHERE timestamp < SYSTIMESTAMP -5;
COUNT(*)
----------
3
Step 2: Set the LAST_ARCHIVE_TIMESTAMP
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
Step 3: Purge Records Older Than the Archive Timestamp
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>
Step 4: Verify the Purge
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>
Alternative: Purge Directly on sys.fga_log$
As with the standard audit trail, you can also work against the underlying table directly, though DBMS_AUDIT_MGMT remains the safer path for routine housekeeping.
To Purge Records Older Than 30 Days:
SQL> DELETE FROM sys.fga_log$ WHERE ntimestamp# < SYSTIMESTAMP - INTERVAL '30' DAY;
SQL> COMMIT;
To Empty the Audit Record Table Entirely:
SQL> TRUNCATE TABLE sys.fga_log$;
That covers Fine-Grained Auditing: scoping a policy to specific columns or conditions, testing it, and keeping the FGA log trimmed. The next post in this series will look at Unified Auditing.
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
Oracle DBA with hands-on experience managing production Data Guard, RAC, GoldenGate, and APEX environments. I write practical, tested installation and troubleshooting guides based on real deployment work.
Comments
Post a Comment