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

Document created by RSA Customer Support Employee on Dec 31, 2019Last modified by RSA Customer Support Employee on Jan 23, 2020
Version 2Show Document
  • View in full screen mode

Article Content

Article Number000038236
Applies ToRSA Product Set: RSA Identity Governance & Lifecycle
RSA Version/Condition: 7.1.0, 7.1.1
 
IssueChanges 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.
 
CauseThis 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.
ResolutionThere are three fixes needed for this issue. Implementation of these fixes is version-dependent.
  1. A code change to prevent duplicate role names.
  2. A cleanup script to finish the explosion of role entitlements into the user access table.
  3. A cleanup script to remove duplicate role names.

RSA Identity Governance & Lifecycle 7.1.1.  



  1. The code change is in RSA Identity Governance & Lifecycle 7.1.1 P05. This patch prevents modifications to existing roles that result in duplicate role names. 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



  1. The patch will also run a script file called ACM-101549.sql that will finish the explosion of role entitlements into the user access table.

  1. To cleanup duplicate role names, please use the Workaround below. The patch does not cleanup duplicate role names.

Note: Until you are able to upgrade to 7.1.1 P05, please follow the resolution steps for RSA Identity Governance & Lifecycle 7.1.0.
 



RSA Identity Governance & Lifecycle 7.1.0.



  1. There is no code change available for RSA Identity Governance & Lifecycle 7.1.0. Until you are able to upgrade to 7.1.1 P05, repeat steps 1 and 2 below as needed.

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

  1. To cleanup duplicate role names, please use the Workaround below.

 
Workaround

Cleanup script to remove 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 Support for the workaround and mention this RSA Knowledge Base Article ID 000038236 for reference. 



  1. Run the following SQL query 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.

 
 

Attachments

    Outcomes