How to delete particular entry from mxp_provision table

[former_member198652]

Dear experts,

For one of our requirements, we suppose to delete particular entry/mskey from the mxp_provision table, from IDM side. For that we know mc_reset_provision stored procedure used, but still entry is not deleting from table. for the above SP, it is asking inputs as pisid and presetaudit, am mskey and auditid.

Could you please help, exactly what inputs to give/how to delete particular entry from mxp_provision table.

Thank you in advance.

Regards,

Jay

[niconapo2 · Accepted solution]

--CLEAR PROVISION QUEUE
UPDATE mxp_audit SET ProvStatus = 1100 WHERE AUDITID IN (SELECT auditref FROM MXP_Provision where actionid=XXXXX);
DELETE FROM mxp_provision where actionid=XXXXX;

[former_member198652 · Accepted solution]

Hi Nicolas,

Thank you for the response, but we wanted to do it from IDM to DB pass, using mc_reset_provision Stored procedure. Could you help me, exactly what parameters to pass?

Regards,

Jay

[niconapo2 · Accepted solution]

Hi Jaya,

mc_reset_provision take in parameters pIsId and pResetAudit. With this stored procedure you can't specify the MSKEYVALUE you want to remove.

1. pIsId is the Identity Store Id (if you have only one it should be 1)
2. pResetAudit is just a flag, if it's <> 0 then it will remove all pending provision.

Below the source of the stored procedure

---------------------------------------------------------------------------------------
-- mc_reset_provision
-- This procedure resets all identity store and provisioing data for the given ids identifier.
-- THERE IS NO UNDO FOR THIS FUNCTION
---------------------------------------------------------------------------------------
prompt mc_reset_provision
CREATE OR REPLACE PROCEDURE mc_reset_provision(
	pIsId number,
	pResetAudit number)


is


	lBuf VARCHAR2(256);
	lIdsName VARCHAR2(50);
	lStatus number(10);
	lStatusText varchar2(256);
Begin


	BEGIN
		SELECT IdStoreName INTO lIdsName FROM MXI_IDStores WHERE is_id = pIsId;
		exception when no_data_found then
			lBuf := 'Identity Store ID=' || pIsId || ' does not exist';
			mc_write_syslog (lBuf,'',1,'',0);
			RETURN;
	END;




	lBuf := 'Resetting Identity Store provisioning data "' || lIdsName || '" (ID=' || pIsId || ')';
	mc_write_syslog (lBuf,'',-1,'',0);




	IF pResetAudit <> 0 then


		-- MXP_PROVISION
		delete from mxp_provision WHERE MSKEY IN
			(SELECT    MXP_Provision.MSKey
			 FROM      MXI_VALUES,
			 	   MXI_Attributes,
			 	   MXP_Provision
			 WHERE 	   MXI_VALUES.Attr_ID = MXI_Attributes.Attr_ID AND
				   MXI_VALUES.MSKEY = MXP_Provision.MSKey AND
				   (MXI_Attributes.AttrName = 'MSKEYVALUE') AND
				   (MXI_Attributes.IS_ID = pIsId));


	 END IF;


	lBuf := 'Identity Store provisioning data "' || lIdsName || '" (ID=' || pIsId || ') has been reset';
	mc_write_syslog (lBuf,'',0,'',0);


 	COMMIT;
END;

[devaprakash_b]

Hi Jaya,

If you execute the mentioned stored procedure it would delete all the entries in the provisioning queue.

Can you let us know the reason why you wanted to delete the entry from the mxp_provision table?

IF the entry is stuck in the queue with help of provision monitor you can identify the problem and proposed solution for it.

Regards,

Deva

[former_member198652]

Hi Deva,

Yes entry got struck in the queue and we analyzed, it is due to some provisioning framework changes while patching.

Is there any option to delete particular entry by passing particular MSKEY?. As of now, for workaround we wanted this.

Regards,

Jay

[devaprakash_b]

Hi Jaya,

Did you run provision monitor? Kindly refer to this blog and you would be able to identify teh problem and the solution too for that.

If still it didnt resolve, you have to post here the stuck entry details in the mxp_provision table and also the problem.json file result once you ran provision monitor for that that entry.

Regards,

Deva

[former_member198652]

Hi Nicolas,

Thank you very much. Then to delete particular entry, we can try only from DB side.

Regards,

Jay