REVOKE (global variable privileges) statement

This form of the REVOKE statement revokes one or more privileges on a created global variable.

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 database ACCESSCTRL or SECADM authority or ACCESSCTRL authority on the schema containing the global variable.

Syntax

Read syntax diagramSkip visual syntax diagramREVOKE ALLPRIVILEGES,READWRITE ONVARIABLEvariable-nameFROM,USERGROUPROLEauthorization-namePUBLICBY ALLRESTRICT

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 the WRITE privilege on global variable MYSCHEMA.MYJOB_PRINTER from user ZUBIRI.
   REVOKE WRITE ON VARIABLE MYSCHEMA.MYJOB_PRINTER FROM ZUBIRI