# SQL to Find Responsibilities Assigned to a User in Oracle EBS > Source: https://netray.co/erp-help/ebs-sql-user-responsibilities-query > Publisher: Netray (netray.co), AI and integration engineering for ERP and manufacturing systems. > Section: ERP Help ## 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. ## Kind how-to ## System oracle-ebs ## Erp Oracle E-Business Suite ## Module System Administration / Security ## Query SQL query to find responsibilities assigned to a user in Oracle EBS ## Alternate Queries - 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 ## Applies To Oracle E-Business Suite 11i, 12.1 and 12.2 (FND security tables are stable across these releases) ## Steps ### Write and run the query **Items:** - Start from FND_USER and filter WHERE USER_NAME = 'JSMITH' to get USER_ID; usernames are stored uppercase. - Join to FND_USER_RESP_GROUPS on FND_USER_RESP_GROUPS.USER_ID = FND_USER.USER_ID for the responsibility and security group assignments. - Join to FND_RESPONSIBILITY_TL on RESPONSIBILITY_ID and APPLICATION_ID, filtering LANGUAGE = 'US' to avoid duplicate rows from other installed languages. - Add FND_APPLICATION for the application short name so you can see which product each responsibility belongs to. - Filter 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. - Run 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. ## Sections ### The query **Body:** 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. **Code:** 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 **Body:** 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'. **Code:** - -- Replace the fu.user_name filter above with: AND frt.responsibility_name = 'Payables, Vision Operations (USA)' ### Security groups and menu exclusions **Body:** 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. ## 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 ## Ai Angle 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. ## Faq ### Why do I see duplicate rows for the same responsibility? **Answer:** 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? **Answer:** 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? **Answer:** 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? **Answer:** 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. ## Search terms fnd_user_resp_groups sql, oracle ebs find user responsibilities, sql query user responsibility oracle apps, fnd_responsibility_tl join, oracle ebs security audit sql, list active responsibilities oracle ebs, ebs sox access review sql, fnd_user query --- Talk to an engineer: https://netray.co/contact?intent=erp_ai | Machine catalog: https://netray.co/agent/catalog.json