OIM SQL query to get users whose specific role

We often may need to find user who has specific role in OIM.

Table Name	Table Description
USR	It contains user information like login id, password, etc.,
USG	Role assigned to user

SQL Query:

In this below example, I have used query to get users who have role called ‘System Administrator’.

select usr.usr_display_name, usr.usr_login, usr.usr_email, 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 upper(ugp_name) in (upper('System Administrator'));