Thursday, October 9, 2014

OBIEE 11g - Row Level Security

One of the ways data level security is applied for OBIEE reports is through row-level security. With row-level security implemented a set of users would have access to the same  report, but would  all see different  data within the report due to filters being applied in the background which are not really visible to the users. These filters are applied according to business rules in the RPD without the user being exposed to it


See the two screenshots below, While it’s the same analysis, one user sees one set of regions when they view a particular report, whereas another user viewing the same report gets to see a completely different set of regions, 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.
With this type of row-level security, being applied at the RPD level in the background, generic reports for all users can be rolled out , but while viewing  they will just see the data, or “rows”, that apply to that user.
Row-level security in OBIEE can be set up using two repository features:
1.   Define  a “session variable” to hold the region name that the user is allowed to see   
2.   Use the Identity Manager to filter rows against this country setting.

Row-level filtering can also be achieved at the database level, by changing physical database settings, but that’s outside the scope of this article. 
Assuming that we’ve got two tables stored in a database somewhere, that list out users and the regions they are assigned to, like this:
Below are the steps to set up data level security to restrict users to data for a single region:
  1.     Create a new, dedicated connection pool definition within the physical database settings that connects to the database schema. OBIEE 11g onwards, connection pools that are used for database queries cannot be used for initialization blocks. 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 Oracle Data Warehouse Repository Initblocks Connection Pool.


2.   With the repository opened online, select Manage > Variables… to open the Variable Manager dialog.
3.   With 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 GET_REGION_VAR 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 REGION_ 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 initialization string radio button. Then type in the SQL that returns the region for a given user, using the :USER substitution variable, like this:

select t2.region from user_logins t1, user_regions t2 where t1.login_name = t2.login_name and t1.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 


9. The Application Role dialog will then display. Press the Permissions… button to open the User/Application Role Permissions dialog


10.    Open the User/Application Role Permissions dialog and then press the Add button to bring up the Browse dialog. In the Browse dialog, select the table you wish to apply the filter to.
There are two ways we can set up the filter. The Region column that corresponds to the filter is found in the Dim Organization 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 Organization table was selected, we’d double-click on the associated fact table say Fact Revenue instead, which would apply the filter regardless of what attribute columns were selected for the analysis criteria.
The filter could be placed either on a business model table or a presentation table. If, you click on the Business Model tab 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. 
If, you click on the Presentation tab, 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.

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 Organization”.”Region” = VALUEOF(NQ_SESSION.REGION_VAR)
Press OK to close the dialog, and 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 Organization 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.  
The weblogic or biadmin user who doesn’t have an entry in the table that is administration users would get all values returned.A regular user that’s not in the table would get an error message saying that the variable hasn’t been populated.
To assign more than one group to a particular user let’s say if the user were to be able to see all of the regions you would have 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 initialized variable, check the row-wise initialization block as shown below.
There is another row-level filtering approach in which 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 and  then click on the Content tab, you can type a  WHERE clause in the section below to limit the rows returned

This filtering would be without reference to a particular user or role


No comments:

Post a Comment