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 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 in effect when compiling anonymous blocks, but are ignored completely when compiling stored programs.

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

Figure 20-11 shows how you can use the definer rights model to control access to underlying data objects. All the order entry data is stored in the OEData schema. All the order entry code is defined in the OECode schema. OECode has been granted the direct privileges necessary to compile the Order_Mgt package, which allows you to both place and cancel orders.