OBIEE 11g Security : Row-Level Security

So we’ll start this week’s focus on OBIEE 11g security with the topic that most people associate with “security” around reports – row-level security. Row-level security is where groups of users have access to a set of reports, but they all see different sets of data within the reports due to filters being applied silently in the background. As a report author you can of course apply filters to an analysis, but in this case I think what people generally associate row-level security with is filters that are applied automatically, according to a set of rules, without the user being aware of it or even filters being visible when you add a filter view to the analysis’ compound layout.
In the two analysis screenshots below, for example, one user sees one set of stores when they view a particular report, whereas another user viewing the same report gets to see a completely different set of stores, but no filters have been explicitly added to the analysis – instead, some other process is applying row-level security to the analysis’ data in the background, automatically.
The advantage of row-level security, particularly if it’s applied automatically by the BI Server in the background, is that you can roll-out generic reports across all of your users, but each one will provide a customised view showing just the data, or “rows”, that apply to that user, even if the report designer forgets to add filters to the analysis themselves to restrict user’s views of data to the correct region, in this case.
So where can you set up row-level security in OBIEE, so that this background row-level filtering takes place? In general, you would set up row-level filtering like this using two repository features, together:

1. First, you would define what’s called a “session variable” to hold the region name that the user is allow to see, and then
2. You would define a filter, using the Identity Manager dialog, to filter rows against this region setting.

You can also apply row-level filtering at the database level, by using what’s called Virtual Private Databases, and then enabling this in the physical database settings, but that’s a topic for another day. In addition, of course the developers of individual analyses and reports can add filters to the report definition, which again can make reference to session or other variables. But for now, let’s look at the most common way to set up background row-level filtering and security.
Assuming that we’ve got two tables stored in a database somewhere, that list out users and the region they are assigned to, like this:

The steps to set up security to restrict users to a single region’s worth of data would look like this:

1. Before we do anything, we’ll need to create a new, dedicated connection pool definition within the physical database settings that connects us to the database schema that holds these tables. This is because, as from OBIEE 11g, by default connection pools that are used for general database queries can’t be used for initialisation blocks, and you’ll get a validation warning if you try and do this. Instead, using the Oracle BI Administrator tool and with the repository open online, create a new connection pool within the database that holds these tables, enter the connection details, and give it a name such as init_blocks_cp.
2. Now, we’ll start by defining the session variable that will hold the region name that the user is allowed to see. Still using the Oracle BI Administrator tool and with the repository open online, select Manage > Variables… to open the Variable Manager dialog.
3. Within the navigation tree on the left-hand side of the Variable Manager dialog, click on the Session > Variables node, then right-click in the area on the right and select New Session Variable…
4. With the Session Variable dialog open, type in REGION as the Name, and then press the New… button next to the Initialization Block: area, which currently has not assigned as its setting.
5. The Session Variable Initialization Block dialog will then be displayed. Enter GET_REGION_SESS_VAR as the name, and then press the Edit Data Source button to bring up the Session Variable Initialization Block Data Source dialog.
6. Using this dialog, select Database as the Data Source Type, select the connection pool that you defined earlier, and then select the Default initialisation string radio button. Then, type in the SQL that returns the region for a given user, using the :USER substitution variable, like this: select r.region from gcbc_hr.staff_logins l, gcbc_hr.staff_regions rwhere l.login_name = r.login_nameand l.login_name = ‘:USER’
   Press OK to close the dialog, and the Session Variable Initialization Block dialog should look as in the screenshot below:
   
7. Using the Oracle BI Administrator tool and with the repository open online, select Manage > Identity… from the application menu.
8. The Identity Manager dialog will then be displayed. Click on the Application Roles tab on the right-hand side, and then then double-click on the role you wish to assign the row-level security settings to; in this case, BIConsumer, to apply the rule to all users of the system.
9. The Application Role dialog will then display. Press the Permissions… button to open the User/Application Role Permissions dialog.
10. With the User/Application Role Permissions dialog open, press the Add button to bring up the Browse dialog. Using the Browse dialog, select the table you wish to apply the filter to. In this instance, there are two ways we can set up the filter. The Region column that corresponds to the filter is found in the Dim Stores table, which means that if we want to apply the filter when any column from this table is included in an analysis, we’d double-click on this particular table to select it for the filter condition. If, however, we wanted the filter to be applied even if no column from the Dim Stores table was selected, we’d double-click on the associated fact table Fact Sales instead, which would apply the filter regardless of what attribute columns were selected for the analysis criteria.
    Note also that you can place the filter on either a presentation table, or a business model table. If you choose the former, and you’ve got several subject areas based off of the same business model, only the table from that particular presentation layer subject area will be subject to the filter. If, however, you click on the Business Model tab instead and then select a business model table, every subject area that contains presentation tables derived from that business model table will have the filter applied.
    
11. After double-clicking on a table, you’re returned back to the User/Application Role Permissions dialog, to define the actual filter expression.
    Click in the Data Filter area and then press the Edit Expression button to bring up the Expression Builder – Data Filter dialog.
