The Ora DBAPractical Oracle Database Administration Knowledge & Solutions

Thursday, August 20, 2026

Configure Fine Grain Auditing (FGA) in Oracle Database

Oracle Database 19c Fine-Grained Auditing FGA DBMS_FGA DBA_AUDIT_POLICIES

Fine-Grained Auditing (FGA) in Oracle Database provides granular auditing of data access. Unlike traditional auditing, FGA allows auditing to be controlled based on specific rows, columns, conditions, and SQL operations.

This guide demonstrates how to configure Fine-Grained Auditing, create a dedicated tablespace for FGA audit records, create different types of FGA policies, test those policies, and verify the generated audit records.

Environment used in this guide: Oracle Database 19c Enterprise Edition, Version 19.28.0.0.0.

What is Fine-Grained Auditing (FGA)?

Fine-Grained Auditing is an Oracle security feature that provides granular auditing of data access based on conditions, columns, and SQL operations instead of auditing every access to an object.

FGA can be used to audit:

  • Specific rows using AUDIT_CONDITION.
  • Specific columns using AUDIT_COLUMN.
  • Specific SQL operations such as SELECT, INSERT, UPDATE, and DELETE.
  • Combinations of conditions and columns.

FGA Implementation Patterns

FGA Type AUDIT_CONDITION AUDIT_COLUMN Purpose
All rows + all columns NULL NULL Audit all specified operations
Column-based NULL Specific columns Audit selected columns
Row-condition based Condition NULL or columns Audit rows matching a condition

The AUDIT_TRAIL Parameter

The AUDIT_TRAIL parameter controls whether and where traditional audit records are stored. The table below summarizes its possible values.

AUDIT_TRAIL Value 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

  • SYSDBA access is required for the DBA-level configuration.
  • Approximately 30 minutes of downtime should be planned because changing AUDIT_TRAIL through the SPFILE requires a database restart.
  • Sufficient database tablespace capacity should be available for audit records.
  • Sufficient operating-system storage should be available where applicable.
  • Review audit retention and cleanup requirements before enabling auditing in a production database.
Production consideration: FGA can generate a significant number of audit records depending on the policies configured and the amount of database activity. Review audit volume and tablespace growth before enabling broad FGA policies in production.

Step 1: Check Current AUDIT_TRAIL Configuration

Before making any changes, check the current value of the AUDIT_TRAIL parameter. 

SHOW PARAMETER AUDIT_TRAIL;
Output
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
audit_trail                          string      DB

Also verify the SPFILE location. 

SHOW PARAMETER SPFILE;
Output
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
spfile                               string      /u01/app/oracle/product/19c/db
                                                 _home/dbs/spfilePROD.ora

Step 2: Configure AUDIT_TRAIL=DB,EXTENDED

Set AUDIT_TRAIL to DB,EXTENDED. This stores audit records in the database and includes SQL statements and bind variables. 

ALTER SYSTEM SET AUDIT_TRAIL=DB,EXTENDED SCOPE=SPFILE;
Output
System altered.

Because the parameter is stored in the SPFILE, restart the database to apply the change. 

SHUT IMMEDIATE;
STARTUP;
Output
Database closed.
Database dismounted.
ORACLE instance shut down.

ORACLE instance started.

Total System Global Area 1694498312 bytes
Fixed Size                  9178632 bytes
Variable Size             419430400 bytes
Database Buffers         1258291200 bytes
Redo Buffers                7598080 bytes
Database mounted.
Database opened.

Verify the parameter after the restart. 

SHOW PARAMETER AUDIT_TRAIL;
Output
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
audit_trail                          string      DB, EXTENDED

Step 3: Check the Existing FGA Audit Trail

The FGA audit trail uses the internal FGA_LOG$ table. Before moving the audit trail, check whether the segment already exists and identify its tablespace. 

SET LINESIZE 333
SET PAGESIZE 333

COLUMN SEGMENT_NAME FORMAT A20
COLUMN TABLE_NAME FORMAT A20

