3.2 The Invoker Rights Model

, rules and restrictions, AUTHID DEFINER clause, DELETE statement, INSERT statement, SELECT statement, UPDATE statement, LOCK TABLE transaction control statement, OPEN cursor control statement, OPEN-FOR statement, native dynamic SQL, EXECUTE IMMEDIATE statement, native dynamic SQL, PARSE procedure, DBMS_SQL package">

Team-Fly

---

 

	Oracle PL/SQL Programming Guide to Oracle 8 i Features By Steven Feuerstein
	Table of Contents
	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 ) 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.

---

Team-Fly

Top