Unable to Remove a Role from a Role due to "ORA-01436: CONNECT BY loop" Error (Doc ID 1491727.1)

Last updated on JANUARY 03, 2023

Applies to:

Symptoms

When performing below sequence of operations:
 
  1. Define Roles: ROLE_A, ROLE_B, ROLE_C and user: USER1
 
  2. Assign ROLE_C as a sub role to ROLE_B.
  at this moment you have a record in WF_ROLE_HIERARCHIES with :
  sub_name = UMX|ROLE_C
  super_name = UMX|ROLE_B
  enabled_flag='Y'
 
  3. "Regret" what you did in step 2, i.e. Remove ROLE_B from ROLE_C.
  at this moment you have a record in WF_ROLE_HIERARCHIES with :
  sub_name = UMX|ROLE_C
  super_name = UMX|ROLE_B
  enabled_flag='N'
 
  4. Now you add ROLE_B as a sub role to ROLE_C (vice versa connection).
  at this moment you have a NEW record in WF_ROLE_HIERARCHIES with :
  sub_name = UMX|ROLE_B
  super_name = UMX|ROLE_C
  enabled_flag='Y'
 
 
  5. Add ROLE_A as sub role to ROLE_B
  at this moment you have a NEW record in WF_ROLE_HIERARCHIES with :
  sub_name = UMX|ROLE_A
  super_name = UMX|ROLE_B
  enabled_flag='Y'
 
  The hierarchy now is: (sub) A->B->C (super)
 
  6. Assign ROLE_A to USER1.
  Querying :
  SELECT role_name FROM wf_user_roles WHERE user_name='USER1' should retrieve
  (correctly):
 
  USER1
  UMX|ROLE_A
  UMX|ROLE_B
  UMX|ROLE_C
 
  7. "Regret" what you've done in step 4., i.e. Remove ROLE_C from ROLE_B
 
  The hierarchy now is: (sub) A->B (super)
 
  You know that USER1 should have only ROLE_A and ROLE_B, but find :
 
  Querying :
  SELECT role_name FROM wf_user_roles WHERE user_name='USER1' retrieves :
 
  USER1
  UMX|ROLE_A
  UMX|ROLE_B
  UMX|ROLE_C
 
  The issue is : UMX|ROLE_C should not be retrieved but it does.


This result in a role hierarchy loop, and when trying to remove Role C from Role B the following error is received:

Cause

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.