GRANT (system privileges)

This form of the GRANT statement grants system privileges.

Syntax


          .-,-------------------------------------------------------------.                  
          V                                                               |  .-ON SYSTEM-.   
>>-GRANT----+-ACCESSCTRL------------------------------------------------+-+--+-----------+-->
            +-ARCHIVE---------------------------------------------------+                    
            +-BINDADD---------------------------------------------------+                    
            +-BINDAGENT-------------------------------------------------+                    
            +-BSDS------------------------------------------------------+                    
            +-CREATEALIAS-----------------------------------------------+                    
            +-CREATEDBA-------------------------------------------------+                    
            +-CREATEDBC-------------------------------------------------+                    
            +-CREATESG--------------------------------------------------+                    
            +-CREATETMTAB-----------------------------------------------+                    
            +-CREATE_SECURE_OBJECT--------------------------------------+                    
            +-DATAACCESS------------------------------------------------+                    
            |       (1)  .-WITH ACCESSCTRL----.  .-WITH DATAACCESS----. |                    
            +-DBADM------+--------------------+--+--------------------+-+                    
            |            '-WITHOUT ACCESSCTRL-'  '-WITHOUT DATAACCESS-' |                    
            +-DEBUGSESSION----------------------------------------------+                    
            +-DISPLAY---------------------------------------------------+                    
            +-EXPLAIN---------------------------------------------------+                    
            +-MONITOR1--------------------------------------------------+                    
            +-MONITOR2--------------------------------------------------+                    
            +-RECOVER---------------------------------------------------+                    
            +-SQLADM----------------------------------------------------+                    
            +-STOPALL---------------------------------------------------+                    
            +-STOSPACE--------------------------------------------------+                    
            +-SYSADM----------------------------------------------------+                    
            +-SYSCTRL---------------------------------------------------+                    
            +-SYSOPR----------------------------------------------------+                    
            '-TRACE-----------------------------------------------------'                    

       .-,----------------------.                              
       V                        |                              
>--TO----+-authorization-name-+-+--+-----------------------+---><
         +-ROLE--role-name----+    |                   (2) |   
         '-PUBLIC-------------'    '-WITH GRANT OPTION-----'

Notes:

The ACCESSCTRL and DATAACCESS clauses can be specified in any order.
The WITH GRANT OPTION can be specified but is ignored for DBADM, DATAACCESS, and ACCESSCTRL.

Description

ACCESSCTRL

Grants the ACCESSCTRL authority. ACCESSCTRL allows the user to grant all authorities and privileges, except system DBADM, DATAACCESS, ACCESSCTRL, and privileges on security related objects.

A warning is issued if the WITH GRANT OPTION is specified when granting this authority.

ACCESSCTRL cannot be granted to PUBLIC.

ARCHIVE

Grants the privilege to use the ARCHIVE LOG and SET LOG commands.

BINDADD

Grants the privilege to create plans and packages by using the BIND subcommand with the ADD option.

BINDAGENT

Grants the privilege to issue the BIND, FREE PACKAGE, or REBIND subcommands for plans and packages and the DROP PACKAGE statement on behalf of the grantor. The privilege also allows the holder to copy and replace plans and packages on behalf of the grantor.

A warning is issued if WITH GRANT OPTION is specified when granting this privilege.

BSDS

Grants the privilege to issue the RECOVER BSDS command.

CREATEALIAS

Grants the privilege to use the CREATE ALIAS statement.

CREATEDBA

Grants the privilege to issue the CREATE DATABASE statement and acquire DBADM authority over those databases.

CREATEDBC

Grants the privilege to issue the CREATE DATABASE statement and acquire DBCTRL authority over those databases.

CREATESG

Grants the privilege to create new storage groups.

CREATETMTAB

Grants the privilege to use the CREATE GLOBAL TEMPORARY TABLE statement.

CREATE_SECURE_OBJECT

Grants the privilege to create a secure object. End of change

DATAACCESS

Grants the DATAACCESS authority. DATAACCESS allows the user to access data in all user tables, views, and materialized query tables in a DB2® subsystem and allows the user to execute plans, packages, functions, and procedures.

A warning is issued if the WITH GRANT OPTION is specified when granting this authority.

DATAACCESS cannot be granted to PUBLIC.

DBADM

Grants the DBADM authority. DBADM allows the user to manage all objects in the DB2 subsystem, except security objects.

A warning is issued if the WITH GRANT OPTION is specified when granting this authority.

DBADM cannot be granted to PUBLIC.

WITH ACCESSCTRL: Specifies that the ACCESSCTRL authority is granted along with the system DBADM authority. ACCESSCTRL allows system DBADM to grant all authorities and privileges, except system DBADM, DATAACCESS, ACCESSCTRL authorities and privileges on security related objects. ACCESSCTRL can be used to REVOKE privileges using the BY clause.
WITH ACCESSCTRL is the default.
WITHOUT ACCESSCTRL: Specifies that system DBADM authority is not granted the ACCESSCTRL authority.
WITH DATAACCESS: Specifies that the DATAACCESS authority is granted along with the system DBADM authority. DATAACCESS allows the system DBADM to access data in all user tables, views, and materialized query tables in a DB2 subsystem and allows the user to execute plans, packages, functions, and procedures.
WITH DATAACCESS is the default.
WITHOUT DATAACCESS: Specifies that system DBADM authority is not granted the DATAACCESS authority.

