GRANT statement (function or procedure privileges)

This form of the GRANT statement grants privileges on user-defined functions, cast functions that are generated for distinct types, array types, and stored procedures.

Syntax for GRANT (function or procedure privileges)

function-designator:

parameter-type:

data-type:

Notes:

¹ AS LOCATOR can be specified only for a LOB data type or a distinct type based on a LOB data type.
² For the syntax diagram for this fragment, see Syntax for built-in-type.
³ array-type-name is supported only for compiled SQL scalar functions.

Description for GRANT (function or procedure privileges)

EXECUTE

Grants the privilege to run the identified user-defined function, cast function that was generated for a distinct type, or stored procedure.

function-designator

Identifies the function on which the privilege is granted. The function must exist at the current server, and it must be a function that was defined with the CREATE FUNCTION statement or a cast function that was generated by a CREATE TYPE statement. The function can be identified by name, function signature, or specific name. The function-designator identifies the SQL function by its name, a function signature that uniquely identifies the function, or its specific name. For more information, see Function designators.

An asterisk (*) can be specified for an unqualified function name. The function can be identified as a qualified or unqualified function-name. For example, * indicates that the privilege is granted on all the functions in the default schema, including those that do not currently exist. schema-name.* indicates that the privilege is granted on all the functions in the specified schema, including those that do not currently exist. SYSADM authority is required if * or schema-name.* is specified. Specifying an asterisk does not affect any EXECUTE privileges that are already granted on a function.

If the function was defined with a table parameter (the LIKE TABLE name AS LOCATOR clause was specified in the CREATE FUNCTION statement to indicate that one of the input parameters is a transition table), the function signature cannot be used to uniquely identify the function. Instead, use one of the other syntax variations to identify the function with its function name, if unique, or its specific name.

PROCEDURE procedure-name

Identifies a stored procedure that is defined at the current server. The name, including the implicit or explicit schema name, must identify a stored procedure that exists at the current server.

An asterisk (*) can be specified for an unqualified procedure name. The procedure can be identified as a qualified or unqualified procedure-name. For example, * indicates that the privilege is granted on all the procedures in the default schema, including those that do not currently exist. schema-name.* indicates that the privilege is granted on all the procedures in the specified schema, including those that do not currently exist. SYSADM authority is required if * or schema-name.* is specified. Specifying an asterisk does not affect any EXECUTE privileges that are already granted on a procedure.

TO

Refer to GRANT statement for a description of the TO clause.

WITH GRANT OPTION

Refer to GRANT statement for a description of the WITH GRANT OPTION clause.

Examples for GRANT (function or procedure privileges)

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;
```
Grant the EXECUTE privilege on procedure VACATION_ACCR to all users at the current server.
```
   GRANT EXECUTE ON PROCEDURE VACATION_ACCR TO PUBLIC;
```
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 that is named DEPT_TOTALS.
```
   GRANT EXECUTE ON SPECIFIC FUNCTION DEPT85_TOT TO ADMIN_A
         WITH GRANT OPTION;
```
Grant the EXECUTE privilege on function NEW_DEPT_HIRES to HR (Human Resources). The function has two input parameters with data types of INTEGER and CHAR(10), respectively. Assume that the schema has more than one function that is named NEW_DEPT_HIRES.
```
   GRANT EXECUTE ON FUNCTION NEW_DEPT_HIRES (INTEGER, CHAR(10))
         TO HR;
```
You can also code the CHAR(10) data type as CHAR().
Grant the EXECUTE privilege on function FIND_EMPDEPT to role ROLE1:
```
GRANT EXECUTE ON FUNCTION FIND_EMPDEPT TO ROLE ROLE1;
```