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