Oracle Training Oracle Support Development Oracle Apps

 E-mail Us
 Oracle Articles
New Oracle Articles

 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog

 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Oracle Support

 SQL Tuning

 Oracle UNIX
 Oracle Linux
 Remote s
 Remote plans
 Application Server

 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S


 Consulting Staff
 Consulting Prices
 Help Wanted!


 Oracle Posters
 Oracle Books

 Oracle Scripts

Don Burleson Blog 







Oracle Business Intelligence, OLAP and BI training and consulting tips . . .

Click here for more
Oracle News Headlines


Applying Fine Grained Access Control to Analytic Workspaces
April 28, 2005
Mark Rittman

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;

create or replace package body fgac_on_logon is
set_fgac_parameters is
v_user varchar2(30);
v_fgac_lvl varchar2(3);
v_cregion varchar2(3);
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');

create or replace context fgac_context using fgac_on_logon;

create or replace trigger fgac_trigger
after logonon database

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

create or replace package body olap_security is
function olap_sec (d1 varchar2, d2 varchar2)
return varchar2 is
d_predicate varchar2(2000);
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;

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

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

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

Thanks to Anthony Waite for the posting.


Oracle Training at Sea
oracle dba poster

Follow us on Twitter 
Oracle performance tuning software 
Oracle Linux poster


Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  

and include the URL for the page.


Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


Copyright © 1996 -  2017

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.

Remote Emergency Support provided by Conversational