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 diagramGRANT EXECUTE ONfunction-designatorFUNCTIONschema.*method-designatorMETHOD * FORtype-nameschema.*procedure-designatorPROCEDUREschema.*TO ,USERGROUPROLEauthorization-namePUBLIC WITH GRANT OPTION
function-designator
Read syntax diagramSkip visual syntax diagramFUNCTIONfunction-name(,data-type)SPECIFIC FUNCTIONspecific-name
method-designator
Read syntax diagramSkip visual syntax diagramMETHODmethod-name(,data-type)FORtype-nameSPECIFIC METHODspecific-name
procedure-designator
Read syntax diagramSkip visual syntax diagramPROCEDUREprocedure-name(,data-type)SPECIFIC PROCEDUREspecific-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