GRANT (system privileges)

This form of the GRANT statement grants system privileges.

Syntax for GRANT (system privileges)

Read syntax diagramSkip visual syntax diagram GRANT ,ACCESSCTRLARCHIVEBINDADDBINDAGENTBSDSCREATEALIASCREATEDBACREATEDBCCREATESGCREATETMTABCREATE_SECURE_OBJECTDATAACCESSDBADM1WITH ACCESSCTRLWITHOUT ACCESSCTRLWITH DATAACCESSWITHOUT DATAACCESSDEBUGSESSIONDISPLAYEXPLAINMONITOR1MONITOR2RECOVERSQLADMSTOPALLSTOSPACESYSADMSYSCTRLSYSOPRTRACE ON SYSTEM TO ,authorization-nameROLErole-namePUBLIC WITH GRANT OPTION2
Notes:
  • 1 The ACCESSCTRL and DATAACCESS clauses can be specified in any order.
  • 2 The WITH GRANT OPTION can be specified but is ignored for DBADM, DATAACCESS, and ACCESSCTRL. The WITH GRANT OPTION is also ignored if BINDAGENT grant is issued by SECADM authority.

Description for GRANT (system privileges)

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 commands 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.

Start of changeWhen BINDAGENT is granted using SECADM authority, it grants the privilege to issue the BIND or REBIND commands for plans and packages by specifying any owner.End of change

Start of changeThis SECADM granted BINDAGENT privilege will not allow the following:
  • Rebind the package or plan without specifying OWNER keyword
  • FREE PACKAGE or FREE PLAN
  • COPY PACKAGE
  • DROP PACKAGE
End of change

Start of changeThe BINDAGENT privilege cannot be granted to PUBLIC using SECADM authority.End of change

Start of changeIf WITH GRANT OPTION is specified when granting this privilege using SECADM authority, the option is ignored.End of change

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.
DATAACCESS
Start of changeGrants the DATAACCESS authority. DATAACCESS allows the user to access data in all user tables, views, materialized query tables, and global variables in a Db2 subsystem, and allows the user to execute plans, packages, functions, and procedures, and use sequences.

Start of changeDATAACCESS authority implicitly includes the SELECT privilege on all catalog tables and implicitly includes the INSERT, DELETE, and UPDATE privileges on updatable catalog tables, except for the SYSIBM.SYSAUDITPOLICIES catalog table.End of change

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

DATAACCESS cannot be granted to PUBLIC.

End of change
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
Start of changeGrants the privilege to issue the DISPLAY commands, with the following exceptions:
  • DISPLAY DYNQUERYCAPTURE
  • DISPLAY ML
  • DISPLAY PROFILE
  • 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 of changeSTART DYNQUERYCAPTURE commandEnd of change
  • Start of changeSTART ML commandEnd of change
  • Start of changeSTART PROFILE commandEnd of change
  • Start of changeSTART TRACE commandEnd of change
  • Start of changeSTOP DYNQUERYCAPTURE commandEnd of change
  • Start of changeSTOP ML commandEnd of change
  • Start of changeSTOP PROFILE commandEnd of change
  • Start of changeSTOP TRACE commandEnd of change
  • Start of changeDISPLAY DYNQUERYCAPTURE commandEnd of change
  • Start of changeDISPLAY ML commandEnd of change
  • 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 Managing administrative authorities.
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 SYSCTRL.
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.
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 for GRANT (system privileges)

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;