SELECT SEGMENT_NAME,
       TABLESPACE_NAME,
       BLOCKS,
       BYTES/1024/1024 "Size Mb"
FROM DBA_SEGMENTS
WHERE SEGMENT_NAME IN ('FGA_LOG$');
Output
no rows selected

Now check the SYS-owned FGA table. 

SELECT TABLE_NAME,
       TABLESPACE_NAME,
       SEGMENT_CREATED
FROM DBA_TABLES
WHERE OWNER='SYS'
AND TABLE_NAME='FGA_LOG$';
Output
TABLE_NAME           TABLESPACE_NAME                SEG
-------------------- ------------------------------ ---
FGA_LOG$             SYSTEM                         NO

Step 4: Create a Dedicated AUDIT_DATA Tablespace

A dedicated tablespace can be used to separate FGA audit records from the SYSTEM tablespace. 

CREATE TABLESPACE AUDIT_DATA
DATAFILE '+DATA'
SIZE 1G
AUTOEXTEND ON;
Output
Tablespace created.

Verify the datafile and its autoextend configuration. 

COLUMN FILE_NAME FORMAT A70
COLUMN TABLESPACE_NAME FORMAT A20

SELECT FILE_NAME,
       TABLESPACE_NAME,
       BYTES/1024/1024,
       STATUS,
       AUTOEXTENSIBLE
FROM DBA_DATA_FILES
WHERE TABLESPACE_NAME IN ('AUDIT_DATA');
Output
FILE_NAME                                                              TABLESPACE_NAME      BYTES/1024/1024 STATUS    AUT
---------------------------------------------------------------------- -------------------- --------------- --------- ---
+DATA/PROD/589D6350A5B241DAE0636538A8C021C3/DATAFILE/audit_data.284.12 AUDIT_DATA                      1024 AVAILABLE YES

Step 5: Move the FGA Audit Trail to AUDIT_DATA

Use DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_LOCATION to configure the standard FGA audit trail location. 

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;
/
Output
PL/SQL procedure successfully completed.

Verify the location of FGA_LOG$. 

SELECT TABLE_NAME,
       TABLESPACE_NAME,
       SEGMENT_CREATED
FROM DBA_TABLES
WHERE OWNER='SYS'
AND TABLE_NAME='FGA_LOG$';
Output
TABLE_NAME           TABLESPACE_NAME      SEG
-------------------- -------------------- ---
FGA_LOG$             AUDIT_DATA           YES
Result: The FGA audit trail is now located in the dedicated AUDIT_DATA tablespace.

Step 6: Create a Test User

Create a dedicated test user so that FGA operations can be tested without using SYS for the data-access operations. 

CREATE USER TESTUSER
IDENTIFIED BY "TestUser@123"
DEFAULT TABLESPACE USERS
TEMPORARY TABLESPACE TEMP
QUOTA UNLIMITED ON USERS;
Output
User created.
GRANT CREATE SESSION TO TESTUSER;
Output
Grant succeeded.

Grant the privileges required to create the test objects. 

GRANT CREATE TABLE,
      CREATE VIEW,
      CREATE SEQUENCE,
      CREATE PROCEDURE,
      CREATE TRIGGER,
      CREATE SYNONYM
TO TESTUSER;
Output
Grant succeeded.

Change the test password used for the test connection. 

ALTER USER TESTUSER IDENTIFIED BY TestUser#123;
Output
User altered.

Step 7: Connect as TESTUSER

sqlplus TESTUSER/TestUser#123@PRODPDB
Output
SQL*Plus: Release 19.0.0.0.0 - Production
Version 19.28.0.0.0

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.28.0.0.0

Step 8: Create the CUSTOMERS Test Table

