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

Last part we have gone through the DB changes and RPD configuration for implementing data level and object level securities. Now there are some final pieces that needs to be done in order to make this implementation perfect. Let's start with object level security. We need to set them up so that different user group will only have access to their dashboards first.

So here are the different folders that stores dashboards and reports meant for different users.

As you can see, I already know what kind of access each folder should have as shown below:

I will show you what I did for manufacturer and dealer group folders. Each will be assigned to the proper application roles with the right level of privileges:

Same idea for partner and dealer folders, which I am going to skip now. For internal user, the below is the configuration:

There are other dashboard objects that allows permission setting, this will further controls object level access based on user groups. I am going to skip all these steps now.

For testing, let's see how things are working when these different users log in:

1. Manufacturer user logs in:
Object level Security working:

Data level security working:

2. Dealer Group user logs in:

Object level security working:

Data level security working:

3. Other user groups are working the same way, I am gonna skip posting the screenshots. 

Let's look at company internal user logging in:

For object level security, this user is unrestricted so all dashboards are accessible:

For data level security, this user is unrestricted, so he can see all data:

Thanks 

Until next time

Read More

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

Read More

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

Read More

Basic Understanding of OBIEE Report Vs BI Publisher Report

Hello All

I recently came across a lot of beginners asking me questions about the difference between BI Publisher and OBIEE reports. To them, BI Publisher is either a 'different Feature' within OBIEE, or it's a completely different Application who competes with OBIEE in 'reporting business'. So today, i am hoping to make it easier to understand the difference between the two.

BI Publisher was originally a different application, which is now part of OBIEE. The best way to put it is, BI Publisher is another technology that has been integrated with OBIEE, so now the two are both parts of Oracle Business Intelligence.

Now let's understand what are each for:

OBIEE analysis provides dynamic and interactive ad-hoc reporting capabilities that allows users to dynamically change queries on the dashboard as well as interacting with other objections on the dashboard through action links. These are the original features of OBIEE, these capabilities can date back to nquire or Siebel Analytics time. The idea was to create a reporting platform and have users stay there, like the dashboards.

However, certain business requires very specific type of reports that are highly standardized across the industry that has to be pixel perfect in very specific ways. These can be financial statement, invoice statements, certain transactional statements in Auto industry and so on. These reports are typically generated by the department and have to be eventually mailed out to external customers, therefore the look and feel of these reports will have to pixel perfect, meaning that the space between each line has to be specific, the colors, the fond and layouts among all the fields have to be arranged in specific ways. Moreover, when you print these reports out, it should ideally fit in to one page (in most cases). All these requirements can't be achieved easily using OBIEE's native dashboard features. Therefore, Oracle has decided to integrate BI Publisher into OBIEE platform and making it additional reporting layer on top of dashboard.

BI Publisher allows you to define your data model using existing OBIEE analysis (or others) as data set where reports draw data from onto it's templates. The template provides features similar to words document that allows you the design your own pixel perfect layouts like the below as an example:

The above report will be printed out on 1 page, pixel perfect. However, it does not have the ability to drill down, interact with other elements on OBIEE dashboard. This is something OBIEE can do but not PI Publisher.

Now that Oracle owns both technology, both are available in the OBIEE product that satisfies different needs. Now in the latest version of OBIEE 11g patch, Oracle has also introduced a Mobile App, which allows you to create custom Mobile App displays of OBIEE reports, which will work for all smart phone and browser independent. This will be additional layer on top of BI Publisher within OBIEE.

Like I said, there are different needs and therefore there will be different technologies. The important thing is to understand what different technologies are for in order to make better distinction among them

Thanks

Until next time

Read More

Configure change password for users in OBIEE 11G

Hello

In all OBIEE products, it is not so simple for users to change passwords themselves. The user accounts are typically managed by the Admin. This poses some realistic problems. Lets say I have a training environment set up and I have to make it available to a list of internal users for their daily activities. What do I do? I go create these users in Weblogic and access passwords to them. Do I send out one email to all of them with their username and password, since the password will be all the same?  Inevitably, the users are going request to update their passwords. If you have a list of 20 users, it's almost impossible to create passwords for each, it's the best to let them update passwords themselves.

Unfortunately, OBIEE doesn't have any out-of-the-box features for it. However, the good news is that, this can be done and has been done.

This article right here is excellent. It is very detailed, easy-to-follow article about how to make password change features available for users. I followed the steps without thinking, and it worked for me, I am sure it will work for you too. Check it out.

Thanks,

Until next time.

PS:

This implementation is for cases where users are managed and authenticated through weblogic. If you are using external table for authentication, then its different.

Typically, when external table is used for authentication, there is usually an integration of OBIEE with home-grown applications. This happens a lot for projects that use OBIEE as a part of product that the company is developing, rather than an BI Platform for internal usages. In this case, there is typically a SSO implementation where users can authenticated to OBIEE after they log on to other apps. A lot of password changing features would be typically implemented there and OBIEE just authenticate using updated user information.

Read More