Advantages of Invoker Rights

The Invoker-rights routines centralize the data retrieval. They are particularly helpful in applications which store data in various schemas. In such situations, the multiple users can manage their own data using the single code base.

Consider a company which uses a definer-rights procedure to analyze the sales. To provide the local sales statistics, the procedure analyze should access the sales tables that exist in at each regional site. Therefore, as figure shows, the procedure should also exist in at each regional site. That causes a maintenance trouble.

Figure: Definer Rights Problem

To resolve the problem, the company installs invoker-rights version of procedure analyzes at the headquarters. Now, as figure shows, all the regional sites can use similar procedure to query their own sales tables.

Figure: Invoker Rights Solution

The Invoker-rights routines also restrict the access to the sensitive data. Assume that the headquarters would like procedure analyze to compute the sales commissions and update the central payroll table.

That represents a problem as invokers of analyze must not have the direct access to the payroll table that stores the employee salary and other sensitive data. As figure shows, the answer is to have procedure analyze to call the definer-rights procedure calc_comm that in turn updates the payroll table.

Figure: Indirect Access