Dylan's BI Study Notes

My notes about Business Intelligence, Data Warehousing, OLAP, and Master Data Management

Data Security in Oracle BI Apps

Posted by Dylan Wan on July 15, 2008

I am describing how data security works in Oracle BI Apps.

The key concept is that Oracle BI Apps can read the permission/grant the security administrator defined in the OLTP during the runtime.  If you change/revoke the access in OLTP, the change can be immediately reflected in OBI Apps.  This is very important if your BI Apps users are the same sets of the users who are using the OLTP application.

How does it work during runtime?

When a user logins to Oracle BI Apps, regardless which user authentication method is used, as long as the user is sync-ed up with the OLTO users.  This is what happens:

During initializing the BI user session, OBIEE server connect to the OLTP database and issue the “Initi-block SQL” which can fetch the list of object instances that the user are allowed to access.

The list of objects are put into OBIEE cache and will be available throughout the session.

When a user access a particular data set (either selecting a dimension or a fact table column), a security predicate may be attached to the generated SQL.  The list of objects cached will be used in the predicate( a IN list or something similar) and only the data set the user can see will be available.

How does it happen during the design time?

1. During  the design time, the logical mapping between the OLTP tables/object and the OBIA logical tables or the presentation layer need to be analyzed.

For the pre-package apps, many of such has been done by Oracle.  However, if you have additional requirements which are either (1) additional data security enforced in OLTP; or (2) the data security defined in OLTP but not supported out of the box, you will need to perform the configuration during design time.

2. Once you map the entity, the security filter should be added manually into the OBIEE repository (RPD) file.  In BIEE Server admin tool, you can go to Manage > Security -> Group.  The filter will mimic the same security policy defined in OLTP.  For example, in Oracle EBS HR, the data are secured by business groups.  A user under the single business group access (SBGA) can only see the HR data within a business group.  The filter will be “Dim_Table.BUS_GRP_COL = <the business group>”

3. Another task you will need to do is to find out how to get the business group a user have access from the OLTP.  This is done via the OBIEE session variable.  If multiple values are possible, a “row wide init-block” will be used.  This can be done via (BIEE Admin  -> Manage -> Variables -> Session -> Initialization Block)


[1] Oracle BI Server Admin Guide (PDF): Chapter 13: Using Variables in Oracle BI Repository

[2] Oracle BI Server Admin Guide (PDF): Chapter 15: Security in BI

[3] Oracle BI Apps Install and Config Guide: Chapter 6: Integrated Security for Oracle BI Apps


One Response to “Data Security in Oracle BI Apps”

  1. justvicky1000 said

    Dear Dylan

    i have been asked to validate the user access to oracle BI. What are the steps that I need to take for checking the user access ?

    Are there any scripts that I can run and analyze in excel /access ?

    I am totally clueless on how it can be done..

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 )

Google+ photo

You are commenting using your Google+ 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 )

Connecting to %s