2
  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?

Sharmistha Mitra
  • 126
  • 1
  • 7
  • This is very unclear, what are you trying to accomplish here ? You should consider adding more information and maybe read the [How to ask](http://stackoverflow.com/help/how-to-ask) article. – Nicolas Feb 27 '17 at 13:46
  • I am new to this site, yes I will surely work through your suggestions. Thanks , – Sharmistha Mitra Feb 28 '17 at 04:40
  • It's fine, that's why we are helping you with your question, Welcome to Stackoverflow ! – Nicolas Feb 28 '17 at 13:20

1 Answers1

6

  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'
Chaitanya K
  • 1,827
  • 4
  • 32
  • 67
Sharmistha Mitra
  • 126
  • 1
  • 7
  • For Role and access policy mapping, here's an alternative query - `Select POL_NAME,ugp.ugp_display_name "ROLE" from POL, UGP where pol.pol_owner=ugp.ugp_key;` – OopsDev Jun 13 '19 at 06:22