Manually Revoking a Resource Object using 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.

To revoke a struck Resource Object, first we need to search the Resource Object which user has, along with their statuses and necessary keys required later to revoke the RO. Below is the query:

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 ID');

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 OBJ_KEY with the OBJ_KEY number from the first query above:

select * from OST where obj_key = 'OBJ_KEY';

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 record in it's bare naked form run this (Replace OIU_KEY with the OIU_KEY from the first query):

select * from oiu where OIU_KEY = 'OIU_KEY';

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, replacing OST_KEY with the OST_KEY from the second query and OIU_KEY with the OIU_KEY from the first query:

update oiu set ost_key = 'OST_KEY' where oiu_key='OIU_KEY';


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".