REVOKE (global variable privileges) statement

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

                  .-PRIVILEGES-.                                  
>>-REVOKE--+-ALL--+------------+-+--ON--VARIABLE--variable-name-->
           | .-,---------.       |                                
           | V           |       |                                
           '---+-READ--+-+-------'                                
               '-WRITE-'                                          

         .-,---------------------------------.                             
         V                                   |  .-BY ALL-.  .-RESTRICT-.   
>--FROM----+-+-------+--authorization-name-+-+--+--------+--+----------+-><
           | +-USER--+                     |                               
           | +-GROUP-+                     |                               
           | '-ROLE--'                     |                               
           '-PUBLIC------------------------'                               

Description

ALL PRIVILEGES

Revokes all privileges held by an authorization-name for the specified global variable. If ALL is not specified, READ or WRITE must be specified. READ or WRITE must not be specified more than once.

READ

Revokes the privilege to read the value of the specified global variable.

WRITE

Revokes the privilege to assign a value to the specified global variable.

ON VARIABLE variable-name

Identifies the global variable on which one or more privileges are to be revoked. The variable-name must identify a global variable that exists at the current server and is not a module variable (SQLSTATE 42704).

FROM

Specifies from whom the privileges are revoked.

USER

Specifies that the authorization-name identifies a user.

GROUP

Specifies that the authorization-name identifies a group.

ROLE

Specifies that the authorization-name identifies an existing role at the current server (SQLSTATE 42704).

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 specified privileges from PUBLIC.

BY ALL

Revokes each specified privilege from all named users who were explicitly granted those privileges, regardless of who granted them. This is the default behavior.

RESTRICT

Specifies that the statement is to fail if any objects depend on the privileges being revoked. This is the default behavior.

Rules

• For each authorization-name specified, if none of the keywords USER, GROUP, or ROLE is specified, then for all rows for the specified object in the SYSCAT.VARIABLEAUTH catalog view where the grantee is authorization-name:
  • If GRANTEETYPE is 'U', USER is assumed.
  • If GRANTEETYPE is 'G', GROUP is assumed.
  • If GRANTEETYPE is 'R', ROLE is assumed.
  • If GRANTEETYPE does not have the same value, an error is returned (SQLSTATE 56092.
• If any SQL function, SQL method, procedure, view, trigger, or another global variable contains a global variable and depends on the privilege being revoked, the revoke operation will fail (SQLSTATE 42893).

Notes

• If the READ privilege on a global variable is revoked, packages with a dependency to write the value of the global variable (for example, by the SET statement) are not affected, because writing to a global variable is controlled by the WRITE privilege on that global variable.
• If the WRITE privilege on a global variable is revoked, packages with a dependency to read the value of the global variable are not affected, because reading from a global variable is controlled by the READ privilege on that global variable.
• Revoking a privilege does not necessarily impair the ability to perform the action. A user might be able to proceed if the required privilege is held through membership in a different group or role, or by PUBLIC.

Example

   REVOKE WRITE ON VARIABLE MYSCHEMA.MYJOB_PRINTER FROM ZUBIRI