Segregation of Duties (SOD) Rule fails with error 'ORA-30926: unable to get a stable set of rows in the source tables' error in RSA Identity Governance & Lifecycle

[#1]

Article Number

000042558

Applies To

RSA Product Set: RSA Identity Governance & Lifecycle
RSA Version/Condition: 7.1.1 P07, 7.2.0 P02
 

Issue

Segregation of Duties (SOD) Rules fails during Rule Processing. The Rule Processing Run Details screen (Admin > Monitoring >  Run ID) shows a Status of Failed during Step 6/9: Populate violation data.
 

Image description
 

The following error is logged to the aveksaServer.log file ($AVEKSA_HOME/wildfly/standalone/log/aveksaServer.log😞

 

09/03/2020 14:56:25.761 WARN  (Exec Task Consumer#0 - Sequence) 
[org.hibernate.engine.jdbc.spi.SqlExceptionHelper] SQL Error: 30926, SQLState: 99999
09/03/2020 14:56:25.761 ERROR (Exec Task Consumer#0 - Sequence) 
[org.hibernate.engine.jdbc.spi.SqlExceptionHelper] ORA-30926: unable to get a stable set of rows in the source tables
ORA-06512: at "AVUSER.RULE_PROCESS_PKG", line 1132
ORA-06512: at "AVUSER.RULE_PROCESS_PKG", line 319
ORA-06512: at line 1

Please see 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, if you are on a WildFly cluster or a non-WildFly platform. The aveksaServer.log may also be downloaded from the RSA Identity Governance & Lifecycle user interface (Admin > System > Server Nodes tab > under Logs.)
 

Cause

This is a known issue reported in engineering ticket ACM-107316.

The following versions and patch levels are affected:

• RSA Identity Governance & Lifecycle 7.1.1 P07
• RSA Identity Governance & Lifecycle 7.2.0 P02

This issue may occur if a Role Member or Entitlement is deleted from a Role and then the same Member or Entitlement is added back to the Role.

  Run the following script as AVUSER to identify if there are any duplicate records that need correcting.

SELECT
    entitled_id      AS user_id,
    entitlement_id   AS role_id,
    COUNT(*)
FROM
    t_av_explodeduserentitlements tavue
WHERE
    tavue.entitlement_derived_from_type = 'explicit'
    AND tavue.entitled_derived_from_type = 'explicit'
    AND tavue.entitlement_type = 'global-role'
    AND tavue.entitled_type = 'user'
GROUP BY
    entitled_id,
    entitlement_id
HAVING
    COUNT(*) > 1;

2. If the query returns no results, you likely do not have this issue.  Please investigate other options.

Resolution

This issue is resolved in the following versions and patches.

• RSA Identity Governance & Lifecycle 7.1.1 P11
• RSA Identity Governance & Lifecycle 7.2.0 P05
• RSA Identity Governance & Lifecycle 7.2.1 P01
• RSA Identity Governance & Lifecycle 7.5.0

The fix includes a migration script that identifies and corrects duplicate records in RSA Identity Governance & Lifecycle internal tables. 

If there is a reason, you cannot patch to the version where this issue is resolved. Please contact RSA Customer Support for guidance.

Workaround

Please contact RSA Identity Governance & Lifecycle Customer Support for a workaround and mention this RSA Knowledge Base Article ID 000042558 for reference.
 

Notes

See related RSA Knowledge Base Article 000042573 -- Role Review Member and/or Entitlement counts are incorrect preventing Role Review completion in RSA Identity Governance & Lifecycle  for other failures that are associated with this same issue.