DB2 10.5 for Linux, UNIX, and Windows

REVOKE (routine privileges) statement

This form of the REVOKE statement revokes privileges on a routine (function, method, or procedure) that is not defined in a module.

Invocation

This statement can be embedded in an application program or issued through the use of dynamic SQL statements. It is an executable statement that can be dynamically prepared only if DYNAMICRULES run behavior is in effect for the package (SQLSTATE 42509).

Authorization

The privileges held by the authorization ID of the statement must include ACCESSCTRL or SECADM authority.

Syntax

Read syntax diagramSkip visual syntax diagram
>>-REVOKE EXECUTE ON--+-| function-designator |----------+------>
                      +-FUNCTION--+---------+--*---------+   
                      |           '-schema.-'            |   
                      +-| method-designator |------------+   
                      +-METHOD * FOR--+-type-name------+-+   
                      |               '-+---------+--*-' |   
                      |                 '-schema.-'      |   
                      +-| procedure-designator |---------+   
                      '-PROCEDURE--+---------+--*--------'   
                                   '-schema.-'               

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

   .-BY ALL-.             
>--+--------+--RESTRICT----------------------------------------><

function-designator

|--+-FUNCTION--function-name--+-------------------------+-+-----|
   |                          '-(--+---------------+--)-' |   
   |                               | .-,---------. |      |   
   |                               | V           | |      |   
   |                               '---data-type-+-'      |   
   '-SPECIFIC FUNCTION--specific-name---------------------'   

method-designator

|--+-METHOD--method-name--+-------------------------+--FOR--type-name-+--|
   |                      '-(--+---------------+--)-'                 |   
   |                           | .-,---------. |                      |   
   |                           | V           | |                      |   
   |                           '---data-type-+-'                      |   
   '-SPECIFIC METHOD--specific-name-----------------------------------'   

procedure-designator

|--+-PROCEDURE--procedure-name--+-------------------------+-+---|
   |                            '-(--+---------------+--)-' |   
   |                                 | .-,---------. |      |   
   |                                 | V           | |      |   
   |                                 '---data-type-+-'      |   
   '-SPECIFIC PROCEDURE--specific-name----------------------'   

Description

EXECUTE
Revokes the privilege to run the identified user-defined function, method, or procedure.
function-designator
Uniquely identifies the function from which the privilege is revoked. For more information, see Function, method, and procedure designators.
FUNCTION schema.*
Identifies the explicit grant for all the existing and future functions in the schema. Revoking the schema.* privilege does not revoke any privileges that were granted on a specific function. In dynamic SQL statements, if a schema is not specified, the schema in the CURRENT SCHEMA special register will be used. In static SQL statements, if a schema is not specified, the schema in the QUALIFIER precompile/bind option will be used.
method-designator
Uniquely identifies the method from which the privilege is revoked. For more information, see Function, method, and procedure designators.
METHOD *
Identifies the explicit grant for all the existing and future methods for the type type-name. Revoking the * privilege does not revoke any privileges that were granted on a specific method.
FOR type-name
Names the type in which the specified method is found. The name must identify a type already described in the catalog (SQLSTATE 42704). In dynamic SQL statements, the value of the CURRENT SCHEMA special register is used as a qualifier for an unqualified type name. In static SQL statements, the QUALIFIER precompile/bind option implicitly specifies the qualifier for unqualified type names. An asterisk (*) can be used in place of type-name to identify the explicit grant on all existing and future methods for all existing and future types in the schema. Revoking the privilege using an asterisk for method and type-name does not revoke any privileges that were granted on a specific method or on all methods for a specific type.
procedure-designator
Uniquely identifies the procedure from which the privilege is revoked. For more information, see Function, method, and procedure designators.
PROCEDURE schema.*
Identifies the explicit grant for all the existing and future procedures in the schema. Revoking the schema.* privilege does not revoke any privileges that were granted on a specific procedure. In dynamic SQL statements, if a schema is not specified, the schema in the CURRENT SCHEMA special register will be used. In static SQL statements, if a schema is not specified, the schema in the QUALIFIER precompile/bind option will be used.
FROM
Specifies from whom the EXECUTE privilege is revoked.
USER
Specifies that the authorization-name identifies a user.
GROUP
Specifies that the authorization-name identifies a group name.
ROLE
Specifies that the authorization-name identifies a role name.
authorization-name,...
Lists the authorization IDs of one or more users, groups, or roles.

The list of authorization IDs cannot include the authorization ID of the user issuing the statement (SQLSTATE 42502).

PUBLIC
Revokes the EXECUTE privilege from PUBLIC.
BY ALL
Revokes the EXECUTE privilege from all named users who were explicitly granted the privilege, regardless of who granted it. This is the default behavior.
RESTRICT
Specifies that the EXECUTE privilege cannot be revoked if both of the following conditions are true (SQLSTATE 42893):
  • The specified routine is used in a view, trigger, constraint, index extension, SQL function, SQL method, transform group, or is referenced as the SOURCE of a sourced function.
  • The loss of the EXECUTE privilege would cause the owner of the view, trigger, constraint, index extension, SQL function, SQL method, transform group, or sourced function to no longer be able to execute the specified routine.

Rules

Notes

Examples