What is definer rights ?

posted Apr 27, 2012, 4:31 PM by Sachchida Ojha   [ updated Apr 27, 2012, 4:39 PM ]
EXECUTION AUTHORITY MODELS: Oracle database offers 2 different models for objects permissions in PL/SQL Programs. The default is definer rights. With this model, a stored program executes under the authority of its owner, or definer. The other permission model uses the privileges of the user invoking the program and is referred to as invoker rights.

The Definer rights Model:  With the definer rights model,

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

2. Database roles are ignored completely when compiling stored programs. All privileges needed for the program must be granted directly to the definer (owner) of the program.

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

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

Relationship between Performance and Invoker/Definer Right model?

it definitely affects overall system performance.

  1. With definers rights routine, the SQL is parsed as the definer. There will be one copy of it in the shared pool.
  2. with invokers rights - it is as if everyone was typing the SQL in at the command line, there will be many copies of that SQL in the shared pool possibly.

You should use invokers rights only for utility type routines - like a utility that accepts aSQL statement as a string and outputs the contents to a file. So the SQL would run as the invoker (which is what you want for that utility typically)

Virtually every other stored procedure should be a definers rights routine, authid current_user would be used very very rarely.