How-toOracle E-Business SuiteSystem Administration / Security

SQL to Find Responsibilities Assigned to a User in Oracle EBS

Question

SQL query to find responsibilities assigned to a user in Oracle EBS

Also searched as

fnd_user_resp_groups query
oracle ebs list user responsibilities sql
sql to check active responsibilities for a user
oracle apps who has this responsibility sql

Short answer

Join FND_USER to FND_USER_RESP_GROUPS on USER_ID, then to FND_RESPONSIBILITY_TL on RESPONSIBILITY_ID and APPLICATION_ID to get the responsibility name, filtering the END_DATE columns to show only currently active assignments. This is the standard query DBAs and support teams use instead of clicking through System Administrator > Security > User > Define for every user.

Applies to: Oracle E-Business Suite 11i, 12.1 and 12.2 (FND security tables are stable across these releases)

Write and run the query

1Start from FND_USER and filter WHERE USER_NAME = 'JSMITH' to get USER_ID; usernames are stored uppercase.
2Join to FND_USER_RESP_GROUPS on FND_USER_RESP_GROUPS.USER_ID = FND_USER.USER_ID for the responsibility and security group assignments.
3Join to FND_RESPONSIBILITY_TL on RESPONSIBILITY_ID and APPLICATION_ID, filtering LANGUAGE = 'US' to avoid duplicate rows from other installed languages.
4Add FND_APPLICATION for the application short name so you can see which product each responsibility belongs to.
5Filter active assignments with (FURG.END_DATE IS NULL OR FURG.END_DATE > SYSDATE), and apply the same pattern to FND_USER.END_DATE for the user account itself.
6Run the query from a read-only reporting schema or as APPS with SELECT only, never with DML rights against these tables, and wrap it in a view if support staff will reuse it often.

The query

This is the standard three-table join used to list a user's active responsibilities, filtered to a single language so the seeded responsibility name is not duplicated once per installed language.

Swap the WHERE clause on fu.user_name for any username, uppercase, and the query runs safely as a read-only SELECT against seeded FND tables in any environment, including production.

SELECT fu.user_name,
       fa.application_short_name,
       frt.responsibility_name,
       furg.start_date,
       furg.end_date
FROM   fnd_user fu,
       fnd_user_resp_groups furg,
       fnd_responsibility_tl frt,
       fnd_application fa
WHERE  fu.user_id = furg.user_id
AND    furg.responsibility_id = frt.responsibility_id
AND    furg.responsibility_application_id = frt.application_id
AND    furg.responsibility_application_id = fa.application_id
AND    frt.language = 'US'
AND    fu.user_name = 'JSMITH'
AND    (furg.end_date IS NULL OR furg.end_date > SYSDATE)
ORDER BY fa.application_short_name, frt.responsibility_name;

Reverse lookup: who has a given responsibility

Flip the filter to the responsibility side, on frt.responsibility_name or FND_RESPONSIBILITY.RESPONSIBILITY_KEY, to find every currently active user holding a sensitive responsibility. This is the query most SOX and access review checklists reduce to when the request is 'show me everyone with AP Super User'.

-- Replace the fu.user_name filter above with:
AND    frt.responsibility_name = 'Payables, Vision Operations (USA)'

Security groups and menu exclusions

FND_USER_RESP_GROUPS also carries SECURITY_GROUP_ID, relevant when Multi-Org Access Control or HR security profiles are in play, since the same responsibility can be assigned more than once with a different security group per row.

A responsibility having a menu or function exclusion, stored in FND_RESP_FUNCTIONS, means two users holding the same responsibility can still see different menu items. The query above proves assignment, not the effective menu a given user actually sees.

Common pitfalls

!Forgetting the LANGUAGE = 'US' filter on FND_RESPONSIBILITY_TL and getting duplicate rows per installed language
!Comparing USER_NAME in lowercase when FND_USER stores it uppercase, returning zero rows
!Ignoring END_DATE and reporting an expired or disabled responsibility assignment as if it were still active
!Joining only on RESPONSIBILITY_ID without also joining on APPLICATION_ID, which can pull the wrong responsibility name when ids overlap across applications
!Running ad hoc updates against these FND tables directly instead of through the Users or Responsibilities forms, bypassing the standard grant and revoke auditing
!Treating 'has the responsibility' as equivalent to 'can access the function', when menu exclusions can still hide a specific form or page

