DBSecWorx: DBMS

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	DROP_POLICY	ENABLE_POLICY
DISABLE_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;