Application Roles Request Query based on entitlement access

[nvachhani]

Is there a way to limit application role request based on an entitlement the user's account has? For example, only members of a certain AD group can request an application role. Not sure if the system supports other tables/joins in role request query besides role and users table. If so, are there any examples of this as the following is not accepted, seeing "Enter the data in a valid format" error:

and rl.customproperty2 in (SELECT
ae.ENTITLEMENT_VALUEKEY
FROM
accounts a
join user_accounts ua ON ua.accountkey = a.accountkey
join users u ON u.userkey = ua.userkey
join account_entitlements1 ae ON ae.accountkey = a.accountkey
where
ae.ENTITLEMENT_VALUEKEY=1234 AND u.id={currentUser})

If this is not possible with role request query is there another way to achieve this? 

[RakeshMG]

Please let us know where are using adding this query.

Can you please try adding your query in : 

Global Config--> Role Request-->Settings

Was able to save your query, but not tested end to end.

[nvachhani]

If this is not possible with role request query is there another way to achieve this? 

[nvachhani]

I am saving it at the endpoint level, Role Type>Edit Role Type>Request Query

I am seeing the same results for the Role Request Query in Global Config,

[naveenss]

Can you try the below query in the role request query?

and rl.customproperty2 in (select ae1.entitlement_valuekey.id from Accounts a,User_accounts ua,Users u,Account_entitlements1 ae1 where ua.accountkey=a.id and u.id=ua.userkey and ae1.accountkey.id=a.id and ae1.entitlement_valuekey.id=1234 and u.id={currentUser})

[nvachhani · Accepted solution]

I have come to the conclusion that this type of query will not work in the Role Request Query at endpoint level perhaps due to HQL constraints. I have gotten around this by using a dynamic attribute Single Select SQL query:

SELECT ev.DISPLAYNAME as ID FROM accounts a join user_accounts ua ON ua.accountkey = a.accountkey join users u ON u.userkey = ua.userkey join account_entitlements1 ae ON ae.accountkey = a.accountkey join entitlement_values ev ON ev.entitlement_valuekey = ae.entitlement_valuekey where ae.ENTITLEMENT_VALUEKEY = 1234 AND u.userkey = ${requestor}

Then using the following as a role request query:

r.customproperty1 = '${dynamic_attr_name}'