BusinessObjects Board

Dynamic condition?

building semantic layer
#1

Hello everyone,

I need some advice on how to implement this in the universe. Please help.

Security table (2 columns) - Note: i’ll link this to some fact tables
userID_1 | Security Rule
Abi | Country = US and Cost center = 123
Teddy | Country = Canada

I need to pass the value of security rule dynamically in the where clause depending on the user id. I’ll be using @variable(‘BOUSER’) = security.userID_1.

The final query should be something like
Select fact.col1, fact.col2 from fact, security
where @variable(‘BOUSER’) = security.userID_1
and Country = US and Cost center = 123

Thank you for your time.

vlie (BOB member since 2006-11-01)

#2

It looks to me like you are planning to implement ROW LEVEL SECURITY…

For this search in this forum with key words: row level security

There is also a thread posted by STEVE KRANDEL regarding this.

If I find it, I’ll post it here.

If you need more information… let us know.

[Moderator Edit: Combined follow-up post here]
Here is the link of STEVE KRANDEL’s presentation.

https://bobj-board.org/t/33904/1

Hope this helps

BO_Chief :us: (BOB member since 2004-06-06)

#3

You need not 2 but 3 columns in the security table considering your example .
[User_ID] [CountryID] [Cost CenterID]

If you join Country ID and Cost Center ID to the corresponding dimensions , you may end up resolving loops (Fct-Dim-Security). To avoid that , you may consider using separate Security table for each level of security… There are other options also … but this is just a suggestion

Shobhit_Acharya (BOB member since 2005-08-11)

#4

One Question for you ? Why do you want to link the security table to the Fct ? wouldn’t the SQL be better performing if it were linked to the dimensions ?

Shobhit_Acharya (BOB member since 2005-08-11)

#5

What if one doesn’t use the dimension table and you don’t want to force joins to all objects from the fact via the “Tables” button? As long as the security table is joined to the fact table, you can force the user to only get those rows s/he is entitled to get.

Anita Craig :us: (BOB member since 2002-06-17)

#6

I was talking about performance which is of major concern (to me atleast) whenever row level security is being considered. Comparing or joining millions of record in fct tables against the security table (which may as well a million rows) is costly. We are talking about the equivalent of a many to many Fct-Fct join in this case.
It would be a lot less costly if the dimensions are involved in the Security. Think of it this way too … I want to see a list of all plants (Dimension Column) that i have access to .Fct based security will go through the fact records to get me the data (Dim-Fct-Security) . Dimension based security will need to involve only the Dimension and security tables (Huge gain).
If the security object sql is embedded in the dimensions , then only the required level of security joins will be pulled into the report.
This is just my practical understanding. May not be relevant to all data scenario’s but definitely is relevant to most.

Shobhit_Acharya (BOB member since 2005-08-11)