This form of the REVOKE statement removes the privileges on
a function or procedure.
This statement can be embedded in an application program or issued interactively.
It is an executable statement that can be dynamically prepared.
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
- Administrative authority
.-PRIVILEGES-.
>>-REVOKE--+-ALL--+------------+-+------------------------------>
| .-,-----------. |
| V | |
'---+-ALTER---+-+-----'
'-EXECUTE-'
.-,-----------------------------------------------------------------------.
V |
>--ON----+-+-+-FUNCTION-+--function-name--+------------------------------+-+---+-+-->
| | '-ROUTINE--' '-(--+--------------------+--)-' | |
| | | .-,--------------. | | |
| | | V | | | |
| | '---parameter-type-+-' | |
| '-SPECIFIC--+-FUNCTION-+--specific-name-------------------------' |
| '-ROUTINE--' |
'-+-+-PROCEDURE-+--procedure-name--+------------------------------+-+-'
| '-ROUTINE---' '-(--+--------------------+--)-' |
| | .-,--------------. | |
| | V | | |
| '---parameter-type-+-' |
'-SPECIFIC--+-PROCEDURE-+--specific-name--------------------------'
'-ROUTINE---'
.-,----------------------.
V |
>--FROM----+-authorization-name-+-+----------------------------><
'-PUBLIC-------------'
parameter-type:
|----data-type--+------------+----------------------------------|
'-AS LOCATOR-'
data-type:
|--+-built-in-type------+---------------------------------------|
'-distinct-type-name-'
built-in-type:
|--+-+---SMALLINT---+----------------------------------------------------------------------------------------------+--|
| +-+-INTEGER-+--+ |
| | '-INT-----' | |
| '---BIGINT-----' |
| .-(5,0)----------------------------. |
+-+-+-DECIMAL-+-+--+----------------------------------+---------------------------------------------------------+
| | '-DEC-----' | '-(--+------------------------+--)-' |
| '-NUMERIC-----' '-integer--+-----------+-' |
| '-, integer-' |
| .-(53)----------. |
+-+-FLOAT--+---------------+-+----------------------------------------------------------------------------------+
| | '-(--integer--)-' | |
| +-REAL---------------------+ |
| | .-PRECISION-. | |
| '-DOUBLE--+-----------+----' |
| .-(1)---------------. |
+-+-+-+-CHARACTER-+--+-------------------+--------------+--+----------------+---------------------------------+-+
| | | '-CHAR------' '-(--+---------+--)-' | +-FOR BIT DATA---+ | |
| | | '-integer-' | +-FOR SBCS DATA--+ | |
| | '---+-+-CHARACTER-+--VARYING-+--(--+---------+--)---' +-FOR MIXED DATA-+ | |
| | | '-CHAR------' | '-integer-' '-CCSID--integer-' | |
| | '-VARCHAR----------------' | |
| | .-(1M)---------------------. | |
| '-----+-+-CHARACTER-+--LARGE OBJECT-+------+--------------------------+--+----------------+--+------------+-' |
| | '-CHAR------' | '-(--+---------+--+---+--)-' +-FOR SBCS DATA--+ '-AS LOCATOR-' |
| '-CLOB------------------------' '-integer-' +-K-+ +-FOR MIXED DATA-+ |
| +-M-+ '-CCSID--integer-' |
| '-G-' |
| .-(1)---------------. |
+-+-+---GRAPHIC----+-------------------+---------+--+----------------+-----------+------------------------------+
| | | '-(--+---------+--)-' | '-CCSID--integer-' | |
| | | '-integer-' | | |
| | '---+-VARGRAPHIC------+--(--+---------+--)---' | |
| | '-GRAPHIC VARYING-' '-integer-' | |
| | .-(1M)---------------------. | |
| '---DBCLOB----+--------------------------+--+----------------+--+------------+-' |
| '-(--+---------+--+---+--)-' '-CCSID--integer-' '-AS LOCATOR-' |
| '-integer-' +-K-+ |
| +-M-+ |
| '-G-' |
| .-(1)---------------. |
+-+-+-BINARY--+-------------------+---------+---------------------------------+---------------------------------+
| | | '-(--+---------+--)-' | | |
| | | '-integer-' | | |
| | '-+-BINARY VARYING-+--(--+---------+--)-' | |
| | '-VARBINARY------' '-integer-' | |
| | .-(1M)---------------------. | |
| '---+-BLOB----------------+----+--------------------------+--+------------+-' |
| '-BINARY LARGE OBJECT-' '-(--+---------+--+---+--)-' '-AS LOCATOR-' |
| '-integer-' +-K-+ |
| +-M-+ |
| '-G-' |
+-+---DATE-----------------+------------------------------------------------------------------------------------+
| | .-(--0--)-. | |
| +-TIME--+---------+------+ |
| | .-(--6--)-. | |
| '-TIMESTAMP--+---------+-' |
| .-(200)-------------. |
+---DATALINK--+-------------------+--+----------------+---------------------------------------------------------+
| '-(--+---------+--)-' '-CCSID--integer-' |
| '-integer-' |
'---ROWID-------------------------------------------------------------------------------------------------------'
- ALL or ALL PRIVILEGES
- Revokes one or more function or procedure privileges from each authorization-name. The privileges revoked are those privileges on the
identified functions or procedures that were granted to the authorization-names. Note that revoking ALL PRIVILEGES on a function
or procedure is not the same as revoking the system authority of *ALL.
If you do not use ALL, you must use one or more of the keywords listed below.
Each keyword revokes the privilege described.
- ALTER
- Revokes the privilege to use the COMMENT statement.
- EXECUTE
- Revokes the privilege to execute a function or procedure.
- FUNCTION or SPECIFIC FUNCTION
- Identifies the function from which the privilege is revoked. 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 revoked. Synonyms for data
types are considered a match.
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 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 or SPECIFIC PROCEDURE
- Identifies the procedure from which the privilege is revoked. 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 revoked. Synonyms for data types are considered
a match.
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 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 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 a distinct
type based on a LOB.
- 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.
- FROM
- Identifies from whom the privileges are revoked.
- authorization-name,...
- Lists one or more authorization IDs. Do not specify the same authorization-name more than once.
- PUBLIC
- Revokes the specified privileges from PUBLIC.
Multiple grants: If you revoke a privilege on a function
or procedure, it nullifies any grant of the privilege on that function or
procedure, regardless of who granted it.
Revoking WITH GRANT OPTION: The only way to revoke
the WITH GRANT OPTION is to revoke ALL.
Privilege warning: Revoking a specific privilege
from a user does not necessarily prevent that user from performing an action
that requires that privilege. For example, the user may still have the privilege
through PUBLIC or administrative privileges.
Corresponding system authorities: When a function
or procedure privilege is revoked, the corresponding system authorities are
revoked. For information on the system authorities that correspond to SQL
privileges see GRANT (Function or Procedure Privileges).
Privileges revoked from either an SQL or external function or procedure
are revoked from its associated program (*PGM) or service program (*SRVPGM)
object. Privileges revoked from a Java external function or procedure are
revoked from the associated class file or jar file. If the associated program,
service program, class file, or jar file is not found when the revoke is executed,
an error is returned.
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.
Revoke the EXECUTE privilege on procedure PROCA from PUBLIC.
REVOKE EXECUTE
ON PROCEDURE PROCA
FROM PUBLIC
[ Top of Page | Previous Page | Next Page | Contents |
Index ]