Hi Helen,

You would need to either use a query with connect by clause or utilise view FND_USER_ROLE_RUNTIME (it may need for security cache to be refreshed after any changes).

Thanks,

Pawel

@Helen Smith  Pawel’s answer above is likely best for your situation, but you could also use the “Security per User” report.  This will also show all of the permission sets granted directly and indirectly to a user, but it will also include all of the objects granted to each of those permission sets.

It looks like the extra information will probably be too much for your requirement here, but it might help others who are trying to get the full list of permission sets so they can then identify what objects are actually granted.

Nick

You could use the view fnd_role_role which may be easier to read as it shows the link between End User Role and Functional Role.

select
a.grantee "User",
a.granted_role "End User Role",
(select listagg(b.granted_role, ', ') WITHIN GROUP (ORDER BY b.grantee) from fnd_role_role b where b.grantee = a.granted_role and b.grantee_type = 'ROLE') "Functional Role(s)"
from fnd_role_role a
where a.grantee_type = 'USER'
order by a.grantee, a.granted_role asc

Whereas just using the FND_USER_ROLE_RUNTIME view alone would give you the following which does not show the structure

You can query the user window from the username as follows (Create User → Search -> Summary Tab). This should list down all permission sets granted to the user