Review generation fails with 'ORA-01427: single-row subquery returns more than one row' in RSA Identity Governance & Lifecycle

[#1]

Article Number

000033888

Applies To

RSA Product Set: RSA Identity Governance & Lifecycle
RSA Version/Condition: 6.9.1, 7.0.0, 7.0.1, 7.0.2, 7.1.0, 7.1.1
 

Issue

When generating a review of type User Access Review with the roles option included (Reviews > Definitions > {name of user review} > Edit Definition > Contents tab > Include roles), the review generation fails. If you create the review without the roles option checked, the review generation succeeds.  

The aveksaServer.log has the following error:
 

Versions prior to 7.0.1 P01:

 

02/26/2016 16:08:48.606 ERROR (Exec Task Consumer#0) 
[com.aveksa.server.review.ReviewDefinitionVersion] 
FAILED method=generateReport subTask=Get entitlements for review 7416 
com.aveksa.server.db.PersistenceException: java.sql.SQLException: 
ORA-20126: The creation of reviews failed.

ORA-20126: The creation of reviews failed.

Stored Procedure:Parse_User_Review execution aborted.
ORA-20126: The creation of reviews failed.

Stored Procedure:Parse_Roles_In_User_Review execution aborted.
ORA-01427: single-row subquery returns more than one row
ORA-06512: at "AVUSER.REVIEW_DEFINITION_PARSER", line 10101

Stack:
ORA-06512: at "AVUSER.REVIEW_DEFINITION_PARSER", line 10101
ORA-06512: at "AVUSER.ERROR_HANDLER_PKG", line 903
ORA-06512: at "AVUSER.ERROR_HANDLER_PKG", line 917
ORA-06512: at "AVUSE
Stored Procedure:Generate_Review execution aborted.
Stack:
ORA-06512: at "AVUSER.ERROR_HANDLER_PKG", line 903
ORA-06512: at "AVUSER.ERROR_HANDLER_PKG", line 917
ORA-06512: at "AVUSER.REVIEW_DEFINITION_PARSER", line 373
ORA-06512: at "AVUSER.REVIEW_DEFINITION_PARSER", line 274
ORA-06512: at "AVUSER.ENTITLEMENT_REVIEW_PKG", line 630
ORA-06512: at "AVUSER.ENTITLEMENT_REVIEW_PKG", line 1749
ORA-06512: at "AVUSER.ERROR_HANDLER_PKG", line 903
ORA-06512: at "AVUSER.ERROR_HANDLER_PKG", line 917
ORA-06512: at "AVUSER.ENTITLEMENT_REVIEW_PKG", line 1895
ORA-06512: at "AVUSER.ENTITLEMENT_REVIEW_PKG", line 1537
ORA-06512: at "AVUSER.ENTITLEMENT_REVIEW_PKG", line 1502
ORA-06512: at line 1

Versions after 7.0.1 P01:

 

06/07/2018 09:41:00.870 ERROR (Exec Task Consumer#0) 
[com.aveksa.server.review.ReviewDefinitionVersion] 
FAILED method=generateReport subTask=Get entitlements for review 7099
com.aveksa.server.db.PersistenceException: java.sql.SQLException: 
ORA-20126: The creation of reviews failed.

ORA-20126: The creation of reviews failed.

Stored Procedure:Parse_User_Review execution aborted.
ORA-20126: The creation of reviews failed.

Stored Procedure:Parse_Roles_In_User_Review execution aborted.
ORA-01427: single-row subquery returns more than one row
ORA-06512: at "AVUSER.REVIEW_DEFINITION_PARSER", line 10078
Stack:
ORA-06512: at "AVUSER.REVIEW_DEFINITION_PARSER", line 10078
ORA-06512: at "AVUSER.ERROR_HANDLER_PKG", line 903
ORA-06512: at "AVUSER.ERROR_HANDLER_PKG", line 917
ORA-06512: at "AVUSE
Stored Procedure:Generate_Review execution aborted.
Stack:
ORA-06512: at "AVUSER.ERROR_HANDLER_PKG", line 903
ORA-06512: at "AVUSER.ERROR_HANDLER_PKG", line 917
ORA-06512: at "AVUSER.REVIEW_DEFINITION_PARSER", line 373
ORA-06512: at "AVUSER.REVIEW_DEFINITION_PARSER", line 274
ORA-06512: at "AVUSER.ENTITLEMENT_REVIEW_PKG", line 630
ORA-06512: at "AVUSER.ENTITLEMENT_REVIEW_PKG", line 1751
ORA-06512: at "AVUSER.ERROR_HANDLER_PKG", line 903
ORA-06512: at "AVUSER.ERROR_HANDLER_PKG", line 917
ORA-06512: at "AVUSER.ENTITLEMENT_REVIEW_PKG", line 1897
ORA-06512: at "AVUSER.ENTITLEMENT_REVIEW_PKG", line 1537
ORA-06512: at "AVUSER.ENTITLEMENT_REVIEW_PKG", line 1502
ORA-06512: at line 1

 

Cause

This is a known issue reported in engineering tickets ACM-102453 and ACM-97300. The cause of this error is that duplicate roles belong to users.
 

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

The fix includes additional checks to ensure that duplicates are not created. In addition the patches include a migration script that identifies and removes any duplicates. The following text is logged to the migrate.log file ($AVEKSA_HOME/wildfly/standalone/log/migrate.log) file when the migration script runs. 
 

Removed XX duplicate records in RoleMemberships table

Where XX is the number of duplicate records removed.
 

Workaround

If you have users with duplicate roles, there is a cleanup script available to remove the duplicate roles. 

1. First, determine if you have duplicate roles.

1. Download the attached ACM-97300_DupsDetection.sql script to your computer.
2. Login to your RSA Identity Governance & Lifecycle application server as the oracle user.
3. Copy ACM-97300_DupsDetection.sql to $AVEKSA_HOME.
4. $ sqlplus avuser/<password>
5. SQL> @ACM-97300_DupsDetection

2. If the above script returns results, please contact RSA Identity Governance & Lifecycle Support and mention this RSA Knowledge Base Article ID 000033888 for reference. RSA Identity Governance & Lifecycle Support can provide you with the cleanup script and instructions for running the script.