OAS OBIEE Data level security

VijayDC

Hi All,
On OAS environment I have users table like below

I have created this table under BIsample schema, also I have built SA on bisample schema.

I created one Init block for Authorization - as I want to apply data filter on a report with columns "CountryName", "revenue from fact". when the users of a specific Role access this report they need to get only CountryName they are authorized to as per above table.

IB test is working

when I login with user

Manage sessions I see query generated as below, I'm unable to figure out why its throwing an error/no results.
select sum(T262893.REVENUE) as c1,
T262846.COUNTRY_NAME as c2,
T262846.REGION as c3
from
SAMP_ADDRESSES_D T262846 /* D4 Address / ,
SAMP_CUSTOMERS_D T262866 / D3 Customer / ,
SAMP_REVENUE_F T262893 / F1 Revenue */
where ( T262846.ADDRESS_KEY = T262866.ADDRESS_KEY
and T262846.COUNTRY_NAME = 'eeee'
and T262866.CUST_KEY = T262893.CUST_KEY )
group by T262846.COUNTRY_NAME, T262846.REGION)

I need to apply data filter as below, please suggest what change I need to do.

PLease suggest what I'm missing on above steps

I'm getting the results for when I follow the link to apply on CountryName + Fact OR LOB+FACT but not LOB+COUNTRYNAME + FACT as I dont have key columns as shown in Implementing Data Level Security in Oracle Analytics Cloud Using Identity Cloud Service

Also please confirm if I need to join the user table with dim and fact of SA I'm applying security on.

Thank you in Advance

Michal Zima

Apparently you have wrong assignment of variables in Init Block IB_Authorization with respect of SQL for this Init Block. Your SQL returns following columns:

USER_NAME.

COUNNTRYNAME.

LOB

but you are assigning it to following variables (you have just 2 assigned as per screenshot):

Country_Name

Lob

So value of colum USER_NAME gets assigned to variable Counry_Name - what seems to not be correct. So you have either to get rid of column USER_NAME from your SQL or you have to define another variable UserName and assign it to first position.

Michal Zima

@VijayDC .I will point you to old, but very good and still perfectly valid blog from Kurt Wolff :

https://www.kpipartners.com/blog/bid/83347/complex-row-level-security-in-oracle-bi-obiee

This blog describes how to implement row wise session variables and how to use them in security filters in RPD. So follow this blog and you will be a “master of row level security”.

Bhaskar Konar

https://community.oracle.com/products/oracleanalytics/discussion/comment/63428#Comment_63428

Very well explanation and helpful information.

Thank you very much @Michal Zima!

VijayDC

Hi @Michal Zima

Thank you for the reply.

VijayDC

Hi @Michal Zima / All

for Authorization initialization block to fetch users related brand and office I have created table and initialization block sql query as below

Table data:

USER_NAME BRAND OFFICE

JOHN.DUNBAR BizTechBlue Bell Office

JOHN.DUNBAR FunPod FosterOffice

LISA.JONES HomeView FosterOffice

Rob FunPod TellaroOffice

Rob HomeView CasinoOffice

initialization block SQL: working fine

********* instead of above SQL is there any other way to frame the sql as I may need to use more than 3 columns i have to apply Data level security on. Data is like below

USER_NAME BRAND OFFICE Prod_Type
JOHN.DUNBAR BizTech Blue Bell Office Audio
JOHN.DUNBAR FunPod Foster Office Cell Phones
LISA.JONES HomeView Foster Office Plasma

Thank you all in Advance