Search Results per_sec_profile_assignments

Overview

The PER_SEC_PROFILE_ASSIGNMENTS table is a core security data object within the Oracle E-Business Suite Human Resources (PER) module. It functions as the central repository for defining and storing the assignment of security profiles to users, responsibilities, and business groups. Security profiles are a critical component of the Oracle HRMS security model, determining the organizational data—such as employees, positions, and organizations—that a user can access and modify. This table enables the flexible and granular control of data access by linking specific security profiles to user sessions based on their responsibility, business group context, and security group (in a multi-organization access control environment). Its role is fundamental to enforcing data security and segregation of duties across the HRMS and integrated applications.

Key Information Stored

The table stores the linkage between security profile definitions and the contexts in which they are active. Its primary and unique key structure highlights the critical data dimensions. The surrogate primary key is SEC_PROFILE_ASSIGNMENT_ID. The unique key (PER_SEC_PROFILE_ASSIGNMENTS_UK) defines the business logic, comprising the following essential columns: USER_ID (identifies the FND_USER), RESPONSIBILITY_ID and RESPONSIBILITY_APPLICATION_ID (identify the responsibility from FND_RESPONSIBILITY), SECURITY_GROUP_ID (for VPD security), BUSINESS_GROUP_ID (the HR operating unit), and SECURITY_PROFILE_ID (the profile from PER_SECURITY_PROFILES being assigned). The START_DATE and END_DATE columns control the active period of the assignment, allowing for temporal security management. The combination of these fields ensures a user, accessing via a specific responsibility and security group within a business group, is assigned the correct security profile for that session.

Common Use Cases and Queries

A primary use case is troubleshooting user access issues, where a user reports an inability to view expected employee or organizational records. Administrators query this table to verify active security profile assignments. Another critical use is auditing security configurations for compliance, generating reports on which profiles are assigned to which users and responsibilities. Common SQL patterns include joining to FND_USER, FND_RESPONSIBILITY, and PER_SECURITY_PROFILES for meaningful descriptions. For example, to list all active assignments for a specific user:

• SELECT pspa.*, fu.USER_NAME, fr.RESPONSIBILITY_NAME, psp.SECURITY_PROFILE_NAME
• FROM hr.per_sec_profile_assignments pspa,
• applsys.fnd_user fu,
• applsys.fnd_responsibility_tl fr,
• hr.per_security_profiles psp
• WHERE pspa.user_id = fu.user_id
• AND pspa.responsibility_id = fr.responsibility_id
• AND pspa.security_profile_id = psp.security_profile_id
• AND fu.user_name = '&USERNAME'
• AND SYSDATE BETWEEN pspa.start_date AND NVL(pspa.end_date, SYSDATE);

Related Objects

PER_SEC_PROFILE_ASSIGNMENTS has defined foreign key relationships with other key HR and foundation tables, as documented in the ETRM metadata. These relationships are essential for data integrity and meaningful queries:

• PER_SECURITY_PROFILES: Joined via PER_SEC_PROFILE_ASSIGNMENTS.SECURITY_PROFILE_ID = PER_SECURITY_PROFILES.SECURITY_PROFILE_ID. This retrieves the definition and rules of the assigned security profile.
• HR_ALL_ORGANIZATION_UNITS: Joined via PER_SEC_PROFILE_ASSIGNMENTS.BUSINESS_GROUP_ID = HR_ALL_ORGANIZATION_UNITS.ORGANIZATION_ID. This provides the business group name and details.

In practice, queries also frequently join to FND_USER (on USER_ID) and FND_RESPONSIBILITY (on RESPONSIBILITY_ID and RESPONSIBILITY_APPLICATION_ID) to resolve IDs to human-readable names, though these are not listed as formal foreign keys in the provided excerpt.