DW Security Using Views, Roles and Public Synonyms

As an alternative to using Virtual Private Databases to provide row-level security for an Oracle data warehouse, I put together an alternative security set up for a client that achieves much the same thing.

The client's data warehouse held data in four main subject areas; Finance, Manufacturing, Retail and HR. Within the data warehouse, there were in fact a number of fact tables, and these were grouped in to logical areas corresponding to these subjects - for example, the FIN_FORECAST_FACT and FIN_ACTUAL_FACT tables were logically part of the Finance group of fact tables.

The client had four divisions within the company; North, South, East and West. Each of the fact tables in the warehouse contained rows for each of these divisions, and each row had a DIVISION_ID column that identified the division that supplied the data. Queries issued by users should ideally only return those rows from the fact tables that corresponded to divisions for which the user had been granted access.

When granting access to users, the aim was to specify which division, and which subject area, the user could access. Multiple access rights should be grantable to users, so that, for example, User 'A' was entitled to view finance reports for the Northern, Southern and Eastern divisions, whilst User 'B' could view all of these plus Manufacturing reports for Northern division and HR reports for Eastern Division.

Whatever security system was set up, it should be transparent to users and should support their current way of accessing Oracle applications, which was through using their unique userid and password when logging in to SQL*Plus, TOAD and Oracle Discoverer.

The solution that we came up with was as follows;

1. Create a number of Oracle roles that corresponded to the intersection between all divisions and all warehouse subject areas;

create role DW_NORTH_FINANCE;
create role DW_NORTH_MANUF;
create role DW_NORTH_HR;
create role DW_NORTH_RETAIL;
create role DW_SOUTH_FINANCE;
create role DW_SOUTH_MANUF;
create role DW_SOUTH_HR;
create role DW_SOUTH_RETAIL;
create role DW_EAST_FINANCE;
create role DW_EAST_MANUF;
create role DW_EAST_HR;
create role DW_EAST_RETAIL;
create role DW_WEST_FINANCE;
create role DW_WEST_MANUF;
create role DW_WEST_HR;
create role DW_WEST_RETAIL;

Any one user can be assigned between none and all of the roles by issuing the command "GRANT DW_WEST_HR to bsmith" (for example).

2. A control table is set up in the warehouse schema that maps these roles to subject areas and department_ids. The table is defined as follows;

CREATE TABLE DW_ROLES_DEPARTMENTS ( 
  ROLE      VARCHAR2 (20)  NOT NULL, 
  FACTS     VARCHAR2 (20), 
  DEPARTMENT_ID  NUMBER (3), 
  CONSTRAINT DW_ROLES_DEPARTMENTS_PK
  PRIMARY KEY ( ROLE ) ) ;  

CREATE INDEX DW_ROLES_DEPARTMENTS_IDX1 ON 
  DW_ROLES_DEPARTMENTS(DEPARTMENT_ID) ; 

 CREATE INDEX DW_ROLES_DEPARTMENTS_IDX2 ON 
  DW_ROLES_DEPARTMENTS(FACTS)

Values are then inserted into this table as follows;

1.      3. A view is created within the SYS schema that is used to retrieve the DW roles associated with a user. Access to this view is granted to PUBLIC and a public synonym is created for the view.

create or replace view DW_user_roles as select grantee, granted_role from dba_role_privs
where granted_role like 'DW_%'
and grantee != 'SYSTEM'; 

grant select on DW_user_roles to public; 

drop public synonym DW_user_roles; 

create public synonym DW_user_roles for DW_user_roles;

4. Views are created over the fact tables, that join to the DW_ROLES_DEPARTMENTS table to limit user access;

CREATE OR REPLACE VIEW FIN_FORECAST_FACT_V AS select *
from fin_forecast_fact where department_id in 
(select department_id
 from DW_roles_departments c, DW_user_roles d 
 where d.grantee = user 
 and   d.granted_role = c.role 
 and   c.facts = 'FINANCE');

CREATE OR REPLACE VIEW FIN_ACTUAL_FACT_V AS select *
from fin_actual_fact where department_id in 
(select department_id
 from DW_roles_departments c, DW_user_roles d 
 where d.grantee = user 
 and   d.granted_role = c.role 
 and   c.facts = 'FINANCE');

CREATE OR REPLACE VIEW retail_fact_v AS SELECT * from retail_fact
where department_id in 
(select location 
 from DW_roles_departments c, DW_user_roles d 
 where d.grantee = user 
 and   d.granted_role = c.role 
 and   c.facts = 'RETAIL');

And so on. Note the use of the 'FACTS' column in the DW_ROLES_DEPARTMENTS table, which allows us to group fact tables together into subject areas, and grant access to these subject areas to database roles. If more fine-grained access was required (i.e. to individual fact tables, rather than groups of fact tables) this column could be replaced by one listing fact table names, and the view SQL changed to reflect this.

grant select on retail_fact_v to DW_north_retail;
grant select on retail_fact_v to DW_east_retail;
grant select on retail_fact_v to DW_south_retail;
grant select on retail_fact_v to DW_west_retail;
grant select on fin_actual_fact_v to DW_north_finance;
grant select on fin_actual_fact_v to DW_east_finance;
grant select on fin_actual_fact_v to DW_south_finance;
grant select on fin_actual_fact_v to DW_west_finance;
grant select on fin_forecast_fact_v to DW_north_finance;
grant select on fin_forecast_fact_v to DW_east_finance;
grant select on fin_forecast_fact_v to DW_south_finance;
grant select on fin_forecast_fact_v to DW_west_finance;

grant select on outlet_dim to DW_north_retail, DW_south_retail, DW_east_retail, DW_west_retail;
grant select on sector_dim to DW_north_retail, DW_south_retail, DW_east_retail, DW_west_retail;
grant select on date_dim to DW_north_MANUF, DW_north_finance, DW_north_retail, DW_north_hr;
grant select on date_dim to DW_east_MANUF, DW_east_finance, DW_east_retail, DW_east_hr;
grant select on date_dim to DW_south_MANUF, DW_south_finance, DW_south_retail, DW_south_hr;
grant select on date_dim to DW_west_MANUF, DW_west_finance, DW_west_retail, DW_west_hr;
grant select on gl_account_dim to DW_north_finance, DW_south_finance, DW_east_finance, DW_west_finance;

6. Public synonyms are then created for the fact views and dimensions, so that users can log into their own schemas and access them by just using the table name. 'DW' is the name of the schema containing the facts and dimensions..

create public synonym retail_fact for dw.retail_fact_v;
create public synonym fin_actual_fact for dw.fin_actual_fact_v;
create public synonym fin_forecast_fact for dw.fin_forecast_fact_v;
create public synonym outlet_dim for dw.outlet_dim;
create public synonym sector_dim for dw.sector_dim;
create public synonym date_dim for dw.date_dim;
create public synonym gl_account_dim for dw.gl_account_dim;

7. Access rights to the DW objects are then granted to users by granting them one or more of the DW_ roles; for example

GRANT DW_north_finance TO bsmith;

Allows