Step 5: Create the PL/SQL Procedure to Set the Secure Application Role

Now, you are ready to create the secure application role procedure. In most cases, you create a package to hold the procedure, but because this is a simple tutorial that requires only one secure application role test (as defined in the procedure), you will create a procedure by itself. If you want to have a series of procedures to test for the role, create them in a package.

A PL/SQL package defines a simple, clear interface to a set of related procedures and types that can be accessed by SQL statements. Packages also make code more reusable and easier to maintain. The advantage here for secure application roles is that you can create a group of security policies that used together present a solid security strategy designed to protect your applications. For users (or potential intruders) who fail the security policies, you can add auditing checks to the package to record the failure.

To create the secure application role procedure:

In SQL*Plus, connect as user sec_admin.

CONNECT sec_admin
Enter password: password

Enter the following CREATE PROCEDURE statement to create the secure application role procedure. (You can copy and paste this text by positioning the cursor at the start of CREATE OR REPLACE.)

CREATE OR REPLACE procedure sec_roles AUTHID CURRENT_USER
 AS
v_user varchar2(50); 
v_manager_id number :=1;
 BEGIN    
  v_user := lower((sys_context ('userenv','session_user')));
  SELECT manager_id 
     INTO v_manager_id FROM hr.hr_verify WHERE lower(email)=v_user;
   IF v_manager_id = 100
    THEN 
    EXECUTE IMMEDIATE 'SET ROLE employee_role';  
    ELSE NULL; 
   END IF;        
  EXCEPTION  
  WHEN NO_DATA_FOUND THEN v_manager_id:=0;  
 DBMS_OUTPUT.PUT_LINE(v_manager_id);
END;
/

In this example:

Line 1: Appends the AUTHID CURRENT_USER clause to the CREATE PROCEDURE statement, which creates the procedure using invoker's rights. The AUTHID CURRENT_USER clause creates the package using invoker's rights, using the privileges of the current user.

You must create the package using invoker's rights for the package to work. Invoker's rights allow the user to have EXECUTE privileges on all objects that the package accesses.

Roles that are enabled inside an invoker's right procedure remain in effect even after the procedure exits, but after the user exits the session, he or she no longer has the privileges associated with the secure application role. In this case, you can have a dedicated procedure that enables the role for the rest of the session.

Because users cannot change the security domain inside definer's rights procedures, secure application roles can only be enabled inside invoker's rights procedures.

See "About Secure Application Roles" for information about the importance of creating the procedure using invoker's rights.
Line3: Declares the v_user variable, which will store the user session information.
Line 4: Declares the v_manager_id variable, which will store the manager's ID of the v_user user.
Line 6: Retrieves the user session information for the user logging on, in this case, Matthew or Winston. To retrieve user session information, use the SYS_CONTEXT SQL function with the USERENV namespace attributes ('userenv', session_attribute), and the write this information to the v_user variable.

The information returned by this function indicates the way in which the user was authenticated, the IP address of the client, and whether the user connected through a proxy. See Oracle Database SQL Language Reference for more information about SYS_CONTEXT.
Lines 7–8: Get the manager's ID of the current user. The SELECT statement copies the manager ID into the v_manager_id variable, and then checking the HR.HR_VERIFY view for the manager ID of the current user. This example uses the employees' e-mail addresses because they are the same as their user names.
Lines 9–13: Use an IF condition to test whether the user should be granted the sec_roles role. In this case, the test condition is whether the user reports to Matthew's manager, Steven King, whose employee number is 100. If the user reports to King, as Matthew does, then the secure application role is granted to the user. Otherwise, the role is not granted.

The result is that the secure application role will grant Matthew Weiss the role because he is a direct report of Steven King, but will deny the role to Winston, because he is not a direct report of Steven King.
Lines 10–12: Within the IF condition, the THEN condition grants the role by executing immediately the SET ROLE statement. Otherwise, its ELSE condition denies the grant.
Lines 14–15: Use an EXCEPTION statement to set v_manager_id to 0 if no data is found.
Line 16: Copies the manager's ID into a buffer so that it is readily available.

Tip: If you have problems creating or running PL/SQL code, check the Oracle Database trace files. The USER_DUMP_DEST initialization parameter specifies the current location of the trace files. You can find the value of this parameter by issuing SHOW PARAMETER USER_DUMP_DEST in SQL*Plus. See Oracle Database Performance Tuning Guide for more information about trace files.

Step 5: Create the PL/SQL Procedure to Set the Secure Application Role

Related Topics