Clarity: Process instance rights are orphaned in the CMN_SEC_ASSGND_OBJ_PERM table when a process is deleted via the UI or via the Delete Process Instance job.

Products

• Clarity PPM SaaS
• Clarity PPM On Premise

Issue/Introduction

When a process is deleted via the UI or via the Delete Process Instance job the process instance rights are orphaned in the CMN_SEC_ASSGND_OBJ_PERM table. This can add up over time for customers that have a large number of processes running. Having the unnecessary rows in the CMN_SEC_ASSGND_OBJ_PERM table can adversely affect security checks.

Steps to Reproduce:

1. Create a simple process
   
   
2. Create an instance of that process.
   
   
3. Examine the data in the cmn_sec_assgnd_obj_perm table

   select * from cmn_sec_assgnd_obj_perm
   where object_ID = 50680
   and PRINCIPAL_TYPE = 'USER'
   and PRINCIPAL_ID = 5012128 -- internal ID of your test user

4. Then delete the process instance from the UI or using the Delete Process Instance job.
   
   
5. Examine the data in the cmn_sec_assgnd_obj_perm table

Expected Result: The row for the deleted process is removed from cmn_sec_assgnd_obj_perm
Actual Result: The row still exists in cmn_sec_assgnd_obj_perm

Resolution

Workaround:
Use the following query to look for orphans:

select COUNT(*) from (select object_instance_id from cmn_sec_assgnd_obj_perm p
where p.object_ID = 50680
minus
select ID from BPM_RUN_PROCESSES) q

 

If the above query returned a large number, run the following procedure to delete the orphans:

**** Before running the delete statement below be sure to backup the table****

Begin
LOOP
Delete from cmn_sec_assgnd_obj_perm
where object_ID = 50680
and object_instance_id IN (
select object_instance_id from cmn_sec_assgnd_obj_perm p
where p.object_ID = 50680
minus
select ID from BPM_RUN_PROCESSES)
AND ROWNUM <= 100000;
EXIT WHEN SQL%ROWCOUNT = 0;
COMMIT;
END LOOP;
END;