An invoker’s rights procedure is useful in situations where a lower-privileged user must execute a procedure owned by a higher-privileged user.

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.

Use the INHERIT PRIVILEGES and INHERIT ANY PRIVILEGES privileges to secure invoker’s rights procedures.

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.

By default, all users are granted INHERIT PRIVILEGES ON USER newuser TO PUBLIC.

This grant takes place when the user 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

The GRANT statement can grant the INHERIT PRIVILEGES privilege on an invoking user to a procedure owner.

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

GRANT INHERIT PRIVILEGES ON USER jward TO ebrown;

The REVOKE statement can revoke the INHERIT PRIVILEGES privilege from a user.

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

REVOKE INHERIT PRIVILEGES ON USER jward FROM ebrown;

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.

The GRANT statement can grant the INHERIT ANY PRIVILEGES privilege to trusted procedure owners.

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

GRANT INHERIT ANY PRIVILEGES TO ebrown;

By default, PUBLIC has the INHERIT PRIVILEGE privilege on new and upgraded user accounts; the SYS user has the INHERIT ANY PRIVILEGES privilege.

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 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.

The BEQUEATH controls how an invoker’s right function can be executed using the rights of the invoking user.

CREATE VIEW MY_OBJECTS_VIEW BEQUEATH CURRENT_USER AS
 SELECT GET_OBJS_FUNCTION;

CREATE VIEW my_objects_view BEQUEATH DEFINER AS
 SELECT OBJECT_NAME FROM USER_OBJECTS;

PL/SQL functions can be used to find the invoking user, based on whether invoker’s rights or definer’s rights are being used.

You can find out if a view is a BEQUEATH DEFINER or BEQUEATH CURRENT_USER view.

You can use code based access control (CBAC) to better manage definer’s rights program units.

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.

You can create privilege analysis policies that capture the use of CBAC roles.

Code based access control roles can be granted to a program unit if a set of conditions are met.

These conditions are as follows:

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

• If the grantor owns the program unit, then the grantor must have the GRANT ANY ROLE system privilege, or have the ADMIN or DELEGATE option for the roles that they want to grant to program units.

• 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.

Code based access control can run a program unit in an invoking user's context and with roles associated with this context.

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 7-1 shows the database roles 1 and 2 granted to application users 1 and 2, and an invoker's right program unit.

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

Description of "Figure 7-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 7-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 7-2 Security Context in Which Invoker's Right Program Unit IR Is Executed

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

Code based access control can be used to secure definer’s rights.

Code based access control works with definer's rights program units to enable the program unit to run using the defining user's rights, with the privileges of a combined set of database roles that are associated with this user.

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 7-3 shows the database roles granted to application users 1 and 2, and a definer's rights program unit.

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

Description of "Figure 7-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 7-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 7-4 Security Context in Which Definer's Right Program Unit DR Is Executed

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

The DELEGATE option in the GRANT statement can limit privilege grants to roles by users responsible for CBAC grants.

When you grant a database role to a user who is responsible for CBAC grants, you can include the DELEGATE option in the GRANT statement to prevent giving the grantee additional privileges on the roles.

The DELEGATE option enables the roles to be granted to program units, but it does not permit the granting of the role to other principals or the administration of the role itself. You also can use the ADMIN option for the grants, which does permit the granting of the role to other principals. Both the ADMIN and DELEGATE options are compatible; that is, you can grant both to a user, though you must do this in separate GRANT statements for each option. To find if a user has been granted a role with these options, query the DELEGATE_OPTION column or the ADMIN_OPTION column of either the USER_ROLE_PRIVS or DBA_ROLE_PRIVS for the user.

The syntax for using the DELEGATE and ADMIN option is as follows:

GRANT role_list to user_list WITH DELEGATE OPTION;

GRANT role_list to user_list WITH ADMIN OPTION;

For example:

GRANT cb_role1 to usr1 WITH DELEGATE OPTION;

GRANT cb_role1 to usr1 WITH ADMIN OPTION;

GRANT cb_role1, cb_role2 to usr1, usr2 with DELEGATE OPTION;

GRANT cb_role1, cb_role2 to usr1, usr2 with ADMIN OPTION;

In a multitenant environment, you can use the DELEGATE option for common grants such as granting common roles to common users, just as you can with the ADMIN option.

For example:

GRANT c##cb_role1 to c##usr1 WITH DELEGATE OPTION CONTAINER = ALL;

Be aware that CBAC grants themselves can only take place locally in a PDB.

The GRANT and REVOKE statements can grant database roles to or revoke database roles from a program unit.

The following syntax to grants or revokes 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 cb_role1 TO FUNCTION func1, PACKAGE pack1;

GRANT cb_role2, cb_role3 TO FUNCTION HR.func2, PACKAGE SYS.pack2;

REVOKE cb_role1 FROM FUNCTION func1, PACKAGE pack1;

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

This tutorial demonstrates how to control access to sensitive data in the HR schema by using code based access control.

• About This Tutorial
  In this tutorial, you will create a user who must have access to specific employee information for his department.
• Step 1: Create the User and Grant HR the CREATE ROLE Privilege
  To begin, you must create the "Finance" user account and then grant this the HR user the CREATE ROLE privilege.
• 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.
• Step 3: Create the hr_clerk Role and Grant Privileges for It
  Next, you are ready to create the hr_clerk role, which must have the EXECUTE privilege on the print_employees procedure.
• Step 4: Test the Code Based Access Control HR.print_employees Procedure
  At this stage, you are ready to test the code based access control HR.print_employees procedure.
• Step 5: Create the view_emp_role Role and Grant Privileges for It
  Next, user HR must create the view_emp_role role and then grant privileges to it.
• Step 6: Test the HR.print_employees Procedure Again
  With the appropriate privileges in place, user "Finance" can try the HR.print_employees procedure again.
• Step 7: Remove the Components of This Tutorial
  If you no longer need the components of this tutorial, then you can remove them.