Security for hierarchical dimensions

Using IndexCol Function to Handle hierarchy Level security

IndexCol can use external information to return the appropriate column for the logged-in user to see. The Oracle BI Server handles this function in the following ways:

• ODBC Procedures. NQSGetLevelDrillability and NQSGenerateDrillDownQuery return the context-specific drill-down information based on the expression translated from IndexCol. This applies to both IndexCol expressions specified in the logical SQL query and IndexCol expressions specified in a derived logical column.
• Query Log and cache. The logical SQL with IndexCol function appears in the SQL string in the query log. But the logical request will not show the IndexCol function because Oracle BI Server will translate IndexCol to one of the expressions in its expression list in the logical request generator.

  NOTE: The query cache will use the resulting translated expression for cache hit detection.

• Usage Tracking. Usage tracking will insert the logical SQL query string with the IndexCol function.
• Security. As long as the user has the privileges to access the column(s) in the expression translated from IndexCol, then the query will execute.

  When the first argument to IndexCol is a session variable and if a default expression is expected to be returned even if the init block fails, then the Oracle BI Administrator should set a default value for the session variable. Otherwise, the query will fail because the session variable has no value definition.

Syntax

IndexCol( INTEGER literal, expr1, expr2, ... )

The IndexCol function takes in an integer literal value as its first argument, followed by a variable length expression list and translates to a single expression from the expression list. The literal value is the 0-based index of the expression in the expression list to translate to.

Consider the following expression:

IndexCol( INTEGER literal, expr1, expr2, ... )

If the literal value is 0, the above expression is the same as expr1. If the literal value is 1, then the value is the same as expr2, and so on.

NOTE: The primary use case for IndexCol is for the first argument to contain a session variable. Specifying a constant literal would result in IndexCol always choosing the same expression.

Example With Hierarchy Levels

Company ABC has a geography dimension with the hierarchy Country of State, City. The CEO can access the Country level down to the City level, and the sales manager can access the State and City levels, and the sales people can only access the City level. Create the table

CREATE TABLE HIERARCHY_LEVEL
(
  USER_NAME  VARCHAR2(30 BYTE),
  TITLE  VARCHAR2(30 BYTE),
  GEO_LEVEL  NUMBER(1)   DEFAULT 5
)

GEO LEVEL on Oracle must be NUMBER(1) as data type to become a INT in OBIEE. Samples values are given below.

Table Example With Hierarchy Level

USER_NAME	TITLE	GEO LEVEL
Bill	CEO	0
Bruce	VP	1
Mike	Manager	2
Kale	Manager	2

The following steps illustrate one way to create a single query where each user sees the top level to which they have access:

Create the OBIEE user

Create the OBIEE users with the same user names.

The Oracle BI Administrator creates a new session variable GEOGRAPHY_LEVEL that is populated by the initialization block: SELECT GEO_LEVEL from T where USER_NAME = ':USER'.

Create the session variable

SELECT GEO_LEVEL FROM HIERARCHY_LEVEL WHERE USER_NAME = ':USER'

This assume that the Oracle BI Server instance has the same user names.

Create a column formula

IndexCol( VALUEOF( NQ_SESSION.GEOGRAPHY_LEVEL ), Country, State, City )

With the amount sold.

Using SELECT IndexCol( VALUEOF( NQ_SESSION.GEOGRAPHY_LEVEL ), Country, State, City ), Revenue from Sales, the following occurs:

• Bill logs in and IndexCol translates to the Country column because the GEOGRAPHY_LEVEL session variable is 0. He will get the same result and be able to drill down on Country to State as if he had used SELECT Country, Revenue from Sale.
• Bruce logs in and IndexCol translates to the State column because the GEOGRAPHY_LEVEL session variable for Jackson is 1. He will get the same result and be able to drill down on State to City as if he had used SELECT State, Revenue from Sales.
• Mike logs in and IndexCol translates to the City column because the GEOGRAPHY_LEVEL session variable for Mike is 2. He will get the same result and won't be able to drill down on City as if he had used SELECT City, Revenue from Sales.

Reference

Oracle Documentation