Skip Headers
Oracle® Database Security Guide
12c Release 1 (12.1)

E17607-24
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Master Index
Master Index
Go to Feedback page
Contact Us

Go to previous page
Previous
Go to next page
Next
PDF · Mobi · ePub

5 Managing Security for Definer's Rights and Invoker's Rights

This chapter contains:

About Definer's Rights and Invoker's Rights

Both definer's rights and invoker's rights are a way to control access to the privileges necessary to run a user-created procedure, or program unit. In a definer's rights procedure, the procedure executes with the privileges of the owner. The privileges are bound to the schema in which they were created. An invoker's rights procedure executes with the privileges of the current user, that is, the user who invokes the procedure.

For example, suppose user bixby creates a procedure that is designed to modify table cust_records and then he grants the EXECUTE privilege on this procedure to user rlayton. If bixby had created the procedure with definer's rights, then the procedure would look for table cust_records in bixby's schema. Had the procedure been created with invoker's rights, then when rlayton runs it, the procedure would look for table cust_records in rlayton's schema.

By default, all procedures are considered definer's rights. You can designate a procedure to be an invoker's rights procedure by using the AUTHID CURRENT_USER clause when you create or modify it, or you can use the AUTHID DEFINER clause to make it a definer's rights procedure.

See Also:

Oracle Database PL/SQL Language Reference for more details about definer's rights and invoker's rights procedures

How Procedure Privileges Affect Definer's Rights

The owner of a procedure, called the definer, must have all the necessary object privileges for objects that the procedure references. If the procedure owner grants to another user the right to use the procedure, then the privileges of the procedure owner (on the objects the procedure references) apply to the grantee's exercise of the procedure. The privileges of the procedure's definer must be granted directly to the user, not granted through roles. These are called definer's rights.

The user of a procedure who is not its owner is called the invoker. Additional privileges on referenced objects are required for an invoker's rights procedure, but not for a definer's rights procedure.

A user of a definer's rights procedure requires only the privilege to execute the procedure and no privileges on the underlying objects that the procedure accesses. This is because a definer's rights procedure operates under the security domain of the user who owns the procedure, regardless of who is executing it. The owner of the procedure must have all the necessary object privileges for referenced objects. Fewer privileges need to be granted to users of a definer's rights procedure. This results in stronger control of database access.

You can use definer's rights procedures to control access to private database objects and add a level of database security. By writing a definer's rights procedure and granting only the EXECUTE privilege to a user, this user can be forced to access the referenced objects only through the procedure.

At run time, Oracle Database checks whether the privileges of the owner of a definer's rights procedure allow access to that procedure's referenced objects, before the procedure is executed. If a necessary privilege on a referenced object was revoked from the owner of a definer's rights procedure, then no user, including the owner, can run the procedure.

An example of when you may want to use a definer's rights procedure is as follows: Suppose that you must create an API whose procedures have unrestricted access to its tables, but you want to prevent ordinary users from selecting table data directly, and from changing it with INSERT, UPDATE, and DELETE statements. To accomplish this, in a separate, low-privileged schema, create the tables and the procedures that comprise the API. By default, each procedure is a definer's rights unit, so you do not need to specify AUTHID DEFINER when you create it. Then grant the EXECUTE privilege to the users who must use this API, but do not grant any privileges that allow data access. This solution gives you complete control over your API behavior and how users have access to its underlying objects.

Oracle recommends that you create your definer's rights procedures, and views that access these procedures, in their own schema. Grant this schema very low privileges, or no privileges at all. This way, when other users run these procedures or views, they will not have access to any unnecessarily high privileges from this schema.

Note:

Trigger processing follows the same patterns as definer's rights procedures. The user runs a SQL statement, which that user is privileged to run. As a result of the SQL statement, a trigger is fired. The statements within the triggered action temporarily execute under the security domain of the user that owns the trigger. For more information, see "Overview of Triggers" in Oracle Database Concepts.

How Procedure Privileges Affect Invoker's Rights

