Case study: How to implement Object level security and Data Level Security in OBIEE 11G - Part1

Hello Again.

Recently my team has implemented data level security and object level security in OBIEE 11G for a client who is selling their data to external customers. The requirement is very quintessential of any BI Project and the implementation process is trivial. Therefore, I have decided to detail the process, which I think can be very beneficial to any future security implementation.

A little bit of background here. This is a client that incorporates OBIEE into their software solutions to external customers. They are making their BI Platform available through the integration with other apps, which the external customers can subscribes to with a service fee and view the data pertaining to themselves specifically. Since most of the external customers are individual auto dealers and manufacturers, the sheer number of consumers is big and the confidentiality of their data is essential. The requirement pretty much comes down to the following:

1. Individual dealers can log on to the apps and view reports and dashboard for only that dealer

2. If an account is associated with 2 dealers, then it's called 'dealer set'. They can only view dealer set dashboards and reports that the content must be pertaining to that specific dealer set

3. An account can be associated with dealers as well as the manufacturers, such as Honda, BMW etc. Then this is a manufacturer account, so manufacturer dashboard and reports can be accessed for only the data of that manufacturer. 

4. At the parallel level of manufacturer are auto partners and auto dealer group accounts. There will be separate dashboards available for partner and dealer group, each is exclusive from the rest. Only the specific dealer group or partner data will be available for the specific account associated with it.

5. There are also users from within the company itself. They are typically sales people. They should be able to access all of the reports & dashboards and see data for all of the dealers, manufacturers, dealer groups at will.

This requirement can be simply translated to users & user group where users will be individual account and user group will be the specific scope of that user, such as dealer, dealer group, partners, manufacturer or company's internal users. So let's think about how to implement this requirement.  

First and foremost, before implementing data and object level security, we need to first decide the process of authenticating these users into OBIEE. For the most obvious reason, that most of the users will be be stored in LDAP server since they are external to the company, they will be managed by a table. Therefore, external table authentication becomes the choice. Now the first thing to do is designing the table structure that will accommodate all these different users and scopes.

Below is the table designed for storing all of the users and associated dealers and scopes:

I have created 6 users for six different scope:

Company user is flagged bu HAS_GLOBAL_APP_SCOPE = 1. That way, each company user will only occupy one row in the table and by default, we will not restrict such user from accessing any OBIEE report both at object level and data level. Their corresponding company and dealer name will be left blank because they have access to all by default.

Dealer Group users will have the company_type = 'dealer group' and dealer_name = all of the dealers under that dealer group (Colonial VW in my test case).

Partner and Manufacturer users will be similar to dealer group users, except the company_type will change accordingly. 

Dealer Set user will have 2 dealer, indicating it's a set. Their company_name will be populated with a dealer set name (Sorry i forgot to populate that from the above screenshot).

Dealer user will have only 1 record and the company_name will be left blank.

An overview of the user & user group hierarchy will look more or less like the following:

I have left out the internal company user from the diagram because they are unrestricted. Dealer is pretty much the same as user since 1 user for only 1 dealer association is considered 'dealer user' anyways.

Now having done that, we need to start planning the strategy of authenticating users again the information we have stored. 

In any case where you have user and associated groups with different accessing ability, the standard process of implementing external table authentication and then assigning authorizing roles can be broken down into 2 steps:

1. User will attend to logon to the application by passing their username and password that they type onto the screen. These 2 parameters should be passed onto the user table to see if this username/password combination exists or not in the table. If it does, it will return username and password as output and user will get authenticated so their logging in is successful. This step can be named 'authentication'.

2. The output of the above step will be username and password. These 2 values will then need to be associated with the corresponding scope or user group. This step, let's call it 'Authorization' or 'scoping' or whatever you want for your own memory.

Now in my other project, I have done the above 2 steps using different table structures from this. I had 3 tables, one for user only, one for user group only and one stores user & group relationships. But here, we are storing everything in just 1 table. The difference means that we will configure OBIEE differently.

So next time I will get into details of the things to be done at the DB level and RPD level for the actual implementation.

Stay tuned