• 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.)

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

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

• 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.

• 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.

• 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

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;

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.