Show This Dimension, Well Sometimes

Posted on by EV Technologies

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:

  1. Added calculated column to my source table as @Variable(‘BOUSER’)

who

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

relationship

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.

3 thoughts on “Show This Dimension, Well Sometimes”

  1. Another way is to do the following:

    1. Create derived table with the restricted column and ID that can join it to your original table
    2. Map that restricted object to your derived table
    3. Create another DB view or a derived table where the column only holds the text :restricted (using case statement for example )
    4. Create a restriction that uses table mapping that directs the original table to the 2d derived or DB view table
    5. Create the restricted users group in CMC or AD and link it to that restriction

    In general: I don’t use joins to combine a security table to the existing table structure of the universe , I use Exist statement which eliminates that + I inly mange the security in the security editor + low join +extra tables (aliases… ) maintenance

    1. Thanks Yoav, I really appreciate you reading & replying. I’ll have to think about that as an alternative option. In the real life example, another 4 layers were added to the security. It’s been interesting!

  2. I forgot to say : great and interesting article!
    You also have two other methods to deal with specific object security in IDT:

    1.Creating a business security profile
    2.Using the object access level which can make the object visible but not useable

Leave a Reply