Oracle PL/SQL Programming, 5th Edition

Execution Authority Models

The Oracle database offers two different models for object permissions in your PL/SQL programs. The default (and only model way back in the days before Oracle8i Database) is definer rights. With this model, a stored program executes under the authority of its owner, or definer.^[29] The other permission model uses the privileges of the user invoking the program and is referred to as invoker rights.

You need to understand the nuances of both the definer rights model and the invoker rights model because many PL/SQL applications rely on a combination of the two. Let’s explore these in a little more detail, so you know when you want to use each model.

The Definer Rights Model

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, keep the following rules in mind:

• 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 ...