Role modification workflow

[trivi]

Trying to create role mdofication workflow where we need to get the role owner . Here am using the custom assignment as we dont have any hooks for role management workflows.

I have tried using the 

select ro.userkey from role_owners ro,roles r where ro.rolekey=r.rolekey and r.role_name=${requestaccessobj.id}

This is not working it throws SQL error. How do we enable the role owner approval for role modification requests.

[rushikeshvartak]

select userkey from users where username in
(SELECT SUBSTRING_INDEX(newvalue, ' ', 1) FROM roles_historychangelog rhcl, ars_requests ar, request_access ra
WHERE ar.REQUESTKEY =ra.REQUESTKEY and ar.requestkey=${ARSREQUEST.id} and rhcl.FIELDNAME in ('OWNER','Role Owner')
and rhcl.ROLEHISTORYKEY=ra.accesskey and ra.accesstype = 1
and ra.requesttype = 3 and substring_index(substring_index(NEWVALUE, ' ', -1),']', 1) = 1) UNION select userkey from role_owners where rank = 1 and rolekey in
(select distinct r.rolekey from roles_historychangelog rhcl, ars_requests ar, request_access ra,roles r
WHERE ar.REQUESTKEY =ra.REQUESTKEY and ar.requestkey=${ARSREQUEST.id} and rhcl.ROLEHISTORYKEY=ra.accesskey and ra.accesstype = 1
and ra.requesttype = 3 and r.ROLEKEY = rhcl.ROLEKEY)

[trivi]

1. wondering why would be need union query to get the role owner from roles.

2.here it seems it is  retrieving the owner with rank 1 only.

3.i dont see this roles_historychangelog table in DA...where did you find the table schema ?

4.Does modifying the roles is also treated as ars_requests?

[sk]

@trivi : Did you try the query I have shared? In case if you are planning to pull only rank1 owner then include rank condition as well

[trivi]

nope its not working .

[rushikeshvartak]

• Union - it will get current selected owner as well as existing owners . In case of new role one union will return results
• yes we are considering rank 1 , you can change condition as per your needs
• you can check from analytics
• yes its treated in ars requests

[trivi]

This works fine. Now that the requirement changed , we need to check the role owner existence based on which the flow is redirected

have tried role.ownerrank1() and also role.getownerrank1()

these are not working

[rushikeshvartak · Accepted solution]

So if role owner not exists then whats will be flow ?

below condition if role owner and requestor same then auto approve

role.getOwnerRank1().contains(user.username) eq true

[trivi]

can't we directly check the roleowenerslist.size()? do we need to check specifically for the rank1 and rank2 ??

[rushikeshvartak · Accepted solution]

We can check

[sk]

@trivi : Try below

select ro.userkey from role_owners ro where  ro.rolekey=${requestaccessobj.id}

[Manu269]

@trivi Did you check Workflow Components (saviyntcloud.com)

 select userkey from role_owners where rolekey=${REQUESTACCESSOBJ.id} and rank = 1