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
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.
@VijayDC .I will point you to old, but very good and still perfectly valid blog from Kurt Wolff :
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”.
Very well explanation and helpful information.
Thank you very much @Michal Zima!
Hi @Michal Zima
Thank you for the reply.
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
