000033888 - How to overcome review generation failure when roles are included in use RSA Identity Governance and Lifecycle

Document created by RSA Customer Support Employee on Sep 21, 2016Last modified by RSA Customer Support Employee on Apr 21, 2017
Version 4Show Document
  • View in full screen mode

Article Content

Article Number000033888
Applies ToRSA Product Set: RSA Identity Governance and Lifecycle
RSA Product/Service Type: Enterprise Software
RSA Version/Condition: 7.0.1, 7.0, 6.9.1
 
IssueWhen you try to create a User Review with the Roles option selected, the review fails to generate the result.  If we try to create a User Review with other entitlement types (e. g., including app-roles, entitlements, group) then the review generates successfully. 
When running a User Access review, the following error occurs:
ERROR:-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

The error message in the aveksaServer.log is:
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
CauseThe review generation will fail because of duplicated roles. 
If the user creates a local role related change request, the Role_Management_Pkg.Explode_LocalRoles procedure gets called. 
  1. The procedure takes CR ID(s) as the input argument. 
  2. From given CR it evaluates Role IDs to explode based on whether the CR contains the Role as an Operand or Value. 
  3. It then inserts the above evaluated role IDs in a temporary table named GTT_EXP_ROLES (Roles to Explode). 
  4. Then it explodes different types of entitlements into temporary tables named GTT_MODEL_EXPLODEDUSERENTS and GTT_EXPLODEDUSERENTITLEMENTS. 
  5. Now as temp tables contain exploded entitlements, it merges temporary tables to the main XUE and Model XUE tables. 
  6. It then deletes older entries from the XUE and Model XUEs that are there and not in the temporary tables. 
This procedure gets called by the System Fulfiller for a local role for a CR once, as well as by every fulfillment handler node it come across in fulfillment workflows. 
If a CR contains multiple change items, and it usually does in case of role changes, it triggers that many fulfillment workflows, so multiple calls to Explode_LocalRoles. This causes overlapping calls to Explode_LocalRoles. Now, if multiple sessions reach Step 5 at the moment, though the merge query first finds for matches to avoid duplicates, these sessions don’t get committed XUE and Model XUE tables. 
So even if the check is there, it sometimes leads duplicate rows in the XUE and Model XUE tables. 
ResolutionThis is fixed in 6.9.1 P18, 7.0 P05 and 7.0.1 P01.
We need to run the cleanup script to remove the duplicates after upgrading to the above mentioned patch versions.  Please contact RSA Customer Support for the cleanup script. 

Attachments

    Outcomes