Maintenance free security implementation in OBIEE 11g

Tweet

This blog talks about a hassle free mechanism of OBIEE security implementation with almost zero overhead.
We will first look at the data level security implementation and then extend it to object level security.


Data Level Security:
Data level security is usually implemented using database tables. These tables store the users and the filter to be applied when the users log in. These filters are picked by non system session variables and are applied using either the roles assigned to the users (in a more standard implementation) or using the non system session variables as filters in the analysis.

A good and detail description of this approach is documented @ http://www.rittmanmead.com/2012/03/obiee-11g-security-week-row-level-security/

The drawback of this approach is that one has to maintain a table to implement security. So if a new user joins the organization, a record has to be inserted into the table. Again we might have to update/delete records based on organizational changes. This is an overhead.

Hence, I propose this alternative approach. But before I do that, I must acknowledge the contributions of the team in my company for their valuable inputs in this.

Concept:
We first create AD (Active Directory) groups based on the different functional areas of the employees in the organization. Check out http://obiee-oracledb.blogspot.com/2014/11/active-directory-configuration-in-obiee.html to know about a quick process to configure AD in OBIEE 11g.

Once we have created the groups, we create application roles based on the jobs that these AD groups will perform on the OBIEE portal and we assign privileges to these roles by either tagging them OOTB OBIEE roles such as BIAuthor, BIConsumer etc or giving them custom privileges using the  Manage Privileges part of OBIEE Administration screen. Process to use Manage Privileges part of OBIEE Admin screen to assign privileges to roles is described @ http://obiee-oracledb.blogspot.com/2014/08/privilege-to-see-hidden-objects-in.html.

Use Case:

Lets say that our company operates in Americas, APAC and EMEA regions and we want the region filter to be applied when a user of a certain region logs in.

Implementation Strategy:

In order to implement the above use case, we will create 3 users,3 groups and 3 roles (one each for Americas, APAC and EMEA). I will call these roles, Region Roles, for the purpose of explanation. We will first add the users to their corresponding groups and then add the AD groups containing the Americas user to the Americas role, the groups containing the APAC user to the APAC role and the groups containing the EMEA user to the EMEA role.

Ok, so at this point we will have 3 roles and all our users have been mapped to one of these 3 roles. In order to apply data level security, we will have to add the data filters in each of the 3 roles. Now, if the number of such roles are huge, then we have a lot of work.

So we take a step further and create a role called OBIEE_DATA_SECURITY and add these 3 roles to it.

So this essentially means that all users will have OBIEE_DATA_SECURITY role assigned to them (Because all users of the organization are assigned to one of the 3 Region Roles).
So the data filters applied in OBIEE_DATA_SECURITY role will be applied to all the users.

Now, our task is to enable OBIEE to figure out the right value of the filter for each user.

We know that our users will get the correct Region Roles assigned to them because we have tagged the users to the groups which are inturn tagged to one of the three Region Roles. OBIEE has a system session variable called ROLES that holds the value of the roles assigned to a user. We will write an initialization block that uses the ROLES session variable to find out which of the 3 Region Roles have been assigned to the user. We will use a case statement in the initialization block to find the filter to be applied when the user logs in.

For example, lets say that the filter should be 'Americas' for a user belonging to Americas.

We will have the Americas Region Role assigned to this Americas user in EM.
In the case statement of our initialization block, we will say that if the ROLES session variable has Americas Region Role then the value of the session variable should be 'Americas'. I will come to the actual code in some time.

We will then use this session variable populated by this initialization block in the permissions part of OBIEE_DATA_SECURITY role. Again,  I will come to the actual code in some time. Remember that all 3 Region Roles are assigned to OBIEE_DATA_SECURITY role so all employees of the organization are mapped to this role.

So with this mechanism in place, when a new employee joins the organization, AD group will be assigned to him based on his job type and region. That AD group would have already been mapped to one of the 3 Region Roles and all 3 Region Roles would have already been mapped to the OBIEE_DATA_SECURITY role.

So as soon as a user joins the organization and the AD groups are assigned to him, his security is going to work automatically. Similarly, if an employee leaves the organization, no action will be required from OBIEE team. If an employee moves to another region, he will have to be assigned to the AD group of the region and security is going to take care of itself.

Now let us look at the implementation.

Implementation:

1. Let us first create the 3 users(one for each region) as shown in the following screenshot.


2. Let us now create groups for each of these 3 users.


3. Now lets assign each user to its corresponding group.


4. We will now create 3 Region Roles and assign corresponding groups to these roles.


5. We will then assign all 3 Region Roles to BIAuthor role so that all users belonging to Region Roles get all the privileges of BIAuthor. You might want to change this step based on your requirements.


6.  We will then create a role called OBIEE_DATA_SECURITY and assign all 3 Region Roles to it, just as we have assigned them to BIAuthor role.


7. The final role structure will look like the following.


