REVOKE (variable privileges)

This form of the REVOKE statement removes the privileges on a created global variable.

Invocation

This statement can be embedded in an application program or issued interactively. It is an executable statement that can be dynamically prepared.

Authorization

The privileges held by the authorization ID of the statement must include at least one of the following:

  • For each variable identified in the statement:
    • Every privilege specified in the statement
    • The system authority of *OBJMGT on the global variable
    • The system authority *EXECUTE on the library containing the global variable
  • Start of changeDatabase administrator authorityEnd of change
  • Start of changeSecurity administrator authorityEnd of change

Syntax

Read syntax diagramSkip visual syntax diagramREVOKEALLPRIVILEGES,ALTERREADWRITEON VARIABLE,variable-nameFROM ,USERGROUPauthorization-namePUBLIC

Description

ALL or ALL PRIVILEGES
Revokes one or more global variable privileges from each authorization-name. The privileges revoked are those privileges on the identified global variables that were granted to the authorization-names. Note that revoking ALL PRIVILEGES on a global variable is not the same as revoking the system authority of *ALL.

If you do not use ALL, you must use one or more of the keywords listed below. Each keyword revokes the privilege described.

ALTER
Revokes the privilege to use COMMENT and LABEL statements on the specified global variables.
READ
Revokes the privilege to read the value of the specified global variables.
WRITE
Revokes the privilege to assign a value to the specified global variables.
ON VARIABLE variable-name
Identifies the global variables from which one or more privileges are to be revoked. Each variable-name must identify a global variable that exists at the current server.
FROM
Identifies from whom the privileges are revoked.
Start of changeUSEREnd of change
Start of changeSpecifies that the authorization-name identifies a user profile. If USER is specified, authorization-name must be a user profile.End of change
Start of changeGROUPEnd of change
Start of changeSpecifies that the authorization-name identifies a group profile. If GROUP is specified, authorization-name must be a group profile.End of change
authorization-name,…
Lists one or more authorization IDs. Do not specify the same authorization-name more than once.
PUBLIC
Revokes a grant of the privilege to PUBLIC. For more information, see Authorization, privileges and object ownership.

Notes

Multiple grants: If you revoke a privilege on a variable, it nullifies any grant of the privilege on that variable, regardless of who granted it.

Revoking WITH GRANT OPTION: The only way to revoke the WITH GRANT OPTION is to revoke ALL.

Privilege warning: Revoking a specific privilege from a user does not necessarily prevent that user from performing an action that requires that privilege. For example, the user may still have the privilege through PUBLIC privileges or database administrator authority.

Corresponding system authorities: When a global variable privilege is revoked, the corresponding system authorities are revoked. For information on the system authorities that correspond to SQL privileges see GRANT (variable privileges).

Example

REVOKE the WRITE privilege on a global variable MYSCHEMA.MYJOB_PRINTER from user ZUBIRI.

REVOKE WRITE
  ON VARIABLE MYSCHEMA.MYJOB_PRINTER
  FROM ZUBIRI