Oracle | DBMS

Oracle DBMS_FGA Version 21c

General Information

Library Note

Morgan's Library Page Header

ACE Director Alum Daniel Morgan, founder of Morgan's Library, is scheduling complimentary technical Workshops on Database Security for the first 30 Oracle Database customers located anywhere in North America, EMEA, LATAM, or APAC that send an email to asra_us@oracle.com. Request a Workshop for your organization today.

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 and EXECUTE_CATALOG_ROLE 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;