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:
Add custom tables to the standard security system
Integrate your own security system with the supplied mechanisms
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:
Unrestricted
Restricted
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):
Organizations
People
Assignments
Positions
Vacancies
Payrolls
All other entities are unrestricted; that is, restricted security profiles can access all records of tables, views and sequences associated with these entities.
The following Oracle HRMS tables are secured:
HR_ALL_ORGANIZATION_UNITS
PER_ALL_POSITIONS
HR_ALL_POSITIONS_F
PER_ALL_VACANCIES
PER_ALL_PEOPLE_F
PER_ALL_ASSIGNMENTS_F
PAY_ALL_PAYROLLS_F
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 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 |
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:
A public synonym must exist for each table and view. Public synonyms have the same name as the tables and views to which they point. They are created during installation of Oracle HRMS.
The reporting user must have been granted permissions to access the tables and views by the SECGEN process. Reporting users are granted SELECT permission only. See below for more information about SECGEN.
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.
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:
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.
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.
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.
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.
Three processes are used to implement Oracle HRMS security:
Grant Permissions to Roles (ROLEGEN)
Generate Secure User (SECGEN)
Security List Maintenance (PERSLM)
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.
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:
Creates public synonyms for HRMS tables and views, excluding unsecured tables (%_ALL_%)
Revokes all existing permissions from HR_REPORTING_USER roles
Grants SELECT permissions to HR_REPORTING_USER role for HRMS tables and views
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.
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:
If the View All flag is Y, the process ends leaving all security lists empty for the specified security profile.
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.
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.
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.
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.
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.
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.
If you have created your own custom tables, perform the following steps to make them accessible to reporting users:
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.
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.
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;