Configure Access to List of Proxy Users in Role Delegation

The data security policies that contain the Choose Proxy for Role Delegation privilege secure the list of values using the public person security profile. By default, the list of values shows the people in that public person security profile.

In this example, you learn how to create a data security policy to limit the list of values to a user's peers and management hierarchy.

The following table summarizes the key decisions for this scenario.

Decisions to Consider

In This Example

What is the name and display name of the database resource condition for proxy users?

Peers and Above and Peers and Above

How will the database resource conditions be specified?

SQL predicate

Which workers should appear in the list of proxy users?

The peers and management hierarchy of the delegator.

Summary of the Tasks

Enable access to a restricted list of proxy users by:

  1. Creating a database resource condition.

  2. Editing the Employee role to end date existing data security policy.

  3. Creating replacement data security policy for the Employee role that references the new database resource condition.

Create a Database Resource Condition

You create a database resource condition that you will include in data security policy.
  1. Select Navigator > Tools > Security Console.

  2. On the Security Console, click the Administration tab.

  3. On the General subtab, click Manage Database Resources.

  4. On the Manage Database Resources and Policies page, enter PER_PERSONS in the Object Name field and click Search.

  5. In the Search Results section, click the Edit icon.

  6. On the Edit Data Security: PER_PERSONS page, click the Condition tab.

  7. On the Condition tab, click the Create icon.

  8. In the Create Database Resource Condition dialog box, complete the fields as shown in the following fields:




    Peers and Above

    Display Name

    Peers and Above

    Condition Type

    SQL predicate

    In the SQL Predicate field, enter the following statement:

    &TABLE_ALIAS.PERSON_ID in (select manager_id from per_manager_hrchy_dn 
    where person_id = NVL(HRC_SESSION_UTIL.GET_USER_PERSONID,-1) 
    and trunc(sysdate) between effective_start_date and effective_end_date 
    and manager_type = 'LINE_MANAGER' UNION 
    select b.person_id from per_assignment_supervisors_f a, per_assignment_supervisors_f b 
    where a.person_id =  NVL(HRC_SESSION_UTIL.GET_USER_PERSONID,-1) 
    and trunc(sysdate) between a.effective_start_date 
    and a.effective_end_date and a.manager_type = 'LINE_MANAGER' 
    and a.manager_type = b.manager_type and a.manager_id = b.manager_id 
    and a.person_id != b.person_id 
    and trunc(sysdate) between b.effective_start_date and b.effective_end_date)
  9. Click Save.

End Date the Data Security Policy Granted to the Employee Abstract Role

You edit the Employee role to end date the existing data security policy.

  1. Click the Roles tab on the Security Console.

  2. Search for and select the Employee role.

  3. In the search results, select Edit Role on the role's Actions menu.

  4. On the Basic Information page, click the Data Security Policies train stop.

  5. In the Privilege search field, enter Choose Proxy and press Enter.

  6. In the row containing the specified privilege for the Public Person data resource, select Edit Data Security Policy on the Actions menu.

  7. In the Edit Data Security Policy dialog box, enter today's date in the End Date field.

  8. Click OK to close the Edit Data Security Policy dialog box.

    Remain on the Data Security Policies page.

Create Data Security Policy

You create a new data security policy that provides restricted access to proxy users for your Employee role.

  1. On the Data Security Policies page, click Create Data Security Policy.
  2. Complete the fields in the Create Data Security Policy dialog box using the values shown in this table.
    Field Value
    Policy Name Restricted Access to Proxy Users Policy
    Database Resource Public Person
    Data Set Select by instance set
    Condition Name Peers and Above
    Actions Choose Proxy for Role Delegation
  3. Click OK.
  4. Click the Summary train stop.
  5. Click Save and Close to save your changes to the Employee role.