Oracle BI EE - Data Level Security Implementation

Post date: 09-Apr-2010 06:45:46

Data Level Security Approach

The approach we implemented is based on (RW) Row-Wise initialized Nonsystem Session variables and a Logical Table Source (LTS) filter feature. We rely on the idea that by filtering a conforming dimension, we narrow down the information (records or rows) the user has effective visibility to.

We can set a “WHERE clause-like” condition in order to filter Logical Table Sources, in the Business Model and Mapping Layer of the Administration Tool. No user can override or alter this filter simply using Presentation Services (Dashboard and Answers). Of course, an Administrator user (not Presentation Services Administrator but a repository user belonging to the Administrators group) accessing the Admin Tool can alter or even delete all this.

Since every user may need visibility over a different set of records depending on some specified criteria, we decided to use a session variable as part of the filter statement.

Oracle BI EE provides a way to define variables using the Variable Manager. Each time a user begins a session, new session variables instances are created. Nonsystem session variables are similar to a Repository Dynamic variable in that both obtain their values from Initialization Blocks.

An initialization block executes a SQL statement assigning the result of the query to variables. By using Row-Wise initialized variables we can use the many rows retrieved with a query from the database, for instance a list of usernames, keys, organization names, etc.

NOTE: insert at least one row for each user in the table, if the Initialization block returns a record set of 0 rows, an error message is displayed in the dashboard of the user. A workaround on this is to use another init block defaulting the variable value, set the precedence order and then you will get rid of the error message. 

Variables and Initialization Blocks

The following SQL statement retrieves a list of organization names a user has been granted:

SELECT 'LIST_OF_SEGMENTS', SEGMENT_NAME

FROM DW_SEGMENT

WHERE DW_SEGMENT_ID IN

(SELECT SEGMENT_ID

FROM SEGMENT_ACCESS /*table containing Segment Ids and User Ids*/

WHERE USER_ID = LOWER(':USER'))

The session initialization block is executed each time a user logs in. LIST_OF_SEGMENTS is the name of the row-wise initialized nonsystem session variable. First column retrieved has to be the name of the nonsystem session variable if RW initialized. You must specify the variable getting the results of the SQL statement setting ‘Row-Wise initialization’ under Variable Target.

We use “:USER” to obtain the Name (not Full Name) of the user logging in.

NOTE 1 : using NQ_SESSION.USER or NQ_SESSION.”USER” generates an error.

NOTE 2 : use of LOWER() or UPPER() functions in the statement depends on the user ID stored lowercase or uppercase in the table.

BMM Layer Configuration

Every Logical Fact Table and Logical Dimension Table defines at least one source, usually an object (table or alias) from the Physical Layer.

By using the filter feature in the Content tab, all queries executed by the application containing a reference to tables Organization and Project include in the WHERE clause the filter we write.

You can type or use the Expression Builder to get the following filter:

MY_DW.””.SCH.”DIM – SEGMENTS”.SEGMENTS_NAME = VALUEOF(NQ_SESSION.”LIST_OF_SEGMENTS”)

Bold tagged text is just the field we use to match with the results of the variable.

Italic tagged text is transformed when then application writes the physical query into: IN (values of the variable).