Top users by active role count
Active users ranked by number of currently-effective role assignments. High counts can indicate over-provisioning.
Tables used
per_user_roles per_users
Bind parameters
None — the statement runs as written; edit the literals in the WHERE clause to narrow it.
How to run it
Paste it into a BI Publisher data model as a SQL data set, or into QueryWell's Query Studio and run it against your pod with a reporting account. Results come back to a grid you can profile, chart and export with a SHA-256 hash.
SELECT u.username, COUNT(*) AS role_count, MAX(ur.creation_date) AS last_grant
FROM per_user_roles ur
JOIN per_users u ON u.user_id = ur.user_id
WHERE u.active_flag = 'Y' AND (ur.end_date IS NULL OR ur.end_date >= TRUNC(SYSDATE))
GROUP BY u.username
ORDER BY role_count DESCA single read-only SELECT against Oracle Fusion Cloud's supported reporting surface. Column names and views can differ by release and by the roles your reporting account holds, so validate the result against a report you already trust before relying on it.
securityrolesprovisioning
More Security queries
Active Fusion application users
All active application users (per_users). Basic user inventory. (Fusion does not expose email or last-logon on this table.)
Direct role assignments per user
Every role currently granted directly to a user (autoprovisioned + manual). High-volume — filter to specific user via :username.
Run it without the template cycle
QueryWell runs this and 81 other curated scripts against your own Fusion pod from one signed desktop app — grid, charts, profiling and hashed exports included. Perpetual licence, 14-day trial.