Filter selectable application roles based on the selected user's attribute

[Sampsa]

Hi,

I would like to automaticall filter the selectable application roles for an endpoint based on the roles custom property and a attribute of the user who I'm requesting for. I tried setting a request query in the role type for that endpoint but I don't know what variable I can use to refer to the selected user.

This is what I currenlty have, but it doesn't work:

r.customproperty1 is null or r.customproperty1 = '' or r.customproperty1 = (select users.country from Users users where users.country is not null and users.id=$requestee)

I also tried ${requestee}, {requestee}, {currentUser}, ${user}, $user but none of these work.

Br,

Sampsa

[Amit_Malik]

HI @Sampsa , hope you have also tried users.userkey instead users.id if not try that. Referring below samples (I know yours is role but users table syntax will remain the same and below has some samples)

Config For Requestable Entitlement In ARS

Specify the query based on the logged-in user or the user(s) for which request is being placed respectively to limit the display of the entitlements in Available Entitlements. You can use the ‘requestor’ and ‘requestee’ objects in the query to filter the entitlements to be displayed in the Available Entitlements. For Example. · Add the below query to display the entitlement in the request form, when the value specified in customproperty1 is selected from dynamicAttribute1. ev.customproperty1='$(<dynamicAttribute1>)' · Filter entitlements based on the user(s) for whom the request is being placed. ev.customproperty1 in (select city from Users where userkey in (${requestee})) · Filter entitlements based on the requestor. ev.customproperty1 in (select city from Users where userkey in (${requestor})) Note In the above syntax, the variables and table names used are case-sensitive.

[Sampsa]

Hi @Amit_Malik 

I tried that also. That documentation you shared is for Entitlements, not roles. I tried it anyway and I'm getting the error below. It does not understand the curly braces.

"2024-02-13T14:03:37.726+00:00","ecm","","null-lbhz9","","2024-02-13T14:03:37.023835299Z stdout F org.codehaus.groovy.grails.web.errors.GrailsWrappedRuntimeException: unexpected char: '{' [select r from com.saviynt.ecm.identitywarehouse.domain.Roles r where (r.status=1 or r.id in (select rh.rolekey from com.saviynt.ecm.identitywarehouse.domain.Roles_History rh where rh.versionstatus=1)) and r.requestable=true and r.roletype=5 and r.endpointkey=32 and r.id not in(0) and r.id not in(-1) and (r.customproperty1 is null or r.customproperty1 = '' or r.customproperty1 = (select users.country from com.saviynt.ecm.identitywarehouse.domain.Users users where users.country is not null and users.userkey in (${requestee}))) order by r.role_name asc]"

[SumathiSomala]

@Sampsa try below

and rl.customproperty1 = (select users.country from Users users where users.id={currentUser})

and rl.customproperty1 in (select users.country from Users users where users.id={currentUser})

Hope rl.customproperty1 contains user's country.

Solved: Roles Request Query is not working - Saviynt Forums - 57131

[Amit_Malik]

Yeah , a fresdesk ticket would be best option I think. Saviynt doc for role query is https://docs.saviyntcloud.com/bundle/KBAs/page/Content/Use-case-of-Request-Roles-Query.htm which has #23.x mentioned but don't think that also working.

If we hard code r.customproperty1 is null or r.customproperty1 = '' or r.customproperty1 = (select users.country from Users users where users.country is not null and users.id=3 , it will work but is not taking any variable. If wont use $ then it is failing as sql exception and if use $ then no such property.

One option that could work is to have dynamic attribute and use requestee in it and use that in role query.

Thanks, Amit

[Amit_Malik · Accepted solution]

@Sampsa , I hope the dynamic attribute option worked. I just tested it and it is working.

DA query : select country as ID from users where userkey=${requestor}

Role Filter Query : r.customproperty1 = '${RoleFilter}'

And for your original ask, a freshdesk ticket would be best. Hope this will get you going for now.

Thanks,

Amit

[Raghu]

@Sampsa 

try below:

just added in b/w select 'as ID'

rl.customproperty1 = (select users.country as ID from Users users where users.id={currentUser})

or

rl.customproperty1 in (select users.country as ID from Users users where users.id={currentUser})

or

r.customproperty1 = (select users.country as ID from Users users where users.country is not null and users.id=${requestee})

or

r.customproperty1 = (select users.country as ID from Users users where users.id=${requestee})

[rushikeshvartak]

Create hidden dynamic attribute and use that in role type query

[Sampsa]

Thanks all for your help. I was able to make it work using a dynamic attribute like @Amit_Malik suggested.

Br,

Sampsa

[Sampsa]

I need to take that back a little bit. It does not work if the dynamic attribute is hidden. If the attriute is not hidden then it does not work when first entering the select access screen when requesting access for that endpoint. But if I go back and then enter that screen again, then it works.

I can see in the logs that when I first enter the select access page then the filtering query contains the dynamic attribute query as well and not the result of that. Below is the log. I marked the dynamic attribute query as bold.

"2024-02-15T06:46:38.257+00:00","ecm","","null-lbhz9","","2024-02-15T06:46:37.889225735Z stdout F 2024-02-15 06:46:37,889 [http-nio-8080-exec-194] DEBUG services.WorkflownewuiService - listQry:select r from Roles r where (r.status=1 or r.id in (select rh.rolekey from Roles_History rh where rh.versionstatus=1)) and r.requestable=true and r.roletype=5 and r.endpointkey=32 and r.id not in(0) and r.id not in(-1) and (r.customproperty1 is null or r.customproperty1 = '' or r.customproperty1 = 'Select country as ID from users where userkey = ${requestee}') order by r.role_name asc"

Br,

Sampsa

[rushikeshvartak]

You need to log as defect id its working in one way