GRANT (global variable privileges) statement

Invocation

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

Authorization

Syntax

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

       .-,---------------------------------.                          
       V                                   |                          
>--TO----+-+-------+--authorization-name-+-+--+-------------------+-><
         | +-USER--+                     |    '-WITH GRANT OPTION-'   
         | +-GROUP-+                     |                            
         | '-ROLE--'                     |                            
         '-PUBLIC------------------------'                            

Description

ALL PRIVILEGES

Grants all privileges on the specified global variable.

READ

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

WRITE

Grants 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 granted. The variable-name, including an implicit or explicit qualifier, must identify a global variable that exists at the current server and is not a module variable (SQLSTATE 42704).

TO

Specifies to whom the privileges are granted.

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

Grants the specified privileges to a set of users (authorization IDs).

WITH GRANT OPTION

Allows the specified authorization-name to grant the privileges to others. If the WITH GRANT OPTION clause is omitted, the specified authorization-name cannot grant the privileges to others unless that authority has been received from some other source.

Rules

• For each authorization-name specified, if none of the keywords USER, GROUP, or ROLE is specified:
  • If the security plug-in in effect for the instance cannot determine the status of the authorization-name, an error is returned (SQLSTATE 56092).
  • If the authorization-name is defined as ROLE in the database and as either GROUP or USER in the operating system, an error is returned (SQLSTATE 56092).
  • If the authorization-name is defined as both USER and GROUP according to the security plug-in in effect, an error is returned (SQLSTATE 56092).
  • If the authorization-name is defined as USER only according to the security plug-in in effect, or if it is undefined, USER is assumed.
  • If the authorization-name is defined as GROUP only according to the security plug-in in effect, GROUP is assumed.
  • If the authorization-name is defined in the database as ROLE only, ROLE is assumed.

Notes

• Privileges granted to a group: A privilege that is granted to a group is not used for authorization checking on:
  • Static DML statements in a package
  • A base table while processing a CREATE VIEW statement
  • A base table while processing a CREATE TABLE statement for a materialized query table
  • Create SQL routine
  • Create trigger

Example

   GRANT READ, WRITE ON VARIABLE MYSCHEMA.MYJOB_PRINTER TO ZUBIRI