HQL Query is not working on User Account Table in SAV Role Advance config

[rahulyadav]

Hello,

We want to configure SAV Role for Application Owner, it should only show  User having that particular application access, for that we are configuring in Whom to request -> Request for self and user retrieved for advance filter.

We have followed this previous link on forum it says user_account table is not mapped with HQL, but this is known issue before one year, so please confirm issue still persist or it is resolved.

https://forums.saviynt.com/t5/identity-governance/whom-to-request-advanced-filter-is-not-fetching-th...

We have also tried following HQL queries, it says query is incorrect also for some query it accepts and shows error when we view current access after login with user with SAV ROLE.

Query : select distinct us.userkey from User_accounts us, Accounts cc, Endpoints ep where us.accountkey=cc.ACCOUNTKEY and cc.endpointkey =ep.ENDPOINTKEY and ep.endpointname='AD_GoutamAhirwar'

SELECT u FROM users u JOIN user_accounts ua ON u.userkey = ua.userkey JOIN accounts a ON ua.accountkey = a.accountkey Where accounts.endpointkey = '849'

select distinct us.userkey from User_accounts us, Accounts cc, Endpoints ep where us.accountkey=cc.id and cc.endpointkey =ep.id and ep.endpointname='AD_GoutamAhirwar'

Thank you!

[Raghu]

@rahulyadav  try below

SELECT u FROM users u JOIN user_accounts ua ON u.userkey = ua.userkey JOIN accounts a ON ua.accountkey = a.accountkey Where a.endpointkey in ('849')
or

SELECT u FROM users u JOIN user_accounts ua ON u.userkey = ua.userkey JOIN accounts a ON ua.accountkey = a.accountkey Where a.id in ('849')

if not working please try use json

https://forums.saviynt.com/t5/identity-governance/restricting-the-quot-whom-to-request-quot-using-a-...

[naveenss]

Hi @rahulyadav can you try below query?

select u FROM Users u,User_accounts us,Accounts cc,Endpoints ep WHERE u.id=us.userkey AND us.accountkey=cc.id AND cc.endpointkey.id =ep.id and ep.endpointname='ABCD'

 Please update the endpoint name to your endpoint.

[rahulyadav]

It showing error

[naveenss]

Hi @rahulyadav set the query as a JSON

[{"for":"RequestAccessForOthers,ViewExistingAccess","query":"select a FROM Users a,User_accounts us,Accounts cc,Endpoints ep WHERE a.id=us.userkey AND us.accountkey=cc.id AND cc.endpointkey.id =ep.id and ep.endpointname='ABCD'"}]

[rushikeshvartak]

Share sav role config screenshot