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.