Step 3: Create the Secure Application Role

Now, you are ready to create the employee_role secure application role. To do so, you must log on as the security administrator sec_admin. "Step 1: Create a Security Administrator Account" explains how to create the sec_admin account.

To create the secure application role:  

1. Start SQL*Plus and log on as the security administrator sec_admin.

   SQLPLUS sec_admin
   Enter password: password
   

   SQL*Plus starts, connects to the default database, and then displays a prompt.

   SQL> 
   

   For detailed information about starting SQL*Plus, see Starting SQL*Plus and Connecting to the Database.

2. Create the following secure application role:

   CREATE ROLE employee_role IDENTIFIED USING sec_roles;
   

   The IDENTIFIED USING clause sets the role to be enabled (or disabled) only within the associated PL/SQL package, in this case, sec_roles. At this stage, the sec_roles PL/SQL package does not need to exist.

3. Connect as user OE.

   CONNECT oe
   Enter password: password
   

   If you receive an error message saying that OE is locked, then you can unlock the OE account and reset its password by entering the following statements. For greater security, do not reuse the same password that was used in previous releases of Oracle Database. Enter any password that is secure, according to the password guidelines described in "Requirements for Creating Passwords".

   CONNECT sys/as sysdba
   Enter password: sys_password
   PASSWORD OE
   Changing password for OE
   New password: password
   Retype new password: password
   Password changed.
   
   CONNECT oe
   Enter password: password
   

4. Enter the following statement to grant the EMPLOYEE_ROLE role SELECT privileges on the OE.ORDERS table.

   GRANT SELECT ON OE.ORDERS TO employee_role;
   

   Do not grant the role directly to the user. The PL/SQL package will do that for you, assuming the user passes its security policies.