Extending Security in Oracle HRMS

Extending Security in Oracle HRMS

Oracle Human Resources provides a flexible approach to controlling access to tables, records, fields, forms, and functions. You can match each employee's level of access to their responsibilities.

For a discussion of security in Oracle HRMS and how to set it up to meet your requirements, refer to the help topics on Security, Oracle HRMS Configuring, Reporting, and System Administration Guide, and to the implementation steps for User Security

This essay does not repeat the definitions and description in the setup steps and security chapter. It builds on that information to describe the objects and processes that implement the security system. Read this essay if you need to:

Security Profiles

All Oracle Applications users access the system through a responsibility that is linked to a security group and a security profile. The security group determines which business group the user can access. The security profile determines which records (related to organizations, positions and payrolls) the user can access within the business group.

There are two types of security profile:

A responsibility with an unrestricted security profile has unrestricted access to data in Oracle HRMS tables. It connects to the APPS Oracle User. If you connect to an unrestricted security profile, the data you see when you select from a secure view is the same data you see if you select from the table on which the secure view is based.

When you connect to the APPS Oracle User with a restricted security profile you can access the secure tables directly if you want to bypass the security restrictions defined in your security profile. You might want to do this to perform uniqueness checks, or to resolve foreign keys.

Restricted security profiles can optionally make use of read-only, or reporting users. These are separate Oracle Users, one per restricted security profile, that have read-only access to Oracle tables and views. Reporting users do not have execute privilege on Oracle HRMS PL/SQL packages, and do not have direct access to the secured Oracle HRMS tables.

Restricted security profiles may restrict access to the following entities (the exact restrictions are determined by the definition of the security profiles):

All other entities are unrestricted; that is, restricted security profiles can access all records of tables, views and sequences associated with these entities.

Secure Tables and Views

The following Oracle HRMS tables are secured:

Some of these tables (namely PER_ALL_PEOPLE_F, PER_ALL_ASSIGNMENTS_F, HR_ALL_POSITIONS_F, and PAY_ALL_PAYROLLS_F) are datetracked. The following table details the views that are based on the secured tables listed above.

Table of Secure Tables and Views
Table or View Description
HR_ORGANIZATION_UNITS Secure view of Organization table
HR_ALL_ORGANIZATION_UNITS Organization table
PER_ORGANIZATION_UNITS Secure view of Organization view (HR Orgs only)
PER_ALL_ORGANIZATION_UNITS Unsecured view of Organization view (HR Orgs only)
HR_ALL_POSITIONS Unrestricted view of datetracked Positions table, effective at session date
HR_ALL_POSITIONS_F Datetracked Positions table
HR_POSITIONS Secure view of datetracked Positions table, effective at session date
HR_POSITIONS_F Secure view of datetracked Positions table
HR_POSITIONS_X Secure view of datetracked Positions table, effective at system date
PER_POSITIONS Secure view of non-datetracked Positions table
PER_ALL_POSITIONS Non-datetracked Positions table
PER_VACANCIES Secure view of Vacancies table
PER_ALL_VACANCIES Vacancies table
PER_ASSIGNMENTS Secure view of Assignments table, effective at session date
PER_ASSIGNMENTS_F Secure view of Assignments table
PER_ASSIGNMENTS_X Secure view of Assignments table, effective at system date
PER_ALL_ASSIGNMENTS Unrestricted view of Assignments table, effective at session date
PER_ALL_ASSIGNMENTS_F Assignments table
PER_PEOPLE Secure view of Person table, effective at session date
PER_PEOPLE_F Secure view of Person table
PER_PEOPLE_X Secure view of Person table, effective at system date
PER_ALL_PEOPLE Unrestricted view of Person table, effective at session date
PER_ALL_PEOPLE_F Person table
PAY_PAYROLLS Secure view of Payrolls table, effective at session date
PAY_PAYROLLS_F Secure view of Payrolls table
PAY_PAYROLLS_X Secure view of Payrolls table, effective at system date
PAY_ALL_PAYROLLS Unrestricted view of Payrolls table, effective at session date
PAY_ALL_PAYROLLS_F Payrolls table

Accessing Oracle HRMS Data Through Restricted Security Profiles

When you connect to the APPS Oracle User you can access all Oracle HRMS database objects without having to perform any additional setup.

This is not the case for reporting users: two conditions must be met to enable reporting users to access Oracle HRMS tables and views:

How Secure Views Work

The information that is visible through a secure view depends on the definition of the security profile through which the view is being accessed.

