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

Last time, we have described the security requirements and the basic implementation strategy. Now let's take a look at the next step after the user table has been created.

One thing I forgot to mentioned from the last entry was that, the password has encryption. Therefore, a method of decoding encrypted password is needed. Having that into consideration, we have created a few DB functions.

1. Ret_User_Auth does the authentication process by passing user input logins and matches with the DB table to check for authentication.

2. Ret_User_Details takes the same logins passed from Ret_User_Auth and finds it's corresponding groups and scopes.
Below test case shows you the scope name (colonial VW) and group name (dealer group) of test user u1

Function Ret_User_Auth actually calls another function which does encryption. So typically, the encryption function is another object that is created for this implementation. I am not going to show you the details of these 3 functions, you can contact me privately if you want to see the sample.

Now that these are done, let's go to OBIEE's admin tool and create the following session variable initialization blocks:

CompanySecurity: executes Ret_User_Auth
GROUPS: executes: Ret_User_Detail

Below are the contents of these 2 initialization blocks:

Group initialization block returns 4 variables: Username, Groups, Scope_names and Dealer_Names. It also has precedence of CompanySecurity initialization block, this means that CompanySecurity initialization block will always be executed before.

Scope_name will return you the name of manufacturer or dealer group or partner that this user is scoped to. So for the above example of user u1, if he logs in to OBIEE, the value of scope_name variable will be 'colonial VW'. This is why session variable is used for user authentication, not dynamic variable, because when u1 logs in, the scope_name variable will be populated as 'colonial VW' just for this user session, when a different user logs in, he will have a different session which will populate scope_name with a different value that's associated to that user in the table. Dynamic variable doesn't do that. It doesn't differ based on sessions.

Anyways, enough said. Now that both initialization blocks and variables are created using these 2 functions, let's do the next step, which is important for achieving data level security. We need to apply data level filters for each user groups, which in terms of Admin Tool, it's the application roles.

So look at the below screenshot:

We have 6 application roles corresponding to 6 different user groups & scopes.

They obviously have been created through weblogic, just and FYI:

Now for each application roles, depending on their scoping requirement, we need to set the filter for it's permission specifically.

For manufacturer group, it needs to be applied to all of the columns under manufacture dimension where the manufacturer name = session variable 'scope_name'
Remember, scope_name in my case returns the actual manufacturer, partner, dealer group name that the report should filter on.

For Dealer group scope, it is similar setting on dealer group dimension:

For dealer user, since the requirement is that they can access both manufacturer and dealer dashboard filtered for the specific dealer, they will have 2 filter conditions applied, each one is for manufacturer and dealer dimension separately.

Now for the internal users, they are called 'staff' by the application role. Since they should have access to all of the contents everywhere, their condition is simply left blank:

Now you should have a basic idea of how our data model is like. It's a pretty straightforward star-schema implementation where different user scopes are separated by it's own dimension

So the idea is that, when a user logs in, the session variable scope_name will be initialized with a specific value, it could be 'colonial VW' if this is a dealer group user, it would be 'General Motors' if this is a manufacturer user, it could be blank if it's an internal user. So for each dimension, this filter is applied at the rpd level so that when this user access to a dashboard (by having the right object level security to begin with), he can only view reports where the manufacturer name (or dealer group name or partner) = 'general motor' (or 'colonial VW' or a partner scope name). If this user is internal user, then scope_name is blank, he will see everything unrestricted.

Now the last step will be to apply object level security and to apply these session variables at the proper place on answers and dashboards,which we will cover in the next part.

Stay tuned