Step 4: Create a Lookup View

You are almost ready to create the procedure that determines who is granted the employee_role role. The procedure will grant the employee_role only to managers who report to Steven King, whose employee ID is 100. This information is located in the HR.EMPLOYEES table. However, you should not use that table in this procedure, because it contains sensitive data such as salary information, and for it to be used, everyone will need access to it. In most real world cases, you create a lookup view that contains only the information that you need. (You could create a lookup table, but a view will reflect the most recent data.) For this tutorial, you create your own lookup view that only contains the employee names, employee IDs, and their manager IDs.

To create the HR.HR_VERIFY lookup view: 

1. In SQL*Plus, connect as user HR.

   CONNECT hr
   Enter password: password
   

   If you receive an error message saying that HR is locked, then you can unlock the 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: password
   ALTER USER HR ACCOUNT UNLOCK IDENTIFIED BY password;
   CONNECT hr
   Enter password: password
   

2. Enter the following CREATE VIEW SQL statement to create the lookup table:

   CREATE VIEW hr_verify AS 
   SELECT employee_id, first_name, last_name, email, manager_id 
   FROM employees;
   

3. Grant EXECUTE privileges for this view to mweiss and wtaylor by entering the following SQL statements:

   GRANT SELECT ON hr.hr_verify TO mweiss;
   GRANT SELECT ON hr.hr_verify TO wtaylor;
   GRANT SELECT ON hr.hr_verify TO sec_admin;