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.

8 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

  4. Tom

    Hello,
    I have a strange error with RLS – in definition I have SQL
    and upper(VW_D_USER.USER_NAME) = ‘VCN\’ +upper(@Variable(‘BOUSER’))
    In one new BO report it generates sql:
    and upper(VW_D_USER.USER_NAME) = ‘VCN\’ +upper(a246610)
    in all others
    and upper(VW_D_USER.USER_NAME) = ‘VCN\’ +upper(‘a246610’)
    Why ” are not generated – I spent a few hours looking for answer, but no idea. I see that in the first case it is event SP:CacheMiss, in all the rest SP:CacheHit, but it has nothing to do with SQL RLS code generation. Different measures used, but they all use the same RLS just assigned to one table…

    Like

    1. Tom

      I have found a solution, maybe will be helpful for someone. Do not use signes [ and ] in relation expressions – even if you copy them after test in MSSQL. It may cause strande code generation like one described above

      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

This site uses Akismet to reduce spam. Learn how your comment data is processed.