A different look at hierarchy based security in OBIEE

In this blog we will look at several  methods  to implement hierarchy based security in OBIEE, when the number of members in any particular level is very large.

Consider an employee hierarchy consisting of 4 levels. Our goal is to implement a data –level security based on this hierarchy. i.e the employee at a particular level should be able to see only data pertaining to the employees that work under him.

Let us look at one method of implementing this:

#1

Using row-wise initialized session variable

1. Assign all the users to a security group in the RPD.

2. Create a row-wise initialized session variable (——–), which will be initialised with the list of all the Employess that come under a particular user. The following SQL in the initialization block shown below shows you how this is done.

So when MGR1 logs in, the session variable LOGIN_VAR will be poulated with values

(‘EMP1’,EMP2’).

3. Now, we set the security filter for  Employee Group on the Fact table.

Now let us log into answers as a manager and take a look at the query generated by OBIEE in the query log:

I have logged in as MGR1, who has two employees under him ‘EMP1’ and ‘EMP2’

 

select T56.LOGIN as c1, sum(T433.TOTAL_SALE_AMOUNT) as c2 from EMPLOYEE_D T56, AGG_EMP_SALES_F T433 where  ( T56.ROW_WID = T433.EMPLOYEE_WID and (T56.LOGIN in (‘EMP1’, ‘EMP2’)) ) group by T56.LOGIN order by c1;

You will see that OBIEE generates a query with a IN clause that will contain the values of all the Employees assigned to a particular manager. Now, this query will work fine as long as the list in the IN clause is not very large. Suppose, MGR1 had a 1000 Employees under him, then the session variable LOGIN_VAR would have to be populated with  list of 1000 values every time MGR1 logs in. This could prove to be  fairly costly in terms of performance. as it will significantly  affect the time required to authenticate the user in to the application.

In the next method we will see an alternate method of implementing hierarchy based data visibility.

#2

1. Create a table which would have all the employees and their hierarchical level.

2. Create one security group in the RPD and assign all the users to this group.

3. Create an initialization block that will populate a session variable with the value of level to which a user belongs. For eg: when MGR3 logs in, the session variable will have the value 2.

4. In the permissions for the security group you have created enter the following filter condition:

VALUEOF(NQ_SESSION.”LEVEL_1″) = 1 AND CUBE_DATA.EMPLOYEE_D.LOGIN =  VALUEOF(NQ_SESSION.”USER”) OR VALUEOF(NQ_SESSION.”LEVEL_1″) = 2 AND CUBE_DATA.EMPLOYEE_D.SALES_MGR_NAME =  VALUEOF(NQ_SESSION.”USER”) OR VALUEOF(NQ_SESSION.”LEVEL_1″) = 3 AND CUBE_DATA.EMPLOYEE_D.AREA_MANAGER_NAME =  VALUEOF(NQ_SESSION.”USER”) OR VALUEOF(NQ_SESSION.”LEVEL_1″) = 4 AND CUBE_DATA.EMPLOYEE_D.REGION_MANAGER_NAME =  VALUEOF(NQ_SESSION.”USER”)

Save changes to the RPD and check the result in Answers.

Let us take a look at the query that is being issued by OBIEE.

select T2867.LOGIN as c1, sum(T2880.SALE_AMOUNT) as c2 from EMPLOYEE_D T2867, SALES_F T2880 where  ( T2867.ROW_WID = T2880.EMPLOYEE_WID and (T2867.LOGIN in (‘MGR1’) or T2867.SALES_MGR_NAME in (‘MGR1’) or T2867.AREA_MANAGER_NAME in (‘MGR1’) or T2867.REGION_MANAGER_NAME in (‘MGR1’)) and (T2867.LOGIN in (‘MGR1’) or T2867.SALES_MGR_NAME in (‘MGR1’) or T2867.AREA_MANAGER_NAME in (‘MGR1’) or 2.0 in (4)) and (T2867.LOGIN in (‘MGR1’) or T2867.SALES_MGR_NAME in (‘MGR1’) or T2867.REGION_MANAGER_NAME in (‘MGR1’) or 2.0 in (3)) and (T2867.LOGIN in (‘MGR1’) or T2867.SALES_MGR_NAME in (‘MGR1’) or 2.0 in (3, 4)) and (T2867.LOGIN in (‘MGR1’) or T2867.AREA_MANAGER_NAME in (‘MGR1’) or T2867.REGION_MANAGER_NAME in (‘MGR1’) or 2.0 in (2)) and (T2867.LOGIN in (‘MGR1’) or T2867.AREA_MANAGER_NAME in (‘MGR1’) or 2.0 in (2, 4)) and (T2867.LOGIN in (‘MGR1’) or T2867.REGION_MANAGER_NAME in (‘MGR1’) or 2.0 in (2, 3)) and (T2867.LOGIN in (‘MGR1’) or 2.0 in (3, 2, 4)) and (T2867.SALES_MGR_NAME in (‘MGR1’) or T2867.AREA_MANAGER_NAME in (‘MGR1’) or T2867.REGION_MANAGER_NAME in (‘MGR1’) or 2.0 in (1)) and (T2867.SALES_MGR_NAME in (‘MGR1’) or T2867.AREA_MANAGER_NAME in (‘MGR1’) or 2.0 in (1, 4)) and (T2867.SALES_MGR_NAME in (‘MGR1’) or T2867.REGION_MANAGER_NAME in (‘MGR1’) or 2.0 in (1, 3)) and (T2867.SALES_MGR_NAME in (‘MGR1’) or 2.0 in (3, 1, 4)) and (T2867.AREA_MANAGER_NAME in (‘MGR1’) or T2867.REGION_MANAGER_NAME in (‘MGR1’) or 2.0 in (1, 2)) and (T2867.AREA_MANAGER_NAME in (‘MGR1’) or 2.0 in (2, 1, 4)) and (T2867.REGION_MANAGER_NAME in (‘MGR1’) or 2.0 in (2, 1, 3)) and (2.0 in (3, 2, 1, 4)) ) group by T2867.LOGIN order by c1

