Oracle PL/SQL Programming Guide to Oracle 8i Features

Oracle PL/SQL Programming Guide to Oracle 8i FeaturesSearch this book
Previous: 3.1 A Look at the Definer Rights ModelChapter 3
Invoker Rights: Your Schema or Mine?
Next: 3.3 One Program, Multiple Schemas
 

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

Figure 3.3

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:


Previous: 3.1 A Look at the Definer Rights ModelOracle PL/SQL Programming Guide to Oracle 8i FeaturesNext: 3.3 One Program, Multiple Schemas
3.1 A Look at the Definer Rights ModelBook Index3.3 One Program, Multiple Schemas

The Oracle Library Navigation

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

Library Home Oracle PL/SQL Programming, 2nd. Ed. Guide to Oracle 8i Features Oracle Built-in Packages Advanced PL/SQL Programming with Packages Oracle Web Applications Oracle PL/SQL Language Pocket Reference Oracle PL/SQL Built-ins Pocket Reference
This HTML Help has been published using the chm2web software.