Object Level Security Based on User
Recently I had an interesting request come up and I thought I would share the solution with my fellow universe development nerds. Hope you enjoy and questions\comments are always welcome!
Just like in the Universe Designer, Information Design Tool (IDT) in BI4 gives you options to create security profiles based on group membership to secure objects in your universe. In Security Editor you can create a Security Profile and then assign it to Business Objects Groups. This works great for row level security (adding a where clause), setting SQL restrictions (max number of rows to return), hiding particular objects in a Business Layer and even table swapping. Unfortunately none of which fit what I was asked to do. The request was to hide the value of a dimension but only for certain users. So what’s a girl to do? Make up a workaround and get out the duct tape. May not be the most elegant of solutions but it worked. So we’ll go with results not appearance for this one.
The Problem
Change the definition of a dimension to “Classified” when the user is not in the approved security group.
The Solution
Basically I had to create a situation where BO could know who the user refreshing the report was and whether they should have access to classified data. I was able to leverage the universe variable @bouser to identify the user then the trick was to make it mean something in the universe.
It’s a 5 parter but don’t be scared! Here’s what I did:
- Added calculated column to my source table as @Variable(‘BOUSER’)
Let’s us know who’s refreshing the report.
2. Created Security derived table as:
SELECT USERID
FROM security
WHERE USERID=@Variable(‘BOUSER’)
Tells us who’s on the list to see classified data. There is an assumption here that you are maintaining the who’s who list in your data source.
3. Create a left outer join between the source table and security table where:
Source.BOUser= Security.USERID
Gives us the relationship.
4. Created switch dimension object and hide it: case when Source.BOUser= Security.USERID then ‘Y’ else ‘N’ end
Checks to see if current user is on the access list.
5. Changed each dimension that I needed to mask to a version of this:
case @Select(Hidden\Classified Switch)=’Y’ then ‘Classified’ else originaltable.originalobject end
Hopefully the object we want!
Now I’ve tried to make the SQL make sense with generic references but the basic premise is to create the @bouser as a column in your source table so you can join it to your security table. You use a derived table on your security table in order to only bring back the row for the current user (if there is one). Then you evaluate at the dimension level whether the user is approved for classified objects or not. Once you determine that you are using the case statement to either display classified or the actual dimension. You could do it in fewer pieces but this way let’s you focus on a piece if there is an issue.
Again not the most elegant of solutions (just a little duct tape) but it gets the job done when out of the box the tool can’t.
Look forward to your thoughts and thanks for reading.