Implementing Reports-To data-level security in Oracle BI (OBIEE)

In a previous post, Implementing data-level security in Oracle BI (OBIEE), I described data-level security and how to implement it in Oracle Business Intelligence (OBIEE).  In this post I will describe a special type of data-level security, called Reports-To security, and how to implement it in OBI.

For Reports-To data-level security, we want to secure data in such a way that we allow a user access only to data for his/her direct and indirect reports. In other words, each user will be able to see data only for people that are below him/her in the organization hierarchical chain.

Take a look at this example diagram:


If Reports-To security is applied to this example, Position# 303 would only be able to see information for Position# 409; and Position# 305 would only be able to see information for Position#’s 410, 411, 412; and a final example, Position# 201 would be able to see the information for Position#’s 303, 304, 305, 306, and 409, 410, 411, 412.

I use “Position” as the driving entity in the hierarchy instead of “Employee” because there are times when a position is vacant (no employee) and so it’s better to use the position which will always have a value.  However, you can use Employee if that works better in your scenario or if that’s what your data supports.

Let’s move on to how to implement this type of security.  The steps are similar to the steps in a previous post, Implementing data-level security in Oracle BI (OBIEE), but with some key differences.  (Refer to that post for some of the more detailed steps not reiterated in this post.)

First, build a Reports-To data table and create the necessary ETL to ensure that it remains correct and up-to-date.  This table will contain each position (employee/user) and what position (employee) they report to. The data for this table will likely come from your HR system (such as PeopleSoft, Oracle EBS, SAP, Workday, home-grown system, etc.) that contains all the position and employee data.  Using the Organization Position Hierarchy diagram example, the table (REPORTS_TO_DATA) may look something like this:


Next, create a Session Initialization Block (Init Block) with row-wise Initialization that will be used to get the list of all positions that report to the position of the current user and store them in a defined Target Variable.  If you log in, the Init Block will generate the list with all the positions (or employees) that report to you; and when Jane logs in, the Init Block will generate the list of all the positions (or employees) that report to her.

An important component of the SQL in the Init Block is that it needs to be recursive, because for each person, it needs to retrieve their direct reports, and then retrieve the people reporting to their direct reports, and so on down the line.  Using the above Organization Position Hierarchy diagram example, when the user in Position 202 logs in, the SQL needs to retrieve the positions reporting to 202 (which are 307 & 308), and then recursively retrieve the positions reporting to 307 and 308, and so on. The Target Variable used for storing the values in this example is: REPORTS_TO_POSITIONS

The Init Block, its SQL, and variable definition may look something like this:



Then finally, we need to create the data filters on the appropriate data sets (that need to be secured) using the variable containing the “list of positions” reporting to the current user (REPORT_TO_POSITIONS variable).  The needs to be done for each role that will access the reports that need to be secured by Reports-To security.


After this is all set, then Reports-To Security will be in effect for the filtered data sets and the reports that use them.

If you need to make it such that each user can only see data for his or her direct reports, the SQL can be modified to remove the recursion, and just return the direct report positions.

One final point … as you would with all changes, but particularly with solutions involving sensitive data, test your solution thoroughly – including making sure to perform both positive and negative testing.

Thanks for reading!



Implementing data-level security in Oracle BI (OBIEE)

Data Level Security involves securing the data available in an application in such a way that each user will see only the data that he/she is authorized to see, resulting in each user possibly seeing different results on the same report.   In this post I will describe how to implement data-level security in Oracle Business Intelligence (OBIEE).

Let’s use an example to describe data-level security.  Each user of the BI system works in or is assigned to a particular Business Unit.  Each user is allowed to see only the data for his or her assigned Business Unit.

In our example, the below table lists the 4 users and the Business Unit that each of them works in or is assigned to, and therefore, should have access to.  We will call this the USER_TO_BUSINESSUNIT table.

Jane and Xing should only be able to see data for Business Unit BU2000, Bill should be able to access data for both BU3000 and BU4000, and Venkat should be able to access data for BU4000.

Now, we will use the below table as the example data set that we need to secure with the Business Unit data-level security.  We will call this table TRANSACTION_DATA.

When data-level security is applied …

Jane and Xing will be able to access/see the following data:

Bill will able to access/see the following data:

And Venkat will be able to access/see the following data:

So, now let’s move on to how to implement data-level security in OBI to achieve what was described above.

First, ensure that the USER_TO_BUSINESSUNIT table data is correct and up-to-date, and that there is an ETL in place or some other method of keeping that data updated. You want to ensure that if and when a user’s Business Unit changes, it is reflected in this table so that the user will have access to the appropriate data.

