REVOKE (function or procedure privileges)

This form of the REVOKE statement revokes privileges on user-defined functions, cast functions that were generated for distinct types, and stored procedures.

Syntax

>>-REVOKE--EXECUTE--ON------------------------------------------>

                   .-,-------------------------------------------------.         
                   V                                                   |         
>--+-+-FUNCTION--+---function-name--+--------------------------------+-+-+-+-+-->
   | |           |                  |   .-,----------------------.   |   | | |   
   | |           |                  |   V                        |   |   | | |   
   | |           |                  '-(---+--------------------+-+-)-'   | | |   
   | |           |                        '-| parameter-type |-'         | | |   
   | |           |                                                       | | |   
   | |           |                                                       | | |   
   | |           '-*-----------------------------------------------------' | |   
   | |                    .-,-------------.                                | |   
   | |                    V               |                                | |   
   | '-SPECIFIC FUNCTION----specific-name-+--------------------------------' |   
   |              .-,--------------.                                         |   
   |              V                |                                         |   
   '-PROCEDURE--+---procedure-name-+-+---------------------------------------'   
                '-*------------------'                                           

         .-,----------------------.   
         V                        |   
>--FROM----+-authorization-name-+-+----------------------------->
           +-ROLE--role-name----+     
           '-PUBLIC-------------'     

>--+------------------------------------+----------------------->
   |       .-,----------------------.   |   
   |       V                        |   |   
   '-BY--+---+-authorization-name-+-+-+-'   
         |   '-ROLE--role-name----'   |     
         '-ALL------------------------'     

                                           .-RESTRICT-.   
>--+------------------------------------+--+----------+--------><
   +-INCLUDING DEPENDENT PRIVILEGES-----+                 
   '-NOT INCLUDING DEPENDENT PRIVILEGES-'

parameter-type:

>>---| data-type |--+----------------+-------------------------><
                    |            (1) |     
                    '-AS LOCATOR-----'

Notes:

AS LOCATOR can be specified only for a LOB data type or a distinct type that is 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--(--integer--)----+           +-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

Revokes 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 from which the privilege is revoked. 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 * can be specified for a qualified or unqualified function-name. An * (or schema-name.*) indicates that the privilege is revoked for all the functions in the schema. You (or the indicated grantors) must have granted the privilege on FUNCTION * to all identified users (including PUBLIC if specified). Privileges granted on specific functions are not affected.

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.

An * can be specified for a qualified or unqualified procedure-name. An * (or schema-name.*) indicates that the privilege is revoked for all the procedures in the schema. You (or the indicated grantors) must have granted the privilege on PROCEDURE * to all identified users (including PUBLIC if specified). Privileges granted on specific procedures are not affected.

FROM

Refer to REVOKE for a description of the FROM clause.

BY

Refer to REVOKE for a description of the BY clause.

INCLUDING DEPENDENT PRIVILEGES or NOT INCLUDING DEPENDENT PRIVILEGES

Specifies whether revoking a privilege or an authority from an authorization ID or a role also results in revoking the grants that were made by that user. The default value is based on the authority that is being revoked and the REVOKE_DEP_PRIVILEGES system parameter:

When ACCESSCTRL, DATAACCESS, or system DBADM authority is revoked, NOT INCLUDING DEPENDENT PRIVILEGES is assumed and the clause must be specified on the REVOKE statement.
When the REVOKE_DEP_PRIVILEGES system parameter is set to NO, NOT INCLUDING DEPENDENT PRIVILEGES is assumed and an error is returned if the statement includes INCLUDING DEPENDENT PRIVILEGES.
Otherwise, INCLUDING DEPENDENT PRIVILEGES is assumed and the clause must be specified on the REVOKE statement.

INCLUDING DEPENDENT PRIVILEGES

Specifies that revoking a privilege or an authority from an authorization ID or a role also results in revoking dependent privileges. This means that any grants that were made by the user will continue to be revoked, until all grants in the chain have been revoked.

INCLUDING DEPENDENT PRIVILEGES cannot be specified if the system parameter REVOKE_DEP_PRIVILEGES is set to NO, which enforces the behavior to not include the dependent privileges.

NOT INCLUDING DEPENDENT PRIVILEGES

Specifies that revoking a privilege or an authority from an authorization ID or a role does not cause the grants that were made by the user to be revoked. However, for the revoked privileges, all implications of the privilege being revoked are applied. For example, if the revoked privileges were required to bind a package successfully, that package would continue to be invalidated as a result of the package owner losing these privileges. An object might be dropped if a privilege is revoked that was used to create the object.

NOT INCLUDING DEPENDENT PRIVILEGES must be specified when ACCESSCTRL, DATAACCESS, or system DBADM authority is revoked.

NOT INCLUDING DEPENDENT PRIVILEGES cannot be specified if the system parameter REVOKE_DEP_PRIVILEGES is set toYES, which enforces the behavior to include dependent privileges in the revoke.

RESTRICT

Prevents the EXECUTE privilege from being revoked on a user-defined function or stored procedure if the revokee owns any of the following objects and does not have the EXECUTE privilege from another source:

A function that is sourced on the function
A view that uses the function
A trigger package that uses the function or stored procedure
A table that uses the function in a check constraint or user-defined default clause
A materialized query table whose fullselect uses the function
An extended index that uses the function

Examples

Example 1: Revoke the EXECUTE privilege on function CALC_SALARY for user JONES. Assume that there is only one function in the schema with function CALC_SALARY.

   REVOKE EXECUTE ON FUNCTION CALC_SALARY FROM JONES;

Example 2: Revoke the EXECUTE privilege on procedure VACATION_ACCR from all users at the current server.

   REVOKE EXECUTE ON PROCEDURE VACATION_ACCR FROM PUBLIC;

Example 3: Revoke the privilege of the administrative assistant to grant EXECUTE privileges on function DEPT_TOTAL to other users. The administrative assistant will still have the EXECUTE privilege on function DEPT_TOTALS.

   REVOKE EXECUTE ON FUNCTION DEPT_TOTALS
         FROM ADMIN_A;

Example 4: Revoke the EXECUTE privilege on function NEW_DEPT_HIRES for 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.

   REVOKE EXECUTE ON FUNCTION NEW_DEPT_HIRES (INTEGER, CHAR(10))
         FROM HR;

You can also code the CHAR(10) data type as CHAR().

Example 5: Revoke the EXECUTE privilege on function FIND_EMPDEPT from role ROLE1:

   REVOKE EXECUTE ON FUNCTION FIND_EMPDEPT
         FROM ROLE ROLE1;