Hi,
I am trying to implement row level security in Universe by joining the Security tables to the fact tables. For this I want to know how I can use the BOUSER variable in the joins. Please let me know the syntax of this and if there is any better way to implement security please guide me.
I am working on BO XI R2
I dont know what the structure of you security table is so here is a hypothetic scenario:
The fact table FT contains a field with let’s say product IDs. And some other columns, of course.
The security table ST has 2 columns - 1st one contains the BO usernames and the 2nd one contains the product IDs that the relevant usernames can see.
You have a join
FT.productID = ST.productID
Now create a self-restricting join (aka stub join) on ST as
BOusername = @variable('BOUSER')
The important thing is that all objects from FT that you want to restrict based on the security data in ST need to use also ST table - to do it open object properties, definition tab and click on Tables button. Here both tables (FT and ST) need to be highlighted. This ensures that when the object is used in the query then not only FT is included in the SQL but also ST. And the stub join ensures that only product IDs that the currently logged user can see are in the results of the query.