Feuertip #32: An introduction to invoker rights (AUTHID)

The Oracle database offers two different models for object permissions in your PL/SQL programs. The default is definer rights. With this model, a stored program executes under the authority of its owner or definer. 

The other permission model uses the privileges of the user invoking the program and is referred to as invoker rights.

Before a PL/SQL program can be executed from within a database instance, it must be compiled and stored in the database itself. Thus, a program unit is always stored within a specific schema or database account, even though the program might refer to objects in other schemas.

With the definer rights model….

• Any external reference in a program unit is resolved at compile time, using the directly granted privileges of the schema in which the program unit is compiled.
• Database roles are ignored completely when compiling stored programs. All privileges needed for the program must be granted directly to the definer (owner) of the program.
• Whenever you run a program compiled with the definer rights model (the default), its SQL executes under the authority of the schema that owns the program.
• Although direct grants are needed to compile a program, you can grant EXECUTE authority to give other schemas and roles the ability to run your program.

With the invoker rights model….

• Any external reference in a program unit is resolved at compile time, using the directly granted privileges of the schema in which the program unit is compiled.
• When the program unit is executed, all those external references are resolved again, using the directly-granted and role-based granted privileges  of the schema in which the program unit is running.

When to use invoker rights….

• Avoiding compiling copies of code across multiple schemas. Suppose you have implemented multi-lingual support by creating a separate schema for each language. In other words, each schema has a help_text table with different contents. With definer rights, you’d have to compile all code referencing that table in each schema. With invoker rights, the code can be compiled into a single schema, invoked from each language-specific schema.
• (from Oracle doc) When creating a PL/SQL procedure in a high-privileged schema. When lower-privileged users invoke the procedure, it can do no more than those users are allowed to do. In other words, the invoker’s rights procedure runs with the privileges of the invoking user.
• (from Oracle doc) When the PL/SQL procedure contains no SQL and is available to other users. The DBMS_OUTPUT PL/SQL package is an example of a PL/SQL subprogram that contains no SQL and is available to all users. The reason you should use an invoker’s rights procedure in this situation is because the unit issues no SQL statements at run time, so the run-time system does not need to check their privileges. Specifying AUTHID CURRENT_USER makes invocations of the procedure more efficient because when an invoker’s right procedure is pushed onto, or comes from, the call stack, the values of CURRENT_USER and CURRENT_SCHEMA, and the currently enabled roles do not change.

Resources

Managing Security for Definer’s Rights and Invoker’s Rights

Control Invoker Rights Privileges for PL/SQL Code – ORACLE-BASE