An invoker's rights procedure executes with all of the invoker's privileges. Oracle Database enables the privileges that were granted to the invoker through any of the invoker's enabled roles to take effect, unless a definer's rights procedure calls the invoker's rights procedure directly or indirectly. A user of an invoker's rights procedure must have privileges (granted to the user either directly or through a role) on objects that the procedure accesses through external references that are resolved in the schema of the invoker. When the invoker runs an invoker's rights procedure, this user temporarily has all of the privileges of the definer. (See "Controlling Invoker's Rights Privileges for Procedure Calls and View Access" for more on this aspect of invoker's rights procedures.)

The invoker must have privileges at run time to access program references embedded in DML statements or dynamic SQL statements, because they are effectively recompiled at run time.

For all other external references, such as direct PL/SQL function calls, Oracle Database checks the privileges of the owner at compile time, but does not perform a run-time check. Therefore, the user of an invoker's rights procedure does not need privileges on external references outside DML or dynamic SQL statements. Alternatively, the developer of an invoker's rights procedure must only grant privileges on the procedure itself, not on all objects directly referenced by the invoker's rights procedure.

You can create a software bundle that consists of multiple program units, some with definer's rights and others with invoker's rights, and restrict the program entry points (controlled step-in). A user who has the privilege to run an entry-point procedure can also execute internal program units indirectly, but cannot directly call the internal programs. For very precise control over query processing, you can create a PL/SQL package specification with explicit cursors.

When You Should Create Invoker's Rights Procedures

Oracle recommends that you create invoker's rights procedures in these situations:

  • When creating a PL/SQL procedure in a high-privileged schema. When lower-privileged users invoke the procedure, then it can do no more than those users are allowed to do. In other words, the invoker's rights procedure runs with the privileges of the invoking user.

  • When the PL/SQL procedure contains no SQL and is available to other users. The DBMS_OUTPUT PL/SQL package is an example of a PL/SQL subprogram that contains no SQL and is available to all users. The reason you should use an invoker's rights procedure in this situation is because the unit issues no SQL statements at run time, so the run-time system does not need to check their privileges. Specifying AUTHID CURRENT_USER makes invocations of the procedure more efficient, because when an invoker's right procedure is pushed onto, or comes from, the call stack, the values of CURRENT_USER and CURRENT_SCHEMA, and the currently enabled roles do not change. For more information about the differences between invoker's rights and definer's rights units, see Oracle Database PL/SQL Language Reference.

See Also:

Controlling Invoker's Rights Privileges for Procedure Calls and View Access

This section contains:

How the Privileges of a Schema Affect the Use of Invoker's Rights Procedures

When a user runs an invoker's rights procedure (or any PL/SQL program unit that has been created with the AUTHID CURRENT_USER clause), the procedure temporarily inherits all of the privileges of the invoking user while the procedure runs. During that time, the procedure owner has, through the procedure, access to this invoking user's privileges. Consider the following scenario:

  1. User ebrown creates the check_syntax invoker's rights procedure and then grants user jward the EXECUTE privilege on it.

  2. User ebrown, who is a junior programmer, has only the minimum set of privileges necessary for his job. The check_syntax procedure resides in ebrown's schema.

  3. User jward, who is a manager, has a far more powerful set of privileges than user ebrown.

  4. When user jward runs the check_syntax invoker's rights procedure, the procedure inherits user jward's higher privileges while it runs.

  5. Because user ebrown owns the check_syntax procedure, he has access to user jward's privileges whenever jward runs the check_syntax procedure.

The danger in this type of situation—in which the lower privileged ebrown's procedure has access to jward's higher privileges whenever jward runs the procedure—lies in the risk that the procedure owner can misuse the higher privileges of the invoking user. For example, user ebrown could make use of jward's higher privileges by rewriting the check_syntax procedure to give ebrown a raise or delete ebrown's bad performance appraisal record. Or, ebrown originally could have created the procedure as a definer's rights procedure, granted its EXECUTE privilege to jward, and then later on change it to a potentially malicious invoker's rights procedure without letting jward know. These types of risks increase when random users, such as application users, have access to a database that uses invoker's rights procedures.

