GRANT (function or procedure privileges)
This form of the GRANT statement grants privileges on a function or procedure.
Invocation
This statement can be embedded in an application program or issued interactively. It is an executable statement that can be dynamically prepared.
Authorization
The privileges held by the authorization ID of the statement must include at least one of the following:
- For each function or procedure identified in the statement:
- Every privilege specified in the statement
- The system authority of *OBJMGT on the function or procedure
- The system authority *EXECUTE on the library (or directory if this is a Java™ routine) containing the function or procedure
- Database administrator authority
- Security administrator authority
If WITH GRANT OPTION is specified, the privileges held by the authorization ID of the statement must include at least one of the following:
- Ownership of the function or procedure
- Database administrator authority
- Security administrator authority
Syntax
Description
- ALL or ALL PRIVILEGES
- Grants
one or more privileges. The privileges granted are all those grantable
privileges that the authorization ID of the statement has on the specified
functions or procedures. Note that granting ALL PRIVILEGES on a function
or procedure is not the same as granting the system authority of *ALL.
If you do not use ALL, you must use one or more of the keywords listed below. Each keyword grants the privilege described.
- ALTER
- Grants the privilege to use the ALTER FUNCTION, ALTER PROCEDURE, or COMMENT statement.
- EXECUTE
- Grants the privilege to execute the function or 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 user-defined function, but not
a function that was implicitly generated with the creation of a distinct
type. The function can be identified by its name, function signature,
or specific name.
- FUNCTION function-name
- Identifies the function by its name. The function-name must identify exactly one function. The function may 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.
- 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. Parameters that have defaults must be included in this
signature.
If function-name () is specified, the function identified must have zero parameters.
- function-name
- Identifies the name of the function.
- (parameter-type, ...)
- Identifies the parameters of the function.
If an unqualified distinct type name is specified, the database manager 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). 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.
Specifying the FOR DATA clause or CCSID clause is optional. Omission of either clause indicates that the database manager ignores the attribute when determining whether the data types match. If either clause is specified, 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 XML or a distinct type based on a LOB or XML.
- 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 or SPECIFIC PROCEDURE
- Identifies the procedure on which the privilege is granted. The procedure-name must
identify a procedure that exists at the current server.
- PROCEDURE procedure-name
- Identifies the procedure by its name. The procedure-name must identify exactly one procedure. The procedure may have any number of parameters defined for it. If there is more than one procedure of the specified name in the specified or implicit schema, an error is returned.
- PROCEDURE procedure-name (parameter-type, ...)
- Identifies the procedure by its procedure signature, which uniquely
identifies the procedure. The procedure-name (parameter-type,
...) must identify a procedure with the specified procedure signature.
The specified parameters must match the data types in the corresponding
position that were specified when the procedure was created. The number
of data types, and the logical concatenation of the data types is
used to identify the specific procedure instance which is to be granted.
Synonyms for data types are considered a match. Parameters
that have defaults must be included in this signature.
If procedure-name () is specified, the procedure identified must have zero parameters.
- procedure-name
- Identifies the name of the procedure.
- (parameter-type, ...)
- Identifies the parameters of the procedure.
If an unqualified distinct type or array type name is specified, the database manager searches the SQL path to resolve the schema name for the distinct type or array 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 procedure defined with a data type of DEC(7,2). 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 PROCEDURE 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 PROCEDURE statement.
Specifying the FOR DATA clause or CCSID clause is optional. Omission of either clause indicates that the database manager ignores the attribute when determining whether the data types match. If either clause is specified, it must match the value that was implicitly or explicitly specified in the CREATE PROCEDURE statement.
- AS LOCATOR
- Specifies that the procedure is defined to receive a locator for this parameter. If AS LOCATOR is specified, the data type must be a LOB or XML or a distinct type based on a LOB or XML.
- SPECIFIC PROCEDURE specific-name
- Identifies the procedure by its specific name. The specific-name must identify a specific procedure that exists at the current server.
- TO
- Indicates to whom the privileges are granted.
- USER
- Specifies that the authorization-name identifies a user profile. If USER is specified, authorization-name must be a user profile.
- GROUP
- Specifies that the authorization-name identifies a group profile. If GROUP is specified, authorization-name must be a group profile.
- authorization-name,…
- Lists one or more authorization IDs.
- PUBLIC
- Grants the privileges to a set of users (authorization IDs). For more information, see Authorization, privileges and object ownership.
- WITH GRANT OPTION
- Allows the
specified authorization-names to grant privileges on the functions
or procedures specified in the ON clause to other users.
If WITH GRANT OPTION is omitted, the specified authorization-names cannot grant privileges on the functions or procedures specified in the ON clause to another user unless they have received that authority from some other source (for example, from a grant of the system authority *OBJMGT).
Notes
Corresponding System Authorities: Privileges granted to either an SQL or external function or procedure are granted to its associated program (*PGM) or service program (*SRVPGM) object. Privileges granted to a Java external function or procedure are granted to the associated class file or jar file. If the associated program, service program, class file, or jar file is not found when the grant is executed, an error is returned.
GRANT and REVOKE statements assign and remove system authorities for SQL objects. The following table describes the system authorities that correspond to the SQL privileges:
SQL Privilege | Corresponding System Authorities when Granting to or Revoking from a Function or Procedure |
---|---|
ALL (Grant or revoke of ALL grants or revokes only those privileges the authorization ID of the statement has) | *OBJALTER
*OBJOPR *EXECUTE *OBJMGT (Revoke only) |
ALTER | *OBJALTER |
EXECUTE | *EXECUTE
*OBJOPR |
WITH GRANT OPTION | *OBJMGT |
SQL Privilege | Corresponding Data Authorities when Granting to or Revoking from a Java Function or Procedure | Corresponding Object Authorities when Granting to or Revoking from a Java Function or Procedure |
---|---|---|
ALL (Grant or revoke of ALL grants or revokes only those privileges the authorization ID of the statement has) | *RWX | *OBJEXIST
*OBJALTER *OBJMGT (Revoke only) |
ALTER | *R | *OBJALTER |
EXECUTE | *RX | *EXECUTE
|
WITH GRANT OPTION | *RWX | *OBJMGT |
Corresponding System Authorities When Checking Privileges to a Function or Procedure: The following table describes the system authorities that correspond to the SQL privileges when checking privileges to a function or procedure. The left column lists the SQL privilege. The right column lists the equivalent system authorities.
SQL Privilege | Corresponding System Authorities |
---|---|
ALTER | *OBJALTER |
EXECUTE | *EXECUTE and *OBJOPR
|
SQL Privilege | Corresponding Data Authorities when Checking Privileges to a Java Function or Procedure | Corresponding Object Authorities when Checking Privileges to a Java Function or Procedure |
---|---|---|
ALTER | *R | *OBJALTER |
EXECUTE | *RX | *EXECUTE
|
Built-in functions: Privileges cannot be granted on built-in functions.
Syntax alternatives: The following keywords are synonyms supported for compatibility to prior releases. These keywords are non-standard and should not be used:
- The keyword RUN can be used as a synonym for EXECUTE.
Example
Grant the EXECUTE privilege on procedure PROCA to PUBLIC.
GRANT EXECUTE
ON PROCEDURE PROCA
TO PUBLIC