Repair Role retrofit to User Mapping Feature not updating the rolekey in accounts_entitlements table

[sreehariv]

1. Uploaded Role Definition from csv : Role is created in Saviynt
Below tables are updated:

roles - record created with rolekey

2. Uploaded role to entitlement association from csv : Entitlement is added to the role
Below tables are updated:

roles - there is no entitlement value key populated in roles table
role_entitlments - record inserted with rolekey and entitlementvaluekey

3. Uploaded role to user association from csv : user is shown under Users tab for role and there is no add access task created since the user is already having the entitlement acces before the role to user mapping import
Below tables are updated:

Role_user_account - record inserted with rolekey and userkey
account_entitlments1 - verified for user has already entitlement in PeopleSoft - there is no rolekey populated for Assignedfromrole column

Clicked on repair Role retrofit to User Mapping button (displayed on role details page)to ensure that all the role to entitlement and role to user details populated correctly in tables.

It shows repair option and after clicking on it sometime later we don't see any update  in tables.
still there is no entitlement value key populated in roles table and there is no rolekey populated for Assignedfromrole and Assignedfromroles column in accounts_entitlments1 table.

Tried executing the retrofit job for the same role but still there is no change.

When we tried removing the the same role from user (via user update rule - deprovsion role action),
It created One remove access task with new and it can be seen in Pending Task list page and also created another remove access task for the same access with No action required and can be seen in completed task list. (Note: At this moment there is no rolekey populated for Assignedfromrole and Assignedfromroles column in accounts_entitlments1 for the same user acocunt and entitlment)

so we have updated the assignedfromrole and assignedfromroles columns in account_entitlments1 table explicitly by custom query job and it updated the rolekey.

Then we tried removing the role for the user and it created two remove access tasks for the same entitlement and once the wsretry ran, they both got completed with out any errors. Ideally it should create only one remove access task but created two for the same.

When we assign a role to the user via saviynt ARS, rolekey populated for Assignedfromrole and Assignedfromroles column and it created only one remove access task for the access with new status and can be seen in pending tasks list.

Please confirm on how should we update the role to user and role to entitlement mappings in DB tables mentioned above and provide your comments on this.

Thanks

Sreehari

[pmahalle]

Hi @sreehariv ,

Do not associate roles to user using CSV which will not retrofit the roles, so not add assignedfromrole and assignedfromroles  columns in account_entitlments1.

Instead of that, use bulk upload option using below steps.

1. Navigate to Request Home --> Request Access for Others - Multi Users -->Actions -->Bulk Upload Request.

2. Browse and attach the excel file

3. Select "What type of request do you want to upload?" : Access

4. Click Run Now.

Note: I have attached the sample file here, update with your details and don't change the format while saving.

[dgandhi]

Hi @pmahalle 

Are you saying that with above option, assignmedfromrole column will be updated without running the retrofit job?

[rushikeshvartak]

Yes as its access request using Multi user bulk

[pmahalle]

Hi @dgandhi ,

yes it is. In the backend it will create request itself and pending tasks for all the entitlements present in the role. If user already have those entitlements the n, task completed with No Action Required State.

[sreehariv]

Thank you @pmahalle , for the detailed information.

We will check and update here. 

Thanks

Sreehari

[rushikeshvartak]

Currently from Admin, Account to role mapping option is not working as expected, please use Request for others - Multi user tile from ARS.

Open Enhancement : https://ideas.saviynt.com/ideas/EIC-I-4841

[sreehariv]

Hi @pmahalle & @rushikeshvartak 

Can you please suggest on how to do it for enterprise role.

the sample document attached by @pmahalle consists of Application role related data.

How we will add the Endpoint name and Account Name in the bulk upload file since enterprise role can consists multiple entitlements from multiple endpoints

Thanks

Sreehari

[timchengappa]

Hi @sreehariv 

You should be able to use the "Upload Role Association" feature to achieve your use case...

Upon uploading and importing the .csv file successfully and based on the configurations you select at the time of role upload, the role must get created(If not already present in the system), the role must get assigned to the user, add access tasks must be created for the entitlements in the role(and in the csv file), If the entitlements are already assigned to the user's account, tasks with status "No Action Required" must get created and stay in the "Completed Tasks" tab. The 'ASSIGNEDFROMROLES' columns in the "account_entitlements1" table should also get populated with the respective role keys...

PFA sample file that I used and the screen below is the configuration I used at the time of "Upload Role Association".

Preview of my .csv file before confirming upload...

Tasks with "No Action Required" status that got created as part of the .csv file upload...

View of account_entitlements1 prior to and after uploading roles via"Upload Role Association" where ASSIGNEDFROMROLES were not populated...
Query: select ACCENTKEY, ACCOUNTKEY,  ARSTASKKEY, ASSIGNEDFROMCOMPROLE, ASSIGNEDFROMROLE, ASSIGNEDFROMROLES, ENTITLEMENT_VALUEKEY from account_entitlements1 where accountkey = 103088 and ENTITLEMENT_VALUEKEY in (748537, 748650, 749403)

