Step 4: Create the F_POLICY_ORDERS Policy Function

The f_policy_orders policy is a PL/SQL function that defines the policy used to filter users who query the ORDERS table. To filter the users, the policy function uses the SYS_CONTEXT PL/SQL function to retrieve session information about users who are logging in to the database.

To create the application context and its package:

In Database Control, click Logout and then Login.
Log in as user sec_admin.
Click Schema to display the Schema subpage.
Under Programs, select Functions.

The Functions page appears.
Ensure that the Object Type menu is set to Function, and then click Create.

The Create Function page appears.

Enter the following information:

Name: F_POLICY_ORDERS
Schema: SEC_ADMIN

Source: Delete the empty function code that has been provided, and then enter the following code (but not the line numbers on the left side of the code) to create a function that checks whether the user who has logged on is a sales representative. You can copy and paste this text by positioning the cursor at the start of (schema in varchar2) in the first line.

The f_policy_orders function accomplishes this by using the SYS_CONTEXT PL/SQL function to get the session information of the user, and then it compares this information with the job ID of that user in the HR.EMPLOYEES table, for which sec_admin has SELECT privileges.

(schema in varchar2,
tab in varchar2)
return varchar2 
as 
 v_job_id   varchar2(20);
 v_user     varchar2(100);
 predicate  varchar2(400);
 
begin
 v_job_id  := null;
 v_user    := null;
 predicate := '1=2';

v_user := lower(sys_context('userenv','session_user'));

 select lower(job_id) into v_job_id from hr.employees
   where lower(email) = v_user;
 
 if  v_job_id='sa_rep' then
    predicate := '1=1';
 else 
    null; 
 end if;

 return predicate;

 exception 
  when no_data_found then 
   null;
end;

In this example:

Lines 1–2: Define parameters for the schema (schema) and table (tab) that must be protected. Notice that the function does not mention the OE.ORDERS table. The ACCESSCONTROL_ORDERS policy that you create in Step 5: Create the ACCESSCONTROL_ORDERS Virtual Private Database Policy uses these parameters to specify the OE schema and ORDERS table. Ensure that you create the schema parameter first, followed by the tab parameter.
Line 3: Returns the string that will be used for the WHERE predicate clause. Always use VARCHAR2 as the data type for this return value.
Lines 4–7: Define variables to store the job ID, user name of the user who has logged on, and predicate values.
Lines 9–25: Encompass the creation of the WHERE predicate, starting the with the BEGIN clause at Line 9.
Lines 10–12: Sets the v_job_id and v_user variables to null, and the predicate variable to 1=2, that is, to a false value. At this stage, no WHERE predicate can be generated until these variables pass the tests starting with Line 16.
Line 14: Uses the SYS_CONTEXT function to retrieve the session information of the user and write it to the v_user variable.
Lines 16–23: Checks if the user is a sales representative by comparing the job ID with the user who has logged on. If the job ID of the user who has logged on is sa_rep (sales representative), then the predicate variable is set to 1=1. In other words, the user, by being a sales representative, has passed the test.
Line 25: Returns the WHERE predicate, which translates to WHERE role_of_user_logging_on IS "sa_rep". Oracle Database appends this WHERE predicate onto any SELECT statement that users LDORAN and LPOPP issue on the OE.ORDERS table.
Lines 27–29: Provide an EXCEPTION clause for cases where a user without the correct privileges has logged on.

Click OK.

Step 4: Create the F_POLICY_ORDERS Policy Function

Related Topics