DB2 10.5 for Linux, UNIX, and Windows

GRANT (routine privileges) statement

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

Read syntax diagramSkip visual syntax diagram
>>-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

Notes

Examples