OIM SQL query to find who assigned role to users

OIM provides strong auditing features that will capture all user profile modification. It will be stored on UPA table.

Below are tables stores users and audit information:

Table Name	Table Description
USR	It contains user information like login id, password, etc.,
UPA	User profile audit information

SQL Query:

Below query gets list of roles when was assigned to user ‘chellappan.sampath’:

select * from upa 
where usr_key = (select usr_key from usr 
                 where lower(usr_login)= 'chellappan.sampath')
and src like '%RoleManager%CREATE%';

Similarly, we can check for user role revoked by using src with ‘%RoleManager%DELETE%'