To successfully invoke routines, you must
have multiple authorizations and bindings of routines that contain
SQL.
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 or BIND command
parameter 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 or BIND command
parameter 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 or BIND command
parameter. 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 the database 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, the database manager 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).