Search Results igw_prop_user_roles_pk

Overview

The IGW_PROP_USER_ROLES table is a core data object within the Oracle E-Business Suite (EBS) Grants Proposal module (IGW). It functions as a junction table that establishes and manages the security and access model for proposals. Its primary role is to define the specific roles assigned to individual users for a given proposal, thereby controlling who can view, edit, or approve proposal data. This table is essential for enforcing a role-based access control (RBAC) framework within the proposal lifecycle, ensuring that users have appropriate and auditable permissions aligned with their responsibilities.

Key Information Stored

The table stores the unique association between a user, a proposal, and a role. Its structure is defined by a composite primary key, meaning each record is uniquely identified by the combination of three columns. The critical columns are:

• PROPOSAL_ID: References the specific proposal (from IGW_PROPOSALS_ALL) for which the role assignment is valid.
• USER_ID: Identifies the user (from FND_USER or related tables) who is assigned the role.
• ROLE_ID: Identifies the specific role (from IGW_ROLES) being granted to the user for the proposal, such as "Principal Investigator," "Budget Manager," or "Viewer."

Together, these columns create a granular permission record, allowing a single user to have different roles on different proposals.

Common Use Cases and Queries

This table is central to access control reporting and administrative functions. A common use case is generating a report of all users and their roles for a specific proposal for audit or management review. Another scenario involves troubleshooting user access issues by verifying assigned roles. Sample SQL patterns include listing roles for a user or validating access for a specific proposal-user-role combination.

• To find all roles for a user on a specific proposal:

  SELECT role_id FROM igw.igw_prop_user_roles WHERE proposal_id = &PROPOSAL_ID AND user_id = &USER_ID;

• To list all users and their roles for a proposal:

  SELECT user_id, role_id FROM igw.igw_prop_user_roles WHERE proposal_id = &PROPOSAL_ID ORDER BY user_id;

Related Objects

The IGW_PROP_USER_ROLES table maintains documented foreign key relationships with other critical tables in the IGW schema, ensuring referential integrity. These relationships are fundamental for accurate joins in queries and reports.

• IGW_PROP_USERS: The table references IGW_PROP_USERS via the columns PROPOSAL_ID and USER_ID. This links the role assignment to the broader record of user association with a proposal.
• IGW_ROLES: The table references IGW_ROLES via the ROLE_ID column. This join retrieves the descriptive name and definition of the assigned role.

The primary key IGW_PROP_USER_ROLES_PK (PROPOSAL_ID, USER_ID, ROLE_ID) is also likely referenced by other objects or child tables within the module to maintain data consistency.