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-------------------------------------------> .-,-----------------------------------------------------. V | >--+-+-FUNCTION--+---function-name--+------------------------------------+-+-+-+-+--> | | | '-(-+----------------------------+-)-' | | | | | | | .-,----------------------. | | | | | | | | V | | | | | | | | '---+--------------------+-+-' | | | | | | '-| parameter-type |-' | | | | | | | | | | | | | | | | | '-*---------------------------------------------------------' | | | | .-,-------------. | | | | V | | | | '-SPECIFIC FUNCTION----specific-name-+------------------------------------' | | .-,--------------. | | V | | '-PROCEDURE--+---procedure-name-+-+-------------------------------------------' '-*------------------' .-,----------------------. V | >--TO----+-authorization-name-+-+--+-------------------+------->< +-ROLE--role-name----+ '-WITH GRANT OPTION-' '-PUBLIC-------------'
parameter-type:
>>-| data-type |--+----------------+--------------------------->< | (1) | '-AS LOCATOR-----'
- AS LOCATOR can be specified only for a LOB data type or a distinct type based on a LOB data type.
data-type:
>>-+-| built-in-type |--+-------------------------------------->< '-distinct-type-name-'
built-in-type:
>>-+-+-SMALLINT----+-------------------------------------------------------------------------------------------------+->< | +-+-INTEGER-+-+ | | | '-INT-----' | | | '-BIGINT------' | | .-(5,0)--------------------. | +-+-DECIMAL-+--+--------------------------+-----------------------------------------------------------------------+ | +-DEC-----+ '-(integer-+-----------+-)-' | | '-NUMERIC-' '-, integer-' | | .-(53)------. | +-+-FLOAT--+-----------+--+---------------------------------------------------------------------------------------+ | | '-(integer)-' | | | +-REAL------------------+ | | | .-PRECISION-. | | | '-DOUBLE--+-----------+-' | | .-(34)-. | +-DECFLOAT--+------+----------------------------------------------------------------------------------------------+ | '-(16)-' | | .-(1)-------. | +-+-+-+-CHARACTER-+--+-----------+----------+----+--------------------+--+----------------------+---------------+-+ | | | '-CHAR------' '-(integer)-' | '-CCSID--+-ASCII---+-' '-FOR--+-SBCS--+--DATA-' | | | | '-+-+-CHARACTER-+--VARYING-+--(integer)-' +-EBCDIC--+ +-MIXED-+ | | | | | '-CHAR------' | '-UNICODE-' '-BIT---' | | | | '-VARCHAR----------------' | | | | .-(1M)-------------. | | | '-+-+-CHARACTER-+--LARGE OBJECT-+--+------------------+----+--------------------+--+----------------------+---' | | | '-CHAR------' | '-(integer-+---+-)-' '-CCSID--+-ASCII---+-' '-FOR--+-SBCS--+--DATA-' | | '-CLOB------------------------' +-K-+ +-EBCDIC--+ '-MIXED-' | | +-M-+ '-UNICODE-' | | '-G-' | | .-(1)-------. | +-+-GRAPHIC--+-----------+-------+--+--------------------+--------------------------------------------------------+ | | '-(integer)-' | '-CCSID--+-ASCII---+-' | | +-VARGRAPHIC--(--length--)-----+ +-EBCDIC--+ | | | .-(1M)-------------. | '-UNICODE-' | | '-DBCLOB--+------------------+-' | | '-(integer-+---+-)-' | | +-K-+ | | +-M-+ | | '-G-' | | .-(1)-------. | +-+-BINARY--+-----------+-------------------------+---------------------------------------------------------------+ | | '-(integer)-' | | | +-+-BINARY VARYING-+-(integer)------------------+ | | | '-VARBINARY------' | | | | .-(1M)-------------. | | | '-+-BINARY LARGE OBJECT-+--+------------------+-' | | '-BLOB----------------' '-(integer-+---+-)-' | | +-K-+ | | +-M-+ | | '-G-' | +-+-DATE------------------------------------------------+---------------------------------------------------------+ | +-TIME------------------------------------------------+ | | | .-(--6--)-------. .-WITHOUT TIME ZONE-. | | | '-TIMESTAMP--+---------------+--+-------------------+-' | | '-(--integer--)-' '-WITH TIME ZONE----' | +-ROWID-----------------------------------------------------------------------------------------------------------+ '-XML-------------------------------------------------------------------------------------------------------------'
Description
- 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 TYPE statement. The function can be
identified by name, function signature, or specific name.
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. The function-name must identify
exactly one function. The function can have any number of parameters defined for it. If there is
more than one function of the specified name in the specified or implicit schema, an error is
returned.
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.
- FUNCTION function-name (parameter-type,...)
- Identifies the function by its function signature, which uniquely
identifies the function. The function-name (parameter-type,
...) must identify a function with the specified function
signature. The specified parameters must match the data types in the
corresponding position that were specified when the function was created.
The number of data types, and the logical concatenation of the data
types is used to identify the specific function instance on which
the privilege is to be granted. Synonyms for data types are considered
a match.
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.
If function-name () is specified, the function identified must have zero parameters.
- function-name
- Identifies 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 parameters of the function.
If an unqualified distinct type name is specified, DB2® searches the SQL path to resolve the schema name for the distinct type.
For data types that have a length, precision, or scale attribute, use one of the following:
- Empty parentheses indicate that the database manager ignores the attribute when determining whether the data types match. For example, DEC() will be considered a match for a parameter of a function defined with a data type of DEC(7,2). Similarly, DECFLOAT() will be considered a match for DECFLOAT(16) or DECFLOAT(34). However, FLOAT cannot be specified with empty parenthesis because its parameter value indicates a specific data type (REAL or DOUBLE).
- If a specific value for a length, precision, or scale attribute is specified, the value must exactly match the value that was specified (implicitly or explicitly) in the CREATE FUNCTION statement. If the data type is FLOAT, the precision does not have to exactly match the value that was specified because matching is based on the data type (REAL or DOUBLE).
- If length, precision, or scale is not explicitly specified, and empty parentheses are not specified, the default attributes of the data type are implied. 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 subtype DATA clause or the 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.
- AS LOCATOR
- Specifies that the function is defined to receive a locator for this parameter. If AS LOCATOR is specified, the data type must be a LOB or a distinct type based on a LOB.
- SPECIFIC FUNCTION specific-name
- Identifies the function by its specific name. The specific-name must identify a specific function that exists at the current server.
- 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 for a description of the TO clause.
- WITH GRANT OPTION
- Refer to GRANT for a description of the WITH GRANT OPTION clause.
Examples
GRANT EXECUTE ON FUNCTION CALC_SALARY TO JONES; GRANT EXECUTE ON PROCEDURE VACATION_ACCR TO PUBLIC; GRANT EXECUTE ON SPECIFIC FUNCTION DEPT85_TOT TO ADMIN_A
WITH GRANT OPTION; GRANT EXECUTE ON FUNCTION NEW_DEPT_HIRES (INTEGER, CHAR(10))
TO HR;You can also code the CHAR(10) data type as CHAR().
GRANT EXECUTE ON FUNCTION FIND_EMPDEPT TO ROLE ROLE1;