Invoker and Definer Rights

Summary

In this chapter we thoroughly explored the concepts of definer rights and invoker rights procedures. We learned how easy it is to enable invoker rights, but we also learned of the price that is to be paid with regards to:

• Error detection and handling.

• Subtle errors that could be introduced by different table structures at run-time.

• Additional shared SQL area overhead potential.

• Additional parse times incurred.

At first glance, these seem too high a price to pay - and in many cases it is. In other cases, such as the generic routine to print out comma-separated data from any query, or to print out the results of a query down the screen instead of across the screen, it is an invaluable feature. Without it, we just could not accomplish what we set out to do.

Invoker rights routines make the most sense in the following cases:

• When the SQL to be processed is dynamic in nature (as these examples are).

• When the SQL to be processed is set up to enforce security by the SCHEMAID, as in the case of the data dictionary (or your own application).

• When you need roles to be in place, invoker rights routines are the only way to do it.

Invoker rights can be used to provide access to different schemas based on the current schema (as returned by SYS_CONTEXT(‘USERENV’,‘CURRENT_SCHEMA’)), but care must be taken here to ensure the schemas are consistent with each other, and that the necessary privileges are in place (or that your code is set up to handle the lack of access gracefully). You must also be prepared to pay the price in shared pool utilization, and additional overhead with regards to parsing.

Definer rights procedures are still the correct implementation for almost all stored procedures. Invoker rights routines is a powerful tool, but should only be used where appropriate.