REVOKE (type or JAR file privileges)

This form of the REVOKE statement revokes the privilege to use distinct types or JAR files.

Syntax


                             .-,------------------.           
                             V                    |           
>>-REVOKE--USAGE ON--+-TYPE----distinct-type-name-+-+--FROM----->
                     |      .-,--------.            |         
                     |      V          |            |         
                     '-JAR----jar-name-+------------'         

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

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

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

Description

USAGE

Revokes the privilege to use the distinct type in tables, functions procedures, or the privilege to use the JAR file.

TYPE distinct-type-name

Identifies the distinct type. The name, including the implicit or explicit schema name, must identify a unique distinct type that exists at the current server.

JAR jar-name

Identifies the JAR file. The name, including the implicit or explicit schema name, must identify a unique JAR file that exists at the current server.

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 USAGE privilege from being revoked on a distinct type or JAR file if any of the following conditions exist and the revokee does not have the USAGE privilege from another source:

The revokee owns a function or stored procedure that uses the distinct type or references the JAR file.
The revokee owns a JAR file whose path references the JAR file for which USAGE is being revoked.
The revokee owns a table that has a column that uses the distinct type.
A sequence exists for which the data type of the sequence is the distinct type.

Notes

Alternative syntax and synonyms: To provide compatibility with previous releases of DB2® or other products in the DB2 family, DB2 supports DATA TYPE or DISTINCT TYPE as a synonym for TYPE.

Examples

Example 1: Revoke the USAGE privilege on distinct type SHOESIZE from user JONES.

   REVOKE USAGE ON TYPE SHOESIZE FROM JONES;

Example 2: Revoke the USAGE privilege on distinct type US_DOLLAR from all users at the current server except for those who have been specifically granted USAGE and not through PUBLIC.

   REVOKE USAGE ON TYPE US_DOLLAR FROM PUBLIC;

Example 3: Revoke the USAGE privilege on distinct type CANADIAN_DOLLARS from the administrative assistant (ADMIN_A).

   REVOKE USAGE ON TYPE CANADIAN_DOLLARS
         FROM ADMIN_A;

Example 4: Revoke the USAGE privilege on distinct type MILES from the role ROLE1:

   REVOKE USAGE ON TYPE MILES
         FROM ROLE ROLE1;