Definer’s vs. Invokers Rights v13

When an SPL program is about to begin execution, a determination is made as to what user is to be associated with this process. This user is referred to as the current user. The current user’s database object privileges are used to determine whether or not access to database objects referenced in the program will be permitted. The current prevailing search path in effect when the program is invoked will be used to resolve any unqualified object references.

The selection of the current user is influenced by whether the SPL program was created with definer’s right or invoker’s rights. The AUTHID clause determines that selection. Appearance of the clause AUTHID DEFINER gives the program definer’s rights. This is also the default if the AUTHID clause is omitted. Use of the clause AUTHID CURRENT_USER gives the program invoker’s rights. The difference between the two is summarized as follows:

• If a program has definer’s rights, then the owner of the program becomes the current user when program execution begins. The program owner’s database object privileges are used to determine if access to a referenced object is permitted. In a definer’s rights program, it is irrelevant as to which user actually invoked the program.
• If a program has invoker’s rights, then the current user at the time the program is called remains the current user while the program is executing (but not necessarily within called subprograms – see the following bullet points). When an invoker’s rights program is invoked, the current user is typically the user that started the session (i.e., made the database connection) although it is possible to change the current user after the session has started using the SET ROLE command. In an invoker’s rights program, it is irrelevant as to which user actually owns the program.

From the previous definitions, the following observations can be made:

• If a definer’s rights program calls a definer’s rights program, the current user changes from the owner of the calling program to the owner of the called program during execution of the called program.
• If a definer’s rights program calls an invoker’s rights program, the owner of the calling program remains the current user during execution of both the calling and called programs.
• If an invoker’s rights program calls an invoker’s rights program, the current user of the calling program remains the current user during execution of the called program.
• If an invokers’ rights program calls a definer’s rights program, the current user switches to the owner of the definer’s rights program during execution of the called program.

The same principles apply if the called program in turn calls another program in the cases cited above.