Access Query Based on SAV ROLE

[HichamElk]

Hello,

We would like to filter the view on an application for people who have specific SAV Roles or Profiles 

We already configured the access query for profiles that works well. 

For SAV ROLES, I can't find a table for savroles that I use to see on this forum

Our actual access query is 

where '${requestor.id}' in (SELECT distinct UA.userkey FROM ACCOUNTS A, USER_ACCOUNTS UA, ENDPOINTS E, account_entitlements1 ae1 WHERE A.ACCOUNTKEY = UA.ACCOUNTKEY  AND A.ENDPOINTKEY = E.ENDPOINTKEY  and ae1.accountkey = a.accountkey and ae1.entitlement_valuekey in (select entitlement_valuekey from entitlement_values where entitlement_value='PF_CORE_LOCAL_ADMIN' or entitlement_value='System Administrator' or entitlement_value='PF_CORE_LOCAL_DATA_ADMIN'))

We would  like to add "or SAV ROLE "ROLE_ADMIN"

[Amit_Malik · Accepted solution]

you can use savroles and user_savroles tables, to see data - use analytics as savroles is not exposed in data analyzer

select us.userkey from savroles sv, user_savroles us where sv.rolekey=us.rolekey and sv.rolename in ('ROLE_ADMIN')

where '${requestor.id}' in (SELECT distinct UA.userkey FROM ACCOUNTS A, USER_ACCOUNTS UA, ENDPOINTS E, account_entitlements1 ae1 ,savroles sv, user_savroles WHERE A.ACCOUNTKEY = UA.ACCOUNTKEY AND A.ENDPOINTKEY = E.ENDPOINTKEY and ae1.accountkey = a.accountkey and ae1.entitlement_valuekey in (select entitlement_valuekey from entitlement_values where entitlement_value='PF_CORE_LOCAL_ADMIN' or entitlement_value='System Administrator' or entitlement_value='PF_CORE_LOCAL_DATA_ADMIN')) OR '${requestor.id}' in (select us.userkey from savroles sv, user_savroles us where sv.rolekey=us.rolekey and sv.rolename in ('ROLE_ADMIN'))

[HichamElk]

Thank you, it works well