Oracle DBMS_FGA Fine Grained Access Control Audit Auditing


	CODE Oracle Code Library


	JOBS Find Or Post Oracle Jobs


	FORUM Oracle Discussion & Chat

Search the Reference Library pages:

Free
Oracle Magazine
Subscriptions
and Oracle White Papers

Oracle DBMS_FGA (Fine Grained Auditing)

Version 11.1

General Information

Source

{ORACLE_HOME}/rdbms/admin/dbmsfga.sql

First Available

9.0.1

Constants

Name	Data Type	Value	Description
EXTENDED	PLS_INTEGER	1	Includes SQL Text and SQL Bind
DB	PLS_INTEGER	2	Sends the audit trail to the SYS.FGA_LOG$ table in the database and omits SQL Text and SQL Bind.
DB_EXTENDED (default)	PLS_INTEGER	3
XML	PLS_INTEGER	4	Writes the audit trail in XML files sent to the operating system and omits SQL Text and SQL Bind.
XML+EXTENDED	PLS_INTEGER	5	may no longer exist
ALL_COLUMNS	BINARY_INTEGER	1
ANY_COLUMNS (default)	BINARY_INTEGER	0

Dependencies

fga$	fga_log$
all_audit_policies	gv$xml_audit_trail
dba_audit_policies	user_audit_policies
dba_fga_audit_trail

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

Execute is granted to the EXECUTE_CATALOG_ROLE

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);

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 / 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

/ 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

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) 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',audit_trail => DBMS_FGA.DB+EXTENDED,
audit_column_opts => dbms_fga.all_columns);

desc dba_audit_policies

set linesize 140
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 / as sysdba

SELECT COUNT(*)
FROM fga_log$;

DELETE FROM fga_log$;

COMMIT;

Related Topics

Audit Vault

Auditing

PRAGMA SUPPLENTAL_LOG_DATA

Home : Code Library : Sponsors : Privacy : Terms of Use : Contact Us