When discussing routine level authorization it is important to
define some roles related to routines, the determination of the roles,
and the privileges related to these roles:
- Package Owner
- The owner of a particular package that participates in the implementation
of a routine. The package owner is the user who executes the BIND
command to bind a package with a database, unless the OWNER precompile/BIND
option is used to override the package ownership and set it to an
alternate user. Upon execution of the BIND command, the package owner
is granted EXECUTE WITH GRANT privilege on the package. A routine
library or executable can be comprised of multiple packages and therefore
can have multiple package owners associated with it.
- Routine Definer
-
The ID that issues the CREATE statement to register a routine.
The routine definer is generally a DBA, but is also often the routine
package owner. When a routine is invoked, at package load time, the
authorization to run the routine is checked against the definer's
authorization to execute the package or packages associated with
the routine (not against the authorization of the routine invoker).
For a routine to be successfully invoked, the routine definer must
have one of:
- EXECUTE privilege on the package or packages
of the routine and EXECUTE privilege on the routine
- DATAACCESS authority
If the routine definer and the routine package owner are the
same user, then the routine definer will have the required EXECUTE
privileges on the packages. If the definer is not the package owner,
the definer must be explicitly granted EXECUTE privilege on the packages
by any user with ACCESSCTRL or SECADM authority, CONTROL or EXECUTE
WITH GRANT OPTION privilege on the package. (The creator of a package
automatically receives CONTROL and EXECUTE WITH GRANT OPTION on the
package.)
Upon issuing the CREATE statement that registers
the routine, the definer is implicitly granted the EXECUTE WITH GRANT
OPTION privilege on the routine.
The routine definer's role
is to encapsulate under one authorization ID, the privileges of running
the packages associated with a routine and the privilege of granting
EXECUTE privilege on the routine to PUBLIC or to specific users that
need to invoke the routine.
Note: For SQL routines the routine definer
is also implicitly the package owner. Therefore the definer will have
EXECUTE WITH GRANT OPTION on both the routine and on the routine package
upon execution of the CREATE statement for the routine.
- Routine Invoker
- The ID that invokes the routine. To determine
which users will be invokers of a routine, it is necessary to consider
how a routine can be invoked. Routines can be invoked from a command
window or from within an embedded SQL application. In the case of
methods and UDFs the routine reference will be embedded in another
SQL statement. A procedure is invoked by using the CALL statement.
For dynamic SQL in an application, the invoker is the runtime authorization
ID of the immediately higher-level routine or application containing
the routine invocation (however, this ID can also depend on the DYNAMICRULES
option with which the higher-level routine or application was bound).
For static SQL, the invoker is the value of the OWNER precompile/BIND
option of the package that contains the reference to the routine.
To successfully invoke the routine, these users will require EXECUTE
privilege on the routine. This privilege can be granted by any user
with EXECUTE WITH GRANT OPTION privilege on the routine (this includes
the routine definer unless the privilege has been explicitly revoked),
ACCESSCTRL, or SECADM authority, by explicitly issuing a GRANT statement.
As an example, if a package associated with an application containing
dynamic SQL was bound with DYNAMICRULES BIND, then its runtime authorization
ID will be its package owner, not the person invoking the package.
Also, the package owner will be the actual binder or the value of
the OWNER precompile/bind option. In this case, the invoker of the
routine assumes this value rather than the ID of the user who is executing
the application.
Note: - For static SQL within a routine, the package owner's privileges
must be sufficient to execute the SQL statements in the routine body.
These SQL statements might require table access privileges or execute
privileges if there are any nested references to routines.
- For dynamic SQL within a routine, the userid whose privileges
will be validated are governed by the DYNAMICRULES option of the BIND
of the routine body.
- The routine package owner must GRANT EXECUTE on the package to
the routine definer. This can be done before or after the routine
is registered, but it must be done before the routine is invoked otherwise
an error (SQLSTATE 42051) will be returned.
The steps involved in managing the execute privilege on a routine
are detailed in the diagram and text that follows:
Figure 1. Managing the EXECUTE privilege on routines
- Definer performs the appropriate CREATE statement to register
the routine. This registers the routine in DB2® with its intended level of SQL access, establishes
the routine signature, and also points to the routine executable.
The definer, if not also the package owner, needs to communicate
with the package owners and authors of the routine programs to be
clear on where the routine libraries reside so that this can be correctly
specified in the EXTERNAL clause of the CREATE statement. By virtue
of a successful CREATE statement, the definer has EXECUTE WITH GRANT
privilege on the routine, however the definer does not yet have EXECUTE
privilege on the packages of the routine.
- Definer must grant EXECUTE privilege on the routine to any users
who are to be permitted use of the routine. (If the package for this
routine will recursively call this routine, then this step must be
done before the next step.)
- Package owners precompile and bind the routine program, or have
it done on their behalf. Upon a successful precompile and bind, the
package owner is implicitly granted EXECUTE WITH GRANT OPTION privilege
on the respective package. This step follows step one in this list
only to cover the possibility of SQL recursion in the routine. If
such recursion does not exist in any particular case, the precompile/bind
could precede the issuing of the CREATE statement for the routine.
- Each package owner must explicitly grant EXECUTE privilege on
their respective routine package to the definer of the routine. This
step must come at some time after the previous step. If the package
owner is also the routine definer, this step can be skipped.
- Static usage of the routine: the bind owner of the package referencing
the routine must have been given EXECUTE privilege on the routine,
so the previous step must be completed at this point. When the routine
executes, DB2 verifies that
the definer has the EXECUTE privilege on any package that is needed,
so step 3 must be completed for each such package.
- Dynamic usage of the routine: the authorization ID as controlled
by the DYNAMICRULES option for the invoking application must have
EXECUTE privilege on the routine (step 4), and the definer of the
routine must have the EXECUTE privilege on the packages (step 3).