Note: Tested in EIC 23.7
Tips: First test requesting for an enterprise role via ARS and ensure the tasks are getting created as expected. This is to ensure that
a) the role is set up correctly and is in 'Active' Status
b) the entitlements in the role are requestable and the entitlement type configuration(endpoint->Entitlement Type-> Request Option) is configured to create tasks(table in my case)

[rushikeshvartak]

Preview of my .cvf file before confirming upload...

Is this typo ?

Role association does not works properly from Admin for Enterprise roles.

https://ideas.saviynt.com/ideas/EIC-I-4841

[sreehariv]

Hello @timchengappa ,

We have tried with the sample file you attached and we have prepared and uploaded the data in the same format.

It created Add Access task with No action Required.

without executing any role retrofit feature, It updated AssignedFromRoles column with rolekey in account_entitlements1 table

But we are uploading Role Definition, Role to Entitlement and role to User Membership mappings separately through Upload Role Associations  option.

In this case it is not creating any add access task with no action required and it is not updating the role key in AssignedFromRole and AssignedFromRoles columns for account_entitlements1 table

We have tried the Retrofit role to User mappings Repair button feature and also the retrofit job. There is no update in account_entitlements1 table.

Ideally, the Retrofit feature should work in this case but it is not. 

We are using version V23.4 for Saviynt

Could you please suggest on the below use cases as it works for single file with all the data in it

1. Suppose if more than 100 users have access to the same role then it makes the file complex to prepare the data and upload into system if we use the file suggested by you (it consists all the data Role definition, Entitlement detail and Role Users ).

It becomes too much complex and time taking process for filling all the same details for those 100 or more than 100 records what ever.

2. What If the Enterprise role consists of multiple entitlements from multiple endpoints and has several  users have access to that role.  In that case for each user are we going to  fill the same data. It increases no.of records in the file.

3. If I want to Modify the Entitlements or Users for a particular role in future, do we have to fill all the information? I think sending the Role name and the entitlement information / User related information would be sufficient I believe. But as per the sample file given, for every modification we need to fill the complete data which will again make the file complex and time consuming process.

So even if we upload the data just as below

1. Role Definition

2. Role to entitlement Composition

3. Role to User Membership 

and we can run retrofit job and this should work as expected but currently it is not working as per the above observations.

Please provide your comments/views on this.

thanks

Sreehari

[sreehariv]

Hello @timchengappa ,

we have verified the logs during the retrofit job execution and below few things.

Saviynt is using the below query to get the count of no of records to be processed through retrofit job.

"select count(rua.id) as count from role_user_Account rua, accounts a, users u, endpoints e where e.endpointkey=a.endpointkey and u.userkey=rua.userkey and a.accountkey=rua.accountkey and u.statuskey=1 and a.status = 'SUSPENDED FROM IMPORT SERVICE' and rua.rolekey in (XXXX)"

the above query gives 0 records because the role_user_account table is not populated with accountkey  after the role to user membership import hence a.accountkey=rua.accountkey will fail and also  a.status ='SUSPENDED FROM IMPORT SERVICE' this should be a.status=1 as the user's account status is active.

so with all the above conditions mismatch it will give 0 records and hence the roleToRuaFixCountMap is empty.

 ruacorrection responseMap :: [roleToRuaFixCountMap:[:], totalRuaFixCount:0

We will email the logs to look further.

Thanks 

Sreehari

[sreehariv]

Hello @timchengappa ,

can you please confirm if the accountkey is populated for the same user and role in role_user_acccount table.

When we followed the same process you did, it did not populated the account key in role_user_account table though it created add access task with no action required .

hence the retrofit feature will not work I believe as it will look for a.accountkey=rua.accountkey check in query for getting the no of records to be processed and it will give 0 records that will cause in empty list of ruacorrection responseMap :: [roleToRuaFixCountMap:[:], 

Please refer to my latest updates for detailed info on logs and updates regarding the same.

Thanks

Sreehari

[sreehariv]

Hello Team,

since we found that the accountkey is missing role_user_account table, the retrofit feature is not working as expected. 

So we have updated the accountkey through custom query job in role_user_account table after that we executed the Retrofit Role to User Mappings repair which triggered the retrofit job in the back end.

Now the AssignedFromroles column in account_entitlements1 table is populated with the role key.

Our question was in what way we can populate the account key for role_user_account table during the role to usermembership import done via Upload role associations . So that the retrofit  feature works as expected.

Please suggest.

Thanks

Sreehari.

[rushikeshvartak]

It seems defect , raise Freshdesk. It should be populated automatically on import

[pmahalle]

Hi @sreehariv ,

You can also use addrole API to add the enterprise role, which will create pending in case user does not have entitlements linked to role :

{{url}}/ECM/{{path}}/addrole

Refer:

https://documenter.getpostman.com/view/1797923/RWaLwo21#140feb81-cc65-4090-baf0-66af64b0c895