When user jward runs ebrown's invoker's rights procedure, there is an element of trust involved. He must be assured that ebrown will not use the check_syntax procedure in a malicious way when it accesses jward's privileges. The INHERIT PRIVILEGES and INHERIT ANY PRIVILEGES privileges can help user jward control whether user ebrown's procedure can have access to his (jward's) privileges. Any user can grant or revoke the INHERIT PRIVILEGES privilege on themselves to the user whose invoker's rights procedures they want to run. SYS users manage the INHERIT ANY PRIVILEGES privilege.

How the INHERIT [ANY] PRIVILEGES Privileges Control Privilege Access

The INHERIT PRIVILEGES and INHERIT ANY PRIVILEGES privileges regulate the privileges used when a user runs an invoker's rights procedure or queries a BEQUEATH CURRENT_USER view that references an invoker's rights procedure. When a user runs an invoker's rights procedure, Oracle Database checks it to ensure that the procedure owner has either the INHERIT PRIVILEGES privilege on the invoking user, or if the owner has been granted the INHERIT ANY PRIVILEGES privilege. If the privilege check fails, then Oracle Database returns an ORA-06598: insufficient INHERIT PRIVILEGES privilege error.

The benefit of these two privileges is that they give invoking users control over who can access their privileges when they run an invoker's rights procedure or query a BEQUEATH CURRENT_USER view.

Granting the INHERIT PRIVILEGES Privilege to Other Users

By default, all users are granted INHERIT PRIVILEGES ON USER newuser TO PUBLIC when their accounts are created or when accounts that were created earlier are upgraded to the current release. The invoking user can revoke the INHERIT PRIVILEGE privilege from other users on himself and then grant it only to users that he trusts.

The syntax for the INHERIT PRIVILEGES privilege grant is as follows:

GRANT INHERIT PRIVILEGES ON USER invoking_user TO procedure_owner;

In this specification:

  • invoking_user is the user who runs the invoker's rights procedure. This user must be a database user account.

  • procedure_owner is the user who owns the invoker's rights procedure. This value must be a database user account. As an alternative to granting the INHERIT PRIVILEGES privilege to the procedure's owner, you can grant the privilege to a role that is in turn granted to the procedure.

The following users or roles must have the INHERIT PRIVILEGES privilege granted to them by users who will run their invoker's rights procedures:

  • Users or roles who own the invoker's rights procedures

  • Users or roles who own BEQUEATH CURRENT_USER views

Example 5-1 shows how the invoking user jward can grant user ebrown the INHERIT PRIVILEGES privilege.

Example 5-1 Granting INHERIT PRIVILEGES on an Invoking User to a Procedure Owner

GRANT INHERIT PRIVILEGES ON USER jward TO ebrown;

The statement enables any invoker's rights procedure that ebrown writes, or will write in the future, to access jward's privileges when jward runs it.

Example 5-2 shows how user jward can revoke the use of his privileges from ebrown.

Example 5-2 Revoking INHERIT PRIVILEGES

REVOKE INHERIT PRIVILEGES ON USER jward FROM ebrown;

Granting the INHERIT ANY PRIVILEGES Privilege to Other Users

By default, user SYS has the INHERIT ANY PRIVILEGES system privilege and can grant this privilege to other database users or roles. As with all ANY privileges, only grant this privilege to trusted users or roles. Once a user or role has been granted the INHERIT ANY PRIVILEGES privilege, then this user's invoker's rights procedures have access to the privileges of the invoking user. You can find the users who have been granted the INHERIT ANY PRIVILEGES privilege by querying the DBA_SYS_PRIVS data dictionary view.

Example 5-3 shows how to grant the INHERIT ANY PRIVILEGES privilege to user ebrown.

Example 5-3 Granting INHERIT ANY PRIVILEGES to a Trusted Procedure Owner

GRANT INHERIT ANY PRIVILEGES TO ebrown;

Be careful about revoking the INHERIT ANY PRIVILEGES privilege from powerful users. For example, suppose user SYSTEM has created a set of invoker's rights procedures. If you revoke INHERIT ANY PRIVILEGES from SYSTEM, then other users cannot run his procedures, unless they have specifically granted him the INHERIT PRIVILEGE privilege.

Managing the INHERIT PRIVILEGES and INHERIT ANY PRIVILEGES Privileges

By default, PUBLIC has the INHERIT PRIVILEGE privilege on all new and upgraded user accounts, and the SYS user has the INHERIT ANY PRIVILEGES privilege. Oracle by default configures a set of grants of INHERIT PRIVILEGES that are designed to help protect against misuse of the privileges of various Oracle-defined users. You can choose to revoke the default grant of INHERIT PRIVILEGES ON USER user_name TO PUBLIC for a customer-defined user and grant more specific grants of INHERIT PRIVILEGES as appropriate for that particular user. To find the users who have been granted the INHERIT ANY PRIVILEGES privilege, query the DBA_SYS_PRIVS data dictionary view.

  1. Revoke the INHERIT PRIVILEGES privilege from PUBLIC.

    For example:

    REVOKE INHERIT PRIVILEGES ON invoking_user FROM PUBLIC;
    

    Be aware that this time, any users who run invoker's rights procedures cannot do so, due to run-time errors from failed INHERIT PRIVILEGES checks.

  2. Selectively grant the INHERIT PRIVILEGES privilege to trusted users or roles.

  3. Similarly, selectively grant the INHERIT ANY PRIVILEGES privilege only to trusted users or roles.

You can create an audit policy to audit the granting and revoking of these two privileges, but you cannot audit run-time errors that result from failed INHERIT PRIVILEGES privilege checks.

See Also:

Controlling Definer's Rights and Invoker's Rights in Views

This section contains:

About Controlling Definer's Rights and Invoker's Rights in Views

You can configure user-defined views to accommodate invoker's rights functions that are referenced in the view. When a user invokes an identity- or privilege-sensitive SQL function or an invoker's rights PL/SQL or Java function, then current schema, current user, and currently enabled roles within the operation's execution can be inherited from the querying user's environment, rather than being set to the owner of the view.

This configuration does not turn the view itself into an invoker's rights object. Name resolution within the view is still handled using the view owner's schema, and privilege checking for the view is done using the view owner's privileges. However, at runtime, the function referenced by view runs under the invoking user's privileges rather than those of the view owner's.

The benefit of this feature is that it enables functions such as SYS_CONTEXT and USERENV, which must return information accurate for the invoking user, to return consistent results when these functions are referenced in a view.

Using the BEQUEATH Clause in the CREATE VIEW Statement

To enable an invoker's rights function to be executed using the rights of the user issuing SQL that references the view, in the CREATE VIEW statement, set the BEQUEATH clause to CURRENT_USER.

If you plan to issue a SQL query or DML statement against the view, then the view owner must be granted the INHERIT PRIVILEGES privilege on the invoking user or the view owner must have the INHERIT ANY PRIVILEGES privilege. If not, when a SELECT or DML statement involves a BEQUEATH CURRENT_USER view, the run-time system will raise error ORA-06598: insufficient INHERIT PRIVILEGES privilege.

Example 5-4 shows how to use BEQUEATH CURRENT_USER to set the view's function to be executed using invoker's rights.

Example 5-4 View Created Using the BEQUEATH Clause

CREATE VIEW MY_OBJECTS_VIEW BEQUEATH CURRENT_USER AS
 SELECT GET_OBJS_FUNCTION;

If you want the function within the view to be executed using the view owner's rights, then either omit the BEQUEATH clause or set it to DEFINER. For example:

CREATE VIEW my_objects_view BEQUEATH DEFINER AS
 SELECT OBJECT_NAME FROM USER_OBJECTS;

See Also:

Finding the User Name or User ID of the Invoking User

You can use the following functions to find the invoking user based on whether invoker's rights or definer's rights are used:

  • ORA_INVOKING_USER: Use this function to return the name of the user who is invoking the current statement or view. This function treats the intervening views as specified by their BEQUEATH clauses. If the invoking user is an Oracle Database Real Application Security-defined user, then this function returns XS$NULL.

  • ORA_INVOKING_USERID: Use this function to return the identifier (ID) of the user who is invoking the current statement or view. This function treats the intervening views as specified by their BEQUEATH clauses. If the invoking user is an Oracle Database Real Application Security-defined user, then this function returns an ID that is common to all Real Application Security sessions but is different from the ID of any database user.

    For example:

    CONNECT HR
    Enter password: password
    
    SELECT ORA_INVOKING_USER FROM DUAL;
    
    ORA_INVOKING_USER
    --------------------
    HR
    

See Also:

Oracle Database Real Application Security Administrator's and Developer's Guide for information about similar functions that are used for Oracle Database Real Application Security applications

Finding BEQUEATH DEFINER and BEQUEATH_CURRENT_USER Views

To find out if a view is BEQUEATH DEFINER or BEQUEATH CURRENT_USER, select the BEQUEATH column of a *_VIEWS or *_VIEWS_AE static data dictionary view for that view. For example:

SELECT BEQUEATH FROM USER_VIEWS WHERE VIEW_NAME = 'MY_OBJECTS';

BEQUEATH
------------
CURRENT_USER

See Also:

Using Code Based Access Control for Definer's Rights and Invoker's Rights

This section contains:

About Using Code Based Access Control for Applications

Applications must often run program units in the caller's environment, while requiring elevated privileges. PL/SQL programs traditionally make use of definer's rights to temporarily elevate the privileges of the program. However, definer's rights based program units run in the context of the definer or the owner of the program unit, as opposed to the invoker's context. Also, using definer's rights based programs often leads to the program unit getting more privileges than required.

Code based access control (CBAC) provides the solution by enabling you to attach database roles to a PL/SQL function, procedure, or package. These database roles are enabled at run time, enabling the program unit to execute with the required privileges in the calling user's environment.

Who Can Grant Code Based Access Control Roles to a Program Unit?

Code based access control roles can be granted to a program unit if all of the following conditions are met:

  • The grantor is user SYS or owns the program unit.

  • The roles to be granted are directly granted roles to the owner.

  • The roles to be granted are standard database roles.

If these three conditions are not met, then error ORA-28702: Program unit string is not owned by the grantor is raised if the first condition is not met, and error ORA-1924: role 'string' not granted or does not exist is raised if the second and third conditions are not met.

How Control Based Access Works with Invoker's Rights Program Units

Consider a scenario where there are two application users, 1 and 2. Application user 2 creates the invoker's right program unit, grants database role 2 to the invoker's rights unit, and then grants execute privileges on the invoker's rights unit to application user 1.

Figure 5-1 shows the database roles 1 and 2 granted to application users 1 and 2, and an invoker's right program unit.

Figure 5-1 Roles Granted to Application Users and Invoker's Right Program Unit

Description of Figure 5-1 follows
Description of "Figure 5-1 Roles Granted to Application Users and Invoker's Right Program Unit"

The grants are as follows:

  • Application user 1 is directly granted database roles 1 and 4.

  • Application user 2 is directly granted database role 2, which includes application roles 3 and 4.

  • The invoker's right program unit is granted database role 2.

When application user 1 logs in and executes the invoker's rights program unit, then the invoker's rights unit executes with the combined database roles of user 1 and the database roles attached to the invoker's rights unit.

Figure 5-2 shows the security context in which the invoker's rights unit is executed. When application user 1 first logs on, application user 1 has the database PUBLIC role (by default), and the database roles 1 and 4, which have been granted to it. Application user 1 next executes the invoker's rights program unit created by application user 2.

The invoker's rights unit executes in application user 1's context, and has the additional database role 2 attached to it. Database roles 3 and 4 are included, as they are a part of database role 2. After the invoker's rights unit exits, then application user 1 only has the application roles that have been granted to it, PUBLIC, role 1, and role 4.

Figure 5-2 Security Context in Which Invoker's Right Program Unit IR Is Executed

Description of Figure 5-2 follows
Description of "Figure 5-2 Security Context in Which Invoker's Right Program Unit IR Is Executed"

How Control Based Access Control Works with Definer's Rights Program Units

Consider a scenario where application user 2 creates a definer's rights program unit, grants role 2 to the definer's rights program unit, and then grants the EXECUTE privilege on the definer's rights program unit to application user 1.

Figure 5-3 shows the database roles granted to application users 1 and 2, and a definer's rights program unit.

Figure 5-3 Roles Granted to Application Users and Definer's Rights Program Unit

Description of Figure 5-3 follows
Description of "Figure 5-3 Roles Granted to Application Users and Definer's Rights Program Unit"

The grants are as follows:

  • Application user 1 is directly granted database roles 1 and 4.

  • Application user 2 is directly granted database role2, which includes database roles 3 and 4.

  • The definer's right program unit is granted database role 2.

When application user 1 logs in and executes definer's right program unit, then the definer's rights unit executes with the combined database roles of application user 2 and the database roles attached to the definer's rights unit (roles 2, 3, and 4).

Figure 5-4 shows the security context in which the definer's right program unit is executed. When application user 1 first logs on, application user 1 has the database PUBLIC role (by default), and the database roles 1 and4, which have been granted to it. Application user 1 next executes the definer's rights program unit created by application user 2.

The definer's rights program unit executes in application user 2's context, and has the additional database role 2 attached to it. Database roles 3 and 4 are included, as they are a part of database role 2. After the definer's rights unit exits, application user 1 only has the database roles that have been granted to it (PUBLIC, role 1, and role 4).

Figure 5-4 Security Context in Which Definer's Right Program Unit DR Is Executed

Description of Figure 5-4 follows
Description of "Figure 5-4 Security Context in Which Definer's Right Program Unit DR Is Executed"

Granting and Revoking Database Roles to a Program Unit

Use the following syntax to grant or revoke database roles for a PL/SQL function, procedure, or package:

GRANT role_list TO code_list
REVOKE {role_list | ALL} FROM code_list
 

In this specification:

role_list ::=  code-based_role_name[, role_list]
code_list ::=  {
      {FUNCTION  [schema.]function_name}
   |  {PROCEDURE [schema.]procedure_name}
   |  {PACKAGE   [schema.]package_name}
                 }[, code_list]

For example:

GRANT cb1 TO FUNCTION func1, PACKAGE pack1;

GRANT cb2, cb3 TO FUNCTION SCOTT.func2, PACKAGE SYS.pack2;

REVOKE cb1 FROM FUNCTION func1, PACKAGE pack1;

REVOKE ALL FROM FUNCTION SCOTT.func2, PACKAGE SYS.pack2;

Tutorial: Controlling Access to Sensitive Data Using Code Based Access Control

This section contains:

About This Tutorial

In this tutorial, you will create a user who must have access to specific employee information for his department. However, the table HR.EMPLOYEES contains sensitive information such as employee salaries, which must not be accessible to the user. You will implement access control using code based access control. The employee data will be shown to the user through an invoker's rights procedure. Instead of granting the SELECT privilege directly to the user, you will grant the SELECT privilege to the invoker's rights procedure through a database role. In the procedure, you will hide the sensitive information, such as salaries. Because the procedure is an invoker's rights procedure, you know the caller's context inside the procedure. In this case, the caller's context is for the Finance department. The user is named "Finance", so that only data for employees who work in the Finance department is accessible to the user.

Step 1: Create the User and Grant HR the CREATE ROLE Privilege

  1. Log into the database instance as an administrator who has privileges to create user accounts and roles.

    For example:

    sqlplus sec_admin
    Enter password: password
    
  2. Create the "Finance" user account.

    GRANT CONNECT TO "Finance" IDENTIFIED BY password;
    

    Ensure that you enter "Finance" in the case shown, enclosed by double quotation marks. Replace password with a password that is secure. See "Minimum Requirements for Passwords" for more information.

  3. Grant the CREATE ROLE privilege to user HR.

    GRANT CREATE ROLE TO HR;
    

Step 2: Create the print_employees Invoker's Rights Procedure

The print_employees invoker's rights procedure shows employee information in the current user's department. You must create this procedure as an invoker's rights procedure because you must know who the caller is when inside the procedure.

  1. Connect as user HR.

    CONNECT HR
    Enter password: password
    
  2. Create the print_employees procedure as follows. (You can copy and paste this text by positioning the cursor at the start of create or replace in the first line.)

     
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    
    create or replace procedure print_employees
    authid current_user
    as 
    begin
      dbms_output.put_line(rpad('ID', 10) ||
                           rpad('First Name', 15)  ||
                           rpad('Last Name', 15)   ||
                           rpad('Email', 15)       ||
                           rpad('Phone Number', 20));
      for rec in (select e.employee_id, e.first_name, e.last_name, 
                         e.email, e.phone_number
                    from hr.employees e, hr.departments d 
                   where e.department_id = d.department_id
                     and d.department_name = 
                         sys_context('userenv', 'current_user'))
      loop
        dbms_output.put_line(rpad(rec.employee_ID, 10)  ||
                             rpad(rec.first_name, 15)   || 
                             rpad(rec.last_name, 15)    ||
                             rpad(rec.email, 15)        ||
                             rpad(rec.phone_number, 20));
      end loop;
    end;
    /
    

    In this example:

    • Lines 5–9: Prints the table header.

    • Lines 10–15: Finds the employee information for the caller's department, which for this tutorial is the Finance department for user "Finance". Had you created a user named "Marketing" (which is also listed in the DEPARTMENT_NAME column of the HR.EMPLOYEES table), then the procedure could capture information for Marketing employees.

    • Lines 16–22: Populates the output with the employee data from the Finance department.

Step 3: Create the hr_clerk Role and Grant Privileges for It

  1. Create the hr_clerk role.

    CREATE ROLE hr_clerk;
    
  2. Grant the EXECUTE privilege on the print_employees procedure to the hr_clerk role.

    GRANT EXECUTE ON print_employees TO hr_clerk;
    
  3. Grant the hr_clerk role to "Finance".

    GRANT hr_clerk TO "Finance";
    

Step 4: Test the Code Based Access Control HR.print_employees Procedure

  1. Connect to the database instance as user "Finance".

    CONNECT "Finance"
    Enter password: password
    
  2. Try to directly query the HR.EMPLOYEES table.

    SELECT EMPLOYEE_ID, FIRST_NAME, LAST_NAME, SALARY FROM HR.EMPLOYEES;
    

    The query fails because user rblakey does not have the SELECT privilege for HR.EMPLOYEES.

    ERROR at line 1:
    ORA-00942: table or view does not exist
    
  3. Execute the HR.print_employees procedure.

    EXEC HR.print_employees;
    

    The query fails because user "Finance" does not have the appropriate privileges.

    ERROR at line 1:
    ORA-00942: table or view does not exist
    ORA-06512: at "HR.PRINT_EMPLOYEES", line 13ORA-06512: at line 1
    

Step 5: Create the view_emp_role Role and Grant Privileges for It

  1. Connect as user HR.

    CONNECT HR
    Enter password: password
    
  2. Create the view_emp_role role.

    CREATE ROLE view_emp_role;
    
  3. Grant the SELECT privilege on HR.EMPLOYEES and HR.DEPARTMENTS to the view_emp_role role.

    GRANT SELECT ON HR.EMPLOYEES TO view_emp_role;
    GRANT SELECT ON HR.DEPARTMENTS TO view_emp_role;
    
  4. Grant the view_emp_role role to the HR.print_employees invoker's rights procedure.

    GRANT view_emp_role TO PROCEDURE HR.print_employees;
    

Step 6: Test the HR.print_employees Procedure Again

  1. Connect as user "Finance".

    CONNECT "Finance"
    Enter password: password
    
  2. Set the server output to display.

    SET SERVEROUTPUT ON;
    
  3. Try to directly query the HR.EMPLOYEES table.

    SELECT EMPLOYEE_ID, FIRST_NAME, LAST_NAME, SALARY FROM HR.EMPLOYEES;
    

    The query fails.

    ERROR at line 1:
    ORA-00942: table or view does not exist
    
  4. Execute the HR.print_employees procedure to show the employee information.

    EXEC HR.print_employees;
    

    The call succeeds.

    ID        First Name     Last Name      Email          Phone Number
    108       Nancy          Greenberg      NGREENBE       515.124.4569
    109       Daniel         Faviet         DFAVIET        515.124.4169
    110       John           Chen           JCHEN          515.124.4269
    111       Ismael         Sciarra        ISCIARRA       515.124.4369
    112       Jose Manuel    Urman          JMURMAN        515.124.4469
    113       Luis           Popp           LPOPP          515.124.4567
     
    PL/SQL procedure successfully completed.
    

Step 7: Remove the Components for This Tutorial

  1. Connect as a user with administrative privileges.

    For example:

    CONNECT sec_admin
    Enter password: password
    
  2. Drop the user "Finance".

    DROP USER "Finance";
    
  3. Drop the hr_clerk role.

    DROP ROLE hr_clerk;
    
  4. Connect as user HR.

    CONNECT HR
    Enter password: password
    
  5. Drop the view_emp_role role and the HR.print_employees procedure.

    DROP ROLE view_emp_role;
    DROP PROCEDURE print_employees;
    
  6. Connect as the administrative user.

    CONNECT sec_admin
    Enter password: password
    
  7. Revoke the CREATE ROLE privilege from HR.

    REVOKE CREATE ROLE FROM HR;