Definer's versus invoker's rights v14

When an SPL program is about to begin executing, a determination is made as to the user to associate with this process. This user is referred to as the current user. The current user’s database object privileges are used to determine whether access to database objects referenced in the program is permitted. The current prevailing search path in effect when the program is invoked is 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, the user who actually invoked the program is irrelevant.
• 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 (but not necessarily the subprogram) is executing. When an invoker’s rights program is invoked, the current user is typically the user that started the session (made the database connection). You can change the current user after the session started using the SET ROLE command. In an invoker’s rights program, who actually owns the program is irrelevant.

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 while the called program executes.
• If a definer’s rights program calls an invoker’s rights program, the owner of the calling program remains the current user whiile both the calling and called programs execute.
• If an invoker’s rights program calls an invoker’s rights program, the current user of the calling program remains the current user while the called program executes.
• If an invoker’s rights program calls a definer’s rights program, the current user switches to the owner of the definer’s rights program while the called program executes.

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