8. Now create all 3 Region Roles and OBIEE_DATA_SECURITY role in the RPD in offline mode. Note that no members show up at this point in the role. However, when you deploy this RPD and then open it in the online mode, you will see the members appear under the roles. We will see this in a minute.


9. Your RPD should have all the 4 roles as shown below.


10. Now click on the Permissions tab on the OBIEE_DATA_SECURITY role as shown below.


11. Add the filter in on the Logical table containing the region column as shown below. Note that the filter is on the Logical table and not the presentation table. Also note that we are using a session variable called DATA_SECURITY_VARIABLE. We will create this variable in a short while from now.


12. Deploy this RPD after doing the consistency check. You should see the members appear under the roles.


13. Now, let us create the initialization block that will feed the DATA_SECURITY_VARIABLE. Note that we are creating a row-wise initialization variable here. Also note that we are using SAMP_CHANNELS_D table in the initialization block. This table should be the smallest table that you have dragged in your physical layer. Note that we are using the LOCATE function to find one of the 3 Region Roles in the roles of the user. ROLES is a system session variable and it captures the roles of the logged in user. Note that DATA_SECURITY_VARIABLE is getting the value as 'AMERICAS' or 'APAC' or 'EMEA'. This variable is then used in the filter that we defined in step 11.


14. Let me edit this data source to show you another key point to note. This initialization block uses OBIEE server to fire the query. This is because we are using the ROLES session variable that exists on the BI server. Since we are firing it on the BI server, the table in the from clause of the query should be there in the physical layer of the RPD and should be as small as possible so that performance of the initialization block is the best.


15. Now login using weblogic and create a report that has 2 columns. The 1st column is the column that contains the names of the regions (AMERICAS, APAC and EMEA) and the 2nd column is any measure. We can pick any column from the Logical table that has been used in step 11 instead of the column containing the names of the regions.


16. Run this report using weblogic. You will see that all 3 regions appear. This is because weblogic is not a part of any of the newly defined 4 application roles.


17. Let us look at the query using weblogic.

WITH 
SAWITH0 AS (select sum(T42433.Revenue) as c1,
     T42430.Region as c2
from 
     BISAMPLE.SAMP_CUSTOMERS_D T42428 /* D60 Customers */ ,
     BISAMPLE.SAMP_ADDRESSES_D T42430 /* D62 Customers Addresses */ ,
     BISAMPLE.SAMP_REVENUE_F T42433 /* F10 Billed Rev */ 
where  ( T42428.Cust_Key = T42433.Cust_Key and T42428.Address_Key = T42430.Address_Key ) 
group by T42430.Region)
select D1.c1 as c1, D1.c2 as c2, D1.c3 as c3 from ( select 0 as c1,
     D1.c2 as c2,
     D1.c1 as c3
from 
     SAWITH0 D1
order by c2 ) D1 where rownum <= 5000001

18. Now login using OBIEE_AMERICAS_USER. You will see only AMERICAS data. Similarly, you will see only APAC and EMEA data when you login using OBIEE_APAC_USER and OBIEE_EMEA_USER respectively. I am sharing the outputs and the queries of each of the 3 users.

WITH 
SAWITH0 AS (select sum(T42433.Revenue) as c1,
     T42430.Region as c2
from 
     BISAMPLE.SAMP_CUSTOMERS_D T42428 /* D60 Customers */ ,
     BISAMPLE.SAMP_ADDRESSES_D T42430 /* D62 Customers Addresses */ ,
     BISAMPLE.SAMP_REVENUE_F T42433 /* F10 Billed Rev */ 
where  ( T42428.Cust_Key = T42433.Cust_Key and T42428.Address_Key = T42430.Address_Key and T42430.Region = 'AMERICAS' ) 
group by T42430.Region)
select D1.c1 as c1, D1.c2 as c2, D1.c3 as c3 from ( select 0 as c1,
     D1.c2 as c2,
     D1.c1 as c3
from 
     SAWITH0 D1
order by c2 ) D1 where rownum <= 5000001

WITH 
SAWITH0 AS (select sum(T42433.Revenue) as c1,
     T42430.Region as c2
from 
     BISAMPLE.SAMP_CUSTOMERS_D T42428 /* D60 Customers */ ,
     BISAMPLE.SAMP_ADDRESSES_D T42430 /* D62 Customers Addresses */ ,
     BISAMPLE.SAMP_REVENUE_F T42433 /* F10 Billed Rev */ 
where  ( T42428.Cust_Key = T42433.Cust_Key and T42428.Address_Key = T42430.Address_Key and T42430.Region = 'APAC' ) 
group by T42430.Region)
select D1.c1 as c1, D1.c2 as c2, D1.c3 as c3 from ( select 0 as c1,
     D1.c2 as c2,
     D1.c1 as c3
from 
     SAWITH0 D1
order by c2 ) D1 where rownum <= 5000001

