Oracle DBMS_FGA
Version 26ai

General Information
Library Note Morgan's Library Page Header
The best Oracle News for FY2026

Oracle Database 26ai will be available on generic Linux platforms in January and soon on AIX and Windows
Purpose Fine Grained Auditing is policy based conditional auditing that can be based on specific columns and specific column values.

DB Audit wends the audit trail to the SYS.FGA_LOG$ table in the database and omits SQL Text and SQL Bind.
XML Audit writes the audit trail in XML files sent to the operating system and omits SQL Text and SQL Bind.
AUTHID CURRENT_USER
Constants
Name Data Type Value
EXTENDED PLS_INTEGER 1
DB PLS_INTEGER 2
DB_EXTENDED (default) PLS_INTEGER 3
XML PLS_INTEGER 4
ALL_COLUMNS BINARY_INTEGER 1
ANY_COLUMNS (default) BINARY_INTEGER 0
Dependencies
ALL_AUDIT_POLICIES DBA_FGA_AUDIT_TRAIL FGA_LOG$
CDB_AUDIT_POLICIES DBMS_FGA_LIB V$XML_AUDIT_TRAIL
DBA_AUDIT_POLICIES FGA$ USER_AUDIT_POLICIES
Documented Yes: Packages and Types Reference
First Available 9.0
Initialization Parameters col name format a30
col value format a40

SELECT name, value
FROM gv$parameter
WHERE name LIKE '%audit%';

NAME                           VALUE
------------------------------ ----------------------------------------
audit_sys_operations           TRUE
audit_file_dest                /u01/app/oracle/admin/test21db_iad25g/adump
audit_syslog_level
unified_audit_systemlog
unified_audit_common_systemlog
audit_trail                    DB
Pragmas PRAGMA SUPPLEMENTAL_LOG_DATA(default, AUTO)
Security Model Owned by SYS with EXECUTE granted to the AUDIT_ADMIN, EXECUTE_CATALOG_ROLE and OGG_APPLY_PROCREP roles.
Source {ORACLE_HOME}/rdbms/admin/dbmsfga.sql
Subprograms
ADD_POLICY DROP_POLICY Demos
DISABLE_POLICY ENABLE_POLICY  
 
ADD_POLICY
Create a new audit policy dbms_fga.add_policy(
object_schema     IN VARCHAR2       := NULL,
object_name       IN VARCHAR2,
policy_name       IN VARCHAR2,
audit_condition   IN VARCHAR2       := NULL,
audit_column      IN VARCHAR2       := NULL,
handler_schema    IN VARCHAR2       := NULL,
handler_module    IN VARCHAR2       := NULL, -- alerting mechanism
enable            IN BOOLEAN        := NULL,
statement_types   IN VARCHAR2       := 'SELECT',
audit_trail       IN PLS_INTEGER    := 3,
audit_column_opts IN BINARY_INTEGER DEFAULT 0,
policy_owner      IN VARCHAR2       := NULL);
exec dbms_fga.add_policy(object_schema=>'UWCLASS', object_name=>'FGA_DEMO', policy_name=>'UW Audit', audit_condition=>'status = ''A''', audit_column=>'last_name, salary', handler_schema=>'UWCLASS', handler_module=>'FGA_HANDLER', enable=>TRUE, statement_types=>'INSERT, UPDATE', audit_trail=>DBMS_FGA.DB_EXTENDED, audit_column_opts=>dbms_fga.all_columns);

col object_schema format a30
col policy_name format a30

SELECT object_schema, object_name, policy_name, audit_trail, enabled
FROM dba_audit_policies;
 
DISABLE_POLICY
Disable an audit policy dbms_fga.disable_policy(
object_schema IN VARCHAR2 := NULL,
object_name   IN VARCHAR2,
policy_name   IN VARCHAR2);
exec dbms_fga.disable_policy('UWCLASS', 'FGA_DEMO', 'UW Audit');

SELECT object_schema, object_name, policy_name, audit_trail, enabled
FROM dba_audit_policies;
 
DROP_POLICY
Drop an audit policy dbms_fga.drop_policy(
object_schema IN VARCHAR2 := NULL,
object_name   IN VARCHAR2,
policy_name   IN VARCHAR2);
exec dbms_fga.drop_policy('UWCLASS', 'FGA_DEMO', 'UW Audit');

SELECT object_schema, object_name, policy_name, audit_trail, enabled
FROM dba_audit_policies;
 
ENABLE_POLICY
Enable or disable an audit policy dbms_fga.enable_policy(
object_schema IN VARCHAR2 := NULL,
object_name   IN VARCHAR2,
policy_name   IN VARCHAR2
enable        IN BOOLEAN  := TRUE);
exec dbms_fga.enable_policy('UWCLASS', 'FGA_DEMO', 'UW Audit', TRUE);