CREATE TABLE CUSTOMERS
(
    CUSTOMER_ID       NUMBER PRIMARY KEY,
    CUSTOMER_NAME     VARCHAR2(100),
    EMAIL             VARCHAR2(200),
    PHONE             VARCHAR2(20),
    CITY              VARCHAR2(50),
    STATE             VARCHAR2(50),
    CUSTOMER_TYPE     VARCHAR2(20),
    STATUS            VARCHAR2(20),
    CREDIT_LIMIT      NUMBER(15,2),
    CREATED_DATE      DATE,
    LAST_LOGIN_DATE   DATE
);
Output
Table created.

Populate the table with 100 test rows. 

INSERT INTO CUSTOMERS
SELECT
    LEVEL,
    'CUSTOMER_' || LEVEL,
    'customer' || LEVEL || '@test.com',
    '98' || LPAD(MOD(LEVEL,100000000),8,'0'),
    CASE MOD(LEVEL,10)
        WHEN 0 THEN 'Delhi'
        WHEN 1 THEN 'Mumbai'
        WHEN 2 THEN 'Pune'
        WHEN 3 THEN 'Bangalore'
        WHEN 4 THEN 'Chennai'
        WHEN 5 THEN 'Hyderabad'
        WHEN 6 THEN 'Noida'
        WHEN 7 THEN 'Gurgaon'
        WHEN 8 THEN 'Kolkata'
        ELSE 'Jaipur'
    END,
    CASE MOD(LEVEL,5)
        WHEN 0 THEN 'Delhi'
        WHEN 1 THEN 'Maharashtra'
        WHEN 2 THEN 'Karnataka'
        WHEN 3 THEN 'Tamil Nadu'
        ELSE 'Haryana'
    END,
    CASE MOD(LEVEL,3)
        WHEN 0 THEN 'RETAIL'
        WHEN 1 THEN 'CORPORATE'
        ELSE 'SME'
    END,
    CASE
        WHEN MOD(LEVEL,10) = 0 THEN 'INACTIVE'
        ELSE 'ACTIVE'
    END,
    ROUND(DBMS_RANDOM.VALUE(10000,1000000),2),
    SYSDATE - TRUNC(DBMS_RANDOM.VALUE(0,1500)),
    SYSDATE - TRUNC(DBMS_RANDOM.VALUE(0,365))
FROM DUAL
CONNECT BY LEVEL <= 100;

COMMIT;
Output
100 rows created.

Commit complete.

Step 9: Create the TEST_DATA Table

CREATE TABLE TEST_DATA
(
    ID      NUMBER PRIMARY KEY,
    NAME    VARCHAR2(50),
    AMOUNT  NUMBER(10,2)
);
Output
Table created.

Populate the table with 100 rows. 

INSERT INTO TEST_DATA (ID, NAME, AMOUNT)
SELECT
    LEVEL,
    'TEST_' || LEVEL,
    ROUND(DBMS_RANDOM.VALUE(100,10000), 2)
FROM DUAL
CONNECT BY LEVEL <= 100;

COMMIT;
Output
100 rows created.

Commit complete.

Step 10: Create FGA Policy 1 – All Rows + All Columns

The first policy audits every specified SQL operation on all rows and all columns of the CUSTOMERS table.

Because both AUDIT_CONDITION and AUDIT_COLUMN are NULL, there is no row or column restriction. 

BEGIN
    DBMS_FGA.ADD_POLICY(
        OBJECT_SCHEMA   => 'TESTUSER',
        OBJECT_NAME     => 'CUSTOMERS',
        POLICY_NAME     => 'FGA_CUST_ALL_DML',
        AUDIT_CONDITION => NULL,
        AUDIT_COLUMN    => NULL,
        STATEMENT_TYPES => 'SELECT,INSERT,UPDATE,DELETE',
        ENABLE          => TRUE
    );
END;
/
Output
PL/SQL procedure successfully completed.

Step 11: Create FGA Policy 2 – Column-Based Auditing

The second policy audits access involving the EMAIL and PHONE columns. 

