Privileges required for executing routines

A number of steps are involved in implementing, defining, and invoking user-defined functions and stored procedures, which are also called routines.

Begin general-use programming interface information.
The following table summarizes the common tasks and the privileges that are required for executing routines.

Table 1. Common tasks and required privileges for routines
Role Tasks Required privileges
Implementer If SQL is in the routine: codes, precompiles, compiles, and link-edits the program to use as the routine. Binds the program as the routine package.

If no SQL is in the routine: codes, compiles, and link-edits the program.

 If binding a package, BINDADD system privilege and CREATE IN on the collection.
Definer Issues a CREATE FUNCTION statement to define a user-defined function or CREATE PROCEDURE statement to define a stored procedure. CREATEIN privilege on the schema. EXECUTE authority on the routine package when invoked.
Invoker Invokes a routine from an SQL application. EXECUTE authority on the routine.

The routine implementer typically codes the routine in a program and precompiles the program. If the program contains SQL statements, the implementer binds the DBRM. In general, the authorization ID that binds the DBRM into a package is the package owner. The implementer is the routine package owner. As package owner, the implementer implicitly has EXECUTE authority on the package and has the authority to grant EXECUTE privileges to other users to execute the code within the package.

The implementer grants EXECUTE authority on the routine package to the definer. EXECUTE authority is necessary only if the package contains SQL. For user-defined functions, the definer requires EXECUTE authority on the package. For stored procedures, the EXECUTE privilege on the package is checked for the definer and other IDs.

The routine definer owns the routine. The definer issues a CREATE FUNCTION statement to define a user-defined function or a CREATE PROCEDURE statement to define a stored procedure. The definer of a routine is determined as follows:

  • If the SQL statement is embedded in an application program, the definer is the authorization ID of the owner of the plan or package.
  • If the SQL statement is dynamically prepared, the definer is the SQL authorization ID that is contained in the CURRENT SQLID special register. If the SQL statement is executed in a trusted context that is specified with the ROLE AS OBJECT OWNER clause, the definer is the role in effect.

The definer grants EXECUTE authority on the routine to the invoker, that is, any user that needs to invoke the routine.

The routine invoker invokes the routine from an SQL statement in the invoking plan or package. The invoker for a routine is determined as follows:

  • For a static statement, the invoker is the plan or package owner.
  • For a dynamic statement, the invoker depends on DYNAMICRULES behavior.
    End general-use programming interface information.