Search Results jtf_um_subscription_role




Overview
Explore in AI Chat

Overview

The JTF_UM_SUBSCRIPTION_ROLE table is a core intersection entity within the Oracle E-Business Suite CRM Foundation (JTF) module, specifically for the User Management (UM) functionality. It serves as a critical junction table that defines the relationship between user enrollments (subscriptions) and security roles. Its primary role is to manage and persist the assignment of authorization roles to specific user subscriptions, thereby controlling access and functional privileges within the CRM and related applications. This table is essential for the proper enforcement of role-based access control (RBAC) in the EBS environment, linking the user's enrollment context to the granular permissions defined by a role.

Key Information Stored

The table stores the direct association between a subscription and a role, with its structure enforcing data integrity through primary and foreign keys. The key columns, as defined by the primary key constraint JTF_UM_SUBSCRIPTION_ROLE_PK, are SUBSCRIPTION_ID, PRINCIPAL_NAME, and EFFECTIVE_START_DATE. The SUBSCRIPTION_ID is a foreign key to JTF_UM_SUBSCRIPTIONS_B, identifying the specific user enrollment. The PRINCIPAL_NAME is a foreign key to JTF_AUTH_PRINCIPALS_B, identifying the security role (principal) being assigned. The inclusion of EFFECTIVE_START_DATE in the primary key allows for the management of role assignments over time, supporting effective-dated changes to a user's privileges. This intersection model ensures that a single subscription can be associated with multiple roles and a single role can be granted to multiple subscriptions.

Common Use Cases and Queries

A primary use case is auditing and reporting on role assignments for compliance or access review. System administrators often query this table to understand which roles are granted to users through specific subscriptions, such as those for a particular responsibility or product suite. A common query pattern involves joining to the subscription and principal tables to retrieve meaningful names. For example, to list all role assignments for a given subscription:

  • SELECT sub.SUBSCRIPTION_NAME, role.PRINCIPAL_NAME, sr.EFFECTIVE_START_DATE FROM JTF.JTF_UM_SUBSCRIPTION_ROLE sr, JTF.JTF_UM_SUBSCRIPTIONS_B sub, JTF.JTF_AUTH_PRINCIPALS_B role WHERE sr.SUBSCRIPTION_ID = sub.SUBSCRIPTION_ID AND sr.PRINCIPAL_NAME = role.PRINCIPAL_NAME AND sub.SUBSCRIPTION_ID = :p_sub_id;

Another critical use case is during the user provisioning process, where this table is populated via the User Management APIs to grant appropriate roles when a user is enrolled in a new subscription.

Related Objects

The JTF_UM_SUBSCRIPTION_ROLE table has defined dependencies on two key master tables, as per its foreign key constraints. The SUBSCRIPTION_ID column references JTF_UM_SUBSCRIPTIONS_B, the core table defining user enrollments and subscriptions. The PRINCIPAL_NAME column references JTF_AUTH_PRINCIPALS_B, the master table for all security principals (roles) within the authorization system. This table is also intrinsically linked to the underlying User Management engine and its public APIs (e.g., in package JTF_UM_SUBSCRIPTION_PUB), which provide the supported methods for creating, updating, and deleting these role assignments rather than direct DML. Reports and administrative forms related to user access and role membership will inherently query this intersection table.