OIM11gR1: Query to find who has assigned what role to which user

You can use below query to find who has assigned what role to which user on what date:

select
extractvalue(upa_xml.deltas,'/Changes/Change/Attribute[@name=''Groups.Group Name'']/NewValue') AS "Role Assigned",
usr.usr_login "Assigned To",
extractvalue(upa_xml.deltas,'/Changes/Change/Attribute[@name=''Groups-Users.Created By Login'']/NewValue') AS "Assigned By",
extractvalue(upa_xml.deltas,'/Changes/Change/Attribute[@name=''Groups-Users.Membership Type'']/NewValue') AS "Membership Type",
extractvalue(upa_xml.deltas,'/Changes/Change/Attribute[@name=''Groups-Users.Creation Date'']/NewValue') AS "Assigned On"
from upa,(SELECT upa_key, xmltype(deltas) deltas FROM upa) upa_xml,usr where upa.upa_key = upa_xml.upa_key and usr.usr_key=upa.usr_key and
SRC LIKE '%CREATE.RoleUser%';

In 11gR2, the usg table contains the field usg_prov_by which contains the usr_key of the user who has assigned the role. You can use the below query:

select ugp_name as "Assigned Role", usr_login as "Assigned To", usg.usg_prov_by as "Assigned By" from usr,ugp,usg
where usr.usr_key=usg.usr_key and ugp.ugp_key=usg.ugp_key and ugp.ugp_name NOT IN ('ALL USERS','SYSTEM ADMINISTRATORS');

OIM Audit Table: Sample Queries to Find Who did What and When

Here are few sample queries provided by Oracle to find who did what and when:

List the last change set for user with usr_key where the changes are represented  in a relational format:


SELECT usr_key,   usr_login as changed_by_user,  upa_usr.upa_usr_eff_from_date AS changed_time,   field_name,   field_old_value,   field_new_value
 FROM upa_usr, upa_fields,   (SELECT field_new_value AS changed_by_user    FROM upa_fields
   WHERE upa_fields_key =  (SELECT MAX(upa_fields_key)      FROM upa_fields, upa_usr
     WHERE upa_usr.upa_usr_key = upa_fields.upa_usr_key
     AND upa_usr.usr_key = <>
     AND upa_fields.field_name ='Users.Updated By Login'
     )
   )
 WHERE upa_usr.upa_usr_key = upa_fields.upa_usr_key
 AND upa_usr.usr_key = <>;


References:

Sample Queries to Find Who did What and When? (Doc ID 1472028.1)