Business Intelligence – Oracle

Oracle BI EE 10.1.3.3.3/2 – Row Level Security and Row-wise Intialized Session Variables

Posted by Venkatakrishnan J on June 30, 2008

As you would probably know, BI EE provides the capability to do row-level security from the Business Model layer. There was a question the other day wherein a user wanted to know how to do row-level security, wherein more than 1 filter value come from some other table. For example, lets consider the standard SH schema that comes with Oracle Database. The BM for this schema would look like the one shown below

    

Consider another table PROD_SECURITY containing the columns USERNAME and PROD_CATEGORY.

    

So, basically, the above table provides the list of users and their corresponding product category for which the users have access to. Now, in our BM, the sales and the product table are joined at the Product level. Each product category can have multiple products and hence we would have to use row-wise initialized session variables to achieve this security. So, start with creating an init block which would populate a session variable PROD_FILTER with all the prod ids belonging to the product category for which the user has access to. The init block would use the sql below

SELECT 'PROD_FILTER',PROD_ID
FROM PRODUCTS A, PROD_SECURITY B
WHERE A.PROD_CATEGORY = B.PROD_CATEGORY

    

Once this done, use the below statement in the where clause of the content tab of the Sales data table.

ORCL."".SH.SALES.PROD_ID IN ( VALUEOF(NQ_SESSION."PROD_FILTER"))

    

So, basically the row wise initialized variable would be initialized to (100,200,….) etc. Lets check whether the security is getting properly applied from answers. Since, we have not secured on the dimension table, lets first include the products column in our report and see what happens to our report

    

As you see, it would list down all the product categories. Now lets include the AMOUNT_SOLD column from the fact table into our report.

    

As you see, the security has been applied and the user Administrator would be able to see only 2 categories for which he has access to.

About these ads

6 Responses to “Oracle BI EE 10.1.3.3.3/2 – Row Level Security and Row-wise Intialized Session Variables”

  1. Deepak Karanwal said

    Hi

    It’s good if your prod filters are leass in numbers. As the query which OBIEE generate will be similar to “where Prod id in (1,2,…..n). If value of n is large you will have a potential performance problem.

    I am just wondering if we can use a subquery instead of initilaizing session variable somewaht similar to

    “where prod_id in (select PROD_ID
    FROM PRODUCTS A, PROD_SECURITY B
    WHERE A.PROD_CATEGORY = B.PROD_CATEGORY
    and B.USERNAME=’:USER’).

    Deepak

  2. Stijn Gabriels said

    Hi Deepak,

    I have had a similar case, where the IN clause isn’t wanted because of the large number of elements you would get within the IN clause.
    I’ve found a workaround by creating a separate PROD_SECURITY dimension, with 2 columns (USERNAME and PROD_ID), which is joined to the fact table (SALES) using the PROD_ID.

    Then I constrain this dimension in the BMM layer by entering USERNAME = VALUEOF(NQ_SESSION.USER) in the where clause filter of the logical table source.

    Next step is to create a hierarchy for this dimension with 3 levels: All (Grand Total Level), USERNAME and PROD_ID. So USERNAME is the parent level of PROD_ID.

    Now comes the trick: on each measure of the SALES fact table I set the logical level of the PROD_SECURITY dimension to level USERNAME, which means that every measure will be aggregated on the USERNAME level.

    This will create the following SQL:

    SELECT SUM(a.SALES_MEASURE)
    FROM SALES a, PROD_SECURITY b
    WHERE a.PROD_ID = b.PROD_ID AND b.USERNAME = ‘userX’
    GROUP BY b.USERNAME

    As you can see there is a filter on the username in the SQL but the IN clause is gone :)

    Regards,
    Stijn

  3. Deepak Karanwal said

    Hi Stijn

    This will be good if you have username/userid as a element in your dimension. In my case security is based on code_combination key which is different for each users and derived from complex logic. Also If user does not select anything from the new dimension then security will not be implemented.

    Deepak

  4. Stijn Gabriels said

    Deepak,

    You said: “Also If user does not select anything from the new dimension then security will not be implemented.” This is not true. If you put the logical level to USERNAME on each measure, every SQL query will be grouped by the USERNAME even you do not use a column for that dimension. But you do need a separate PROD_SECURITY dimension, next to your “normal” product dimension.

    Regards,
    Stijn

  5. Matt Bedin said

    Venkatakrishnan,

    Oracle BI Security filters are a more flexible and pervasive way to deploy row-level security. These filters are defined once (to the Business Model), assigned to user roles, and enforced for all underlying physical data sources (e.g. filters would be invoked across federated, partitioned or aggregated sources).

    Matt.

  6. Balaa said

    Hi,
    we have different users like Administrator, Test, Dev,..etc and they are work in one Dashboards.

    so I want to display all users and active users information like Log in Time, log out Time, current user information and which objects (Variables) are using in Answers or in Dashboards.
    I want to display these information in one dashboard page (Tab).

    Is it possible….. yes means plz inform the way to accomplish this issue

    Thanks in Advance
    Balaa

Leave a Reply

Fill in your details below or click an icon to log in:

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

 
Follow

Get every new post delivered to your Inbox.

Join 158 other followers

%d bloggers like this: