Oracle DBMS_FGA Built-In Package
Versions 9.0 - 21c

Security Advisory
Fine Grained Auditing is policy based conditional auditing that can be based on specific columns and specific column values.

DB Audit sends 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.
 
Recommended Security Rules

 NEVER
  • If deploy an application on Oracle Database Enterprise Edition without Fine Grained Auditing. To do so is irresponsible as you are not just ignoring an incredible security tool ... you are wasting money.
 WITH GREAT CARE
  • Test your Fine Grained Auditing deployment to verify that it is giving you visibility into what is going on inside your database.
 CAUTIONS
  • Be careful to protect the source and deployed code of a Fine Grained Auditing application from tampering.
 
How Oracle Works
The code for a working demo can be found at the bottom of this page. The demo can be built with cut-and-paste in just a few minutes. Spend a few hours working with the demo then go to the Oracle docs, read up on this incredibly powerful tool, then deploy it in your organization.

But remember, auditing is worthless if no one is monitoring the audit output. If one person, or one group, is not responsible for monitoring then no one is monitoring.
 
DBMS_FGA Package Information
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
DBMS_CRYPTO_FFI DBMS_REGISTRY KUPM$MCP
DBMS_ISCHED DBMS_SCHEDULER MGWI_CRYPTO
DBMS_ISCHED_REMOTE_ACCESS DBMS_SQLHASH UTL_RAW
DBMS_METADATA_DIFF DBMS_REGISTRY  
Documented in Types & Packages Yes
First Available 9.0.1
Initialization Parameters col name format a30
col value format a40

SELECT name, value
FROM gv$parameter
WHERE name LIKE '%audit%';
Pragmas PRAGMA SUPPLEMENTAL_LOG_DATA(default, AUTO)
Security Model Owned by SYS with EXECUTE granted to the AUDIT_ADMIN and EXECUTE_CATALOG_ROLE roles
Source {ORACLE_HOME}/rdbms/admin/dbmsfga.sql
Subprograms
 
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
Auditing
Audit Vault
DBMS_AUDIT_MGMT
DBMS_AUDIT_UTIL
Startup Parameters
Unified Audit Policies