12. Using this dialog, set the following filter, which references the session variable you defined earlier using the VALUEOF(NQ_SESSION.variablename) syntax: “Sales”.”Dim Stores”.”Region Name” = VALUEOF(NQ_SESSION.REGION)
    Press OK to close the dialog, then keep pressing OK with the other dialogs to eventually return back to the Identity Manager dialog. To close that final dialog, select Action > Close.

Now, when users log in that have entries in this table, you should see that queries that reference either the Dim Stores table (in this case), or if you’ve set it up like this, any query against the fact table, will have the required restriction applied to the rows of data returned. If you don’t see this restriction happening, check the nqserver.log file for an error message like:

[2012-03-08T00:16:24.000+00:00] [OracleBIServerComponent] [ERROR:1] 

[] [] [ecid: 3f3d2d8955322f32:1843bf05:135ee2cec8c:-8000-00000000000008c3] [tid: 10f4]  [nQSError: 17010] SQL statement preparation failed. [[[nQSError: 17001] Oracle Error code: 904, message: ORA-00904: "R"."LOGIN_NAME": invalid identifier at OCI call OCIStmtExecute.[nQSError: 13011] Query for Initialization Block 'GET_REGION_SESS_VAR' has failed.

which indicates that there’s an error in your SQL, and the session variable value couldn’t be set.
So what happens if you log in as the weblogic or biadmin user, for example, who doesn’t have an entry in this table, or indeed a regular user that’s not in the table? If you try it out, you’ll see that in these cases, administration users get all values returned, whereas regular users get an error message saying that the variable hasn’t been populated, which probably is the behaviour that you wanted.
The other obvious question is how you would go about assigning more than one group to a particular user. For example, if you wanted the sfranceso user to be able to see all of the San Francisco regions, not just North SF, you could put entries in the STAFF_REGIONS table like this:

But the SQL in the variable definition that we defined earlier is expecting a single value, not a set of values. The way that you in fact deal with this is to create what’s called a “row-wise initialization” variable that can contain multiple values – internally, it still holds a single value, but this consists of all the values you load in, separated by semi-colons.
To define a row-wize initialised variable, follow these steps:

1. Using the Oracle BI Administration tool and the repository online, select Manage > Variables… as before. This time though, do not create the variable first; instead, click on the Session > Initialization Blocks entry in the Variable Manager navigation tree, then right-cick on the right-hand panel and select New Initialization Block…
2. With row-wise variables, you define the SQL query, and the variable, at the same time. Using the Session Variable Initialization Block dialog, type in a name for the initialisation block, for example GET_REGIONS_ROWWISE_SESS_VAR, and then press the Edit Data Source… button to bring up the Session Variable Initialization Block Data Source dialog. As before, select Database as the Data Source Type, select the Default initialization string radio button and select the connection pool as before, but then when you come to type in the SQL you define the variable at the same time, like this:
   select ‘REGIONS’, r.region
   from gcbc_hr.staff_logins l, gcbc_hr.staff_regions r
   where l.login_name = r.login_name
   and l.login_name = ‘:USER’
   with the literal values ‘REGIONS’, in this case, defining the row-wise variables as part of the initialisation block definition.
   Press OK to close this dialog and return to the Session Variable Initialization Block dialog.
3. In the previous example, you’d see the variable name listed in the Variable Target area. To assign this initialisation block to the row-wise variable we’ve just defined in the SQL statement, press the Edit Data Target… button, and then when the Session Variable Initialization Block Variable Target dialog is shown, check the Row-wise initialisation checkbox. Your final Session Variable Initialization Block dialog should now look like this:
4. Now, to reference the row-wise session variable in a filter, for example in the mandatory filter we applied to the BIConsumer role, you’d reference it in the same way as with a regular variable: “Sales”.”Dim Stores”.”Region Name” = VALUEOF(NQ_SESSION.”REGIONS”)
   Then, you should see any users that have more than one region assigned, getting the correct values in their analyses:
   

So that’s the basics of how row-level security works in OBIEE – you define a session variable using an initialisation block, which then gets used to create a data filter “behind the scenes” using the Identity Manager. There’s a few variants around this – you might use Virtual Private Database, for example, to enforce the row-level security at the database level – but this is the most common approach.
One other row-level filtering approach that you might sometimes come across though is where the filter is applied at the logical table source level. If you open up the repository and double-click on a logical table source within the Business Model and Mapping layer, then click on the Content tab, you’ll that one of the sections lets you type in an SQL WHERE clause to limit the rows returned, like this:

By default, and in most cases, this section is usually empty for most logical table sources, but you could use it to enforce row-level security for all users, without reference to a particular user or role, like this:

I personally don’t tend to use this type of approach to row-level security, as it’s applied across all users regardless of roles etc, and also it’s not so obvious to find, hidden away in a logical table source definition. But you ought to be aware of how it’s done, as its used extensively, for example, by Oracle with the BI Applications repository model.

Reference:
http://www.rittmanmead.com/2012/03/obiee-11g-security-week-row-level-security/