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 Jan 15, 2020
Version 12Show 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 being investigated by the Engineering team in order to provide a permanent resolution in a future release.
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