Next, create a Session Initialization Block with row-wise Initialization that will be used to get the list of Business Units that a user has access to.

Open the RPD -> Manage -> Variables

In the Variable Manager -> Action -> New -> Session -> Initialization Block

This needs to be a “Session” Init block so that it will run each time a user logs in, and gets that user’s list of Business Units; and it needs to be row-wise because some users will have more than 1 value returned.


In the Session Variable Initialization Block Dialog, enter a Name for the Init Block.

Then click Edit Data Source

In the Data Source dialog, enter the SQL to get the Business Units for the current logged in user.  Click OK when done which closes this window and brings you back to the Session Variable Initialization Block Dialog.


Click Edit Data Target in the Session Variable Initialization Block Dialog.

Enter your Variable name and check “Row-wise initialization”. As mentioned above, we need to select row-wise because our Init Block SQL may return more than 1 value for some users.   For example, when Bill in our example above data logs in, the Initialization Block will return values BU3000 and BU4000, and store them in the Target Variable, “BUSINESS_UNIT”.

You may also check “Use caching” to store the values in cache. Click OK when done.

Then click OK to save the Init Block.


Next, apply data filter(s) to the appropriate data set(s) for the appropriate role(s) using the Target Variable above.  You may have role(s) specifically used for data-level security and will need to apply it there, but if not, you will need to apply the filters in each role that has access to the datasets/dashboards/reports that you want to apply data-level security to.

Manage -> Identity

Go to the Application Roles tab, and select the Application Role to which you would like to apply the data-level security.  In the APplication Role dialog, click Permissions.

In the Permissions dialog, select the layer and data table that you want to apply the data security to, and then enter the appropriate filter.  In this example, you are filtering by BUSINESS_UNIT.  This will cause the data to be filtered to only include each users’ Business Units.

Save your changes.  You have now applied data-level security.  This is what will happen now:

User logs in -> Init Block runs and selects the Business Units associated with the user’s User ID -> Init Block assigns value(s) to the variable BUSINESS_UNIT -> if the user is a member of a role that has data security applied to -and- the user visits the report -> the data filter will be triggered/run -> User only sees data for the Business Units the user is allowed to see.

Look out for my upcoming post on implementing a special type of data-level security: Reports-To Data Level Security.

Thanks for reading!

Disallow online RPD updates in OBIEE

You may want to disable online updates on your OBIEE RPD for performance reasons or because you have a specific development process that prohibits online updates.

To disallow online RPD updates, do the following:
Log into Enterprise Manager. Navigate the tree menu to Business Intelligence -> coreapplication.  Click tabs “Capacity Management”, and “Performance”.

Under the RPD Updates section, check the box for “Disallow RPD updates”.


This will prevent online RPD updates for all.

If you want to allow a select group of people to have access to perform online updates, such as a lead developer or administrator, then don’t do the above, but instead provide Administrator role to those that should have the access, and remove it from those that should not (and give them BI Author role for example instead).


WebLogic startup failure – BackendRoot cannot cast to BackendStandard

My colleague from a previous company contacted me recently to help with a problem. OBIEE was not starting up.  They had a power failure the night before, and then OBIEE would not start up.  The system is OBIEE 11g on Linux.

This is the error that was generated when trying to start the WebLogic Admin Server…


