FND Role Hierarchy – 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

User Management (UMX) role hierarchy to manage role-based access control (RBAC). When run for a specified role, the report shows all hierarchies that contain or lead to inheriting that role.

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

Role Name, Role Code

Oracle EBS Tables Used

umx_role_categories_v, wf_role_hierarchies, wf_roles, fnd_application_vl

Report Categories

Enginatics

FND Roles, FND User Roles, AR Customer Upload, 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 Role Hierarchy 10-Jan-2021 133945.xlsx
Blitz Report™ XML Import FND_Role_Hierarchy.xml
SQL Source FND Role Hierarchy.sql
Full Report on Enginatics www.enginatics.com/reports/fnd-role-hierarchy/

Executive Summary

The FND Role Hierarchy report visualizes the structure of User Management (UMX) roles, specifically focusing on role inheritance. It allows administrators to understand how roles are nested and how permissions flow from child roles to parent roles within the Role-Based Access Control (RBAC) model.

Business Challenge

In complex RBAC implementations, roles often inherit permissions from other roles. As these hierarchies grow deep, it becomes difficult to trace exactly why a user has a certain permission or to predict the impact of changing a child role. Lack of visibility into this hierarchy can lead to unintended privilege escalation or redundant role definitions.

The Solution

This report unravels the role hierarchy. When run for a specific role, it shows all the hierarchies that contain or lead to inheriting that role. This provides a clear lineage of access, helping administrators:

Technical Architecture

The report utilizes the Workflow directory service tables, specifically wf_role_hierarchies and wf_roles, which underpin the UMX security model in Oracle EBS.

Parameters & Filtering

Performance & Optimization

The report is efficient for analyzing specific roles. Querying the entire hierarchy of a large system without filters may produce a large dataset due to the many-to-many nature of role relationships.

FAQ

Q: Is this related to Responsibilities? A: Yes, in the UMX model, responsibilities can be assigned to roles, and roles can be assigned to users. This report focuses on the relationship between the roles themselves.

Q: Can I see which users are assigned to these roles? A: This report focuses on the role structure. Use the FND Roles or FND User Roles reports to see user assignments.

© Enginatics GmbH