Role changes are not accurately reflected under the User Access tab or in a User Access Review in RSA Identity Governance & Lifecycle

[#1]

Article Number

000038236

Applies To

RSA Product Set: RSA Identity Governance & Lifecycle
RSA Version/Condition: 7.1.0, 7.1.1
 

Issue

Changes to entitlements that have been granted indirectly through roles show correctly when viewing the role definition (Roles > Roles > Role Name > Members and Entitlements tab) but are not reflected correctly under the user's access tab (Users > Users > Name > Access tab) or in a User Access Review under the All entitlements tab.

Symptoms manifest in several ways:

• A member of a business role (as seen in the role definition) does not show the business role as a direct entitlement under their user access tab.
• A member of a business role (as seen in the role definition) with a technical role as an entitlement shows the business role as a direct entitlement but does not show the technical role or the technical role entitlements as indirect entitlements under their user access tab.
• A member of a business role (as seen in the role definition) with a technical role as an entitlement shows the business role as a direct entitlement and the technical role as an indirect entitlement under their user access tab but does not show the technical role entitlements as indirect entitlements.
• Entitlements granted via roles show as direct entitlements on user access reviews.
• After a user is terminated, the user's access tab shows entitlements even though a user access review does not show these entitlements. 
• The Compare With User feature (Users > Users > Access tab > Other Changes)  shows that a user is still is a member of a business role even though the user has been removed from the role (as seen in the role definition.)

In all instances the role definition screen shows the correct information and may show a change request generated to remove or add the entitlements.

The following ERROR level log messages are logged to the aveksaServer.log file:
 

09/26/2019 08:55:46.012 INFO (Thread-288) [com.aveksa.server.db.persistence.PersistenceServiceProvider] 
executeCallableStatement giving up after hitting SQLException: 
ORA-01427: single-row subquery returns more than one row
ORA-06512: at "AVUSER.ROLE_MANAGEMENT_PKG", line 4550
ORA-06512: at line 1

09/26/2019 08:55:46.012 WARN (Thread-288) [org.hibernate.engine.jdbc.spi.SqlExceptionHelper] 
SQL Error: 1427, SQLState: 21000

09/26/2019 08:55:46.012 ERROR (Thread-288) [org.hibernate.engine.jdbc.spi.SqlExceptionHelper] 
ORA-01427: single-row subquery returns more than one row
ORA-06512: at "AVUSER.ROLE_MANAGEMENT_PKG", line 4550
ORA-06512: at line 1

Please refer to RSA Knowledge Base Article  000030327 -- Artifacts to gather in RSA Identity Governance & Lifecycle to find the location of the aveksaServer.log file for your specific deployment.
 

Cause

This is a known issue in the following RSA Identity Governance & Lifecycle versions:

• RSA Identity Governance & Lifecycle 7.1.0
• RSA Identity Governance & Lifecycle 7.1.1

This issue occurs when there are unexpected duplicate role names in the tables used to explode the role entitlements into the user access table. The duplicate items cause the population of the user access table to fail which results in the role changes to be incompletely reflected in the database.

The duplicate items can occur under the following circumstances:

• If a role import was done for an existing role, some entitlement relationships were duplicated instead of overwriting existing relationships.
• If a role import was done for an existing role, some entitlement relationships that were absent from the role were deleted instead of being marked as inactive.
• If a role import was done for an existing role where a previously deleted version of the role existed, the changes were applied to the deleted role.
• If an attempt was made to import a role with the same name as a collected role, the collected role and associated entitlements would be overwritten. 
• If a role is deleted and recreated with the same name.

Resolution

This issue is resolved in the following RSA Identity Governance & Lifecycle versions and/or patch levels: 

• RSA Identity Governance & Lifecycle 7.1.1 P05
• RSA Identity Governance & Lifecycle 7.2.0

IMPORTANT: A manual step is required after applying one of the above patches to remove existing duplicate role names.
 

Summary of fixes required for this issue:

1. A code change to prevent duplicate role names. (Available in RSA Identity Governance & Lifecycle 7.2.0 and 7.1.1 P05.)
2. A cleanup script to finish the explosion of role entitlements into the user access table. (Available in RSA Identity Governance & Lifecycle 7.2.0 and 7.1.1 P05.)
3. Removal of duplicate role names. (Manual step required after applying one of the above patches or as part of the workaround outlined below.)
   1. A cleanup script to remove deleted roles that have the same name as active roles.
   2. Deletion of any active roles with the same name as other active roles to be performed in the user interface.

  

To fully resolve this issue:

1. Upgrade/patch to RSA Identity Governance & Lifecycle 7.2.0 or 7.1.1 P05.

• After patching to 7.2.0 or 7.1.1 P05, modifications to existing roles that result in duplicate role names will be prevented. If an attempt is made to import a role that has the same name as an existing collected role, the following exception will be generated.

Cannot import over an existing collected role

• The patch will run a cleanup script file that will finish the explosion of role entitlements into the user access table as part of the migration process.

2. Cleanup duplicate role names. This is a manual step required after patching your system to RSA Identity Governance & Lifecycle 7.2.0 or 7.1.1 P05. The patch does not cleanup existing duplicate role names. The patch only prevents future duplicate role names from being created. See section below entitled Cleanup Duplicate Role Names for instructions. NOTE: This step is also required if you are implementing the workaround below.

  

Cleanup Duplicate Role Names

Run the following scripts to identify and correct (or advise) on duplicate role names that need to be corrected.

1. Run the following SQL query as AVUSER to identify if there are any deleted roles that have the same name as active roles. 

select rl.* from t_av_roles rl
where deletion_date is not null and (name, roleset_id) in
(select name, roleset_id from t_av_roles 
group by name, roleset_id
having count(*) > 1)
order by rl.name;

If the query above returns a result, please contact RSA Identity Governance & Lifecycle Cutomer Support for a cleanup script and mention this RSA Knowledge Base Article ID 000038236 for reference. 

2. Run the following SQL query as AVUSER to identity any active roles that have the same name as other active roles:

select rl.* from t_av_roles rl
where deletion_date is null and (name, roleset_id) in
(select name, roleset_id from t_av_roles 
group by name, roleset_id
having count(*) > 1)
order by rl.name;

 If the query above returns a result, then identify and manually delete one of the active roles by selecting it from the Roles page (Roles > Roles > check the box next to the Role Name) and select Delete Roles under the Actions drop down menu.

 

Workaround

If you are on RSA Identity Governance & Lifecycle 7.1.0 or RSA Identity Governance & Lifecycle 7.1.1 GA through P04, the following workaround is available:

1. Contact RSA Identity Governance & Lifecycle Customer Support for a cleanup script that will finish the explosion of role entitlements into the user access table and mention this RSA Knowledge Base Article ID 000038236 for reference.

2. Cleanup duplicate role names as described in the Resolution section above under Cleanup Duplicate Role Names.

IMPORTANT: Until you upgrade/patch to 7.2.0 or 7.1.1 P05, you may encounter this issue again. Repeat steps #1 and #2 of this workaround every time there is a recurrence of this issue.