BusinessObjects Board

Row level Security at the Universe- forced join question

building semantic layer
#1

I’ve read designer where it says that all objects used in the report must have a forced join to the security table.

Will this work the same? Add the security table forced join on just the join object between each and every table, not exactly the object in the report.
Example-
sec table had 2 columns userID and entity which is a loc.cd

sec table.entity joins to shp table.loc cd.
shp table.shp id joins to element table on shp id.

Can I add the forced security table to the object Element table.ship Id even if that is not the object in the report- perhaps -Element.Hold YN indicator was used in the report with shp table.loc cd.

This is/would be helpful if all of the reports have not been developed yet- and the Universe design team is totally separate from the report/customer team and the universe team can’t stay ahead of the report writers !

BO 6.5.1 Oracle 10

benslow :us: (BOB member since 2005-11-04)

#2

If you are going to use the forced join method, you have to put it on all objects that would require security. If a particular table will be in every query, you might be able to make this work in the join definition of the table.

Personally, I don’t like this type of security. It is very invisible and can break easily if an object is modified.

Have you looked at this presentation: Using Designer to Implement Row-Level Security

Steve Krandel :us: (BOB member since 2002-06-25)

#3

This is an area where the Business Views (used by Crystal Reports) are superior. Joins are given a property like “always use” or “only when needed” (or something like that). Hopefully this is a feature from Business Views that DOES make its way into the “combined” semantic layer. Still on track for Titan?

Until then though, as has been suggested a solution is to associate the table with every object that needs it … and as Steve points out, it is very fragile. An alternative is using a sub-query instead of a join. Something like this on the DataTbl:

DataTbl.PK IN (SELECT SecTable.FK WHERE SecTable.User = @variable('BOUSER'))

The sub-query can be done as a “self-join” on the table in Designer, or applied as an override in Supervisor. Because it is on the table, not individual objects, it will always be enforced.

Dwayne Hoffpauir :us: (BOB member since 2002-09-19)

#4

Instead of forcing a join to the security table we have used the method shown by Dwayne:

Table: MarketingGroup
Security Table: UserMarketingGroup

so we have a row level restriction defined as:

MarketingGroup.MarketingGroupID in (select MarketingGroupID from UserMarketingGroup where LoginID = @Variable(‘BOUSER’) )

You can then apply this restriction to relevant BO groups and exclude other groups.

Zulfiqar_Taj (BOB member since 2002-09-16)

#5

Hi bouser,

 Isn't it when you use @Variable('BOUSER') , User names and not groups are the ones implementing security? I just want to verify your statment on this "You can then apply this restriction to relevant BO groups and exclude other groups".

thanks,
Antzcp04

antzcp04 (BOB member since 2006-08-04)

#6

But, users are members of groups. You apply the restriction on the group and it is inherited by the users.

Steve Krandel :us: (BOB member since 2002-06-25)

#7

So in effect if a user has multiple groups , then all of the groups are enforcing row level security?

antzcp04 (BOB member since 2006-08-04)

#8

No. It’s applied on a universe at the group level.

Steve Krandel :us: (BOB member since 2002-06-25)

#9

It’s been a while since I tested this, but I remember the results to be ambiguous … all ignored, or the first, or the last … problematic. TEST THOROUGHLY! Our front-end security application (home grown, NOT SUPERVISOR) ensures that a given user has access to a given universe via one and only one group.

Dwayne Hoffpauir :us: (BOB member since 2002-09-19)