This
form of the GRANT statement grants privileges on a routine (function,
method, or procedure) that is not defined in
a module.
Invocation
This statement can be embedded
in an application program or issued through the use of dynamic SQL
statements. It is an executable statement that can be dynamically
prepared only if DYNAMICRULES run behavior is in effect for the package
(SQLSTATE 42509).
Authorization
The privileges held by the
authorization ID of the statement must include at least one of the
following authorities:
- The WITH GRANT OPTION for EXECUTE on the routine
- ACCESSCTRL or SECADM authority
To grant all routine EXECUTE privileges in the schema
or type, the privileges held by the authorization ID of the statement
must include at least one of the following authorities:
- The WITH GRANT OPTION for EXECUTE on all existing and future routines
(of the specified type) in the specified schema
- ACCESSCTRL or SECADM authority
SECADM
authority is required to grant EXECUTE privilege on audit routines
and the SET_MAINT_MODE_RECORD_NO_TEMPORALHISTORY procedure. EXECUTE
privilege WITH GRANT OPTION cannot be granted for these routines (SQLSTATE
42501). EXECUTE privilege cannot be granted to PUBLIC on the SET_MAINT_MODE_RECORD_NO_TEMPORALHISTORY
procedure (SQLSTATE 42501).
Syntax
>>-GRANT EXECUTE ON--+-| function-designator |----------+------->
+-FUNCTION--+---------+--*---------+
| '-schema.-' |
+-| method-designator |------------+
+-METHOD * FOR--+-type-name------+-+
| '-+---------+--*-' |
| '-schema.-' |
+-| procedure-designator |---------+
'-PROCEDURE--+---------+--*--------'
'-schema.-'
.-,---------------------------------.
V |
>--TO----+-+-------+--authorization-name-+-+-------------------->
| +-USER--+ |
| +-GROUP-+ |
| '-ROLE--' |
'-PUBLIC------------------------'
>--+-------------------+---------------------------------------><
'-WITH GRANT OPTION-'
function-designator
|--+-FUNCTION--function-name--+-------------------------+-+-----|
| '-(--+---------------+--)-' |
| | .-,---------. | |
| | V | | |
| '---data-type-+-' |
'-SPECIFIC FUNCTION--specific-name---------------------'
method-designator
|--+-METHOD--method-name--+-------------------------+--FOR--type-name-+--|
| '-(--+---------------+--)-' |
| | .-,---------. | |
| | V | | |
| '---data-type-+-' |
'-SPECIFIC METHOD--specific-name-----------------------------------'
procedure-designator
|--+-PROCEDURE--procedure-name--+-------------------------+-+---|
| '-(--+---------------+--)-' |
| | .-,---------. | |
| | V | | |
| '---data-type-+-' |
'-SPECIFIC PROCEDURE--specific-name----------------------'
Description
- EXECUTE
- Grants the privilege to run the identified user-defined function,
method, or procedure.
- function-designator
- Uniquely identifies the function on
which the privilege is granted. For more information, see Function, method, and procedure designators.
- FUNCTION schema.*
- Identifies all the functions in the schema, including any functions
that may be created in the future. In dynamic SQL statements, if
a schema is not specified, the schema in the CURRENT SCHEMA special
register will be used. In static SQL statements, if a schema is not
specified, the schema in the QUALIFIER precompile/bind option will
be used.
- method-designator
- Uniquely identifies the method on which the privilege is granted. For
more information, see Function, method, and procedure designators.
- METHOD *
- Identifies all the methods for the type type-name,
including any methods that may be created in the future.
- FOR type-name
- Names the type in which the specified method is found. The name
must identify a type already described in the catalog (SQLSTATE 42704).
In dynamic SQL statements, the value of the CURRENT SCHEMA special
register is used as a qualifier for an unqualified type name. In static
SQL statements, the QUALIFIER precompile/bind option implicitly specifies
the qualifier for unqualified type names. An asterisk (*) can be used
in place of type-name to identify all types
in the schema, including any types that may be created in the future.
- procedure-designator
- Uniquely identifies the procedure on
which the privilege is granted. For more information, see Function, method, and procedure designators.
- PROCEDURE schema.*
- Identifies all the procedures in the schema, including any procedures
that may be created in the future. In dynamic SQL statements, if
a schema is not specified, the schema in the CURRENT SCHEMA special
register will be used. In static SQL statements, if a schema is not
specified, the schema in the QUALIFIER precompile/bind option will
be used.
- TO
- Specifies to whom the EXECUTE privilege is granted.
- USER
- Specifies that the authorization-name identifies
a user.
- GROUP
- Specifies that the authorization-name identifies
a group name.
- ROLE
- Specifies that the authorization-name identifies
a role name. The role name must exist at the current server (SQLSTATE
42704).
- authorization-name,...
- Lists the authorization IDs of one or more users, groups, or
roles.
- PUBLIC
- Grants the EXECUTE privilege to a set of users (authorization
IDs).
- WITH GRANT OPTION
- Allows the specified authorization-names
to GRANT the EXECUTE privilege to others.
If the WITH GRANT OPTION
is omitted, the specified
authorization-name can
only grant the EXECUTE privilege to others if they:
- have SYSADM or DBADM authority or
- received the ability to grant the EXECUTE privilege from some
other source.
Rules
- It is not possible to grant the EXECUTE privilege on a function
or method defined with schema 'SYSIBM' or 'SYSFUN' (SQLSTATE 42832).
- For each authorization-name specified, if neither
USER, GROUP, nor ROLE is specified, then:
- If the security plug-in in effect for the instance cannot determine
the status of the authorization-name, an error
is returned (SQLSTATE 56092).
- If the authorization-name is defined as ROLE
in the database, and as either GROUP or USER according to the
security plug-in in effect, an error is returned (SQLSTATE 56092).
- If the authorization-name is defined according
to the security plug-in in effect as both USER and GROUP, an
error is returned (SQLSTATE 56092).
- If the authorization-name is defined according
to the security plug-in in effect as USER only, or if it is undefined,
USER is assumed.
- If the authorization-name is defined according
to the security plug-in in effect as GROUP only, GROUP is assumed.
- If the authorization-name is defined in
the database as ROLE only, ROLE is assumed.
- In general, the GRANT statement will process the granting of privileges
that the authorization ID of the statement is allowed to grant, returning
a warning (SQLSTATE 01007) if one or more privileges was not granted.
If the package used for processing the statement was precompiled with
LANGLEVEL set to SQL92E or MIA, and no privileges were granted, a
warning is returned (SQLSTATE 01007). If the grantor has no privileges
on the object of the grant operation, an error is returned (SQLSTATE
42501).
Notes
- Privileges for a routine defined in a module are granted at the
module level using the GRANT (module privileges) statement. The EXECUTE
privilege on the module allows access to all objects in the module.
- Privileges granted to a group: A privilege that
is granted to a group is not used for authorization checking on:
- Static DML statements in a package
- A base table while processing a CREATE VIEW statement
- A base table while processing a CREATE TABLE statement for a materialized
query table
- Create SQL routine
- Create trigger
Examples
- Example 1: Grant the EXECUTE privilege on function CALC_SALARY
to user JONES. Assume that there is only one function in the schema
with function name CALC_SALARY.
GRANT EXECUTE ON FUNCTION CALC_SALARY TO JONES
- Example 2: Grant the EXECUTE privilege on procedure VACATION_ACCR
to all users at the current server.
GRANT EXECUTE ON PROCEDURE VACATION_ACCR TO PUBLIC
- Example 3: Grant the EXECUTE privilege on function DEPT_TOTALS
to the administrative assistant and give the assistant the ability
to grant the EXECUTE privilege on this function to others. The function
has the specific name DEPT85_TOT. Assume that the schema has more
than one function named DEPT_TOTALS.
GRANT EXECUTE ON SPECIFIC FUNCTION DEPT85_TOT
TO ADMIN_A WITH GRANT OPTION
- Example 4: Grant the EXECUTE privilege on function NEW_DEPT_HIRES
to HR (Human Resources). The function has two input parameters of
type INTEGER and CHAR(10), respectively. Assume that the schema has
more than one function named NEW_DEPT_HIRES.
GRANT EXECUTE ON FUNCTION NEW_DEPT_HIRES (INTEGER, CHAR(10)) TO HR
- Example 5: Grant the EXECUTE privilege on method SET_SALARY
of type EMPLOYEE to user JONES.
GRANT EXECUTE ON METHOD SET_SALARY FOR EMPLOYEE TO JONES