Oracle8i Database introduced the AUTHID clause for procedures, functions and packages. When set to AUTHID DEFINER (the default), then your program runs under "definer rights." This means that any references to data objects (such as tables and views) are resolved at compile time, based on the directly granted privileges of the definer or owner of the program. Roles are ignored. If, on the other hand, you set the clause to AUTHID CURRENT_USER, then any references to data objects are resolved at run time, based on the privileges of the currently-connected schema. And (the DBAs have got to love this) role-based privileges are now applied.

Invoker rights comes in very handy when your application architecture requires that you have multiple schemas with the same table structures, but you don't want to maintain multiple copies of your code base. It is also extremely important to use in all stored programs that contain dynamic SQL -- if you want to make sure that the dynamically contructed and executed SQL statement runs in the currently connected schema.

Suppose for example that I have created a utility that reads the contents of a file and then executes it as a DDL statement. In fact, I have created such a program and it is described (with link) in the Download section. The header of the program looks like this:

PROCEDURE exec_ddl_from_file (
 dir_in IN VARCHAR2
 , file_in IN VARCHAR2
)
IS ...

That is, you pass in the location and name of the file, and I execute its contents for you. For example, you can use this program to compile programs from file. Now suppose I want all my developers to be able to use this handy utility. I could do the following:

GRANT EXECUTE ON exec_ddl_from_file TO PUBLIC
/
CREATE PUBLIC SYNONYM exec_ddl_from_file FOR exec_ddl_from_file
/

So everyone can have at it! Now suppose that SCOTT is the owner of exec_ddl_from_file. If the HR schema then runs this program to execute a file that contains a "CREATE TABLE MY_TABLE", HR will create this table in the SCOTT schema. Not exactly what was desired. All I have to do, though, is add AUTHID CURRENT_USER to header and then HR will create the table in her own schema, not that of SCOTT:

PROCEDURE exec_ddl_from_file (
 dir_in IN VARCHAR2
 , file_in IN VARCHAR2
) AUTHID CURRENT_USER
IS ...

So that's the idea behind the AUTHID clause and invoker rights. There is, unfortunately, another complication that lies at the heart of this month's tip: if definer rights program DEFPROG (with AUTHID DEFINER) calls an invoker rights program INVPROG (with AUTHID CURRENT_USER), then the invoker rights program will be run with "current user" set to the definer/owner of DEFPROG. That is, the currently-connected schema will be ignored. (Check out the invdefinv.sql script in the Download section for an example of this behavior).

There is, in other words, no way to guarantee that your invoker rights program will indeed be run under the invoker's privileges. It depends on the call stack and the AUTHID setting of previous programs in the stack. Too bad....

In the case of a program like exec_ddl_from_file, I really don't want it to even run if it is not running under the authority of the currently-connected schema. Fortunately, there is a way to to this. I can take advantage the SYS_CONTEXT built-in to obtain information about my "user environment" by specifying the "USERENV" context. I can then ask for the name of the session user and compare it to that of the current user. If they are the same, I can proceed. If not, then I terminate my program. Here is an example of applying this logic:

CREATE OR REPLACE PROCEDURE exec_ddl_from_file (
 dir_in IN VARCHAR2
 , file_in IN VARCHAR2
)
AUTHID CURRENT_USER
IS
 FUNCTION invoker_rights_mode RETURN BOOLEAN
 IS
 -- Original idea from Solomon Yakobson 
 v_retval NUMBER;
 BEGIN 
 RETURN SYS_CONTEXT ( 'USERENV', 'SESSION_USER' ) = 
 SYS_CONTEXT ( 'USERENV', 'CURRENT_USER' ); 
 END; 
 ...
BEGIN
 IF ( NOT invoker_rights_mode ) 
 THEN 
 raise_application_error 
 ( -20999 
 , 'Exec DDL from file must run under invoker privileges!' 
 ); 
 END IF;