BEGIN
    DBMS_FGA.ADD_POLICY(
        OBJECT_SCHEMA   => 'TESTUSER',
        OBJECT_NAME     => 'CUSTOMERS',
        POLICY_NAME     => 'FGA_CUST_CONTACT',
        AUDIT_CONDITION => NULL,
        AUDIT_COLUMN    => 'EMAIL,PHONE',
        STATEMENT_TYPES => 'SELECT,UPDATE',
        ENABLE          => TRUE
    );
END;
/
Output
PL/SQL procedure successfully completed.

Step 12: Create FGA Policy 3 – Row-Condition-Based Auditing

The third policy audits rows where the AMOUNT is greater than 5000. 

BEGIN
    DBMS_FGA.ADD_POLICY(
        OBJECT_SCHEMA   => 'TESTUSER',
        OBJECT_NAME     => 'TEST_DATA',
        POLICY_NAME     => 'FGA_TEST_HIGH_AMOUNT',
        AUDIT_CONDITION => 'AMOUNT > 5000',
        AUDIT_COLUMN    => NULL,
        STATEMENT_TYPES => 'SELECT,UPDATE,DELETE',
        ENABLE          => TRUE
    );
END;
/
Output
PL/SQL procedure successfully completed.

Step 13: Verify FGA Policies

Use DBA_AUDIT_POLICIES to verify the configured FGA policies and the operations they audit. 

SET LINESIZE 333
SET PAGESIZE 333

COLUMN POLICY_OWNER FORMAT A20
COLUMN POLICY_COLUMN FORMAT A30
COLUMN OBJECT_NAME FORMAT A15
COLUMN OBJECT_SCHEMA FORMAT A20
COLUMN POLICY_NAME FORMAT A30

SELECT OBJECT_SCHEMA,
       OBJECT_NAME,
       POLICY_OWNER,
       POLICY_NAME,
       POLICY_COLUMN,
       SEL,
       INS,
       UPD,
       DEL
FROM DBA_AUDIT_POLICIES;
Output
OBJECT_SCHEMA        OBJECT_NAME     POLICY_OWNER         POLICY_NAME                    POLICY_COLUMN                  SEL INS UPD DEL
-------------------- --------------- -------------------- ------------------------------ ------------------------------ --- --- --- ---
TESTUSER             CUSTOMERS       SYS                  FGA_CUST_ALL_DML                                              YES YES YES YES
TESTUSER             CUSTOMERS       SYS                  FGA_CUST_CONTACT               EMAIL                          YES NO  YES NO
TESTUSER             TEST_DATA       SYS                  FGA_TEST_HIGH_AMOUNT                                          YES NO  YES YES

Check the columns associated with the column-based policy. 

SELECT OBJECT_SCHEMA,
       OBJECT_NAME,
       POLICY_NAME,
       POLICY_COLUMN
FROM DBA_AUDIT_POLICY_COLUMNS
WHERE OBJECT_SCHEMA = 'TESTUSER'
ORDER BY OBJECT_NAME, POLICY_NAME, POLICY_COLUMN;
Output
OBJECT_SCHEMA        OBJECT_NAME     POLICY_NAME                    POLICY_COLUMN
-------------------- --------------- ------------------------------ ------------------------------
TESTUSER             CUSTOMERS       FGA_CUST_CONTACT               EMAIL
TESTUSER             CUSTOMERS       FGA_CUST_CONTACT               PHONE

Step 14: Test Policy 1 – FGA_CUST_ALL_DML

Test the broad FGA policy using SELECT, INSERT, UPDATE, and DELETE operations. 

SET LINESIZE 250
SET PAGESIZE 100

COLUMN CUSTOMER_ID      FORMAT 999999
COLUMN CUSTOMER_NAME    FORMAT A20
COLUMN EMAIL            FORMAT A30
COLUMN PHONE            FORMAT A15
COLUMN CITY             FORMAT A15
COLUMN STATE            FORMAT A15
COLUMN CUSTOMER_TYPE    FORMAT A12
COLUMN STATUS            FORMAT A10
COLUMN CREDIT_LIMIT     FORMAT 999,999,999.99
COLUMN CREATED_DATE     FORMAT A12
COLUMN LAST_LOGIN_DATE  FORMAT A12

