Micro Focus Enterprise Test Server 2.3 Update 1 > Data Access > Database Access > SQL Option for DB2 > Reference > SQL Reference > SQL Statements > GRANT (Function or Procedure Privileges)

GRANT (Function or Procedure Privileges)

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

Syntax

GRANT EXECUTE ON
{   FUNCTION {function-name [(parameter-type[,...])] | * } |
    SPECIFIC FUNCTION specific-name[,...] |
    PROCEDURE {procedure-name[,...] | 
    *
}
    TO {authorization-name[,...] | PUBLIC} [WITH GRANT OPTION]

parameter-type:

{data-type [AS LOCATOR]}

data-type:

{built-in-data-type | distinct-type-name}

built-in-data-type:

SMALLINT |
{INTEGER | INT} |
{DECIMAL | DEC | NUMERIC} [integer[,...]] |
{FLOAT [integer] | REAL [PRECISION] | DOUBLE} |
{ {CHARACTER | CHAR} [(integer)] | {CHARACTER | CHAR} VARYING (integer) | VARCHAR (integer) } 
    [FOR {SBCS | MIXED | BIT} DATA] [CCSID {EBCDIC | ASCII}] |
{ {CHARACTER | CHAR} LARGE OBJECT | CLOB} [(integer [K|M|G])] } 
    [FOR {SBCS | MIXED} DATA] [CCSID {EBCDIC | ASCII}] } |
{BINARY LARGE OBJECT | BLOB} (integer [K|M|G]) |
{GRAPHIC [(integer)] | VARGRAPHIC (integer) | DBCLOB (integer [K|M|G])} [CCSID {EBCDIC | ASCII}] |
{DATE | TIME | TIMESTAMP} |
ROWID

EXECUTE

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

FUNCTION or SPECIFIC FUNCTION

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 DISTINCT TYPE statement.

If the function was defined with a table parameter (the LIKE TABLE 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 identify the function. Instead, identify the function with its function name, if unique, or with its specific name.

FUNCTION function-name

Identifies the function by its name. You can identify a function by its name only if there is exactly one function with function name in the schema. If you do not explicitly qualify the function name with a schema name, the function name is implicitly qualified with a schema name according to the following rules:

If the statement is embedded in a program, the schema name is the authorization ID in the QUALIFIER option when the plan or package was created or last rebound. If QUALIFIER was not used, the schema name is the owner of the plan or package.
If the statement is dynamically prepared, the schema name is the SQL authorization ID in the CURRENT SQLID special register.

An * can be specified for a qualified on unqualified function-name. An * (or schema-name.*) indicates that the privilege is granted on all the functions in the schema including those that do not currently exist. Specifying an * does not affect any EXECUTE privileges that are already granted on a function.

FUNCTION `function-name` (`parameter-type`,...)

Identifies the function by its function signature, which uniquely identifies the function.

function-name

Specifies the name of the function. If you do not explicitly qualify the function name with a schema name, the function name is implicitly qualified with a schema name as described in the preceding description for FUNCTION function-name.

(parameter-type,...)

Identifies the number of input parameters of the function and their data types.

The data type of each parameter must match the data type that was specified in the CREATE FUNCTION statement for the parameter in the corresponding position. The number of data types and the logical concatenation of the data types is used to uniquely identify the function.

For data types that have a length, precision, or scale attribute, you can specify a value or use a set of empty parentheses:

Empty parentheses indicate that DB2 ignores the attribute when determining whether the data types match.
FLOAT cannot be specified with empty parentheses because its parameter value indicates different data types (REAL or DOUBLE).
If you use a specific value for a length, precision, or scale attribute, the value must exactly match the value that was specified (implicitly or explicitly) in the CREATE FUNCTION statement.
The specific value for FLOAT(n) does not have exactly match the defined value of the source function because 1<=n<= 21 indicates REAL and 22<=n<=53 indicates DOUBLE. Matching is based on whether the data type is REAL or DOUBLE.
If length, precision, or scale is not explicitly specified, and empty parentheses are not specified, the default length of the data type is implied. For example:
- CHAR implies CHAR(1)
- GRAPHIC implies GRAPHIC(1)
- DECIMAL implies DECIMAL(5,0)
- FLOAT implies DOUBLE (length of 8)
The implicit length must exactly match the value that was specified (implicitly or explicitly) in the CREATE FUNCTION statement.

For data types with a subtype or encoding scheme attribute, specifying the FOR DATA clause or CCSID clause is optional. Omission of either clause indicates that DB2 ignores the attribute when determining whether the data types match. If you specify either clause, it must match the value that was implicitly or explicitly specified in the CREATE FUNCTION statement.

SPECIFIC FUNCTION specific-name

Identifies the function by its specific name.

PROCEDURE procedure-name

Identifies a stored procedure that is defined at the current server. If you do not explicitly qualify the procedure name with a schema name, the procedure name is implicitly qualified with a schema name according to the following rules:

If the statement is embedded in a program, the schema name is the authorization ID in the QUALIFIER option when the plan or package was created or last rebound. If QUALIFIER was not used, the schema name is the owner of the plan or package.
If the statement is dynamically prepared, the schema name is the SQL authorization ID in the CURRENT SQLID special register.

An * can be specified for a qualified or unqualified procedure-name. An * (or schema-name.*) indicates that the privilege is granted on all the stored procedures in the schema including those that do not currently exist. Specifying an * does not affect any EXECUTE privileges that are already granted on a stored procedure.

Example:

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().