Oracle BI 11g – Active Directory Security using Init Blocks & Variables – 10g Style

In the last 2 training days event that we ran in Atlanta and Bangalore, we showed a demo of how the BI EE 11g security works. One common question that we got out of both the events was how do we use 10g model security in 11g. Though the BI EE 10g model security is not recommended, there are quite a lot of companies who have built their process around this model for user provisioning etc. So, lets take a look at how we can use BI EE 11g to use the BI EE 10g model of security.
Before we go into BI EE 11g, lets do a quick recap on how the BI EE 10g security model works. Let's use the Active Directory as the underlying LDAP where users will be authenticated. In 10g for authenticating & authorizing against LDAP, we will be using 2 init blocks. They are

1. Authentication Init Block: In this init block, USER variable will be defined as the variable target and a connection to the Active Directory LDAP server will be defined as shown below.

2. Authorization Init Block: BI EE 10g, had an issue in extracting the LDAP groups directly. So for authorization there are a couple of alternative ways that are typically used

a. Using DBMS_LDAP pl/sql and extract the Active Directory groups dynamically using an init block. These groups will have to be manually defined in the repository as well.

b. Using an external database table to hold the user to group mapping. Then an init block will be used to extract the groups dynamically. Even in this case the Groups will have to be defined in the repository.

In both the cases above, the important point to note is the dynamic assignment of Group(s) to an User using Row-Wise initialization. An example is shown below showing the User to Group assignment table in 10g (and also the corresponding Init Blocks)

After doing this, if we now log into BI EE 10g as each of these users, we should be able to look at the USER and GROUP session variables. For Pierre Houdan it will be 2 groups assigned and for Charla Malinsky, it will be only one group as shown below.

This way 10g offered good flexibility of having dynamic USER to GROUP assignment. In BI EE 11g, since we have moved all the Users and Groups to Weblogic Default Provider, we cannot use dynamic init block based assignment for Users and Application Roles residing within Weblogic. There are other methods for doing dynamic user-group assignment within weblogic Default provider. But for now, lets consider a method that we have used before in the past i.e. 10g way of using Init Blocks.

In the article here, i had shown how we can replace the Default Provider with Active Directory within Weblogic. But even in that case, weblogic User-Group assignments are obtained directly from Active Directory. Also, the Group/User to Application Role Mapping will have to be done manually. To make it dynamic (from a database table) using init blocks, we cannot use that method. So, we will have to follow the 10g method of authentication/authorization.

To begin with, we need to ensure that we do not have overlapping users/groups in both the Weblogic Provider and Active Directory. After ensuring this, we start off with defining a server connection to Active Directory from within the BI EE 11g repository as shown below

We then define an initialization block to set the USER variable using the above defined LDAP server as shown below.

To make it similar to what we have in 10g, lets create a database table containing the User to Group (Application Role in 11g) mapping.

Create the 3 Groups(sfusers, sfmanagers & ManagementGroup) as Application Roles within BI EE 11g. The procedure for defining the Application Roles is given here.

After defining the application roles, restart the BI Server so that the changes can be picked up by the BI Server.

In our repository we have 2 subject areas, Sales - Fact Sales & Sales - Store Quality. Sales - Fact Sales can be used by users belonging to sfusers & sfmanagers application roles. Sales - Store Quality can be used by users belonging to ManagementGroup application role. So, lets define the security in the repository accordingly. Also, the Staff Dimension in the Sales - Fact Sales Subject area can be accessed only by users belonging to sfusers group.

Before starting to create the Initialization blocks for authorization, lets try to understand how application roles in 11g work. Lets define a user in Active Directory called ADUser. For now, this user does not belong to any application role. Also, no initialization blocks for authorization have been defined yet in the repository. If you go into the repository and look at the list of System Session Variables, there are 2 variables now in 11g that determine Authorization. They are ROLES and GROUP.

Remember, even though we have a variable called GROUP, this variable will actually affect the ROLES variable. ROLES variable cannot be updated directly through an initialization block. To understand further, lets try to login as the ADUser.

As you see, the user is not able to login. This is different from a user defined in Weblogic Default Authenticator. For external AD users coming through an Init Block, the GROUP variable should have a value. And the value should match with atleast one of the Application Roles. So, to test this out, lets add the ADUser to the sfusers Group in our authorization table. Also, lets define a new Authorization Init Block which will set the GROUP variable from this database table.

If we now login as the ADUser, we should now be able to login.

Though this user is able to login, this user still does not have the privilege to create new reports. To do that, lets add the ADUser to the BIAuthor role as well in the database table.

If we now log back in as the ADUser again, this user should have access to create reports.

To make it a bit more interesting, lets make the following changes to the permissions in the presentation layer. Lets provide access to sfusers & sfmanagers group to create reports. Also, lets provide them access to create reports on their respective subject areas as shown below

If we now login as Pierre.Houdan, we should be able to create report out of both the Subject Areas available.

Also, if we go into the Sales - Fact Sales subject area, the Dim Staff will not be visible to this user as only sfusers group have access to this.

If we login as Charla.Malinsky, we should be able to create report only out of the Sales - Fact Sales subject area.

As you see both the Presentation Services privileges and the RPD privileges get honored properly. Lets now create a report to look at the USER and ROLES session variable values. Following is what we would get.

As you see, the values that we pushed into the GROUP variable (sfusers, sfmanagers, ManagementGroup etc) are now available in the ROLES variable. Another important point to note is, whenever we setup the security in the above method in 11g, we would be getting the warnings in the Admin Console as shown below. The reason for this is Weblogic will try to authenticate these users first in its default authenticator before pushing it through the init blocks. So, its important to be aware of this overhead.