Extend Fine Grained Auditing to Only Audit Specific Database Users
 
Home

Resources
Library
How Can I?
Presentations
Links
Book Reviews
Downloads
User Groups


General
Contact Us
About Us
Services
Legal Notices & Terms of Use
Privacy Statement
Extending The Basic Implementation of DBMS_FGA
Oracle's Fine Grained Auditing, implemented through the use of the DBMS_FGA built-in package, provides a capability for HIPAA and other compliance requirements that can not be duplicated in other commercial RDBMS products such as SQL Server (which BTW can not ever be truly HIPAA compliant at least through the current version 2008).

In the following demo I am going to use the profile column to select a specific application profile that will not be audited (for example one used by an application web server) and audit everything else. This will be done by utilizing AUDIT_CONDITION parameter of DBMS_FGA.ADD_POLICY to utilize a user-defined PL/SQL function within the FGA filter clause.

First let's create a demo table to be audited. The constraints are not required but are just there support additional user testing outside the scope of this HCI document: You do not need to build them to run the demo.
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'));
Next we will build the user-defined function that will be executed by the Fine Grained Auditing policy each time it executes. Note that if the user's profile, in DBA_USERS, is 'UWAPP' the function returns the integer value 1: Otherwise it returns 0. A call is made to dbms_snapshot to eliminate the possibility of auditing materialized view refreshes: It can be dropped if the table being audited is not a materialized view.
conn uwclass/uwclass

CREATE OR REPLACE PACKAGE uw_utilities AUTHID CURRENT_USER IS
 vUserProfile dba_users.profile%TYPE;
 FUNCTION fga_user_validate RETURN PLS_INTEGER;
END uw_utilities;
/

CREATE OR REPLACE PACKAGE BODY uw_utilities IS
--================================================================
FUNCTION fga_user_validate RETURN PLS_INTEGER IS
 IsRefresh EXCEPTION;
BEGIN
  -- if the profile name has been previous collected skip the SELECT
  IF dbms_snapshot.i_am_a_refresh THEN
    RAISE IsRefresh;
  END IF;

  IF vUserProfile IS NULL THEN
    SELECT /* dsdm_utilities.fga_user_validate */ du.profile
    INTO vUserProfile
    FROM dba_users du
    WHERE du.username = USER;
  END IF;

  -- audit only the specified profile (return value 0)
  IF vUserProfile = 'UWAPP' THEN
    RETURN 0;
  ELSE
    RETURN 1;
  END IF;
EXCEPTION
  WHEN IsRefresh THEN
    -- do not audit MV refreshes or users with non-listed profiles
    RETURN 1;
  WHEN OTHERS THEN
    -- in the event of any failure ... audit the user
    RETURN 0;
END fga_user_validate;
--================================================================
BEGIN
  NULL;
END uw_utilities;br> /
And now we will create a simple policy such that the auditing only takes place when the function returns 0.
conn uwclass/uwclass

exec dbms_fga.add_policy(
     object_schema     => 'UWCLASS',
     object_name       => 'FGA_DEMO',
     policy_name       => 'UW Audit',
     audit_condition   => 'uw_utilities.fga_user_validate = 0',
     audit_column      => NULL,
     handler_schema    => NULL,
     handler_module    => NULL,
     enable            => TRUE,
     statement_types   => 'INSERT,UPDATE,DELETE,SELECT',
     audit_trail       => DBMS_FGA.DB_EXTENDED,
     audit_column_opts => dbms_fga.all_columns);
Finally it is time to test the functionality.
conn / as sysdba

-- create the new profile and give it to hr

conn uwclass/uwclass

grant insert on fga_demo to public;

conn hr/hr

INSERT INTO uwclass.fga_demo
(person_id, last_name, salary, status)
VALUES
(1, 'Morgan', 1000, 'A');
COMMIT;

conn scott/tiger

SELECT * FROM uwclass.fga_demo;

conn / as sysdba

SELECT dbuid, lsqltext
FROM fga_log$;
As you can see the application user, the HR schema, is not audited for the insert but SCOTT, the end-user, is audited on the SELECT.

For more demos click on the DBMS_FGA link below and for more information about the DBMS_FGA package go to Tahiti.
 
Related Topics
DBMS_FGA
 
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-2013 Daniel A. Morgan All Rights Reserved