SELECT * FROM TESTUSER.CUSTOMERS
WHERE CUSTOMER_ID = 1;

INSERT INTO TESTUSER.CUSTOMERS
VALUES (
    101,
    'TEST_CUSTOMER_101',
    'test101@test.com',
    '9876543210',
    'Gurgaon',
    'Haryana',
    'RETAIL',
    'ACTIVE',
    50000,
    SYSDATE,
    SYSDATE
);

UPDATE TESTUSER.CUSTOMERS
SET CREDIT_LIMIT = CREDIT_LIMIT + 1000
WHERE CUSTOMER_ID = 1;

DELETE FROM TESTUSER.CUSTOMERS
WHERE CUSTOMER_ID = 101;

COMMIT;
Output
The SELECT, INSERT, UPDATE and DELETE operations completed successfully.

Step 15: Test Policy 2 – FGA_CUST_CONTACT

The second policy audits SELECT and UPDATE operations involving the EMAIL and PHONE columns. 

SELECT EMAIL
FROM TESTUSER.CUSTOMERS
WHERE CUSTOMER_ID = 1;

SELECT PHONE
FROM TESTUSER.CUSTOMERS
WHERE CUSTOMER_ID = 1;

SELECT EMAIL,PHONE
FROM TESTUSER.CUSTOMERS
WHERE CUSTOMER_ID = 1;

SELECT CUSTOMER_NAME
FROM TESTUSER.CUSTOMERS
WHERE CUSTOMER_ID = 1;

UPDATE TESTUSER.CUSTOMERS
SET EMAIL = 'updated@test.com'
WHERE CUSTOMER_ID = 1;

UPDATE TESTUSER.CUSTOMERS
SET PHONE = '9876543210'
WHERE CUSTOMER_ID = 1;

UPDATE TESTUSER.CUSTOMERS
SET CITY = 'Delhi'
WHERE CUSTOMER_ID = 1;

COMMIT;
Output
The SELECT and UPDATE operations completed successfully.

The EMAIL and PHONE operations are relevant to FGA_CUST_CONTACT, while the operations involving columns outside the policy column list can still be captured by the broader FGA_CUST_ALL_DML policy.

Step 16: Test Policy 3 – FGA_TEST_HIGH_AMOUNT

This policy audits operations only when the FGA condition AMOUNT > 5000 is satisfied. 

SELECT ID,
       NAME,
       AMOUNT
FROM TESTUSER.TEST_DATA
WHERE AMOUNT > 5000;

SELECT *
FROM TESTUSER.TEST_DATA
WHERE AMOUNT > 5000;

SELECT *
FROM TESTUSER.TEST_DATA
WHERE AMOUNT <= 5000;

UPDATE TESTUSER.TEST_DATA
SET NAME = 'HIGH_VALUE_TEST'
WHERE ID = (
    SELECT ID
    FROM TESTUSER.TEST_DATA
    WHERE AMOUNT > 5000
    AND ROWNUM = 1
);

INSERT INTO TESTUSER.TEST_DATA
VALUES (101,'TEMP_HIGH_VALUE',9000);

DELETE FROM TESTUSER.TEST_DATA
WHERE ID = 101;

COMMIT;
Output
The condition-based SELECT, UPDATE and DELETE operations completed successfully.

Step 17: Check FGA Audit Records

After executing the test operations, query DBA_FGA_AUDIT_TRAIL to view the generated FGA audit records. 

SET LINESIZE 250
SET PAGESIZE 100

COLUMN DB_USER FORMAT A15
COLUMN OBJECT_SCHEMA FORMAT A15
COLUMN OBJECT_NAME FORMAT A15
COLUMN POLICY_NAME FORMAT A30
COLUMN SQL_TEXT FORMAT A80
COLUMN EXTENDED_TIMESTAMP FORMAT A50

