Forum Stats

  • 3,767,751 Users
  • 2,252,713 Discussions
  • 7,874,327 Comments

Discussions

Row level data security in OBIEE is not working properly when left/right outer join is used between

Let's say I have two tables:

table A

Trx #Org id
181
281
381
491
543

and table B

Trx #Org id
181
281
381

Left outer join between A and B will give me this:

Trx # Org id (table A)Org Id (table B)
18181
28181
38181
491null
543null

So after this is pulled, my row level security data filters kicks in on each table. I have defined row level security in RPD and created session variables. So each time a table is used in analysis, it gets filtered by ORG_ID column that each logged in user have access to. ORG_ID access is defined in another security table in DB.

So in this example OBIEE applies two conditions in where clause beside the left outer join

1- where org_id from table A in (81, 91, 43)

AND

2- where org_id from table B in (81, 91, 43)

Let's assume user xyz have access to all the ORG_IDs

Unfortunately, the second AND condition is getting rid of trx 4 and 5 because it's org_id (table B) is null even though this record doesn't exist in table B and I don't want this condition to be applied on trx 4 and 5. User needs to see this because they have access to org_id 91 and 43 and the data is only coming from table A.

Please help and let me know how to resolve this issue. If I have an option to change the AND condition between two data filtered tables to OR, that might help me show trx 4 and 5. But the AND condition is not meeting the criteria between both tables when records from A doesn't exist in B and needed to be shown to users.

Answers