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

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.

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

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.

REVOKE INHERIT PRIVILEGES ON USER jward FROM ebrown;

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

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.

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.

Use of 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, you can 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, then when a SELECT query 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.

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 you should 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;

Finding the User Name or User ID of the Invoking User

You can use the ORA_INVOKING_USER and ORA_INVOKING_USERID 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
  

Finding BEQUEATH DEFINER and BEQUEATH_CURRENT_USER Views

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

• To find if a view is BEQUEATH DEFINER or BEQUEATH CURRENT_USER, query 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
  

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 a set of conditions are met.

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

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

Code based access control works with invoker's rights program units by enabling the program unit to run in the invoking user's context and with the database roles that have been 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 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 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 Security Context in Which Invoker's Right Program Unit IR Is Executed"

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

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

Grants of Database Roles to Users for Their 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.

Grants and Revokes of Database Roles to a Program Unit

You can use the GRANT and REVOKE SQL statements to 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;

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

This tutorial demonstrates how to control access to sensitive data in the HR schema by using code based access control. It shows how to create sample users, create an invoker's rights procedure, grant privileges to a user for the procedure, create a role and grant privileges for the invoker's rights procedure to it, test the invoker's rights procedure, and then remove the components of the tutorial if you no longer need them.

Topics:

• About This Tutorial

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

• Step 2: Create the print_employees Invoker's Rights Procedure

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

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

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

• Step 6: Test the HR.print_employees Procedure Again

• Step 7: Remove the Components of This Tutorial