When an SPL program is about to execute, you need to determine what user is to be associated with this process. This user is referred to as the current user. Database object privileges of the current user are used to determine whether 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 the rights of definers or invokers. The AUTHID clause determines that selection. Appearance of the clause AUTHID DEFINER gives the program rights of the definer. This is also the default value if the AUTHID clause is omitted. Use of the clause AUTHID CURRENT_USER gives the program rights of invokers. The following content summarizes the differences between the two rights:

  • If a program has rights of the definer, the owner of the program becomes the current user when program execution begins. Database object privileges of the program owner are used to determine if access to a referenced object is permitted. In a program that has rights of the definer, it is irrelevant as to which user actually invoked the program.
  • If a program has the rights of the invoker, the current user at the time when 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 a program that has rights of the invoker is invoked, the current user is typically the user that started the session (that is, made the database connection). However, the current user can be changed after the session has started by using the SET ROLE statement. In a program that has rights of the invoker, it is irrelevant as to which user actually owns the program.

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

  • If a program that has rights of the definer calls a program that has rights of the definer, 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 program that has rights of the definer calls a program that has rights of the invoker, the owner of the calling program remains the current user during execution of both the calling and called programs.
  • If a program that has rights of the invoker calls a program that has rights of the invoker, the current user of the calling program remains the current user during execution of the called program.
  • If a program that has rights of the invoker calls a program that has rights of the definer, the current user switches to the owner of the program that has rights of the definer during execution of the called program.

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