<Mar 28, 2014 9:11:35 AM EDT> <Critical> <WebLogicServer> <BEA-000362> <Server failed. Reason: There are 1 nested errors: java.lang.ClassCastException: com.octetstring.vde.backend.BackendRoot cannot be cast to com.octetstring.vde.backend.standard.BackendStandard         at weblogic.ldap.EmbeddedLDAP.start(         at         at         at > < Mar 28, 2014 9:11:35 AM EDT> <Notice> <WebLogicServer> <BEA-000365> <Server state changed to FAILED> < Mar 28, 2014 9:11:35 AM EDT> <Error> <WebLogicServer> <BEA-000383> <A critical service failed. The server will shut itself down> < Mar 28, 2014 9:11:35 AM EDT> <Notice> <WebLogicServer> <BEA-000365> <Server state changed to FORCE_SHUTTING_DOWN>


After trying a few things that did not resolve the issue, an online search helped with the solution. This post was very helpful:

After reading through the post, we went to the below directory on the OBIEE server (Linux) and examined its contents:


[oracle@[SERVERNAME]]$ cd /u01/product/middleware/user_projects/domains/bifoundation_domain/servers/AdminServer/data/ldap/ldapfiles [oracle@aeledwpbi ldapfiles]$ ls -l total 11308

-rw-r—– 1 oracle oinstall 10071624 Mar 27 08:40
-rw-r—– 1 oracle oinstall    56940 Mar 27 08:40 changelog.index
-rw-r—– 1 oracle oinstall   804359 Mar 27 08:40
-rw-r—– 1 oracle oinstall     2028 Jun 25  2013 EmbeddedLDAP.delete
-rw-r—– 1 oracle oinstall     3576 Jun 25  2013 EmbeddedLDAP.index
-rw-r—– 1 oracle oinstall        0 Mar 28 12:36 EmbeddedLDAP.lok
-rw-r—– 1 root   root       615242 Mar 27 08:40 EmbeddedLDAP.tran
-rw-r—– 1 oracle oinstall        8 Mar 27 08:40 EmbeddedLDAP.trpos
-rw-r—– 1 oracle oinstall        8 Mar 27 08:40 EmbeddedLDAP.twpos

Note how one of the files (EmbeddedLDAP.tran) is owned by “root”. It seems the power outage caused something unusual to happen resulting in “root” being assigned ownership of the file.

After having the system administrator change the owner from “root” to “oracle” (the OBIEE admin user), we were able to start the OBIEE system back up.

It’s all about the users – Identifying Users for your OBIEE application

One of the first things you will need to do before developing your Oracle Business Intelligence (OBIEE) application is … identify who will use it.  You need to identify who will be using the application – what business areas they belong to, what groups they belong to, what are the various functions or roles within those groups, and eventually, who are the actual people.  After identifying the various roles (groups of users typically associated with a business process or function), then you can identify their needs.  Starting any development before knowing who will be using the system could result in a lot of wasted time and effort or a sub-optimal system.  The grouping of information on dashboards, the available functionality and security will be driven by these roles and their respective needs.

After identifying the various functions or roles that users posses, then it is important to understand how each role performs their job functions.  You need to understand what information they need and in what order, how it’s used, and the level of detail required at various stages. With this information, you will determine the dashboards, dashboard pages and their order, the information on each dashboard page and its precedence and level of detail, and what detailed information is needed via drill down. Basically, you will be creating the analytic workflows for the identified roles and the various processes, functions and tasks that they perform.

When performing the above exercise, please be as discrete as possible.  For example, even if someone doubles as an AP/AR Analyst, you should still analyze and plan for 2 separate roles – AP Analyst and AR Analyst – because those are 2 separate functions.  Later, the individual or group can be granted permissions to both roles.  From a security standpoint in general, you will create the necessary OBIEE application roles to support your business roles.  And then assign security based on these roles.

In general, always keep the focus on the users, what they need to accomplish, and the most efficient ways to help them perform their job.  When you build the OBI system to meet those needs and usage scenarios, it will result in higher and faster user adoption.  This will take time, so do not rush the process.  Get detailed information about all the steps in their workflow upfront, document it, and then build around it.  However, on the other hand, you do not have to document to perfection upfront, you can take a more agile approach of developing based on fairly good user profiles to give users working prototypes, and then adjusting as new information and feedback is received from the users.

Good luck identifying your users and their needs as you get your OBIEE project rolling.  And remember, it’s all about the users!

Creating a new Security Realm in OBIEE 11g

When setting up security in OBIEE 11g, you may modify the default security realm (myrealm) that installs with OBIEE.   But even better, you may create a new security realm and leave the default realm untouched.

My preference is to leave the default realm untouched and create a new realm – this is a best practice in my opinion.  I think it is helpful to always be able to go back and look at the features and settings of the default security realm.  And you can name your new security realm more appropriately, such as, ABCIncSecurityRealm.

The link below (Paul Cannon’s Blog) brings you to a blog post about configuring OBIEE to use LDAP authentication, and the first part of the post covers creating the new security realm.  It is very detailed. I used it the first time I created a new security realm.

Good luck creating your new security realm.

Direct Links to various OBIEE User and Administration Presentation functions

These are some direct links to various OBIEE User and Administration functions.  These can be useful to know for efficiency and at times can also be useful for debugging security.

To go directly to “Home Page”

To go directly to “Dashboard”

To go directly to “Manage Privileges”

To go directly to “Manage Catalog Groups”

To go directly to “Manage Sessions”

To go directly to “Manage Agent Sessions”

To go directly to “Issue SQL”

To go directly to “Manage Map Data”

To go directly to “Manage BI Publisher”