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

Document created by RSA Customer Support Employee on Sep 21, 2016Last modified by RSA Customer Support on Mar 26, 2020
Version 36Show Document
  • View in full screen mode

Article Content

Article Number000033888
Applies ToRSA 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
 
IssueWhen 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


 
CauseThis 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.
 
ResolutionThis 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.
 
WorkaroundIf 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

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

 

Outcomes