DISPLAY

Grants the privilege to use most of the DISPLAY commands except DISPLAY PROFILE and DISPLAY RLIMIT. No authorization is required for the DISPLAY UTILITY command. End of change

DEBUGSESSION

Grants the privilege to attach a debug client to the current application process connection, which enables client application debugging of native SQL or Java procedures that are executed within the session.

EXPLAIN

Grants the privilege to issue the following without requiring the privileges needed to execute the statement:

EXPLAIN statement with the options:
- PLAN
- ALL
PREPARE statement
DESCRIBE TABLE statement
Explain dynamic SQL statements that execute under the special register CURRENT EXPLAIN MODE, when CURRENT EXPLAIN MODE = EXPLAIN
BIND options: EXPLAIN(ONLY) and SQLERROR(CHECK)
EXPLAIN(ONLY) allows to explain the statements.

SQLERROR(CHECK) performs all syntax and semantic checks on the SQL statements that are being bound.

MONITOR1

Grants the privilege to obtain IFC data classified as serviceability data, statistics, accounting, and other performance data that does not contain potentially secure data.

MONITOR2

Grants the privilege to obtain IFC data classified as containing potentially sensitive data such as SQL statement text and audit data. Users with MONITOR2 privileges have MONITOR1 privileges.

RECOVER

Grants the privilege to issue the RECOVER INDOUBT command.

SQLADM

Grants the authority to perform the following actions without requiring any additional privileges:

DESCRIBE TABLE statement
EXPLAIN statement with the following options:
- PLAN
- ALL
- STMTCACHE ALL
- STMTID
- STMTTOKEN
- MONITORED STMTS
PREPARE statement
Explain dynamic SQL statements that execute under the special register CURRENT EXPLAIN MODE, when CURRENT EXPLAIN MODE = EXPLAIN
BIND options: EXPLAIN(ONLY) and SQLERROR(CHECK)
EXPLAIN(ONLY) allows to explain the statements.

SQLERROR(CHECK) performs all syntax and semantic checks on the SQL statements that are being bound.
START command
STOP command
DISPLAY PROFILE command
Execute the RUNSTATS utility and the MODIFY STATISTICS utility in any database.
MONITOR2 privilege to obtain IFC data classified as containing potentially sensitive data, such as SQL statement text and audit data, as well as IFC data classified as serviceability data, statistics, accounting, and other performance data.

STOPALL

Grants the privilege to issue the STOP DB2 command.

STOSPACE

Grants the privilege to use the STOSPACE utility.

SYSADM

Grants all DB2 privileges except for a few reserved for installation SYSADM authority. The privileges the user possesses are all grantable, including the SYSADM authority itself. The privileges the user lacks restrict what the user can do with the directory and the catalog. Using WITH GRANT OPTION when granting SYSADM is redundant but valid. For more on SYSADM and installation SYSADM authority, see DB2 Administration Guide.

SYSCTRL

Grants the system control authority, which allows the user to have most of the privileges of a system administrator but excludes the privileges to read or change user data. Using WITH GRANT OPTION when granting SYSCTRL is redundant but valid. For more information on SYSCTRL authority, see DB2 Administration Guide.

SYSOPR

Grants the privilege to have system operator authority.

TRACE

Grants the privilege to issue the MODIFY TRACE, START TRACE, and STOP TRACE commands.

ON SYSTEM

Identifies that the system privilege is granted for the entire DB2 subsystem. End of change

TO

Refer to GRANT for a description of the TO clause.

WITH GRANT OPTION

If you grant the SYSADM or SYSCTRL system privilege, WITH GRANT OPTION is valid but unnecessary. It is unnecessary because whoever is granted SYSADM or SYSCTRL has that authority and all the privileges it implies, with the GRANT option.

Examples

Example 1: Grant DISPLAY privileges to user LUTZ.

   GRANT DISPLAY
     TO LUTZ;

Example 2: Grant BSDS and RECOVER privileges to users PARKER and SETRIGHT, with the WITH GRANT OPTION.

   GRANT BSDS,RECOVER
     TO PARKER,SETRIGHT
     WITH GRANT OPTION;

Example 3: Grant TRACE privileges to all local users.

   GRANT TRACE
     TO PUBLIC;

Example 4: Grant ARCHIVE privileges to role ROLE1:

   GRANT ARCHIVE TO ROLE ROLE1;

Example 5: SECADM Linda grants the privilege to Steve to create a secure object:

   GRANT CREATE_SECURE_OBJECT 
		TO STEVE;

Example 6: Grant system DBADM with ACCESSCTRL and with DATAACCESS to role, ADMINROLE and authid, SALLY. Since GRANT system DBADM also grants ACCESSCTRL and DATAACCESS by default, WITH ACCESSCTRL and WITH DATAACCESS clauses need not be specified explicitly.

   GRANT DBADM ON SYSTEM 
		TO ROLE ADMINROLE;
	GRANT DBADM, ACCESSCTRL, DATAACCESS 
		ON SYSTEM 
		TO SALLY;

Example 7: Grant system DBADM without ACCESSCTRL and without DATAACCESS to John. The WITHOUT ACCESSCTRL and WITHOUT DATAACCESS clauses need to be specified explicitly.

   GRANT DBADM WITHOUT ACCESSCTRL 
		WITHOUT DATAACCESS 
		ON SYSTEM 
		TO JOHN;