cancel
Showing results for 
Search instead for 
Did you mean: 

Filtering emergency access roles available for request

Sampo
New Contributor II
New Contributor II

Hi,

we have a requirement that user should be able to request pre-approval for emergency access roles. When a user has pre-approval for an emergency access role, then they can request the emergency access role for limited time with auto-approval.

We're considering the following design:

- For each emergency access role there is an enterprise role that grants pre-approval for the emergency access role. Emergency access role's custom property contains name of the enterprise role.

- When user requests an emergency access role system filters out any emergency access roles for which the user doesn't have pre-approval.

To achieve this we'd like to filter the roles that user can see in Emergency Access -> Request Emergency Access Roles. This seems to work with the following query in Global Config -> Role Request -> Request Role Query:

and ((rl.roletype != 3) or (rl.customproperty1 in (select r.role_name from Roles r where r.roletype = 4 and r.customproperty1 = 'PRE_APPROVAL' and r.id in (select ruax.rolekey.id from Role_user_account ruax where ruax.userkey.id = {currentUser})) and rl.roletype = 3))

But unfortunately this HQL query breaks other ARS requests forms, for example My Access -> Request New Access and ARS is unable to show list of available applications and enterprise roles. The following error appears in logs:

{"log":"java.lang.IllegalArgumentException: org.hibernate.hql.internal.ast.QuerySyntaxException: Role_user_account is not mapped [Select rl.id from com.saviynt.ssm.entity.Roles rl where 1=1 and ((rl.roletype != 3) or (rl.customproperty1 in (select r.role_name from com.saviynt.ssm.entity.Roles r where r.roletype = 4 and r.customproperty1 = 'PRE_APPROVAL' and r.id in (select ruax.rolekey.id from Role_user_account ruax where ruax.userkey.id = 847)) and rl.roletype = 3))]\n","stream":"stdout","time":"2022-11-23T23:21:16.955913557Z"}

So it seems that the Role_user_account is mapped in Hibernate configuration that's used when requesting emergency access roles, but not when doing access requests for applications and enterprise roles.

Can you think of other ways to achieve the same end result? One option would be to store pre-approval information in user custom properties, but this is not our preferred option since number of emergency access roles could be big.

Is there any documentation available that lists which Saviynt tables can be used when writing HQL query for Global Config -> Role Request -> Request Role Query?

best regards,

Sampo

17 REPLIES 17

rushikeshvartak
All-Star
All-Star

Request New Access why role query will execute ? Emergency role should be visible from emergency tile

Sampo
New Contributor II
New Contributor II

Yes, emergency roles can be requested from "Request Emergency Access Roles" tile, but it looks like Saviynt also runs the same role query when requesting access from "Request New Access" or "Request Access for Others", probably to filter enterprise roles that should be visible for the user.

Role_user_account is not exposed under request for access self / others

Hi @rushikeshvartak 
We are also trying to filter emergency access roles using this query.

 

We want that the user should be able to request only those emergency access roles for which endpoint they already have an account.

I mean, if a user has only 1 account in Azure endpoint, and there are 2 emergency access roles in saviynt , one for azure endpoint, and other role for SAP.. so currently when the user goes to ARS>>EMergency access role request, he sees both the roles.

 

So we configured this query 

 

and ((rl.roletype != 3) or (rl.endpointkey in (select endpointkey from accounts where accountkey in (select accountkey from user_accounts where userkey={currentUser})) and rl.roletype=3))

But now the user is unable to see any role in request page.

haardik_verma_0-1672147031359.png

What are we missing here, can you please suggest

Does same query works in data analyzer 

Yes.

I used the below query in Data Analyzer:

select rl.role_name,rl.endpointkey from roles rl where rl.endpointkey in (select endpointkey from accounts where accountkey in (select accountkey from user_accounts where userkey=**inserted my userkey**))

which did return only those emergency access roles that had endpoints based on the accounts that my user has.

So we went ahead and used below query in Global Config:

and rl.endpointkey in (select endpointkey from accounts where accountkey in (select accountkey from user_accounts where userkey={currentUser})) and rl.roletype=3

but after adding this, we are unable to see any roles while requesting from emergency access request tile

Since you are using multiple inner query currentuser must not be evaluated. Do you see error in logs

Getting the below in logs when trying to request for user with userkey 8014

 

2022-12-29 07:48:30,567 [https-jsse-nio-443-exec-84] ERROR errors.GrailsExceptionResolver - QuerySyntaxException occurred when processing request: [POST] /ECM/roles/requestrolesjson
accounts is not mapped [Select new map( COALESCE(rl.displayname,rl.role_name) as tr,rl.id as id,rl.glossary as glossary,rl.displayname as displayname,rl.role_name as role_name, rl.description as description) from com.saviynt.ecm.identitywarehouse.domain.Roles rl where rl.requestable = true and rl.id not in (select rua.rolekey from com.saviynt.ecm.identitywarehouse.domain.Role_user_account rua where rua.userkey =8014 ) and ((select count(*) from com.saviynt.ecm.identitywarehouse.domain.Role_entitlements re where re.rolekey.id=rl.id )=0 or
(select count(*) from com.saviynt.ecm.identitywarehouse.domain.Role_entitlements re where re.rolekey.id=rl.id and re.entitlement_valuekey.entitlementtypekey.endpointkey.status<>0)>0) and rl.requestable=true and rl.roletype=3 and(rl.status=1 or rl.id in (select rh.rolekey from com.saviynt.ecm.identitywarehouse.domain.Roles_History rh where rh.versionstatus=1)) and rl.endpointkey in (select ac.endpointkey from accounts ac where ac.accountkey in (select ua.accountkey from user_accounts ua where ua.userkey=8014)) order by 1 asc]

 

