Results for “per_security_organizations”

AI-generated from documented ETRM metadata — verify critical details on the linked pages.

Overview

The PER_SECURITY_ORGANIZATIONS table, owned by the HR schema within the PER (Human Resources) product module, stores the list of organizations that are either included in or excluded from a security list. It is a core component of Oracle E-Business Suite's organization-level security model, allowing administrators to define fine-grained access control over which organization units a given security profile can access. Each row associates an organization unit with a security profile and specifies whether that organization is granted or denied access.

From a heuristic Data Vault modeling perspective, this table is best classified as a link entity. It resolves a many-to-many relationship between two hub-like entities—organizations and security profiles—by capturing the association between them. As a link, it records the intersection of these two dimensions, with its own surrogate key and relationship semantics.

Key Information Stored

The table contains 10 documented columns. The most significant are outlined below:

• SECURITY_ORGANIZATION_ID — The surrogate primary key defined by the constraint PER_SECURITY_ORGANIZATIONS_PK. It uniquely identifies each row in the table.
• ORGANIZATION_ID — A foreign key referencing HR_ALL_ORGANIZATION_UNITS. This column holds the identifier of the organization unit being included or excluded from the security list.
• SECURITY_PROFILE_ID — A foreign key referencing PER_SECURITY_PROFILES. This identifies the security profile to which the organization inclusion or exclusion rule applies.
• ENTRY_TYPE — A discriminator that specifies whether the referenced organization is included in or excluded from the security profile's list. This column determines the nature of the access rule.
• OBJECT_VERSION_NUMBER — Supports optimistic locking, ensuring concurrent updates to the same row are handled consistently.
• Audit columns — CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, and LAST_UPDATE_LOGIN capture standard who/when audit information for each row.

No unique business-key index beyond the primary key is documented; the surrogate SECURITY_ORGANIZATION_ID remains the sole unique identifier.

Common Use Cases and Queries

A frequent scenario involves listing all organizations included in a specific security profile. The following SQL pattern joins the table to HR_ALL_ORGANIZATION_UNITS to resolve organization names:

• Selecting included organizations: SELECT o.name FROM per_security_organizations pso JOIN hr_all_organization_units o ON o.organization_id = pso.organization_id WHERE pso.security_profile_id = :profile_id AND pso.entry_type = 'INCLUDE';
• Auditing exclusions: query rows where ENTRY_TYPE indicates exclusion to verify a profile's deny rules.
• Security troubleshooting: identify which organizations a user can access by traversing from PER_SECURITY_PROFILES through this table to HR_ALL_ORGANIZATION_UNITS.
• Reporting: producing a matrix of security profiles versus organizations for compliance reviews and segregation-of-duties analysis.

Because the table is a pure intersection entity, reporting queries generally focus on counts or membership lookups rather than aggregation of measures.

Related Objects

The most significant objects related to PER_SECURITY_ORGANIZATIONS, based on documented foreign key relationships, include:

• HR_ALL_ORGANIZATION_UNITS — joined via PER_SECURITY_ORGANIZATIONS.ORGANIZATION_ID; supplies organization names and hierarchy details.
• PER_SECURITY_PROFILES — joined via PER_SECURITY_ORGANIZATIONS.SECURITY_PROFILE_ID; defines the parent security profile.
• PER_SECURITY_ORGANIZATION_PK — the primary key constraint enforcing uniqueness on SECURITY_ORGANIZATION_ID.
• PER_SECURITY_PROFILE_PK — the referenced unique constraint on PER_SECURITY_PROFILES.
• PER_ALL_PEOPLE_F — indirectly related through security profile assignments used to derive user access.

Together these objects form the backbone of Oracle EBS organization security, with PER_SECURITY_ORGANIZATIONS acting as the pivotal link between organizations and the profiles that govern access to them.