#

| Chapter 3 Invoker Rights: Your Schema or Mine? |

## 3.2 The Invoker Rights Model

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

Figure 3.3 demonstrates the fundamental difference between the definer and the invoker rights models. Recall that in Figure 3.2, 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.

#### Figure 3.3: Use of invoker rights model to allow a "pass through" to user data

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.

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

### 3.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 a generic "run DDL" engine that relies on the new Oracle 8.1 native dynamic SQL statement EXECUTE IMMEDIATE (described in Chapter 4, Native Dynamic SQL in Oracle8i ) and the invoker rights model:

>     CREATE OR REPLACE PROCEDURE runddl (ddl_in in VARCHAR2)
>        AUTHID CURRENT_USER
>     IS
>     BEGIN
>        EXECUTE IMMEDIATE ddl_in;
>     END;
>     /

That's certainly lots simpler than the earlier implementation, isn't it?

The AUTHID CURRENT_USER clause before the IS keyword indicates that when runddl 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.

### 3.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 external references to database objects (but not PL/SQL program units). Even with invoker rights, however, roles are ignored.

- 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 at compile time all external references to PL/SQL programs and object type methods. To verify and understand this behavior, consider the following script:

  >     /* Filename on companion disk: authid2.sql */
  >     CONNECT demo/demo
  >     CREATE PROCEDURE dummy1 IS
  >     BEGIN
  >        DBMS_OUTPUT.put_line ('Dummy1 owned by demo');
  >     END;
  >     /
  >     GRANT execute on dummy1 to public;
  >     CONNECT scott/tiger
  >     CREATE PROCEDURE dummy1 IS
  >     BEGIN
  >        DBMS_OUTPUT.put_line ('Dummy1 owned by scott');
  >     END;
  >     /
  >     GRANT execute on dummy1 to public;
  >     CREATE PROCEDURE dummy2 AUTHID CURRENT_USER
  >     IS
  >     BEGIN
  >        dummy1;
  >     END;
  >     /
  >     GRANT execute on dummy2 to public;

  >     EXEC scott.dummy2

  >     CONNECT demo/demo
  >     SET serveroutput on
  >     EXEC scott.dummy2

  When you run this script (needing both the DEMO and SCOTT accounts to be defined), you will see the following output:

  >     SQL> @authid2
  >     Connected.
  >     Procedure created.
  >     Grant succeeded.
  >     Connected.
  >     Procedure created.
  >     Grant succeeded.
  >     Procedure created.
  >     Grant succeeded.
  >     Connected.
  >     Dummy1 owned by scott

  As you can see, DEMO called SCOTT's dummy2 procedure, which was set up as an invoker rights procedure. But SCOTT.dummy2 did not call DEMO's dummy1 procedure. Instead it called its own version. Contrast the behavior of authid2.sql with that found in authid3.sql (you'll find it on the companion disk) ; there you will see that table access is redirected to the DEMO schema.

  So just remember this: you can use invoker rights to change the resolution of external data element references (tables and views) but not that of program elements.

3.1 A Look at the Definer Rights Model |  | 3.3 One Program, Multiple Schemas

Copyright (c) 2000 O'Reilly & Associates. All rights reserved.