Implementing Row-Level-Security in Oracle BI EE

Sometimes there is a need to restrict data access to certain groups of users. Oracle provides a mechanism called Row-Level-Security. You could achieve similar functionality from within Oracle BI EE.

Picture this; You have a table of Sales Managers which are responsible for a certain region. Each Sales Manger may only see the data for his / her region.

First you have to know which user has logged on and to which region this user belongs. Therefore you should use Session variables. To set this up properly you could refer to the documentation. You validate the logged on user to a table of Sales Managers. This way you could also select the region a Sales Manager is responsible for. The principle of this solution is that you have the Sales Manager and their regions in a table which you can select from. Let’s say we now have a ‘REGION’ session variable.

We can go on to the Security Groups. Create a new Security Group called; ‘Sales Managers’. Assign all the Sales Managers (Repository Users) to this newly created group.

The final step is to set Business Model Filters on this group. The concept of these filters is thatb you add all Logical Tables to this group, which you want to restrict on a Sales Managers’ region. You could achieve this by following the next steps;

  1. Open the ‘Sales Manager’-Security Group,
  2. Click on; ‘Permissions’,
  3. Click on the Tab; ‘Filters’,
  4. Click; ‘Add’,
  5. Select the table you want to restrict, eg.; “Sales”.”Dim Region”.”Region Name”,
  6. Use the Expression Builder to create the actual filter; “Sales”.”Dim Region”.”Region Name” = VALUEOF(NQ_SESSION.”REGION”).

Now when you use the “Sales”.”Dim Region”-table in an Oracle BI Answers query, the Business Model Filter will be applied. This filter only applies to this Security Group. User which do not belong to this group will see all the regions.

Similar functionality is used when implementing Oracle BI Apps Security.

Author: Daan Bakboord

I am an Oracle Big Data Analytics Consultant with great interest in anything closely related to the Oracle Big Data Analytics (OBIEE, BICS, OAC, Big Data, Data Integration, Data Visualization, Data Management, Data Architecture).

One thought on “Implementing Row-Level-Security in Oracle BI EE”

  1. “First you have to know which user has logged on and to which region this user belongs.”

    Hi, is there a way I an capture this information with internal authentication? So by default I know the “Username” and “password” and now I need a third entry which is the region or list of region this user has access to.

    Cheers.

    Like

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s