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');