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
1 | SELECT 'PROD_FILTER',PROD_ID |
2 | FROM PRODUCTS A, PROD_SECURITY B |
3 | 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.
1 | 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.
Posts
