**NOTE: As with all Tips and Tricks we provide on the IDMWorks blog, use the following AT YOUR OWN RISK. We do not guarantee this will work in your environment and make no warranties***
Oracle Identity Manager: Manually Revoking a Stuck Resource Object through the Database
Have you ever had a Resource Object stuck in a Pending or Provisioning state that you just couldn’t do anything about? This happens a lot when first setting up a Resource Object and running Revoke before you create the Revoke tasks. The status will stay on “Provisioned” but all the tasks inside will say “Cancelled” and there’s nothing more you can do to it. If you only allow one instance that user is now stuck.
Here is how to set the status to Revoked manually, through the database, so you can re-provision a new instance of the Resource Object.
First, let’s look at all the resource the user has. This query will show you his resources, their statues, and some necessary keys you’ll need later (Replace USER with your USERID):
select oiu.oiu_key, oiu.obi_key, oiu.orc_key, ost.ost_status, obj.obj_name, obj.obj_key,oiu.req_key from oiu inner join ost on oiu.ost_key = ost.ost_key inner join obi on oiu.obi_key = obi.obi_key inner join obj on obi.obj_key = obj.obj_key where oiu.usr_key=(select usr_key from usr where usr_login='USER');
Look at the results and find the line that has the stuck object and save the OIU_KEY and the OBJ_KEY.
Next we need the key for this Object’s Revoked status. Each Object has it’s own set of Status Codes, so to find the ones for our object above, run this query and replace YOUROBJKEY with the OBJ_KEY number from the first query above:
select * from OST where obj_key = YOUROBJKEY;
Look at the results and find the line where the OST_STATUS is “Revoked” and save the OST_KEY.
Next we will update the Object Instance, and set it’s status to the new key. If you want to see the current recode in it’s bare naked form run this (Replace THEKEY with the OIU_KEY from the first query):
select * from oiu where OIU_KEY = THEKEY;
You will see in the results the OST_KEY column. This is the current status of your Resource Object. This is what we are going to change to the new status. So let’s run this query, replaceing YOUROSTKEY with the OST_KEY from the second query and YOUROIUKEY with the OIU_KEY from the first query:
update oiu set ost_key = YOUROSTKEY where oiu_key=YOUROIUKEY;
Perform a Commit and that’s it. Pull up the resource profile for the user in the web console and you should see the status for that resource object is now “Revoked“.
Questions, comments or concerns? Feel free to reach out to us at IDMWorks.