We see that OBIEE is issuing a complicated where clause in the query. The logical request generated by OBIEE is shown:

——————– Logical Request (before navigation): RqList EMPLOYEE_D.LOGIN as c1 GB, SALE_AMOUNT:[DAggr(SALES_F.SALE_AMOUNT by [ EMPLOYEE_D.LOGIN] )] as c2 GB DetailFilter: EMPLOYEE_D.REGION_MANAGER_NAME = ‘MGR1’ and 4 = 2.0 or EMPLOYEE_D.AREA_MANAGER_NAME = ‘MGR1’ and 3 = 2.0 or EMPLOYEE_D.LOGIN = ‘MGR1’ and 1 = 2.0 or EMPLOYEE_D.SALES_MGR_NAME = ‘MGR1’ and 2 = 2.0 OrderBy: c1 asc

 

#3

Now let us see if we can further simplify this query, by tweaking our security implementation.

The following steps outline the modifications to above security scheme.

1. Instead of having just one session variable that will get populated with the value of the hierarchy level of a user, let us initialize as many session variables as there are levels in the hierarchy with a value ‘Y’ if the user belongs to a particular level and ‘N’ if he does not.

For eg:

The following SQL is used to populate the session variable for manager. So if MGR1 logs in then the MGR_LEVEL session variable will be populated with value ‘Y’

SELECT CASE WHEN LEVEL_1=2 THEN ‘Y’ ELSE ‘N’ END FROM employee_level WHERE login=’:USER’

2. In  permissions for the security group you have created enter the following filter condition:

VALUEOF(NQ_SESSION.”EMP_LEVEL”) = ‘Y’ AND CUBE_DATA.EMPLOYEE_D.LOGIN =  VALUEOF(NQ_SESSION.”USER”) OR  VALUEOF(NQ_SESSION.”MGR_LEVEL”) = ‘Y’ AND CUBE_DATA.EMPLOYEE_D.SALES_MGR_NAME =  VALUEOF(NQ_SESSION.”USER”) OR  VALUEOF(NQ_SESSION.”AREA_MGR_LEVEL”) = ‘Y’ AND CUBE_DATA.EMPLOYEE_D.AREA_MANAGER_NAME =  VALUEOF(NQ_SESSION.”USER”) OR  VALUEOF(NQ_SESSION.”RM_LEVEL”) = ‘Y’ AND CUBE_DATA.EMPLOYEE_D.REGION_MANAGER_NAME =  VALUEOF(NQ_SESSION.”USER”)

3. Log in to Answers and run a request and check the query being issued by OBIEE.

If we look at the logical request being issued by OBIEE in the query log:

——————– Logical Request (before navigation): RqList EMPLOYEE_D.LOGIN as c1 GB, SALE_AMOUNT:[DAggr(SALES_F.SALE_AMOUNT by [ EMPLOYEE_D.LOGIN] )] as c2 GB DetailFilter: EMPLOYEE_D.REGION_MANAGER_NAME = ‘MGR1’ and ‘Y’ = ‘N’ or EMPLOYEE_D.AREA_MANAGER_NAME = ‘MGR1’ and ‘Y’ = ‘N’ or EMPLOYEE_D.LOGIN = ‘MGR1’ and ‘Y’ = ‘N’ or EMPLOYEE_D.SALES_MGR_NAME = ‘MGR1’ and ‘Y’ = ‘Y’ OrderBy: c1 asc

You can see that using a ‘Y’ or ‘N’ Flag in the filter helps OBIEE generate a simpler and more efficient query.

select T2867.LOGIN as c1, sum(T2880.SALE_AMOUNT) as c2 from EMPLOYEE_D T2867, SALES_F T2880 where  ( T2867.ROW_WID = T2880.EMPLOYEE_WID and T2867.SALES_MGR_NAME = ‘MGR1’ ) group by T2867.LOGIN order by c1