If you have connected with a restricted security profile the information you can see is derived from denormalized lists of organizations, positions, people and payrolls.

The lists are used only when required. For example, the payroll list is empty for a security profile that can see all payrolls, and in the case of a security profile that can see all applicants but a restricted set of employees, the Person list contains employees but no applicants.

If the HR:Cross Business Groups profile option is 'N', the secure views return data only for the current business group.

If the HR:Cross Business Groups profile option is 'Y', the secure views return data for all business groups, subject to any further restrictions that apply by virtue of the current security profile.

Here is the text of the HR_ORGANIZATION_UNITS secure view:

SELECT HAO.ORGANIZATION_ID, HAOTL.NAME .....
FROM HR_ALL_ORGANIZATION_UNITS HAO,
                HR_ALL_ORGANIZATION_UNITS_TL HAOTL
WHERE DECODE(HR_SECURITY.VIEW_ALL, 'Y', 'TRUE',
                HR_SECURITY.SHOW_RECORD
                ('HR_ALL_ORGANIZATION_UNITS',HAOTL.ORGANIZATION_ID))='TRUE'
        AND DECODE(HR_GENERAL.GET_XBG_PROFILE,
                'Y', HAO.BUSINESS_GROUP_ID,
                HR_GENERAL.GET_BUSINESS_GROUP_ID_ = HAO.BUSINESS_GROUP_ID
        AND HAO.ORGANIZATION_ID = HAOTL.ORGANIZATION_ID
        AND HAOTL.LANGUAGE = USERENV('LANG')

Most HR security logic is encapsulated in a PL/SQL package, HR_SECURITY.

HR_SECURITY.VIEW_ALL returns the value of the VIEW_ALL_FLAG for the current security profile.

HR_SECURITY.SHOW_RECORD is called if the current security profile is a restricted security profile. It validates whether the row in question is visible through the current security profile.

HR_GENERAL.GET_XBG_PROFILE returns the value of the HR:Cross Business Group profile option.

HR_GENERAL.GET_BUSINESS_GROUP_ID returns the current business group ID. The HR: Business Group profile option supplies this ID.

Security Context

The HR security context contains values for all the attributes of the current security profiles. It is implemented using PL/SQL globals. The current security profile is derived as follows:

  1. If you have logged onto Oracle Applications using the Oracle Applications sign-on screen, your security context is automatically set as part of the Oracle Applications sign-on procedure. Your current security_profile_id is derived from the responsibility and security group you select during sign-on.

  2. If you have connected to an HR reporting user your current security_profile_id is taken from the PER_SECURITY_PROFILES table, where REPORTING_ORACLE_USERNAME matches the name of the Oracle User to which you have connected.

  3. If it is not possible to derive a security_profile_id by either of the above two methods, the system looks for the default view-all security profile created for the business group. This gives you unrestricted access to the business group. If it cannot find this, the current security_profile_id is set to null, which prevents you from accessing any records.

So, if you connect directly to the APPS Oracle User through SQL*Plus, you will have unrestricted access to the HRMS tables. But if you connect to an HR reporting user, your access is restricted according to the definition of your security profile.

You can simulate the security context for an Oracle Applications session by calling FND_GLOBAL.APPS_INITIALIZE (user_id, resp_id, resp_appl_id, and security_group_id), passing the IDs of the user, responsibility, application, and security group for the sign-on session you want to simulate. The security_group_id is defaulted to zero (that is, the setup business group).

Note: FND_GLOBAL is not accessible from HR reporting users.

Security Lists

The security profile list tables contain denormalized lists of people, positions, organizations and payrolls.

Security profile lists are intersection tables between a security profile and secured tables, as follows:

Security List Table Name Columns
PER_PERSON_LIST SECURITY_PROFILE_ID, PERSON_ID
PER_POSITION_LIST SECURITY_PROFILE_ID, POSITION_ID
PER_ORGANIZATION_LIST SECURITY_PROFILE_ID, ORGANIZATION_ID
PAY_PAYROLL_LIST SECURITY_PROFILE_ID, PAYROLL_ID
PER_PERSON_LIST_CHANGES SECURITY_PROFILE_ID, PERSON_ID

These tables are periodically refreshed by the Security List Maintenance process (PERSLM). They are also written to when some relevant business processes are performed through Oracle HR, for example, employee hire or transfer.

If people are being secured via the supervisor hierarchy and organizations, positions and payrolls are not secured, the security list tables mentioned above are not used, and the Security List Maintenance process need not be run. The list of visible people is derived dynamically based on the current user.

If, however, supervisor security is being used in conjunction with organization and/or position and/or payroll security, you must run the Security List Maintenance process periodically to refresh the security list tables. The list of visible people is derived dynamically based on the current user, and is a subset of the people that are visible via the PER_PERSON_LIST table.

Security Processes

Three processes are used to implement Oracle HRMS security:

ROLEGEN runs automatically as part of an installation or upgrade. If you are not setting up reporting users, you need not run SECGEN.

Refer to the topic on Security Processes, Oracle HRMS Configuring, Reporting, and System Administration Guide for details of how to submit SECGEN and PERSLM from the Submit Requests window. This section describes how the processes work.

ROLEGEN: Grant Permissions to Roles Process

A role is a set of permissions that can be granted to Oracle users or to other roles. Roles are granted to users by the SECGEN process (see below).

The ROLEGEN process must run before you run SECGEN. ROLEGEN dynamically grants select permissions on Oracle HRMS tables and views to the HR_REPORTING_USER role. This role must exist before ROLEGEN runs.

The HR_REPORTING_USER role is created during the install of Oracle HRMS. ROLEGEN is run during the install of Oracle HRMS.

Note: As ROLEGEN runs as part of the installation and upgrade processes, you do not need to run ROLEGEN manually.

ROLEGEN performs the following actions:

SECGEN - Generate Secure User Process

You run SECGEN for a specified security profile. It grants the HR_REPORTING_USER role to the Oracle User associated with the security profile.

SECGEN must be run after ROLEGEN. However, once SECGEN has been run for a particular security profile, you need not rerun it even if ROLEGEN is run again.

SECGEN is a PRO*C process with embedded SQL statements. You initiate it from the Submit Requests window.

PERSLM - Security List Maintenance Process

You should run PERSLM periodically (for example, nightly) to refresh the security lists upon which the secure views are built.

Important: This process has the capability to run multi-threaded, allowing it to take advantage of the capabilities of your hardware.

PERSLM is a PL/SQL procedure that you submit from the Submit Requests window. It builds the required security lists based on the restrictions defined for the security profiles being processed.

For each security profile within the scope specified when the process is submitted, PERSLM performs the following steps:

  1. If the View All flag is Y, the process ends leaving all security lists empty for the specified security profile.

  2. Builds a payroll list.

    If the View All Payrolls flag is Y, the process leaves the payroll list empty. If the View All Payrolls flag is N, the process checks the Include Payroll flag. If this flag is Y, the process makes a list of all payrolls in the pay_security_payrolls list. If the flag is N, the process makes a list of all payrolls except those in the pay_security_payrolls list. The pay_security_payrolls list is populated when you enter payrolls on the Define Security Profile screen.

  3. Builds an organization list.

    If the View All Organizations flag is Y, the process leaves the organization list empty. If this flag is N, the process builds a list of all organizations below the top one you specified for the organization hierarchy you chose on the Define Security Profile screen. The process uses the version of the hierarchy that is effective on the date passed to PERSLM. If the Include Top Organization flag is Y, the top organization you specified is included in the list. Any organizations specifically listed in the Define Security Profile window are included or excluded as specified. If the Exclude Business Group flag is N, the business group is included in the list to allow newly entered employees and applicants to be visible before they are assigned to an organization.

  4. Builds a position list.

    If the View All Positions flag is Y, the process builds a list of all positions within the organizations on the organization list. If this flag is N, the process builds a list of all positions below the top one you specified for the position hierarchy you chose on the Define Security Profile screen. The process uses the version of the hierarchy that is effective on the date passed to PERSLM. If the Include Top Position flag is Y, the top position you specified is included in the list. The list of positions is built up for all organizations on the organization list, or for all organizations if the View All Organizations flag is Y.

  5. Builds a person list.

    The process creates person list information for all people within the specified scope, including terminated employees, applicants, and contingent workers. The process uses the assignment data effective on the date passed to PERSLM to determine eligibility for current employees, applicants, and contingent workers. For terminated employees, applicants, and contingent workers, the process identifies the maximum effective end date for any assignment defined for the person, and uses the assignment data effective on this date to determine eligibility.

    The system processes future-dated hires, placements, or applicants using the date when the assignment becomes effective.

    Note: If the process is running for a terminated person with a future-dated hire or placement, the system uses the future-dated assignment information to determine eligibility. If a terminated employee or contingent worker is also a current or future applicant, the system determines eligibility using both the terminated assignment data and the current or future assignment data.

    The mechanism used to generate the person list depends on the value selected for the "Generate For" parameter selected at the time PERSLM was submitted:

    • One Named Security Profile

      The process generates the list of people visible to the security profile identified by the Security Profile parameter. This process runs single-threaded.

      For the named profile, PERSLM determines what security restrictions have been entered and dynamically builds a SQL statement to identify all the people who match the restriction criteria as follows:

      • If the View Employee field is Restricted then process all Employee assignments. If the View Employee field is either None or All then no data for employees is written to the person list table and access is controlled within the secure view.

      • If the View Contingent Worker field is Restricted then process all Contingent Worker assignments. If the View Contingent Worker field is either None or All then no data for contingent workers is written to the person list table and access is controlled within the secure view.

      • If the View Applicant field is Restricted then process all Applicant assignments provided that the Applicant is not also an Employee or Contingent Worker, in which case access is granted based on their Employee or Contingent Worker assignment. If the View Applicant field is either None or All then no data for applicants is written to the person list table and access is controlled within the secure view.

      • If the Organization Security Type is "Secure by Organization Hierarchy and/or Organization List" then PERSLM restricts access to Employees, Applicants and Contingent Workers with a current assignment to organizations in the organization list.

      • If the View All Positions flag is N, then PERSLM restricts access to Employees, Applicants and Contingent Workers with a current assignment to positions in the position list. PERSLM includes people who are not currently assigned to a position.

      • If the View All Payrolls flag is N, then PERSLM restricts access to Employees and Applicants with a current assignment to payrolls in the payroll list.

      • If the Custom Restriction flag is Y, then PERSLM restricts access to Employees, Applicants and Contingent Workers using the conditions defined within the custom restriction.

        If a security profile contains multiple restrictions then data is only written to the person list table for people who satisfy all the restrictions defined.

    • All Security Profiles, All Global Security Profiles, All Security Profiles in One Named Business Group

      The process generates the list of people visible to the security profiles within the scope of the option selected. For example, if the "All Global Security Profiles" option is selected, the person list information is regenerated for all global security profiles but for no business group-specific profiles. This process is implemented using the Oracle Payroll Archiver process, allowing it to run multi-threaded if your system has been configured correctly.

      When generating security lists for one of these options, all people in the system are checked and processed for eligibility. (If you elect to generate list information for security profiles in a single named business group, then only people defined within that business group are processed. For the other two options all people within the database are processed.)

      The individual assignments for people requiring processing are examined to determine which security profiles can access them. Based on the Organization, Position, and Payroll data present on the assignment the set of security profiles that can see the assignment is determined using the restrictions defined on each security profile within scope and Organization, Position and Payroll list information previously generated for those security profiles. In addition, any custom restriction for each of the profiles is evaluated to ensure that the assignment and person comply with any criteria entered.

  6. Adds person list changes.

    PERSLM adds a person to the person list if an entry exists in the PER_PERSON_LIST_CHANGES table, there is no current period of service, and there is no current application for the person. It only adds people if they are not already in the list.

  7. Contacts (Persons with system person type 'OTHER')

    Some security profiles restrict a user's access to contacts. If this is the case, the process generates access to the contacts who are related to the current and terminated employees, applicants, and contingent workers within the user's security profile. The process also allows access to contacts who are unrelated to any person within the system. The process uses the PER_CONTACT_RELATIONSHIPS table to determine a contact's relationships.

Securing Custom Tables

If you have created your own custom tables, perform the following steps to make them accessible to reporting users:

  1. Create table.

    Select a table name that does not conflict with any tables or views that might exist in Oracle Applications.

    Do not use two or three character prefixes such as HR, PER, PAY, FF, DT, SSP, GHR, BEN, OTA, HXT, EDW, HRI, HXC, PQH, PQP or IRC.

  2. Grant select access on the table to HR_REPORTING_USER role, from the user that owns the custom table.

    GRANT SELECT ON custom_table TO hr_reporting_user;
    

    You must repeat this step every time you perform an installation or upgrade. However, you do not need to rerun SECGEN as existing reporting users that have already been granted access to the HR_REPORTING_USER role will automatically receive any new permissions added to the role.

  3. Create a synonym to the table.

    If you use public synonyms, remember that the Oracle user from which you create the public synonym must have CREATE PUBLIC SYNONYM system privilege.

    CREATE PUBLIC SYNONYM custom_table
              FOR base_table_account.custom_table;