2022-12-29 07:48:30,571 [https-jsse-nio-443-exec-84] ERROR error.ErrorController - Exception
org.*********************.GrailsWrappedRuntimeException: accounts is not mapped [Select new map( COALESCE(rl.displayname,rl.role_name) as tr,rl.id as id,rl.glossary as glossary,rl.displayname as displayname,rl.role_name as role_name, rl.description as description) from com.saviynt.ecm.identitywarehouse.domain.Roles rl where rl.requestable = true and rl.id not in (select rua.rolekey from com.saviynt.ecm.identitywarehouse.domain.Role_user_account rua where rua.userkey =8014 ) and ((select count(*) from com.saviynt.ecm.identitywarehouse.domain.Role_entitlements re where re.rolekey.id=rl.id )=0 or
(select count(*) from com.saviynt.ecm.identitywarehouse.domain.Role_entitlements re where re.rolekey.id=rl.id and re.entitlement_valuekey.entitlementtypekey.endpointkey.status<>0)>0) and rl.requestable=true and rl.roletype=3 and(rl.status=1 or rl.id in (select rh.rolekey from com.saviynt.ecm.identitywarehouse.domain.Roles_History rh where rh.versionstatus=1)) and rl.endpointkey in (select ac.endpointkey from accounts ac where ac.accountkey in (select ua.accountkey from user_accounts ua where ua.userkey=8014)) order by 1 asc]

 

Does account is mapped to user you are trying?

does query from logs working in data analyzer ( remove com.)

Yes, The account is mapped to the user we are testing on.

are you saying to try this query in Data Analyzer >>

Select new map( COALESCE(rl.displayname,rl.role_name) as tr,rl.id as id,rl.glossary as glossary,rl.displayname as displayname,rl.role_name as role_name, rl.description as description) from com.saviynt.ecm.identitywarehouse.domain.Roles rl where rl.requestable = true and rl.id not in (select rua.rolekey from com.saviynt.ecm.identitywarehouse.domain.Role_user_account rua where rua.userkey =8014 ) and ((select count(*) from com.saviynt.ecm.identitywarehouse.domain.Role_entitlements re where re.rolekey.id=rl.id )=0 or
(select count(*) from com.saviynt.ecm.identitywarehouse.domain.Role_entitlements re where re.rolekey.id=rl.id and re.entitlement_valuekey.entitlementtypekey.endpointkey.status<>0)>0) and rl.requestable=true and rl.roletype=3 and(rl.status=1 or rl.id in (select rh.rolekey from com.saviynt.ecm.identitywarehouse.domain.Roles_History rh where rh.versionstatus=1)) and rl.endpointkey in (select ac.endpointkey from accounts ac where ac.accountkey in (select ua.accountkey from user_accounts ua where ua.userkey=8014)) order by 1 asc

Yes also remove new map keep rl.displayname

Tried this query after removing map, but showing many syntax errors

 

Select rl.displayname as displayname
from Roles rl
where
rl.requestable = true
and
rl.id not in (select rua.rolekey from Role_user_account rua where rua.userkey =8014)
and
(
(select count(*) from Role_entitlements re where re.rolekey.id=rl.id)=0 or (select count(*) from Role_entitlements re where re.rolekey.id=rl.id and re.entitlement_valuekey.entitlementtypekey.endpointkey.status <> 0)
>0)
and
rl.requestable=true
and
rl.roletype=3
and
(
rl.status=1
or
rl.id in (select rh.rolekey from Roles_History rh where rh.versionstatus=1)
)
and
rl.endpointkey in (select ac.endpointkey from accounts ac where ac.accountkey in (select ua.accountkey from user_accounts ua where ua.userkey=8014))
order by 1 asc

 

But also, as we got this query from logs, and this query has 8014, so I think we can say that {currentUser} is working, as it properly took userkey 8014 of the request beneficiary

In your query try with class name for table 

Sampo
New Contributor II
New Contributor II

Hi Haardik_verma,

the error message says: accounts is not mapped

You could try to change your query to use Accounts instead of accounts and User_accounts instead of user_accounts, as HQL class names are case-sensitive. Some of the HQL class names are documented here: https://saviynt.freshdesk.com/support/solutions/articles/43000616285-obtaining-the-attribute-details...

best regards,

Sampo

haardik_verma
New Contributor III
New Contributor III

Hi @rushikeshvartak @Sampo 

Tried these in Global config

1) and rl.endpointkey in (select ac.endpointkey from Accounts ac where ac.accountkey in (select ua.accountkey from User_accounts ua where ua.userkey={currentUser}))

2) and rl.endpointkey in (select ac.endpointkey from Accounts ac where ac.accountkey.id in (select ua.accountkey from User_accounts ua where ua.userkey.id={currentUser}))

3) and rl.endpointkey in (select ac.endpointkey from Accounts ac where ac.id in (select ua.accountkey from User_accounts ua where ua.id={currentUser}))

None of them worked.

Still showing 0 results in request emergency access page.

And for some reason, there is no error or any track of the request in the logs

Use saviynt class name

@rushikeshvartak Do you mean this?

and rl.endpointkey in (select ac.endpointkey from accounts ac where ac.id in (select ua.accountkey from user_accounts ua where ua.id={currentUser}))


Tried this too, but did not work