Workflow condition to check logged in user is in a group

[Manju]

Hi Team, 

Workflow should allow  the privilege access request for auto approval if the user is from particular usergroup, else request should go for one level manager approval. 

Tried below  query, but it is giving error as the list contains multiple users from usergroup .  Please suggest the right approach.  

${ARSREQUEST.REQUESTOR}= select users.username from users where users.username is not null and users.userkey in (select userkey from usergroup_users where USER_GROUPKEY='2'

Thanks, 

Manju

[Saathvik]

Use below condition in if/else block with groovy as expression language if outcome of this block is true then requestor is member of user group otherwise not

(com.saviynt.ecm.identitywarehouse.domain.Usergroup_users.executeQuery("select ugu.id from Usergroup_users ugu where ugu.usergroupkey.id = 2 and ugu.userkey.id= requestedby?.id ")?.size() != 0)

[Manju]

Hi Saathvik, 

Tried the above suggested query. Request ID is not getting generated.  With &quote entry in condition, workflow approval is not successful

Regards, 

Manjunatha V

[Saathvik]

@Manju : Can you provide the respective logs from server to understand the issue better where it is failing?

[Manju]

Hi Saathvik,   Here comes the log entries related to request. 

2023-03-28T20:46:41+05:30-arsms-{"log":"2023-03-28 15:16:40.580 DEBUG [] 7 --- [HikariPool-1 housekeeper] com.zaxxer.hikari.pool.HikariPool : HikariPool-1 - Pool stats (total=25, active=0, idle=25, waiting=0) \n","stream":"stdout","time":"2023-03-28T15:16:40.580806544Z"}

2023-03-28T20:46:40+05:30-windows-connectorms-{"log":"15:16:40 [Debug] Quartz.Core.QuartzSchedulerThread Batch acquisition of 0 triggers\n","stream":"stdout","time":"2023-03-28T15:16:40.104761456Z"}

Regards, 

Manju

[Saathvik]

Try below I don't in your screenshot " are added in subquery so please try below

(com.saviynt.ecm.identitywarehouse.domain.Usergroup_users.executeQuery("select ugu.id from Usergroup_users ugu where ugu.usergroupkey.id = 2 and ugu.userkey.id= requestedby?.id")?.size() != 0)

[Manju]

Hi Saathvik, 

As implementation partner we do not have access to catapult.  ECM related detailed errors are not coming up in logviewer. For endusers, is there any option to change the debug level from application frontend to get detailed errors. 
Managed to fetch logs from catapult ECM logs. 
Looks like system is not accepting "and" condition in subquery.   

2023-03-31 14:16:46,705 [http-nio-8080-exec-92] ERROR services.WorkflowService - Exception in workflow service
org.jbpm.api.JbpmException: script evaluation error: javax.script.ScriptException: org.springframework.orm.hibernate3.HibernateQueryException: unexpected char: '&' [select ugu.id from com.saviynt.ecm.identitywarehouse.domain.Usergroup_users ugu where ugu.usergroupkey.id = 2 && ugu.userkey.id= requestedby?.id]; nested exception is org.hibernate.QueryException: unexpected char: '&' [select ugu.id from com.saviynt.ecm.identitywarehouse.domain.Usergroup_users ugu where ugu.usergroupkey.id = 2 && ugu.userkey.id= requestedby?.id]
at org.jbpm.pvm.internal.script.ScriptManager.evaluate(ScriptManager.java:127)
at org.jbpm.pvm.internal.script.ScriptManager.evaluate(ScriptManager.java:115)
at org.jbpm.pvm.internal.script.ScriptManager.evaluateExpression(ScriptManager.java:87)
at org.jbpm.pvm.internal.el.ScriptExpression.evaluateInScope(ScriptExpression.java:48)
at org.jbpm.pvm.internal.el.Expression.evaluate(Expression.java:108)
at org.jbpm.pvm.internal.model.ExpressionCondition.evaluate(ExpressionCondition.java:41)
at org.jbpm.jpdl.internal.activity.DecisionConditionActivity.findTransitionUsingConditions(DecisionConditionActivity.java:62)
at org.jbpm.jpdl.internal.activity.DecisionConditionActivity.execute(DecisionConditionActivity.java:47)
at org.jbpm.jpdl.internal.activity.DecisionConditionActivity.execute(DecisionConditionActivity.java:43)
at org.jbpm.pvm.internal.model.op.ExecuteActivity.perform(ExecuteActivity.java:60)
at org.jbpm.pvm.internal.model.ExecutionImpl.performAtomicOperationSync(ExecutionImpl.java:672)
at org.jbpm.pvm.internal.model.ExecutionImpl.fire(ExecutionImpl.java:582)
at org.jbpm.pvm.internal.model.ExecutionImpl.take(ExecutionImpl.java:487)
at org.jbpm.jpdl.internal.activity.ForEachActivity.execute(ForEachActivity.java:123)
at org.jbpm.jpdl.internal.activity.ForEachActivity.execute(ForEachActivity.java:53)
at org.jbpm.pvm.internal.model.op.ExecuteActivity.perform(ExecuteActivity.java:60)
at org.jbpm.pvm.internal.model.ExecutionImpl.performAtomicOperationSync(ExecutionImpl.java:672)
at org.jbpm.pvm.internal.model.ExecutionImpl.performAtomicOperation(ExecutionImpl.java:632)
at org.jbpm.pvm.internal.model.ExecutionImpl.start(ExecutionImpl.java:217)
at org.jbpm.pvm.internal.cmd.StartProcessInstanceInLatestCmd.execute(StartProcessInstanceInLatestCmd.java:63)
at org.jbpm.pvm.internal.cmd.StartProcessInstanceInLatestCmd.execute(StartProcessInstanceInLatestCmd.java:36)
at org.jbpm.pvm.internal.svc.DefaultCommandService.execute(DefaultCommandService.java:42)
at org.jbpm.pvm.internal.tx.SpringCommandCallback.doInTransaction(SpringCommandCallback.java:45)
at org.jbpm.pvm.internal.tx.SpringTransactionInterceptor.execute(SpringTransactionInterceptor.java:49)
at org.jbpm.pvm.internal.svc.EnvironmentInterceptor.executeInNewEnvironment(EnvironmentInterceptor.java:53)
at org.jbpm.pvm.internal.svc.EnvironmentInterceptor.execute(EnvironmentInterceptor.java:40)
at org.jbpm.pvm.internal.svc.RetryInterceptor.execute(RetryInterceptor.java:56)
at org.jbpm.pvm.internal.svc.ExecutionServiceImpl.startProcessInstanceByKey(ExecutionServiceImpl.java:71)
at com.saviynt.ecm.services.WorkflowService.workflowaccessreqStart(WorkflowService.groovy:1057)
at com.saviynt.ws.JbpmapiService.workflowaccessreqStartMS(JbpmapiService.groovy:64)
at com.saviynt.ecm.ws.JbpmapiController$_closure4.doCall(JbpmapiController.groovy:83)
at grails.plugin.springsecurity.rest.RestTokenValidationFilter.processFilterChain(RestTokenValidationFilter.groovy:118)
at grails.plugin.springsecurity.rest.RestTokenValidationFilter.doFilter(RestTokenValidationFilter.groovy:84)
at grails.plugin.springsecurity.web.filter.GrailsAnonymousAuthenticationFilter.doFilter(GrailsAnonymousAuthenticationFilter.java:53)
at com.saviynt.webservice.SaviyntRestAuthenticationFilter.doFilter(SaviyntRestAuthenticationFilter.groovy:133)
at grails.plugin.springsecurity.web.authentication.logout.MutableLogoutFilter.doFilter(MutableLogoutFilter.java:62)
at grails.plugin.springsecurity.web.SecurityRequestHolderFilter.doFilter(SecurityRequestHolderFilter.java:59)
at com.mrhaki.grails.plugin.xframeoptions.web.XFrameOptionsFilter.doFilterInternal(XFrameOptionsFilter.java:69)
at com.brandseye.cors.CorsFilter.doFilter(CorsFilter.java:82)
at java.lang.Thread.run(Thread.java:750)
Caused by: javax.script.ScriptException: javax.script.ScriptException: org.springframework.orm.hibernate3.HibernateQueryException: unexpected char: '&' [select ugu.id from com.saviynt.ecm.identitywarehouse.domain.Usergroup_users ugu where ugu.usergroupkey.id = 2 && ugu.userkey.id= requestedby?.id]; nested exception is org.hibernate.QueryException: unexpected char: '&' [select ugu.id from com.saviynt.ecm.identitywarehouse.domain.Usergroup_users ugu where ugu.usergroupkey.id = 2 && ugu.userkey.id= requestedby?.id]
at org.codehaus.groovy.jsr223.GroovyScriptEngineImpl.eval(GroovyScriptEngineImpl.java:152)
at javax.script.AbstractScriptEngine.eval(AbstractScriptEngine.java:264)
at org.jbpm.pvm.internal.script.ScriptManager.evaluate(ScriptManager.java:123)
... 39 more
Caused by: javax.script.ScriptException: org.springframework.orm.hibernate3.HibernateQueryException: unexpected char: '&' [select ugu.id from com.saviynt.ecm.identitywarehouse.domain.Usergroup_users ugu where ugu.usergroupkey.id = 2 && ugu.userkey.id= requestedby?.id]; nested exception is org.hibernate.QueryException: unexpected char: '&' [select ugu.id from com.saviynt.ecm.identitywarehouse.domain.Usergroup_users ugu where ugu.usergroupkey.id = 2 && ugu.userkey.id= requestedby?.id]
at org.codehaus.groovy.jsr223.GroovyScriptEngineImpl.eval(GroovyScriptEngineImpl.java:349)
at org.codehaus.groovy.jsr223.GroovyScriptEngineImpl.eval(GroovyScriptEngineImpl.java:146)
... 41 more
Caused by: org.springframework.orm.hibernate3.HibernateQueryException: unexpected char: '&' [select ugu.id from com.saviynt.ecm.identitywarehouse.domain.Usergroup_users ugu where ugu.usergroupkey.id = 2 && ugu.userkey.id= requestedby?.id]; nested exception is org.hibernate.QueryException: unexpected char: '&' [select ugu.id from com.saviynt.ecm.identitywarehouse.domain.Usergroup_users ugu where ugu.usergroupkey.id = 2 && ugu.userkey.id= requestedby?.id]
at Script11.run(Script11.groovy:1)
at org.codehaus.groovy.jsr223.GroovyScriptEngineImpl.eval(GroovyScriptEngineImpl.java:346)
... 42 more
Caused by: org.hibernate.QueryException: unexpected char: '&' [select ugu.id from com.saviynt.ecm.identitywarehouse.domain.Usergroup_users ugu where ugu.usergroupkey.id = 2 && ugu.userkey.id= requestedby?.id]
... 44 more
2023-03-31 14:16:46,706 [http-nio-8080-exec-92] DEBUG println.PrintlnToLogger - Println :: | Error org.jbpm.api.JbpmException: script evaluation error: javax.script.ScriptException: org.springframework.orm.hibernate3.HibernateQueryException: unexpected char: '&' [select ugu.id from com.saviynt.ecm.identitywarehouse.domain.Usergroup_users ugu where ugu.usergroupkey.id = 2 && ugu.userkey.id= requestedby?.id]; nested exception is org.hibernate.QueryException: unexpected char: '&' [select ugu.id from com.saviynt.ecm.identitywarehouse.domain.Usergroup_users ugu where ugu.usergroupkey.id = 2 && ugu.userkey.id= requestedby?.id]

Thanks, 

Manju

[suresh_ravuri]

Our client has a similar requirement, is it working ?

[Nidhins27]

Please share the solution if found.

[naveenss]

please use the below query format

(com.saviynt.ecm.identitywarehouse.domain.Usergroup_users.executeQuery("select ugu.id from Usergroup_users ugu where ugu.user_groupkey = 2 AND ugu.userkey= '${requestedby?.id}'")?.size() != 0)

[NageshK]

@naveenss Thank you for the query. I have validated this in the latest version 23.6 and it is working well. Just one thing to note is that we have to make sure the expression language is selected as groovy in the workflow for this condition. Else workflow evaluation will fail. 

@Manju @suresh_ravuri please make a note of that

Thanks,

Nagesh K

[suresh_ravuri]

Thank you @NageshK for your response.

We are in a plan to implement manager approval workflow for JIT access request, How can we implement this manager workflow for a specific access method ? 

[suresh_ravuri]

Hi @NageshK 

Is it possible to enable manager's approval workflow only for JIT requests ?

[Saathvik]

@suresh_ravuri : Can you please elaborate the requirement like what type of target/endpoint? Because we are able to check the JIT account condition for different purpose for that we are able to build a logic wanted to check if that will fit for your requirement

Also other JIT Do you have credential and credentialless accounts on same endpoint?

[suresh_ravuri]

We are planning to configure JIT account access method for Windows, Unix and MSSQL endpoints. 

Users are configured with credentials and credential-less access methods on the same endpoint

Use-case: For any user who attempts to create a JIT account request to Windows/Unix/MSSQL endpoints, it should trigger the manager's approval workflow and for other access methods, it should be auto-approval

[Saathvik]

@suresh_ravuri : Thanks for providing more information. Since you have credential-less accounts as well along with JIT, It would be tricky to differentiate those two type of requests. Can you share the sample output for below three queries for both JIT type request and credential-less request 

1. select * from ars_requests where jbpmprocessinstanceid like '%<request_number>%';

2. select * from request_access where requestkey in (select requestkey from ars_requests where jbpmprocessinstanceid like '%<request_number>%');

3. select * from request_access_attrs where request_access_key in (select request_accesskey from request_access where requestkey in (select requestkey from ars_requests where jbpmprocessinstanceid like '%<request_number>%'));

[NageshK]

@suresh_ravuri @Saathvik the only difference between JIT and non-JIT is the account config in accounts object. Here is the workflow I tried in 23.7 and it worked. It should work in older versions too. Please try and let me know if it worked.