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.

Document ID : KB000049179
Last Modified Date : 14/02/2018
Show Technical Document Details

Description:

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

Solution:

This issue affects V12 and V13 and is currently under Development review for a resolution in a future release.

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;

Keywords: CLARITYKB, slow, response, performance, CLRT-57954, redundant.