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