Skip to Main Content
Forums

Security Software

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

sql query to get the role assigned/revoked in last 7 days in OIM?

2656016May 29 2014 — edited Jun 2 2014


hi all,

does anyone have sql query to get the role assigned/revoked in last x days?

the query output should be

userlogin,rolename,status(revoked/assigned)

TIA

Comments

Kevin Pinsky

It's all in the table upa_grp_membership.

-Kevin

1 person found this helpful
2656016

thanks.

here are the query i used:

select usr.usr_login,LISTAGG(ugp.ugp_name, ';') WITHIN GROUP (ORDER BY ugp.ugp_name) from identmgr.upa_grp_membership,identmgr.usr,identmgr.ugp,identmgr.upa_usr

where usr.usr_key=upa_usr.usr_key  and upa_grp_membership.status='Active' AND ugp.ugp_key=upa_grp_membership.ugp_key and upa_grp_membership.upa_usr_key=upa_usr.upa_usr_key and

TRUNC(upa_grp_membership.CREATE_DATE) > TO_DATE('2014-06-01', 'YYYY-MM-DD') group by usr.usr_login;

select usr.usr_login,LISTAGG(ugp.ugp_name, ';') WITHIN GROUP (ORDER BY ugp.ugp_name) from identmgr.upa_grp_membership,identmgr.usr,identmgr.ugp,identmgr.upa_usr

where usr.usr_key=upa_usr.usr_key  and upa_grp_membership.status='Revoked' AND ugp.ugp_key=upa_grp_membership.ugp_key and upa_grp_membership.upa_usr_key=upa_usr.upa_usr_key and

TRUNC(upa_grp_membership.CREATE_DATE) > TO_DATE('2014-06-01', 'YYYY-MM-DD') group by usr.usr_login;

1 - 2
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Jun 30 2014
Added on May 29 2014
#audit, #identity-management, #identity-manager, #query, #sql
2 comments
342 views