Deprovisioning role membership - role and account in custom Target System (SQL Server)


Beforehand my apologies for the rather long context description ...

This question is for One Identity Manager 8.1.2

The custom target system is a SQL database with three tables, account, role and rolemembership.

I have configured a sync project with the appropriate mappings and workflows.

One Identity is configured that when external users receive a new business role (via IT Shop, the INSERT event on the PersonInOrg table calls a script that inserts new entries in the UNSAccountB, UNSGroupB and UNSAccountBInUNSGroupB tables.

I have custom processes for the INSERT event on each of tables UNSAccountB, UNSGroupB and UNSAccountBInUNSGroupB. Each of these processes invoke the appropriate AdHocProjection.

The provisioning cycle works as expected when a user requests or gets assigned a new business role. The UNS tables are populated and after the adhoc projection has finished its work, the target database has the three tables correctly populated.

It is when the role is removed from the user that things do not go as I would expect.

The trigger is activated by capturing the DELETE event on the table PersonInOrg (thank you Barry for pointing me to the correct solution)

The script performs first the removal of the entries in the table UNSAccountBInUNSGroupB (1 in the snapshot), then from the table UNSGroupB (2 in the snapshot) then at last from the table UNSAccount (3 in the snapshot).

Remark: I am currently not able to upload images so there is no snapshot at this time.

I also have custom processes for the DELETE event on each of tables UNSAccountBInUNSGroupB, UNSGroupB and UNSAccountB. Each of these processes invoke also the appropriate AdHocProjection.

Even though the script performs the removal process in the correct sequence, I notice that this is not the sequence with which the AdHocProjection occurs. The DELETE event for the table UNSGroupB is triggered first. The end result is that entries are correctly removed from the account and role table, but the rolemembership table is not updated (because the generated SQL statement refers to a role that doesn't exist anymore). I attach a snapshot (will follow later once I am able to upload images) with the sequence of activity on the JobQueue. I see there that the DELETE event processing for the UNSAccountBInGroupB is performed last although the script that triggers this event is executed first.

Is the fact that the delete event for the UNSAccountBInGroupB table is triggered last by design, or is this just a timing issue (meaning that if I would repeat the process enough times it might work sometimes correctly)

How can I ensure that the deprovisioning is processed correctly. It seems that merely invoking script in the correct sequence is not enough. Can we force in someway the sequence of the event triggering, or can we configure the process steps in such a way that the second step waits until the first is completely finished?

Thank you very much in advance.

Kind regards,