Oracle DBMS_FGA
Version 12.1.0.2

General Information
Library Note Morgan's Library Page Footer
The Library is currently in the process of being upgraded from Oracle Database Version 12.1.0.1 to 12.1.0.2. Demos are being upgraded to reflect the new Container paradigm as well as EBR (Edition Based Redefinition) and may contain references to CDBs, PDBs, and other objects you may not be familiar with such as CDB_OBJECTS_AE: Welcome to 12c.

Interested in Oracle GoldenGate? Check out the IGGOUG,
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 DEFINER
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
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);
 
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', 'emp', 'UWAudit');
 
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', 'UWAudit');
 
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', 'emp', 'UWAudit', TRUE);

set linesize 121
col audit_type format a20
col os_user format a25
col userhost format a15
col sql_bind format a20

SELECT audit_type, session_id, os_user, userhost, session_cpu, scn, sql_bind
FROM dba_common_audit_trail;
 
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 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
Audit Vault
Auditing
DBMS_AUDIT_MGMT
Morgan's How Can I Demo
Packages
PRAGMA SUPPLENTAL_LOG_DATA
Security

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