OBIEE 11g INIT Block and User and Table filtering from RPD to Analysis

OBIEE 11g INIT Block and User and Table filtering from RPD to Analysis

Tested on OBIEE 11.1.1.6.0 tried and tested.
Tested on OBIEE 11.1.1.6.8 tried and tested.

Purpose is to restrict user data on a region table based on mapping table that stores usernames and Regions for each user that logs into system.

1. Usernames and Regions and we restrict users data on the Business Model layer to specfic region mappings in mapping table based on user login.
2. super users - create a open filter condition for all values without populating all values in table for each individual user that requires all values.
3. users not in table and pass dummy value to return no values.
------------------------
ADMIN = Entry must exist in REGION TABLE if not entry exists user will not be able to query any Region.
No Region = No user exists in REGION TABLE
DUMMY = NO value Does nothing Returns no Values at all
------------------------

----------------------------------------------------------------------------------------------------------------------------  
Initialization Block 1 Session INIT Block

Init Block Name = MY_REGION_SECURITY
Row wise initialization

-- SQL. TO filter by user logging into OBIEE and handle users not in the Mapping table for regions by selecting a No Region record.

select 'REGION_SECURITY', REGION FROM MY_SCHEMA.MY_U_REGION_SECURITY WHERE USER_NAME = ':USER'
UNION
select 'REGION_SECURITY',REGION
from MY_SCHEMA.MY_U_REGION_SECURITY
where user_name = 'No Region'
and not exists (select NULL from  MY_SCHEMA.MY_U_REGION_SECURITY where user_name =  ':USER')

Description = Region Security INIT Block for Where condition REQUIRED in Business Model

----------------------------------------------------------------------------------------------------------------------

Initialization Block 2  Session INIT Block - Filters Results of Initialization Block 1 and prepares them for Analysis and BMM Conditions.

Init Block Name = MY_REGION_SECURITY_OUTPUT
Session Variable OUTPUT = REGION_SECURITY_OUT

-- SQL. To take values from

SELECT LISTAGG (REGION,'; ')  WITHIN GROUP (ORDER BY REGION)
from (select distinct REGION from MY_SCHEMA.MY_U_REGION_SECURITY) T
WHERE REGION IN (VALUELISTOF(NQ_SESSION.REGION_SECURITY))

Description = Region Unit to be displayed in Analysis and for BMM Filter

Execution Presedence = MY_REGION_SECURITY

----------------------------------------------------------------------------------------------------------------------
Business Model in RPD needs filtering.
BMM Logic for Region Security on D_REGION_TABLE

D_REGION_TABLE in Source and Filter Clause Exclude Where. Take out comment lines and remove spaces.

-- Normal User
"MYDB".""."MYSCHEMA"."D_REGION_TABLE"."REGION"  =  VALUEOF(NQ_SESSION.REGION_SECURITY)
AND (VALUEOF(NQ_SESSION."REGION_SECURITY_OUT") != 'ADMIN' AND VALUEOF(NQ_SESSION."REGION_SECURITY_OUT") !='NOREGION')
-- Super User
OR ("MYDB".""."MYSCHEMA"."D_REGION_TABLE"."REGION"  =  "MYDB".""."MYSCHEMA"."D_REGION_TABLE"."REGION"
 AND VALUEOF(NQ_SESSION."REGION_SECURITY_OUT") = 'ADMIN')
-- User does not exist in MY_U_REGION_SECURITY TABLE
OR (VALUEOF(NQ_SESSION."REGION_SECURITY_OUT") = 'NOREGION'
  AND "MYDB".""."MYSCHEMA"."D_REGION_TABLE"."REGION" = 'DUMMY')


----------------------------------------------------------------------------------------------------------------------
Database Objects used and created


Table :
CREATE TABLE MY_SCHEMA.MY_U_REGION_SECURITY
(
  USER_ID               NUMBER,
  USER_NAME             VARCHAR2(100 BYTE),
  REGION          VARCHAR2(7 BYTE),
  EFFECTIVE_START_DATE  DATE,
  EFFECTIVE_END_DATE    DATE,
  LAST_UPDATE_DATE      DATE
);

GRANT SELECT ON MY_SCHEMA.MY_U_REGION_SECURITY TO MY_RO_USER; 


Dummy insert Statements.

noregion  user is mandatory
weblogic user could also be usefull for testing from rpd.

SET DEFINE OFF;
--------------------------- Insert region specific user -----------------------------
Insert into MY_SCHEMA.MY_U_REGION_SECURITY
   (USER_ID, USER_NAME, REGION, EFFECTIVE_START_DATE, EFFECTIVE_END_DATE, LAST_UPDATE_DATE)
 Values
   (0, 'weblogic', 'WESTMIDLANDS', TO_DATE('04/04/2013 11:49:41', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('05/04/2013 11:49:41', 'MM/DD/YYYY HH24:MI:SS'),
    TO_DATE('04/04/2013 11:49:41', 'MM/DD/YYYY HH24:MI:SS'));
Insert into MY_SCHEMA.MY_U_REGION_SECURITY
   (USER_ID, USER_NAME, REGION, EFFECTIVE_START_DATE, EFFECTIVE_END_DATE, LAST_UPDATE_DATE)
 Values
   (0, 'weblogic', 'EASTMIDLANDS', TO_DATE('04/04/2013 11:49:36', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('05/04/2013 11:49:36', 'MM/DD/YYYY HH24:MI:SS'),
    TO_DATE('04/04/2013 11:49:36', 'MM/DD/YYYY HH24:MI:SS'));

-------------------Insert a Admin User--- Super User ---------------------------
Insert into MY_SCHEMA.MY_U_REGION_SECURITY
   (USER_ID, USER_NAME, REGION, EFFECTIVE_START_DATE, EFFECTIVE_END_DATE, LAST_UPDATE_DATE)
 Values
   (0, 'JOHNM', 'ADMIN', TO_DATE('04/04/2013 12:52:08', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('05/04/2013 12:52:08', 'MM/DD/YYYY HH24:MI:SS'),
    TO_DATE('04/04/2013 12:52:08', 'MM/DD/YYYY HH24:MI:SS'));
-------------------- insert mandatory record ---------------------------------------
Insert into MY_SCHEMA.MY_U_REGION_SECURITY
   (USER_ID, USER_NAME, REGION, EFFECTIVE_START_DATE, EFFECTIVE_END_DATE, LAST_UPDATE_DATE)
 Values
   (0, 'noregion', 'No Region', TO_DATE('04/04/2013 11:40:59', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('05/04/2013 11:40:59', 'MM/DD/YYYY HH24:MI:SS'),
    TO_DATE('04/04/2013 11:40:59', 'MM/DD/YYYY HH24:MI:SS'));
COMMIT;