GRANT (variable privileges)

This form of the GRANT statement grants privileges on a 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 global 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

If WITH GRANT OPTION is specified, the privileges held by the authorization ID of the statement must include at least one of the following:

  • Ownership of the global variable
  • Start of changeDatabase administrator authorityEnd of change
  • Start of changeSecurity administrator authorityEnd of change

Syntax

Read syntax diagramSkip visual syntax diagramGRANTALLPRIVILEGES,ALTERREADWRITEON VARIABLE ,variable-name TO,USERGROUPauthorization-namePUBLIC WITH GRANT OPTION

Description

ALL or ALL PRIVILEGES
Grants one or more privileges. The privileges granted are all those grantable privileges that the authorization ID of the statement has on the specified global variables. Note that granting ALL PRIVILEGES on a global variable is not the same as granting the system authority of *ALL.

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

ALTER
Grants the privilege to use the COMMENT and LABEL statements on a global variables.
READ
Grants the privilege to read the value of a global variable.
WRITE
Grants the privilege to assign a value to a global variable.
ON VARIABLE variable-name
Identifies the global variables on which the privilege is granted. The variable-name must identify a global variable that exists at the current server.
TO
Indicates to whom the privileges are granted.
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.
PUBLIC
Grants the privileges to a set of users (authorization IDs). For more information, see Authorization, privileges and object ownership.
WITH GRANT OPTION
Allows the specified authorization-names to grant privileges on the global variables specified in the ON clause to other users.

Notes

Corresponding System Authorities: GRANT and REVOKE statements assign and remove system authorities for SQL objects. The following table describes the system authorities that correspond to the SQL privileges:

Table 1. Privileges Granted to or Revoked from Global Variables
SQL Privilege Corresponding System Authorities when Granting to or Revoking from a Global Variable
ALL (Grant or revoke of ALL grants or revokes only those privileges the authorization ID of the statement has)
*OBJALTER
*OBJOPR
*EXECUTE
*UPD
*READ
*OBJMGT (Revoke only)
ALTER *OBJALTER
READ
*OBJOPR
*EXECUTE
*READ
WRITE
*OBJOPR
*EXECUTE
*UPD
WITH GRANT OPTION *OBJMGT

Corresponding System Authorities When Checking Privileges to a Global Variable: The following table describes the system authorities that correspond to the SQL privileges when checking privileges to a global variable. The left column lists the SQL privilege. The right column lists the equivalent system authorities.

Table 2. Corresponding System Authorities When Checking Privileges to a Global Variable
SQL Privilege Corresponding System Authorities
ALTER *OBJALTER
READ *OBJOPR and *EXECUTE and *READ
WRITE
*OBJOPR and *EXECUTE and
*UPD

Example

Grant the READ and WRITE privileges on global variable MYSCHEMA.MYJOB_PRINTER to user ZUBIRI.

GRANT READ, WRITE
  ON VARIABLE MYSCHEMA.MYJOB_PRINTEER
  TO ZUBIRI