Db queries of OIM

Question

1. DB query to check Account status of the user
2. DB query to check Entitlement status of the user
3. DB query to check Role and Access Policy mapping.

Please let me know if anyone have these queries?

Answer 1

1. For Account status

   SELECT usr.usr_login,obj.obj_name,ost.ost_status 
   FROM orc, usr, obj, oiu, ost, obi WHERE orc.orc_key = oiu.orc_key AND  oiu.usr_key = usr.usr_key AND oiu.ost_key = ost.ost_key 
   AND oiu.obi_key = obi.obi_key AND obi.obj_key = obj.obj_key AND obj.obj_name='ABC' order by usr.usr_login
   

2. Entitlement status of the user

   select usr.usr_login,ENT_LIST.ent_display_name,
   ENT_LIST.ent_value,ENT_ASSIGN.ent_status 
   from ENT_ASSIGN, usr, ENT_LIST where usr.usr_key = ent_assign.usr_key and 
   ENT_LIST.ent_list_key = ENT_ASSIGN.ent_list_key 
   and ENT_LIST.ent_value like 'ABC' order by usr.usr_login,ENT_DISPLAY_NAME;
   

3. Role and Access Policy mapping

   select pol.pol_name, poc.poc_field_value from pol, poc where poc.pol_key  = pol.pol_key AND poc.poc_field_name = 'ABC' order by pol.pol_name, poc.poc_field_value
   

4. To check Role assigned to User

   select usr.usr_login, ugp.ugp_name from usg usg left outer join usr usr on (usg.usr_key = usr.usr_key) 
       left outer join ugp ugp on (ugp.ugp_key = usg.ugp_key) 
   where ugp_name ='ABC'