Kindness is the beginning of cruelty
Frank Herbert

Syntax

If you check the syntax for the different program objects you can see that the entire program is
defined either using definer or invoker rights. The default is definer rights, so if you don’t supply
the clause, the program will be defined using definer rights. This means you don’t have to go
back into your codebase to supply this clause when you migrate to a newer version of the
Oracle database.

It is not defined at package body level:

This means that an entire package is defined either definer rights or invoker rights. You cannot define a single subprogram to be invoker rights inside a package where the rest of the programs are defined definer rights. If you need some subprograms to be definer rights and some invoker rights then you will need to create two separate packages.

Creating with ‘mock’ objects

When you are creating code for the Invoker Rights model make sure you have access to all objects needed by the code. The easiest way to do this is to create ‘mock’ objects. These are objects, for instance tables, which look (and feel) the same as the objects that will be used when the code is deployed, but they will not actually be used. That is, when the defining schema will not be used to execute the code. When the code is being compiled into the database references to other objects are being checked for availability. There doesn’t need to be any data available at compile time but the objects and their layout need to be available otherwise you will get compile time errors.

Protection against SQL Injection

When you create code that uses dynamic SQL it is good practice to define these programs as invoker rights programs. You don’t want a user of your code to make changes, possibly devastating changes, to your objects. Consider for instance a utility program that can drop an object in the schema. If this was defined as a definer rights program, then this program would drop the object in your schema (if it exists) instead of the object in the schema of the user invoking the program. If your code updates records in a table, you want it to be the records owned by that user, not the ones owned by you (or somebody else).

Rules and restrictions

There are a couple of rules (and restrictions) you should consider when you are using the invoker rights model:

AUTHID DEFINER is the default option. If you don’t specify what model you want to use for your program the database assumes you want to use the Definer Rights model. This also comes in handy when you upgrade to a newer version of the database. You don’t have to go back into your code to change all this. It is however good practice to use the AUTHID clause.

The invoker rights model checks the privileges assigned to the invoker at the time of program execution to resolve any SQL-based references to database objects. With invoker rights, roles are being used as long as the invoker rights program has not called any definer rights programs.

The AUTHID clause cannot be applied to individual programs within a package. Definer rights will always be used to resolve all external references to PL/SQL programs and object type methods at compilation time, even if you define a program to be invoker rights. Hence, the need to create ‘mock-objects’ when creating the code. These ‘mock-objects’ should stay available for as long as the program is available otherwise the program will become invalid.

Oracle PL/SQL Programming 5th Edition – Steven Feuerstein
tahiti.oracle.com – all Oracle Documentation online