Search Results wf_all_user_roles




Overview
Explore in AI Chat

Overview

WF_ALL_USER_ROLES is a reporting view owned by the APPS schema in Oracle E-Business Suite, registered under the FND - Application Object Library product. It presents the effective mapping between EBS users and the roles assigned to them, and is one of the primary read-only interfaces used by Oracle Workflow, the User Management (UMX) infrastructure, and third-party integrations to determine who holds which role at a given point in time. Because role assignments in EBS are frequently replicated from external directories, the view exposes both the native Oracle identifiers and the originating system identifiers for the user, the role, and the assignment itself, making it suitable for reconciliation and audit reporting across heterogeneous identity stores.

The search term "role_orig_system" corresponds directly to the ROLE_ORIG_SYSTEM column documented in the view text. This column identifies the source system from which the role definition originated, and it is complemented by ROLE_ORIG_SYSTEM_ID, which carries the unique key of that role within the source system.

Underlying Base Objects

The view is defined exclusively over WF_LOCAL_USER_ROLES, which is referenced in ETRM 12.2.2 as a synonym. WF_LOCAL_USER_ROLES is the local staging table maintained by the Workflow Directory Services / UMX synchronization processes. It holds the flattened user-to-role relationships that have been resolved into the EBS instance, regardless of whether the originating assignment was created natively in EBS or loaded through bulk synchronization from an LDAP directory such as Oracle Internet Directory or an external HR system.

WF_ALL_USER_ROLES is a thin projection: it selects a defined subset of columns from that base synonym without joins, filters, or aggregation. Consequently, all referential and integrity constraints, as well as the performance profile, are inherited from WF_LOCAL_USER_ROLES. The term "ALL" in the view name signals that the view is not constrained to the currently connected user, unlike user-scoped Workflow views that restrict results based on session context.

Key Columns

  • USER_NAME – the EBS username of the assignee.
  • ROLE_NAME – the internal name of the role granted to the user.
  • USER_ORIG_SYSTEM / USER_ORIG_SYSTEM_ID – the source system and source key for the user principal, used to trace the user back to the originating directory.
  • ROLE_ORIG_SYSTEM / ROLE_ORIG_SYSTEM_ID – the source system and source key for the role; this is the pair most relevant to queries referencing role_orig_system. Both are listed twice in the documented view text.
  • ASSIGNMENT_TYPE – indicates how the assignment arose (for example, direct grant versus group-derived or responsibility-driven).
  • START_DATE / EXPIRATION_DATE – the validity window of the assignment; an assignment is effective when the current date falls between these values and EXPIRATION_DATE is null or in the future.
  • OWNER_TAG – a label used by Workflow to associate the assignment with an owning entity or process.
  • ASSIGNMENT_REASON – free-text or coded explanation of why the role was granted.
  • PARTITION_ID – the partition grouping used by multi-tenant or logically separated directory configurations.
  • CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN – standard EBS audit columns identifying who created and last changed the row.

Common Use Cases and Queries

The most frequent use of this view is auditing role membership and tracing roles back to their source directory. To find every user holding roles originating from a specific external system:

  • SELECT user_name, role_name, role_orig_system, role_orig_system_id FROM wf_all_user_roles WHERE role_orig_system = '&orig_system' ORDER BY user_name, role_name;

To identify all currently effective assignments for a given user, filtering on the validity window prevents reporting on expired grants:

  • SELECT role_name, assignment_type, start_date, expiration_date FROM wf_all_user_roles WHERE user_name = '&user_name' AND (expiration_date IS NULL OR expiration_date > SYSDATE) AND start_date <= SYSDATE;

Reconciliation queries compare the EBS-resident assignment set against the authoritative directory, joining ROLE_ORIG_SYSTEM_ID to the external system's role key. Because the view does not restrict rows to the session user, it is also the correct source for administrative extracts, responsibility-to-role impact analysis, and scheduled reports that must enumerate every user-role pairing in the instance.