20.6.2 The Invoker Rights Model

To help developers get around the obstacles raised by the definer rights model, Oracle 8.1 and later offers an alternative: the invoker rights model. With this approach, all external references in the SQL statements in a PL/SQL program unit are resolved according to the directly granted privileges of the invoking schema, not those of the owning or defining schema.

Figure 20-13 demonstrates the fundamental difference between the definer and the invoker rights models. Recall that in Figure 20-12, it was necessary for me to push out copies of my application to each regional office so that the code would manipulate the correct tables. With invoker rights, this step is no longer necessary. Now I can compile the code into a single code repository. When a user from the Northeast region executes the centralized program (probably via a synonym), it will automatically work with tables in the Northeast schema.

Figure 20-13. Use of invoker rights model

So that's the idea behind invoker rights. Let's see what is involved in terms of code, and then explore how best to exploit the feature.

20.6.2.1 Invoker rights syntax

The syntax to support this feature is simple enough. You add the following clause before your IS or AS keyword in the program header:

AUTHID CURRENT_USER

Here, for example, is that generic "exec DDL" engine again, this time defined as an invoker rights program:

/* File on web: execddl.sql */

CREATE OR REPLACE PROCEDURE execddl (ddl_in in VARCHAR2)

AUTHID CURRENT_USER

IS

BEGIN

EXECUTE IMMEDIATE ddl_in;

END;

The AUTHID CURRENT_USER clause before the IS keyword indicates that when execddl executes, it should run under the authority of the invoker or "current user," not the authority of the definer. And that's all you have to do. If you do not include the AUTHID clause, or if you include it and explicitly request definer rights as shown:

AUTHID DEFINER

then all references in your program will be resolved according to the directly granted privileges of the owning schema.

Invoker Rights for Dynamic SQL I have written hundreds of programs using dynamic SQL, and prior to Oracle 8.1, I always had to worry about schema issues. Where is the program running? Who is running it? What will happen when someone runs it? These are scary questions to ask about your own code! You may be tempted to use the AUTHID CURRENT_USER clause with every stored program unit that uses any sort of dynamic SQL. Once you take this step, you reason, you can rest assured that no matter where the program is compiled and which schema runs the program, it will always act on the currently connected schema. The problem with this approach, though, is that the extra runtime checking Oracle must perform for invoker rights programs can be a real drag on performance. Use invoker rights sparingly.

20.6.2.2 Some rules and restrictions

There are a number of rules and restrictions to keep in mind when you are taking advantage of the invoker rights model:

• AUTHID DEFINER is the default option.

• The invoker rights model checks the directly granted privileges assigned to the invoker at the time of program execution to resolve any SQL-based references to database objects.

• With invoker rights, roles are in effect at runtime as long as the invoker rights program hasn't been called from a definer rights program.

• The AUTHID clause is allowed only in the header of a standalone subprogram (procedure or function), a package specification, or an object type specification. You cannot apply the AUTHID clause to individual programs or methods within a package or object type.

• Invoker rights resolution of external references will work for the following kinds of statements:

  • SELECT, INSERT, UPDATE, and DELETE data manipulation statements

  • LOCK TABLE transaction control statement

  • OPEN and OPEN FOR cursor control statements

  • EXECUTE IMMEDIATE and OPEN FOR USING dynamic SQL statements

  • SQL statements parsed using DBMS_SQL.PARSE

• Definer rights will always be used to resolve all external references to PL/SQL programs and object type methods at compilation time.

• You can use invoker rights to change the resolution of static external data element references (tables and views).

You can also use invoker rights to resolveexternal references to PL/SQL programs. Here is one way to do it:

EXECUTE IMMEDIATE 'BEGIN someprogram; END;';

In this fragment, someprogram would get resolved at runtime according to the rights and namespace of the invoker. Alternatively, I could have used SQL's CALL statement instead of the anonymous block. (I can't just use a naked CALL statement because it is not directly supported within PL/SQL.)