SELECT object_schema, object_name, policy_name, audit_trail, enabled
FROM dba_audit_policies;
 
DBMS_FGA Demo
As SYS conn sys@pdbdev as sysdba

desc fga_log$

SELECT COUNT(*)
FROM fga_log$;

desc dba_common_audit_trail

SELECT COUNT(*)
FROM dba_common_audit_trail;

GRANT EXECUTE ON dbms_fga TO uwclass;
GRANT select ON dba_audit_policies TO uwclass;
GRANT select ON dba_fga_audit_trail TO uwclass;

col name format a30
col value format a40

SELECT name, value
FROM gv$parameter
WHERE name LIKE '%audit%';

ALTER SYSTEM SET audit_sys_operations = TRUE SCOPE=SPFILE;
-- will require a restart so change it back
ALTER SYSTEM SET audit_sys_operations = FALSE SCOPE=SPFILE;

-- ALTER SYSTEM SET audit_file_dest = <dir> DEFERRED;
Session 1 conn sys@pdbdev as sysdba

desc fga_log$

SELECT COUNT(*)
FROM fga_log$;

GRANT EXECUTE ON dbms_fga TO uwclass;

GRANT select ON dba_audit_policies TO uwclass;

conn uwclass/uwclass@pdbdev

CREATE TABLE uwclass.fga_demo (
person_id  NUMBER(5),
last_name  VARCHAR2(25),
salary     NUMBER(9,3),
status     VARCHAR2(1));

ALTER TABLE fga_demo
ADD CONSTRAINT pk_fga_demo
PRIMARY KEY (person_id)
USING INDEX
PCTFREE 0;

ALTER TABLE fga_demo
ADD CONSTRAINT cc_fga_demo_status
CHECK (status IN ('A','I'));

CREATE TABLE fga_tab (
owner       VARCHAR2(30),
table_name  VARCHAR2(30),
policy_name VARCHAR2(30));|

CREATE OR REPLACE PROCEDURE fga_handler (
sname VARCHAR2, tname VARCHAR2, pname VARCHAR2) AUTHID DEFINER IS

PRAGMA AUTONOMOUS_TRANSACTION;

BEGIN
  INSERT INTO fga_tab
  (owner, table_name, policy_name)
  VALUES
  (sname, tname, pname);
  COMMIT;
END fga_handler;
/

exec dbms_fga.add_policy(object_schema=>'UWCLASS', object_name=> 'FGA_DEMO', policy_name=> 'UW_Audit', audit_condition=> 'status = ''A''', audit_column=> 'last_name, salary', handler_schema => 'UWCLASS', handler_module=> 'FGA_HANDLER', enable => TRUE, statement_types => 'INSERT, UPDATE, SELECT',audit_trail => DBMS_FGA.DB_EXTENDED, audit_column_opts => dbms_fga.all_columns);

desc dba_audit_policies

set linesize 141
col policy_text format a30

SELECT object_schema, object_name, policy_name
FROM dba_audit_policies;

SELECT policy_text, policy_column, enabled
FROM dba_audit_policies;

SELECT pf_schema, pf_package, pf_function
FROM dba_audit_policies;

SELECT sel, ins, upd, del, audit_trail, policy_column_options
FROM dba_audit_policies;

SELECT * FROM fga_tab;
SELECT * FROM fga_demo;

GRANT ALL on fga_demo TO abc;

-- run Session 2

SELECT * FROM fga_demo;
SELECT * FROM fga_tab;
Session 2 CREATE SYNONYM fga_demo FOR uwclass.fga_demo;

INSERT INTO fga_demo
(person_id, last_name, salary, status)
VALUES
(1, 'Morgan', 2500, 'A');

COMMIT;

UPDATE fga_demo
SET salary = salary * 1.05;

COMMIT;

UPDATE fga_demo
SET status = 'I';

COMMIT;

UPDATE fga_demo
SET salary = salary * 1.05;

COMMIT;

UPDATE fga_demo
SET status = 'A';

COMMIT;

UPDATE fga_demo
SET salary = salary * 1.05;

COMMIT;
Clean up conn sys@pdbdev as sysdba

SELECT COUNT(*)
FROM fga_log$;

DELETE FROM fga_log$;

COMMIT;

Related Topics
Built-in Functions
Built-in Packages
Security
Audit Vault
Auditing
DBMS_AUDIT_MGMT
Morgan's How Can I Demo
PRAGMA SUPPLENTAL_LOG_DATA
What's New In 21c
What's New In 26ai

Morgan's Library Page Footer
This site is maintained by Daniel Morgan. Last Updated: This site is protected by copyright and trademark laws under U.S. and International law. © 1998-2026 Daniel A. Morgan All Rights Reserved