OBIEE 11.1.1.7 + Essbase : Essbase Filters within Fusion Middleware 11g Security

Data and metadata filters are used with Essbase to limit access to data in a cube. When you use data filters to restrict access to a certain set of dimension members in the cube, the end-user can see that the member exists, but can't see any data associated with it. Metadata filters go one step further, stopping the end-user even seeing the dimension member if they've not got permission to view it. In the past, Essbase filters were defined using MaxL or Essbase Administration Services, and then assigned to individual users or groups using Hyperion Shared Services; however, if you install Essbase as part of OBIEE 11.1.1.7, these filters are assigned to users and groups using Fusion Middleware Security's application roles and policies. So how does this process work?

There are three main steps required to set up an Essbase filter under this new Fusion Middleware security model:

1. Identify the user or group to which the filter is to be applied (or, as in the case of this note, create a new FMW user and add to an existing default group)
2. Create an Essbase Filter, typically using Essbase Administration Services
3. Set up the required policies within Fusion Middleware security, using either WLST scripting or more usually, Oracle Enterprise Manager Fusion Middleware Control. 

Creating a new Embedded LDAP Server User

To start by creating a new user, login to Weblogic Administration Console (eg http://localhost:7001/console/login/LoginForm.jsp) , and click on Domain Structure > Security Realms, like this:

On the subsequent screen, click on the Users and Groups tab.  Then, click on New to add a user.  On the resultant form, enter a user name (eg markcann) and a password.  Confirm the password, and then click OK to save those details.

Back in the main users screen, click on the newly-added user, like this, so that we can then assign it to a group.

Then, in the resultant screen, click on the Groups tab, then select just the BIConsumers group, and press Save. This user is now part of the basic group that can access OBIEE, and will be able to run reports and other catalog content.

Create the Essbase FIlter

Now we're going to create an Essbase filter to apply to this user. In this example, the Essbase test database EXA102_2.EXA102_2 contains the following dimensions:

The filter to be created needs to hide the bottom levels of the Product Hierarchy from view: For example PT1 within PC12 contains PI3, PI2, etc as lowest level values.  All such leaf nodes need to be hidden from user view for this dimension.

Although filters can be written / uploaded using MAXL, even though we are using Fusion MIddleware security with Essbase, they can still be created in Essbase Administration Services (EAS).   Once logged into EAS, navigate through the Enterprise View tree to locate the required database, then right-click on the database, and choose Edit > Filters.

In the Filter Editor screen, click New to create a new filter.  Assign the new filter a name (for example, "DropLeaves"), and add a MetaRead filter, the most restrictive of the two filter types.

@REMOVE(@IDESCENDANTS("Product"),@LEVMBRS("Product",0))

Click Verify to check the content of the filter code, then click Save to return to the main filter screen:

Then finally, click Close.  The filter is now created and ready for assignment.

Assigning the Filter using Application Policy Permission Objects

According to the OBIEE 11.1.1.7 Systems Administrator Guide's section on Configuring Data-Level Security using Essbase Filters:

"An application role requires at least two policy store permission grants to access to a specific filter. You must give an application role permission to use filters within a specific scope eg

• oracle.essbase.application, /EssbaseCluster-1/Demo, use_filter

and

• oracle.essbase.filter, /EssbaseCluster-1/Demo/Basic/read_filter, apply"

This is true - an application role does indeed require at least two policy store permission grants to access to a specific filter. It requires a whole lot more to work if starting from scratch though.

It is important to recognise that the default groups set up by the install contain a lot of policies by default and they are nested and hence inherit policies from their 'subordinate' members. The three groups allocated and set up for the BI elements of FMW are set up thus:

• BIAdministrators
• BIAuthors
• BIConsumers

By adding our new user, markcann, to the BIConsumers group, it automatically assumes all of the rights of that group.  We can however overlay additional policies just to the markcann user that will not affect the other members of the group.  It is here that the two additional policies to effect the filter - ie to this user - need to be added.

To do this, login now to Enterprise Manager Fusion Middleware Control, and in the left hand pane navigate to Business Intelligence > Core Application and left-click on it.  Once the right-hand pane has refreshed, click on the dropdown next to Business Intelligence, then select Security / Application Policies, like this:

In the resultant screen, select obi as the Application Stripe (if not already defaulted to this value), select User as the Principal Type, and then press the Create button.

 In the resultant screen, click the top Add:

Change the Type field value to User, then locate the new user (click on the blue arrow on the right hand side of the form after having entered the first letter in the search box if required).  Highlight the user, then click OK.

Back in the main screen, the selected user will now be showing.  Click the bottom Add to start adding the required policy permission.

In the resultant dialog, click the Resource Types radio button.  Choose oracle.essbase.application as the Resource Type, then click Continue.

Type in the cluster / app name (/EssbaseCluster-1/EXA102_2) as the resource.  Check only the use_filter permission action, then press the Select button.

Back in the main screen, the newly added application policy permission should now be showing. Click the bottom Add button again to add a second policy permission, following these steps:

1. Check the 'Resource Types' radio button
2. Choose oracle.essbase.filter as the Resource Type
3. Click 'Continue' to access the second screen

Enter the Cluster / App / DB / Filter (eg /EssbaseCluster-1/EXA102_2/EXA102_2/DropLeaves) as the Resource Name. Check the apply check box, then press Select to return to the main application policy screen for the user.

Back in the main screen, with the two application policy permissions added now showing, click on OK.

The previously created filter is now effective for the markcann user.

Testing the New Filter

To test the filter, I now log into Smartview, initially as the main administrator user 'weblogic'. To test access without the filter applied, I access the relevant database (in this case, EXA102_2.EXA102_2), place the Product dimension in the 'row' position, and perform a Zoom In / All Levels operation.  Note that level 0 values of product ARE visible:

Now I re-connect to the database via Smartview using the new markcann user and repeat the orientation / Zoom actions.  Note that the level 0 values are no longer visible:

When actually drilling (zooming) from Smartview, double-clicking on (e.g.) Bread Clubs results in no change to the report, exactly as if it was the bottom level of the hierarchy.

Obviously in a real system you would not look to set up access like this on a user by user basis.  One approach may be to add access-based groups (UserGroup1, UserGroup2, etc) to the BIConsumers (or indeed, BIAuthors) group, assign the relevant filter (or filters....Essbase under FMW allows recognises multiple filters for a user, which is new) to those groups.  Provisioning (access to functions) would then be controlled at 'Standard Group level, and Scoping (preventing access to data) controlled by the Custom Groups.

So - a bit of a different process than we're used to with Shared Services, but it has the advantage of using the same users, groups, application roles and application policies as the rest of OBIEE. Remember also to ensure that any connection from OBIEE's repository to the Essbase database uses the actual users' credentials (i.e. :USER and :PASSWORD, or use the CSS Token feature) otherwise the filters won't get applied to the users' analyses and reports.