Oracle PL/SQL Programming, Third Edition

Execution Authority Models

Back in the old days before Oracle8i, a stored program was always executed under the authority of its owner, or definer. This was not a big deal if your entire application—code and data—worked out of the same Oracle account. The centralized, stored code would not automatically apply the privileges of a user (also known as an invoker) to the code’s objects. The user might not have had DELETE privileges on a table, but the stored code did, so delete away! Now, in some circumstances, that may have been just how you wanted it to work. In other situations, particularly when you were executing programs relying on the DBMS_SQL (dynamic SQL) package, awesome complications could ensue.

In Oracle 8.1, PL/SQL was enhanced so that at the time of compilation, the application programmer could decide whether a program (or all programs in a package) should run under the authority of the definer (the only choice in Oracle 8.0 and earlier) or of the invoker of that program.

The Definer Rights Model

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.

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 Oracle account, even though the program might refer to objects in other schema.

With the definer rights model, you should ...