Overview
This howto is specific to Oracle Hyperion System 9.3.1, but I believe it may work on previous versions of Hyperion. I’m also using Oracle 11g for the Hyperion repository in this howto. The official documentation that describes the Hyperion Row Level Security comes with the product (if you chose to install it) and can be found in /Hyperion/9.3.1/BrioHelp/en/frameset.htm?launch.html.
This howto focuses on the steps that I personally found difficult and which aren’t fully described in the official documentation. So I put more detail in those steps. Please remember that you’ll need to alter the steps described here to fit your environment and configuration.
Step 1 – Setup a Data Access Service
Row Level Security uses a set of 3 tables, BRIOSECG, BRIOSECP, and BRIOSECCR. Hyperion Workspace needs to know where these tables exists. The data access service you setup in this step is for the database where these tables exists.
Run the Service Configurator by selecting Start > All Programs > Hyperion > Reporting and Analysis > Utilities and Administration > Service Configurator.
Once the Service Configuration window is up and running, from it’s menu select Modules > Local Service Configurator. (Note that my Hyperion installation is local, so I choose the Local Service Configurator). You will be prompted to login.
Once the Local Service Configurator is up, double-click on the service DAS1_[hostname].
Once the DAS1_[hostname] properties window is up, click on the Data Source Tab. You will see all the data sources already available to you.
If you already see the data source where your Row Level Security tables exists, then you’re all set. Make a note of the Hostname/Provider of the data source where they exists. This is information you will need later. If you don’t see the data source, then you’ll have to create one. Click on the New… button to get the Add Data Source to DAS window.
Select the Connectivity Type and Database Type and fill in the name of the data source. Note that for Oracle databases, this is the TNSNAME of the database.
Step 2 – Create the Row Level Security Tables
I found that by default Hyperion already creates these tables in it’s schema during installation. For my particular local setup, the tables where created in a schema named “hyperion”. If you don’t find these three tables BRIOSECG, BRIOSECP, and BRIOSECCR or if you choose to use some other schema for them, then you’ll have to create them. There are two ways to do this:
-
Hyperion provides you with an Interactive Report specifically for administering the Row Level Security which includes a section for creating the tables. You can find this report in \Hyperion\BIPlus\docs\en\row_level_security.bqy.
-
Run a SQL script manually. You can find the script in \Hyperion\BIPlus\database\[databasetype]\CreateRLS.sql.
Personally, I found the “Row Level Security Administration Tool” report difficult to understand and follow, so I would choose to run the script manually. But you’ll still have to use the report later in the setup.
I’ll assume you know how to run scripts against your database, so I am not providing the steps here.
Step 3 – Configure Row Level Security in Workspace
Login to Hyperion Workspace as an administrator and from the menu select Navigate > Administer > Row Level Security. You’ll get the Manage Row-Level Security page.
Check Enable Row Level Security. Select the Connectivity and Database Type. Enter the Data Source Name which must match the data source name you created in Step 1 – Setup a Data Access Service. Enter the User Name and Password to connect to the database. Click on Save Properties. You will be notified that the service needs to be restarted for the feature to be enabled.
Hyperion consists of so many dependent services. If you know exactly the dependencies between services then you can be brave and restart them in the proper order. I find it much easier to restart the entire machine, but then I was doing this only in a development environment, not production.
Step 4 – Define the Row Level Security Rules
You need to be very familiar with your datamodel to setup the rules. The examples I have here is for a data warehouse using a star schema. I have a fact table named AUDIT_FACT and a dimension table called OFFICE_DIM. And they are joined via AUDIT_FACT.OFFICE_KEY = OFFICE_DIM.OFFICE_KEY.
Although you could define the rules directly in the 3 security tables, I recommend using the provided Interactive Report for administering those rules. You can find the report in \Hyperion\BIPlus\docs\en\row_level_security.bqy. Start up Hyperion Interactive Report Studio and open this report.
When you click on the Click Here to Proceed button you’ll get a Warning message “Could not find connection file: C:\nosuchoce.oce”. Ignore this message and click on OK. You will be prompted to select an existing Interactive Report OCE file. Hopefully you’ve already have one created for your schema. Select that OCE to continue and you’ll be prompted to enter the login information for the OCE.
You’ll get a couple more messages. Read them and then click OK. They’re mostly informational messages and they’ll popup every time you run the report.
When you finally get to the main page for the tool, enter the User ID and Password for the schema that contains those 3 security tables. If you’re properly connected you should see the proper table specified with the label Restriction Table in Use in the orange box.
Click on the Change Multi-Group Conjunction button. This will take you to the Change Multi-Group page that explains what it does.
Select the conjuction you wish and click on OK. WARNING: This page may not be functioning properly. When I used it, it did not create the necessary record in the BRIOSECP table. So I resorted to creating the record manually. The table consists of simply one column. Insert one record into the table and set the data to the string “AND” or “OR”.
Return to the main page and click on the Add New Security Restrictions button. You’ll get the Add Row Level Security Rules page.
The sample rule I’ve defined restricts the user “johndoe” to only see audit records that belong to the “Main” office. Specifically, the rule defines a constraint that applies to the dimension table that is joined to the fact table. The contraint is OFFICE_DIM.OFFICE_NAME in (‘Main’). And this constraint is applied only when AUDIT_FACT.OFFICE_KEY = OFFICE_DIM.OFFICE_KEY.
WARNING: The security rules entries are case sensitive! Although in this write-up I upper case all table and field names for clarity, in my screen capture I show the entries all in lower case. Unfortunately the Hyperion documentation shows all entries in upper case. So which is correct? I found out which is correct for my environment by monitoring the SQL generated by Hyperion against my Oracle database. They were all in lower case. So all my rules are in lower case. This was by far the most crucial piece of information that I wish Hyperion documented. I spent hours trying to figure this out.
Once you have defined and saved your rule, go ahead and login using the test user (“johndoe” in this example) and try your Interactive Report. You must refresh or re-process your Interactive Report for the rule to take affect with the current user.
Another important note to remember is that the Row Level Security feature only applies to Interactive Reports that connect to a relational database. It does not apply to OLAP Queries, such as those against ESSBASE databases. And it certainly does not apply to Web Analysis Reports.
Essentially this security feature does SQL injection. It parses the SQL and checks against the rules you’ve setup. If it finds a match it will append/inject the constraints that you’ve defined into the SQL before executing it against the database.
