Business Objects Universe Row Level Security

If you are a business objects universe designer that needs to implement row level security in your universe, here is a simple method. The following are the steps necessary when using the business objects information design tool. Similar steps can be done in the legacy universe designer tool.

Create security table

The first step is to create a security table and add it to your universe data foundation. The security table has 2 fields:

  1. BOUSER – the business objects user id that the user uses to access business objects
  2. DEPARTMENT – the department this user should have access to

Join the security table to the relevant dimension or fact table in your data foundation

Business Objects Row Level Security Data Foundation

In this example the user can have access to multiple departments.

Create a security filter in your business objects universe business layer

The SQL definition is SECURITY.BOUSER = @Variable(‘BOUSER’)

On the properties tab you will need to check Use filter as mandatory in query and choose the Filter Scope Apply on Universe. This will make this filter used on all queries using this universe.

Business Objects Row Level Security Universe Filter

Test

Here is the query that will be created when selecting the department and department name object from the universe:

SELECT
DEPARTMENT.DEPARTMENT,
DEPARTMENT.DEPARTMENT_NAME
FROM
DEPARTMENT,
SECURITY
WHERE
( SECURITY.DEPARTMENT=DEPARTMENT.DEPARTMENT )
AND
( SECURITY.BOUSER = @Variable('BOUSER') )

In the query generated above the business objects user id of the user executing the query will be substituted for the @Variable(‘BOUSER’) statement. As a result, the user will only have access to the departments that have a row in the security table associated with their business objects user id.

Please leave a comment with your experience in implementing row level security in a business objects universe.

6 thoughts on “Business Objects Universe Row Level Security

  1. Adnan

    Would this approach work in the following scenario where another dimension table for example “EMPLOYEE” is joined to “DEPARTMENT” through 2 different fact tables. Hence you would have 2 contexts in your universe.

    Once this filter object(as shown in this article) is defined wouldn’t users be prompted to select a context in which they want to see the LOV’s from EMPLOYEE table? As the join from SECURITY to EMPLOYEE table can be established in more than one way? Any input on this would be of great help.

    Like

  2. John

    Can you please more specific on how to get to the properties tab? Are you in the data foundation, or business layer? Do you click on the join first to see the property tab?

    Thank you,

    John

    Like

  3. Anuradha

    Hi John,

    In above case, would the reports yield any data for BOUSER’s not in security table.
    Eg.. Security table has three BOUSERs say 1,2,3. In case BOUSER 4 refreshes the report, will he see any data? My understanding is he will get a blank report.

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

w

Connecting to %s