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:
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
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
This issue affects V12 and V13 and is currently under Development review for a resolution in a future release.
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;
Keywords: CLARITYKB, slow, response, performance, CLRT-57954, redundant.