4 Replies Latest reply: Jul 13, 2006 10:01 AM by 522413 RSS

    Custom Security profiles

    522413
      I am trying to create a custom security profile in Oracle HRMS. So I went to the 'Security Profiles' screen -> Custom Security tab, checked the 'custom
      restriction' check box and began typing my SQL where clause. My question is -
      I type this:

      ASSIGNMENT.grade_id not in ( select grade_id from
      per_grades where PER_GRADES.NAME like 'G%' or
      PER_GRADES.NAME like 'M%') and it all works fine. (I am trying exclude grades that begin with G or M from being accessible)

      However, if I type per_all_assignments_f instead of ASSIGNMENT, I get ORA - 00904 error. Why does this happen? How will I know what alias to use in case I want to refer to a field in a different table in the query? Any help is greatly appreciated.

      Thanks in advance!
        • 1. Re: Custom Security profiles
          Gregoryclark-Oracle
          References:

          Oracle® Human Resources Management Systems
          Configuring, Reporting, and System Administration Guide
          Release 11i
          Part No. A95418-02

          Defining a Security Profile; page 1-37

          Note 202478.1- Enhancements in Oracle HRMS Security in R11.5

          Custom Security
          ---------------

          Allows you to specify additional sql to generate additional restrictions
          which are not possible using the standard security profile options

          It effectively adds the sql you enter appended to the following
          statement (that does not apppear on the screen)

          SELECT PERSON.person_id
          FROM per_all_people_f PERSON,
          per_all_assignments_f ASSIGNMENT
          WHERE PERSON.person_id = ASSIGNMENT.person_id
          AND ........

          The following example would add an additional restriction based on specified
          Locations

          ASSIGNMENT.location_id in
          (select LOC.location_id
          from hr_locations_all LOC
          where LOC.location_code in ('HR- London,UK','HR- Paris,France'))

          Another example shows how you can specify additional restrictions based on
          Person types

          PERSON.PERSON_ID IN
          (select pptu.person_id
          from
          per_person_type_usages_f pptu,
          per_person_types ppt
          where pptu.person_type_id = ppt.person_type_id
          and ppt.user_person_type not in ('Retiree','Pensioner','Participant'))

          Alternatively for restricting security to against People Group Flexfield
          values i.e to subdefine a Payroll security profile to include subgroups
          that have setup using People Group Flexfield segment(s)

          ASSIGNMENT.people_group_id IN ('<ID1>', '<ID2>')

          It is recommended that fields used in custom sql are limited to those based
          on the People (PER_ALL_PEOPLE_F),Assignment(PER_ALL_ASSIGNMENTS_F) or
          PER_PERSON_TYPE_USAGES_F tables as generating sql based on
          other tables using complex join conditions may not give
          the expected results and/or may cause issues with the running of the
          Listgen process


          Regards,
          Greg
          • 2. Re: Custom Security profiles
            522413
            Thanks a lot for that, Greg. My question was more to do with how one would know the right alias to use, i.e. for instance, if I used the word 'ASSIGNMENTS' as opposed to 'ASSIGNMENT', I would get an error.

            I was wondering if we cannot use the table name instead...that would avoid confusion.
            • 3. Re: Custom Security profiles
              Gregoryclark-Oracle
              Aruna,

              Going back to the entry in the note I provided...it states the limits for the Custom Security tab:

              It effectively adds the sql you enter appended to the following statement (that does not apppear on the screen)

              SELECT PERSON.person_id
              FROM per_all_people_f PERSON,
              per_all_assignments_f ASSIGNMENT
              WHERE PERSON.person_id = ASSIGNMENT.person_id
              AND ........

              Regards,
              Greg
              • 4. Re: Custom Security profiles
                522413
                Ah! I get it. Thanks a bunch Greg!