SELECT DB_USER,
       OBJECT_NAME,
       POLICY_NAME,
       SQL_TEXT,
       EXTENDED_TIMESTAMP
FROM DBA_FGA_AUDIT_TRAIL;
Output
DB_USER         OBJECT_NAME     POLICY_NAME                    SQL_TEXT
--------------- --------------- ------------------------------ ------------------------------
TESTUSER        CUSTOMERS       FGA_CUST_CONTACT               SELECT * FROM TESTUSER.CUSTOMERS WHERE CUSTOMER_ID = 1
TESTUSER        CUSTOMERS       FGA_CUST_ALL_DML               SELECT * FROM TESTUSER.CUSTOMERS WHERE CUSTOMER_ID = 1
TESTUSER        CUSTOMERS       FGA_CUST_ALL_DML               INSERT INTO TESTUSER.CUSTOMERS VALUES (101,'TEST_CUSTOMER_101',...
TESTUSER        CUSTOMERS       FGA_CUST_ALL_DML               UPDATE TESTUSER.CUSTOMERS SET CREDIT_LIMIT = CREDIT_LIMIT + 1000...
TESTUSER        CUSTOMERS       FGA_CUST_ALL_DML               DELETE FROM TESTUSER.CUSTOMERS WHERE CUSTOMER_ID = 101
TESTUSER        CUSTOMERS       FGA_CUST_CONTACT               SELECT EMAIL FROM TESTUSER.CUSTOMERS WHERE CUSTOMER_ID = 1
TESTUSER        CUSTOMERS       FGA_CUST_ALL_DML               SELECT EMAIL FROM TESTUSER.CUSTOMERS WHERE CUSTOMER_ID = 1
TESTUSER        CUSTOMERS       FGA_CUST_CONTACT               SELECT PHONE FROM TESTUSER.CUSTOMERS WHERE CUSTOMER_ID = 1
TESTUSER        CUSTOMERS       FGA_CUST_ALL_DML               SELECT PHONE FROM TESTUSER.CUSTOMERS WHERE CUSTOMER_ID = 1
TESTUSER        CUSTOMERS       FGA_CUST_CONTACT               SELECT EMAIL,PHONE FROM TESTUSER.CUSTOMERS WHERE CUSTOMER_ID = 1
TESTUSER        CUSTOMERS       FGA_CUST_ALL_DML               SELECT EMAIL,PHONE FROM TESTUSER.CUSTOMERS WHERE CUSTOMER_ID = 1
TESTUSER        CUSTOMERS       FGA_CUST_ALL_DML               SELECT CUSTOMER_NAME FROM TESTUSER.CUSTOMERS WHERE CUSTOMER_ID = 1
TESTUSER        CUSTOMERS       FGA_CUST_ALL_DML               UPDATE TESTUSER.CUSTOMERS SET EMAIL = 'updated@test.com'...
TESTUSER        CUSTOMERS       FGA_CUST_CONTACT               UPDATE TESTUSER.CUSTOMERS SET EMAIL = 'updated@test.com'...
TESTUSER        CUSTOMERS       FGA_CUST_ALL_DML               UPDATE TESTUSER.CUSTOMERS SET PHONE = '9876543210'...
TESTUSER        CUSTOMERS       FGA_CUST_CONTACT               UPDATE TESTUSER.CUSTOMERS SET PHONE = '9876543210'...
TESTUSER        CUSTOMERS       FGA_CUST_ALL_DML               UPDATE TESTUSER.CUSTOMERS SET CITY = 'Delhi'...
TESTUSER        TEST_DATA       FGA_TEST_HIGH_AMOUNT           SELECT ID,NAME,AMOUNT FROM TESTUSER.TEST_DATA WHERE AMOUNT > 5000
TESTUSER        TEST_DATA       FGA_TEST_HIGH_AMOUNT           SELECT * FROM TESTUSER.TEST_DATA WHERE AMOUNT > 5000
TESTUSER        TEST_DATA       FGA_TEST_HIGH_AMOUNT           UPDATE TESTUSER.TEST_DATA SET NAME = 'HIGH_VALUE_TEST'...
TESTUSER        TEST_DATA       FGA_TEST_HIGH_AMOUNT           UPDATE TESTUSER.TEST_DATA SET NAME = 'HIGH_VALUE_TEST'...
TESTUSER        TEST_DATA       FGA_TEST_HIGH_AMOUNT           DELETE FROM TESTUSER.TEST_DATA WHERE ID = 101

22 rows selected.

The actual execution generated 22 audit records. :contentReference[oaicite:2]{index=2}

Step 18: Count Audit Records by FGA Policy

Use the following query to summarize the number of audit records generated by each FGA policy. 

SELECT OBJECT_NAME,
       POLICY_NAME,
       COUNT(*) AUDIT_COUNT
FROM DBA_FGA_AUDIT_TRAIL
GROUP BY OBJECT_NAME, POLICY_NAME
ORDER BY OBJECT_NAME, POLICY_NAME;
Output
OBJECT_NAME     POLICY_NAME                    AUDIT_COUNT
--------------- ------------------------------ -----------
CUSTOMERS       FGA_CUST_ALL_DML                        11
CUSTOMERS       FGA_CUST_CONTACT                         6
TEST_DATA       FGA_TEST_HIGH_AMOUNT                     5
Total FGA audit records: 22

Understanding Multiple FGA Audit Records

A single SQL operation can generate more than one FGA audit record when it matches multiple enabled FGA policies.

For example, a SELECT statement accessing the EMAIL column can be captured by both:

  • FGA_CUST_ALL_DML – because the policy audits all columns.
  • FGA_CUST_CONTACT – because EMAIL is explicitly included in the policy.

This explains why the number of audit records is not necessarily equal to the number of SQL statements executed during testing. :contentReference[oaicite:3]{index=3}

Final FGA Configuration Verification

The following queries can be used as a final verification of the FGA configuration. 

SELECT OBJECT_SCHEMA,
       OBJECT_NAME,
       POLICY_OWNER,
       POLICY_NAME,
       POLICY_COLUMN,
       SEL,
       INS,
       UPD,
       DEL
FROM DBA_AUDIT_POLICIES;

SELECT OBJECT_SCHEMA,
       OBJECT_NAME,
       POLICY_NAME,
       POLICY_COLUMN
FROM DBA_AUDIT_POLICY_COLUMNS
WHERE OBJECT_SCHEMA = 'TESTUSER'
ORDER BY OBJECT_NAME, POLICY_NAME, POLICY_COLUMN;

SELECT DB_USER,
       OBJECT_NAME,
       POLICY_NAME,
       SQL_TEXT,
       EXTENDED_TIMESTAMP
FROM DBA_FGA_AUDIT_TRAIL;

Operational Considerations

  • Perform application/test data-access operations using the intended test or application user rather than SYS.
  • Monitor the tablespace containing the FGA audit trail.
  • Review audit retention and cleanup requirements before production implementation.
  • Broad policies auditing all rows and all columns can generate significantly more audit records than targeted policies.
  • After testing, review whether the test FGA policies should remain enabled.
Production Note: Before implementing FGA in production, validate audit volume, tablespace capacity, audit retention, and cleanup requirements according to your organization's audit policy.

Conclusion

Fine-Grained Auditing provides a flexible way to monitor sensitive data access in Oracle Database. By using DBMS_FGA.ADD_POLICY, auditing can be targeted at specific columns, specific rows, specific conditions, or combinations of these controls.

In this implementation, three different FGA approaches were configured and tested: all-row/all-column auditing, column-based auditing, and condition-based auditing. The generated records were then verified through DBA_FGA_AUDIT_TRAIL.

The test demonstrated that FGA can provide detailed visibility into who accessed an object, which policy was triggered, what SQL was executed, and when the operation occurred.

at
Labels: , ,