Applying Fine Grained Access Control To Analytic Workspaces

One of our customers I worked with recently asked about implementing the equivalent of row-level security within analytic workspaces. I subsequently came across this posting on the OTN OLAP Forum which details a method of using the Virtual Private Database (VPD) feature within the Oracle RDBMS to achieve this, which in the VPD world is termed "fine grained access control". I've reproduced the relevant part of the posting in case it later becomes unavailable:

"From the OLAP Field Guide:

Virtual Private Database (VPD or FGAC) on an AW View

This example creates an administrator named FGAC_SYS which manages the VPD (Fine Grained Access Control) configuration of the database. A table named USR_FGAC_LVL contains information on what group they belong to (i.e., DEF [default], MGR [manager]) and REGION they are allowed to see. The DBMS_RLS (row level security) package uses this table as a reference to create policies for managing access to the CUSTOMER_VIEW owned by GLOBAL_AW user. So if SCOTT wished to select from the CUSTOMER_VIEW (which uses the OLAP_TABLE function to retrieve data from the AW) he will only see data relating to REGION 10. While GLOBAL user can see everything because this user is a member of the MGR group.

NOTE: In Oracle Database 10g Virtual Private Database supports parallel query, resulting in performance and scalability improvements, lets you distinguish between static policies, which are suitable for hosting environments that always need to enforce an unchanging policy, and dynamic policies, which are suited for time-dependent enforcement, such as time of day, where rows returned must vary at a particular time and able to enforce VPD rewrite when a query references a particular column. (See Oracle Database New Features Guide,10g Release 1 (10.1), Part Number B10750-01 for more information)

conn / as sysdba
drop user fgac_sys cascade
create user fgac_sys profile default identified by oracle default tablespace system account unlock;
grant execute on sys.dbms_rls to fgac_sys;
grant dba, olap_user to fgac_sys;

set serveroutput on size 1000000

conn fgac_sys/oracle

drop table USR_FGAC_LVL cascade constraints';
create table usr_fgac_lvl (username varchar2(20) not null, fgac_lvl varchar2(3) not null, cregion varchar2(3) not null);

create or replace public synonym usr_fgac_lvl for fgac_sys.usr_fgac_lvl;
grant select on usr_fgac_lvl to public;
create or replace public synonym dbms_rls for sys.dbms_rls;

insert into usr_fgac_lvl values ('SCOTT' ,'DEF','10');
insert into usr_fgac_lvl values ('GLOBAL','MGR','999');
insert into usr_fgac_lvl values ('GLOBAL_AW','MGR','999');
insert into usr_fgac_lvl values ('SYSTEM','DEF','9');

create or replace package fgac_on_logon as
procedure set_fgac_parameters;
end;
/

create or replace package body fgac_on_logon is
procedure
set_fgac_parameters is
v_user varchar2(30);
v_fgac_lvl varchar2(3);
v_cregion varchar2(3);
begin
v_user := sys_context ('userenv','session_user');
select fgac_lvl, cregion into v_fgac_lvl, v_cregion from usr_fgac_lvl where username = v_user;
dbms_session.set_context ('FGAC_CONTEXT','FGAC_LVL',v_fgac_lvl);
dbms_session.set_context ('FGAC_CONTEXT','CREGION',v_cregion);
exception when no_data_found
then dbms_session.set_context ('FGAC_CONTEXT','FGAC_LVL','NA');
end;
end;
/

create or replace context fgac_context using fgac_on_logon;

create or replace trigger fgac_trigger
after logonon database
begin
fgac_on_logon.set_fgac_parameters;
end;
/

create or replace package olap_security as
function olap_sec (d1 varchar2, d2 varchar2)
return varchar2;
end;
/

create or replace package body olap_security is
function olap_sec (d1 varchar2, d2 varchar2)
return varchar2 is
d_predicate varchar2(2000);
begin
if sys_context('FGAC_CONTEXT','FGAC_LVL') = 'NA' then d_predicate := '0=1'; end if;
if sys_context('FGAC_CONTEXT','FGAC_LVL') = 'DEF'
then d_predicate := 'REGION = SYS_CONTEXT(''FGAC_CONTEXT'',''CREGION'')'; end if;
if sys_context('FGAC_CONTEXT','FGAC_LVL') = 'MGR' then d_predicate := ''; end if;
return d_predicate;
end;
end;
/

begin
dbms_rls.add_policy ('GLOBAL_AW','CUST_VW','cust_vw_policy','fgac_sys','olap_security.olap_sec','select');
dbms_rls.enable_policy ('GLOBAL_AW','CUST_VIEW','customer_view_policy', true);
end;
/

connect global_aw/global_aw
grant select on CUSTOMER_VIEW to public;


FGA (Fine-Grained Auditing) Auditing an AW View

BEGIN DBMS_FGA.add_policy( object_schema=> 'GLOBAL_AW', object_name=> 'CUSTOMER_VIEW', policy_name=> 'CUSTVIEW_ACCESS', audit_column => 'REGION', audit_condition => 'REGION = 9');
END;
/

select timestamp, db_user, os_user, object_schema, object_name, sql_text from dba_fga_audit_trail;"

Thanks to Anthony Waite for the posting.