WITH 
SAWITH0 AS (select sum(T42433.Revenue) as c1,
     T42430.Region as c2
from 
     BISAMPLE.SAMP_CUSTOMERS_D T42428 /* D60 Customers */ ,
     BISAMPLE.SAMP_ADDRESSES_D T42430 /* D62 Customers Addresses */ ,
     BISAMPLE.SAMP_REVENUE_F T42433 /* F10 Billed Rev */ 
where  ( T42428.Cust_Key = T42433.Cust_Key and T42428.Address_Key = T42430.Address_Key and T42430.Region = 'EMEA' ) 
group by T42430.Region)
select D1.c1 as c1, D1.c2 as c2, D1.c3 as c3 from ( select 0 as c1,
     D1.c2 as c2,
     D1.c1 as c3
from 
     SAWITH0 D1
order by c2 ) D1 where rownum <= 5000001

19. Do not forget to note the roles of the users. All users should have BIAuthor role (because I want them to have BIAuthor privileges. You might have your own requirement here. BIAuthor appears in the screenshot below as a result of step 3,4 and 5). All users should have OBIEE Data Security Role. OBIEE Data Security Role appears in the below screenshot as a result of step 3,4 and 6. All users should have one of the 3 Region Roles. OBIEE EMEA Role appears in the below screenshot as a result of step 3 and 4.


20. Now, lets say that another user joins the AMERICAS region. All we have to do is to create his user id and assign the OBIEE_AMERICAS_GROUP to the user. The security will take care of itself. Let us now create the user and assign him to the group.



21. We can see the result in the analysis, the filter in the log and in the physical query in the following screenshots.

WITH 
SAWITH0 AS (select sum(T42433.Revenue) as c1,
     T42430.Region as c2
from 
     BISAMPLE.SAMP_CUSTOMERS_D T42428 /* D60 Customers */ ,
     BISAMPLE.SAMP_ADDRESSES_D T42430 /* D62 Customers Addresses */ ,
     BISAMPLE.SAMP_REVENUE_F T42433 /* F10 Billed Rev */ 
where  ( T42428.Cust_Key = T42433.Cust_Key and T42428.Address_Key = T42430.Address_Key and T42430.Region = 'AMERICAS' ) 
group by T42430.Region)
select D1.c1 as c1, D1.c2 as c2, D1.c3 as c3 from ( select 0 as c1,
     D1.c2 as c2,
     D1.c1 as c3
from 
     SAWITH0 D1
order by c2 ) D1 where rownum <= 5000001

22. Note that all roles, discussed in step 19, are automatically applied to the user just because the user is added to the group.

23. Now, lets say that OBI_AMERICAS_USER2 changes his region to APAC. All we have to do is to change his group to OBIEE_APAC_GROUP (as shown below) and the security will take care of itself.


24. The following screenshot show the change in the role being reflected in the user's My Account page because of the change in the group of the user.


25. We can see that the report now shows the data for APAC region for the same user. We can also see the data level filter applied in the logs and the change in the physical sql generated for the user in the following screenshots.

WITH 
SAWITH0 AS (select sum(T42433.Revenue) as c1,
     T42430.Region as c2
from 
     BISAMPLE.SAMP_CUSTOMERS_D T42428 /* D60 Customers */ ,
     BISAMPLE.SAMP_ADDRESSES_D T42430 /* D62 Customers Addresses */ ,
     BISAMPLE.SAMP_REVENUE_F T42433 /* F10 Billed Rev */ 
where  ( T42428.Cust_Key = T42433.Cust_Key and T42428.Address_Key = T42430.Address_Key and T42430.Region = 'APAC' ) 
group by T42430.Region)
select D1.c1 as c1, D1.c2 as c2, D1.c3 as c3 from ( select 0 as c1,
     D1.c2 as c2,
     D1.c1 as c3
from 
     SAWITH0 D1
order by c2 ) D1 where rownum <= 5000001

Object Level Security:
Roles have traditionally been used to grant access to specific subject areas. This process is described @ http://www.rittmanmead.com/2012/03/obiee-11g-security-week-subject-area-catalog-and-functional-area-security-2/.

Roles can also be used to grant exclusive and hand picked permissions in OBIEE. This process is described @ http://obiee-oracledb.blogspot.com/2014/08/privilege-to-see-hidden-objects-in.html.

Roles can also be used to grant access to a specific user list if the list of such users do not fall in any single group. The only difference in such a scenario and the one described above will be in step 4. Instead of assigning a group to a role, we will have to assign a user list to the role.

Till next time ..

Update 13-Mar-2016:

If you are using Oracle database, you may use Dual instead of using the smallest table in step 13. To use Dual, you will have to create a physical table called Dual in the physical layer. Add a column to it called "Dummy" and make its datatype varchar with the length as 1. Dual table has a column called Dummy with 'X' as it value. You can see this when you issue the following SQL.

Select * from Dual;

This will not only take away the dependency on the smallest table (SAMP_CHANNELS_D is this blog) but will also make the init block query more efficient.