Search Results per_security_organizations




Overview
Explore in AI Chat

Overview

The PER_SECURITY_ORGANIZATIONS table is a core data object within the Oracle E-Business Suite Human Resources (PER) module, specifically for versions 12.1.1 and 12.2.2. It functions as a junction table that defines the relationship between security profiles and the organizational units they govern. Its primary role is to store the explicit list of organizations that are either included in or excluded from a user's data access when a security profile is applied. This table is essential for implementing fine-grained, organization-based data security, enabling administrators to control which business groups, departments, or other organizational structures a user can view or modify within the HRMS application.

Key Information Stored

The table's structure is designed to link security profiles to specific organizations. While the full column list is not detailed in the provided metadata, the documented foreign keys and primary key reveal its critical components. The SECURITY_ORGANIZATION_ID column serves as the table's unique primary key. The two fundamental foreign key columns are SECURITY_PROFILE_ID, which links to the PER_SECURITY_PROFILES table to identify the security rule, and ORGANIZATION_ID, which links to the HR_ALL_ORGANIZATION_UNITS table to identify the specific organizational unit. The table likely includes additional columns to specify whether the organization is part of an inclusion or exclusion list, and potentially an effective date range to manage changes over time.

Common Use Cases and Queries

This table is central to security administration and auditing. A common use case involves diagnosing a user's data access issues by reviewing which organizations are attached to their assigned security profile. System administrators frequently query this table to generate reports on security profile definitions or to validate security setup during implementations. A typical analytical query would join PER_SECURITY_ORGANIZATIONS to PER_SECURITY_PROFILES and HR_ALL_ORGANIZATION_UNITS to produce a human-readable list.

Sample Query:
SELECT psp.SECURITY_PROFILE_NAME, hou.NAME ORGANIZATION_NAME
FROM hr.PER_SECURITY_ORGANIZATIONS pso,
hr.PER_SECURITY_PROFILES psp,
hr.HR_ALL_ORGANIZATION_UNITS hou
WHERE pso.SECURITY_PROFILE_ID = psp.SECURITY_PROFILE_ID
AND pso.ORGANIZATION_ID = hou.ORGANIZATION_ID
AND psp.SECURITY_PROFILE_ID = :profile_id
ORDER BY hou.NAME;

Related Objects

The PER_SECURITY_ORGANIZATIONS table has defined relationships with two key HRMS tables, as per the provided foreign key metadata:

  • PER_SECURITY_PROFILES: This is the parent table for security profile definitions. The relationship is established via the SECURITY_PROFILE_ID column in PER_SECURITY_ORGANIZATIONS. Each record in PER_SECURITY_ORGANIZATIONS must correspond to a valid security profile.
  • HR_ALL_ORGANIZATION_UNITS: This is the master table for all organizations within the HRMS module. The relationship is established via the ORGANIZATION_ID column in PER_SECURITY_ORGANIZATIONS. Each record must point to a valid organization defined in this master table.

These relationships ensure referential integrity, meaning a security profile cannot reference an organization that does not exist, and all organization security assignments are tied to a valid security profile.