How To Implement Row Level Security in EPM 11.1.1.2.0

How To Implement Row Level Security in EPM 11.1.1.2.0 [ID 970369.1]
Modified 01-APR-2011 Type HOWTO Status PUBLISHED
In this Document
Goal
Solution

Applies to:

Hyperion BI+ - Version: 11.1.1.2.00 and later [Release: 11.1 and later ]
Information in this document applies to any platform.
Goal

Implement Row Level Security in EPM Interactive Reporting 11.1.1.2.

Solution

A) Set background resources for Row Level Security (RLS)
1. Create a database to hold the RLS tables,
2. Create an ODBC connection to the RLS DataBase using appropriate Merant Wire Protocol for your RDBMS.
3. Create an OCE file with ODBC/ODBC connection types and publish it to the workspace, call it RLS.
4. Open Row_Level_Security.BQY (i.e, for system 9 it’s located in %Hyperion_Home%\BIPlus\docs\en; not bundled with EPM 11.1.1.x) and select the RLS.OCE file created in the previous step and connect to RLS database.
5. If this is the first time to establish a connection to the RLS database you will be asked to create these tables first, click yes.
6. Enter the username and password then on Create, now you should have three tables created in your database:
7. Define the OCE connection in DAS using Local Services Configurator, and then restart all the services.
8. Configure RLS in workspace, Navigate > Administer > Row Level Security, check Enable Row Level Security and enter your details of RLS OCE.
9. Restart all the services to ensure changes take effect

B) Define Users and Groups for RLS
1. Click on Work with Users\Groups button and Create a group called it AMERICAS
2. Create a user called "process" and add it as a member in AMERICAS.
3. Create a user called "process" and in shared services (it’s very important to spell it identically to the user in the RLS tables with the same letter case otherwise RLS will not work).

C) Build your report and publish it to the workspace, edit the permission for this report and grant “Full Access” and “View and Process” permissions for user called “process”.

D) Define Restrictions
1. We need to hide the column called “STORE_KEY” in “SALES_FACT” table to prevent the employess from tying back the sales figures with the stores. We can hide the column by adding a row in BRIOSECR to restrict access for “PUBLIC” group as below:
2. Then override this restriction for “AMERICAS” group by adding a second restriction to the BRIOSECR as below:
3. Now, I f we run the report using “admin” which is not a member of “AMERICAS” group we will not be able to see “STORE_KEY”.
4. If we open the same report using “process” user we will be able to see the values in STORE_KEY
5. Now, we will add another constraint to restrict access of AMERICAS group to Sales amount to the store named ‘BMV Lyon’ and to hide other rows of data. This condition will be applied as an inner join constraint.
6. Run the report again, you get only sales amount of ‘BMV Lyon’ store.

For more information, please refer to IR_user.pdf. This contains the official documentation for Row Level Security. Check Chapter 22 "Row Level Security in Interactive Reporting" page 505.