Indirect relationship processing is failing at step 11 on "AVUSER.ROLE_MANAGEMENT_PKG", line 3376 in RSA Governance & Lifecycle

[#1]

Article Number

000068113

Applies To

RSA Product Set: RSA Governance & Lifecycle
RSA Version/Condition: 

• RSA Governance & Lifecycle 7.5.2

Issue

Indirect relationship processing is failing at step 11: calculate role metrics.

Image description

With the following error showing in AveksaServer.log
 

02/12/2023 09:44:53.950 ERROR (Exec Task Consumer#0 - Sequence) [com.aveksa.server.xfw.TaskExecutor] Failed method=Execute ExecutionTask[TaskID=2275 RunID=2099 Source=1 Type=EntitlementExplosionProcessing Status=InProgress] 
com.aveksa.server.xfw.ExecutionException: com.aveksa.server.db.PersistenceException: java.sql.SQLException: ORA-01436: CONNECT BY loop in user data
ORA-06512: at "AVUSER.ROLE_MANAGEMENT_PKG", line 3376
ORA-06512: at "AVUSER.ROLE_MANAGEMENT_PKG", line 3982
ORA-06512: at "AVUSER.COMMON_EXPLODER", line 181
ORA-06512: at line 1

 

Cause

After importing roles from an older environment it is observed that under a Role entitlement, the Role itself is listed as an entitlement which created cyclic dependency. 

Resolution

Contact RSA Customer Support and quote this KB article for a cleanup script for this issue. 

Notes

Run the following query to check does the "ent_in_parent_role" column Role is referring back to the Parent Role present in the "ent_path_Parent_to_child_role" column as a direct entitlement or not.
 

select cy.path ent_path_Parent_to_child_role ,r.name ent_in_parent_role from
(SELECT SUBSTR(PATH, 3) PATH,child_role_id
FROM (SELECT ent_id, SYS_CONNECT_BY_PATH(name,' > ') PATH, ent_id child_role_id,CONNECT_BY_ISCYCLE cycle_flag 
FROM (SELECT rl.name AS name, role_id, ent_id FROM t_av_roledefinitions tavrd 
JOIN t_av_roles rl ON tavrd.role_id = rl.id 
WHERE tavrd.deletion_date IS NULL 
AND ent_type = 'global-role') 
CONNECT BY NOCYCLE PRIOR ent_id = role_id START WITH ROLE_ID IN (SELECT ID FROM t_av_roles )) 
WHERE cycle_flag = 1) cy
inner join t_av_roles r
on cy.child_role_id=r.id;