FND User Roles – Oracle EBS SQL Report

Oracle E-Business Suite SQL report from the Enginatics Library powered by Blitz Report™. Oracle EBS module: Application Object Library · all Application Object Library reports

Overview

Report for User Management (UMX) roles and their assigned users to manage role-based access control (RBAC). User uoles are maintained from the User Management responsibility, but you would require the UMX|SECURITY_ADMIN role for this. If you do not have this role but apps DB access, you can add it from the backend:

begin wf_local_synch.propagateuserrole( p_user_name=>’ANDY.HAACK’, p_role_name=>’UMX|SECURITY_ADMIN’ ); commit; end;

Run this report in Excel in seconds: download Blitz Report™ free (your first 30 reports are free) or try it in the online demo.

Report Parameters

User Name, Role Name, Role Code, Active only

Oracle EBS Tables Used

wf_local_roles, wf_local_roles_tl, fnd_responsibility_vl, wf_local_user_roles, wf_user_role_assignments

Report Categories

Enginatics

FND Roles, OKL Termination Quotes, FND Role Hierarchy, Blitz Report Template Sharing

Running This SQL Without Blitz Report

The SQL source can be run in any SQL client. Some Oracle EBS SQL reports in this library use functions from the utility package xxen_util – install it first. Blitz Report™ adds parameters, Excel output with pivots, scheduling and security on top.

Download & Import Options

Resource Link
Excel Example Output FND User Roles 10-Jan-2021 130904.xlsx
Blitz Report™ XML Import FND_User_Roles.xml
SQL Source FND User Roles.sql
Full Report on Enginatics www.enginatics.com/reports/fnd-user-roles/

Executive Summary

The FND User Roles report details the assignment of User Management (UMX) roles to users. It is a key component for managing Role-Based Access Control (RBAC) in Oracle EBS, providing visibility into who holds which roles.

Business Challenge

As organizations move towards RBAC, access is increasingly granted via Roles rather than direct Responsibility assignments. Managing these role assignments is critical. Administrators need to ensure that users have the correct roles for their job functions and that roles are not over-provisioned.

The Solution

This report provides a clear mapping between Users and Roles. It helps security teams:

Technical Architecture

The report utilizes the Workflow directory service tables (wf_local_roles, wf_user_role_assignments) which underpin the UMX security model.

Parameters & Filtering

Performance & Optimization

The report joins user and role tables. In systems with very large numbers of users and complex role hierarchies, filtering by User or Role is recommended.

FAQ

Q: How is this different from FND User Responsibilities? A: FND User Responsibilities shows the result of access (the responsibilities). FND User Roles shows the mechanism of access (the roles). A single role might grant multiple responsibilities.

Q: Can I use this to add roles? A: This is a reporting tool. However, the description includes a PL/SQL snippet showing how to grant roles via the backend if needed.

© Enginatics GmbH