Invoker Rights versus Definer Rights

By default, Java stored procedures execute with the privileges of their invoker, not their definer. Invoker-rights procedures are not bound to a particular schema. Their unqualified references to schema objects (such as database tables) are resolved in the schema of the invoker, not the definer.

To override the default behavior, specify the loadjava option definer. Definer-rights procedures are bound to the schema in which they reside. They execute with the privileges of their definer, and their unqualified references to schema objects are resolved in the schema of the definer.

Invoker-rights procedures let you centralize data retrieval. Consider a company that uses a definer-rights procedure to analyze sales. To provide local sales statistics, the procedure analyze must access sales tables that reside at each regional site. To do so, the procedure must also reside at each regional site. This causes a maintenance problem.

To solve the problem, the company installs an invoker-rights version of the procedure analyze at headquarters. Now, as Figure 2-2 shows, all regional sites can use the same procedure to query their own sales tables.

Figure 2-2 Invoker-Rights Solution

Occasionally, you might want to override the default invoker-rights behavior. Suppose headquarters would like the procedure analyze to calculate sales commissions and update a central payroll table. That presents a problem because invokers of analyze should not have direct access to the payroll table, which stores employee salaries and other sensitive data. As Figure 2-3 shows, the solution is to have procedure analyze call the definer-rights procedure calcComm, which, in turn, updates the payroll table.

Figure 2-3 Indirect Access

---