CODE
Oracle Code Library
JOBS
Find Or Post Oracle Jobs
FORUM
Oracle Discussion & Chat
PSOUG Home Code Snippets Oracle Reference Oracle Functions PSOUG Forum Oracle Blogs Bookmark and Share
 
 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);
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 / 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 [72 users online]    © 2010 psoug.org