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:
- BOUSER – the business objects user id that the user uses to access business objects
- DEPARTMENT – the department this user should have access to
Join the security table to the relevant dimension or fact table in your 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.
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.
John Levandowski 
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.
LikeLike
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
LikeLike
The properties tab is on the object definition in the business layer.
LikeLike
Very helpful, thank you very much!
LikeLike
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.
LikeLike
You are correct, the user would get no data in the report.
LikeLike
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…
LikeLike
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
LikeLike
Our IT department had the bright idea of creating Windows AD userid with apostrophes in them causing issues with BOUSER. Anyone would know how to get around this?
LikeLike