How an ERP-grounded AI assistant handles this

With an AI assistant grounded in the instance's FND security tables, a support analyst can ask 'who currently has AP Super User' or 'list JSMITH's active responsibilities' in plain English and get the query result back directly, instead of writing or re-finding this join every time a ticket comes in.

See ERPray

Frequently asked questions

Why do I see duplicate rows for the same responsibility?

This almost always means the LANGUAGE filter on FND_RESPONSIBILITY_TL was left out, so the query returns one row per installed language instead of one row per assignment.

How do I find only currently active responsibilities?

Filter on END_DATE IS NULL OR END_DATE > SYSDATE for the FND_USER_RESP_GROUPS row, and apply the same check to FND_USER.END_DATE so a disabled user account is not reported either.

Can I query which users can access a specific form or page?

Not directly from this join alone. Responsibility assignment does not account for menu exclusions in FND_RESP_FUNCTIONS, so a separate query against the responsibility's menu and its exclusions is needed to prove actual function-level access.

Is it safe to run this query against production?

Yes, it is a read-only SELECT against seeded FND tables with no locking concerns, which is why it is commonly run directly in production for access reviews and support tickets.

How-to

How to Use Forms Personalization in Oracle EBS (No CUSTOM.pll Needed)

Forms Personalization lets you change field properties, set defaults, run built-ins and show messages on any Oracle Forms screen through Help > Diagnostics > Custom Code > Personalize, with no custom library or forms compile required. Rules are stored per form and responsibility in FND_FORM_CUSTOM_RULES and apply at runtime for every user who opens that form.

Advanced

How to Extend an OAF Page in Oracle EBS Without Touching Seeded Code

Use OAF Personalization, through the Personalize Self-Service Defn responsibility or the Personalize Page link, for layout, prompts and simple rules, and Controller Extension, a custom Java class that extends the seeded controller, for logic changes such as new validation or events. Both approaches survive patching because the seeded XML and Java classes are never edited directly.

How-to

How to Import AP Invoices in Oracle EBS with Payables Open Interface Import

Load invoice header and line data into AP_INVOICES_INTERFACE and AP_INVOICE_LINES_INTERFACE, then run the Payables Open Interface Import concurrent program to create standard invoices in Oracle Payables. Rows that fail validation land in AP_INTERFACE_REJECTIONS with a specific reject reason you can query directly, without opening the Invoice Workbench.

Advanced

How the adop Patching Cycle Works in Oracle EBS 12.2

adop, AD Online Patching, applies patches to the offline patch edition of the file system while users keep working on the current run edition, then switches editions in a short cutover window. A standard cycle runs prepare, apply, finalize, cutover and cleanup, each invoked as its own adop phase= command.

Error fix

Fix FRM-40735: WHEN-VALIDATE-RECORD Trigger Raised Unhandled Exception ORA-06508

FRM-40735 with ORA-06508 almost always means a PL/SQL package used by the form was recompiled while your session still held the old cached state, or the trigger's own exception handling does not trap a real data error. Exit the form completely and re-enter it; if the error repeats for all users, recompile invalid objects on the database and check for a bad custom trigger.

Error fix

Oracle EBS Concurrent Manager Will Not Start: How to Fix It

A concurrent manager that will not start in Oracle EBS is almost always one of three things: the node registered in FND_NODES no longer matches the actual hostname (common after cloning), stale OS processes are blocking a fresh start, or the database and listener the manager connects to are unreachable. Check the internal manager log, verify the node name, clear stale processes, and restart with adcmctl.sh.

AI for ERP

AI for Oracle E-Business Suite, Without Leaving On-Prem

Add AI to Oracle E-Business Suite 12.2 without moving off-prem. Query concurrent programs, interface tables, and AP/PO data with a private LLM. See how.