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

Abed Jaji

Let's say I have two tables:

table A

Trx #	Org id
1	81
2	81
3	81
4	91
5	43

and table B

Trx #	Org id
1	81
2	81
3	81

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

Trx #	Org id (table A)	Org Id (table
1	81	81
2	81	81
3	81	81
4	91	null
5	43	null

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 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.

User_B8VQC

Please share the screen shots of data filters from RPD and sample nqquery log. We can see how the filters applied in nqquery log.