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 authority on the schema containing 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 authority on the schema containing all the routines
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

function-designator

method-designator

procedure-designator

Description

EXECUTE

Grants the privilege to run the identified user-defined function, method, or procedure.

function-designatorUniquely 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-designatorUniquely 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-designatorUniquely 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).; 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
```