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.
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.