Understanding and Using HRMS Security in Oracle HRMS

Abstract

Understanding and Using HRMS Security in Oracle HRMS

Document History

Author : Steve Cooper
Create Date : 04-OCT-2006
Last Update Date : 18-JUN-2008
Expiration Date : 
Other Information : 

Table of Contents

1. Overview/Key Components
   • a) Introduction
   • b) Security Profile
   • c) Security List Maintenance
   • d) Security Models
   • e) Reporting Users
   • f) Financials and Manufacturing
2. The Security Profile
   • a) Organization Security
   • b) Position Security
   • c) Payroll Security
   • d) Supervisor Security
   • e) Miscellaneous Security
   • f) Custom Security
   • g) Static Lists/User-Based Security
   • h) Assignment Level Security
   • i) Global Security Profiles
3. Technical evaluation
   • a) Static Lists
   • b) Secure Views
4. Troubleshooting Problems
   • a) Check Setup
   • b) HRMS Security and Datetrack
   • c) User-Based or Dynamic Security gives access to Active Assignments only
   • d) Performance Issues
   • e) Generate Secure User errors
   • f) Security List Maintenance errors

1. Overview

a) Introduction

Example Querys using Standard Security Group

select security_group_id,
       security_group_name
from fnd_security_groups_vl
where security_group_id=0;




select name,
       business_group_id
from per_business_groups
where security_group_id=0

b) Security Profile

c) Security List Maintenance

d) Security Models

e) Reporting Users

f) Financials and Manufacturing

2. The Security Profile

a) Organization Security

b) Position Security

c) Payroll Security

d) Supervisor Security

e) Miscellaneous Security

f) Custom Security

select 1
from per_all_assignments_f ASSIGNMENT,
     per_all_people_f PERSON,
     per_person_type_usages_f PERSON_TYPE
where ASSIGNMENT.assignment_id = :asg_id
and :effective_date between ASSIGNMENT.effective_start_date
                        and ASSIGNMENT.effective_end_date
and PERSON.person_id = ASSIGNMENT.person_id
and :effective_date between PERSON.effective_start_date
                        and PERSON.effective_end_date
and PERSON.person_id = PERSON_TYPE.person_id
and :effective_date between PERSON_TYPE.effective_start_date
                        and PERSON_TYPE.effective_end_date
and (CUSTOM CODE GOES HERE)

ASSIGNMENT.location_id in (select LOC.location_id
from hr_locations_all LOC
where LOC.location_code
in (‘London‘,‘Paris‘))

However be sure to force character strings to upper case as custom restricted text is not case sensitive currently. see Note 965961.1.

The above custom code should therefore be rewritten as

ASSIGNMENT.location_id in (select LOC.location_id
from hr_locations_all LOC
where UPPER(LOC.location_code) IN (UPPER(‘London‘),UPPER(‘Paris‘)))

APP-PER-289835:  An SQL error was found in your custom restriction.
The error is `ORA-904: `PERSON_TYPE.PERSON_TYPE_ID:Invalid identifier.  Correct the error before continuing

g) Static Lists/User-Based Security

h) Assignment Level Security

PERWSHRG (Combined Person / Assignment)
PERWSEMA (Fastpath Assignment)
PERWSQHM (People Management)

i) Global Security Profiles

1) Non HRMS users who do not want data to be restricted by Business Group
   when they define Global Security profiles
2) In Self Service HRMS, where Managers using Supervisor hierarchy have access
   to direct reports accross business groups.
3) In R12 Professional HR, People Management can now be used with a Global 
   Security profile. If a Global Security Profile is linked to the responsibility
   users can choose the business group on the Find screen to query cross business
   groups. Records can be updated and secondary assignments created, however new
   employees are created in the default business group set by the HR:Business Group
   profile option or in Assign Security Profiles form depending if Standard security
   or security groups are used. All other forms accessed using the responsibility
   use the Global Security profile too, but are limited to using the default 
   business group.

3. Technical Evaluation

a) Static Lists

b) Secure Views

e.g.  BLOCK - $PROFILES$
      FIELD - USER_ID
      VALUE - 

SET SERVEROUT ON

DECLARE
 l_per_all  NUMBER := 0;
 l_per_sec NUMBER := 0;
 l_asg_all NUMBER := 0;
 l_asg_sec NUMBER := 0;

BEGIN

 fnd_global.apps_initialize(, , , );

 SELECT count(*)
 INTO l_asg_all
 FROM per_all_assignments_f
 WHERE business_group_id = ;

SELECT count(*)
 INTO l_per_all
 FROM per_all_people_f
 WHERE business_group_id = ;

 SELECT count(*)
 INTO l_per_sec
 FROM per_people_f;

 SELECT count(*)
 INTO l_asg_sec
 FROM per_assignments_f;

 dbms_output.put_line(‘Per all: ‘ || to_char(l_per_all));
 dbms_output.put_line(‘Per sec: ‘ || to_char(l_per_sec));
 dbms_output.put_line(‘Asg all: ‘ || to_char(l_asg_all));
 dbms_output.put_line(‘Asg sec: ‘ || to_char(l_asg_sec));

END;
/ 

4. Troubleshooting Problems

a) Check Setup

The tests produce the following report types:

 o Summary   - Summary of all security profiles used in your setup

 o Detail    - Detailed information on the security profile assigned to a given 
               responsibility.

 o Usage     - Usage information on the security profile assigned to a given 
               responsibility, for example, which responsibilities use the 
               security profile.

 o Access    - List of organizations, payrolls, positions, and optionally, 
               person assignments, a named user can access using a given 
               responsibility.

 o Exception - List of security profiles defined in the system whose set up is 
               treated as an exception in the HRMS Security model. 

    set linesize 180
    select security_profile_id,
           substr(security_profile_name,1,40)
      from per_security_profiles;
    

    BLOCK - $PROFILES$
    FIELD - PER_SECURITY_PROFILE_ID
    VALUE - 
    

      select person_id 
        from per_person_list
       where security_profile_id=&security_profile_id

b) HRMS Security and Datetrack

a) For Static lists, by the defined criteria on the effective
   date on which Security List Maintenance is run.
b) For user-based security, by the defined criteria at SYSDATE.

a)	by having a row on the secure person list if the person has a 
        person_type that is Restricted on the security profile.
b)	By not having a row on the secure person list if the person only 
        has a person_type that is All on the Security profile. Eligibility 
        is taken for granted in this case.

c) Prior to 12.06 User-Based or dynamic security gives access to Active assignments only

d) Performance Issues

e) Generate Secure User errors

select security_profile_id,
       security_profile_name,
       reporting_oracle_username
from per_security_profiles
where reporting_oracle_username IS NOT NULL;

f) Security List Maintenance errors

1. Login to the SQLPLUS
2. Set serveroutput on
3. spool pyupip.out
4. Execute the following

BEGIN
hr_utility.set_trace_options (‘TRACE_DEST:DBMS_OUTPUT‘);
hr_utility.trace_on;
pay_pyucslis_pkg.generate_lists(
p_effective_date => trunc(sysdate)
,p_generation_scope => ‘ALL_PROFILES‘
,p_business_group_id => NULL
,p_security_profile_id => NULL
,p_security_profile_name => NULL
,p_who_to_process => ‘ALL‘ -- Current and Terminated people
,p_user_id => NULL 
,p_static_user_processing => ‘ALL_STATIC‘
);
hr_utility.trace_off;
Exception
when others then 
dbms_output.put_line(sqlerrm);
hr_utility.trace_off;
END;

5. spool off

Related Documents

Understanding and Using HRMS Security in Oracle HRMS