GRANT (database authorities) statement

This form of the GRANT statement grants authorities that apply to the entire database (rather than privileges that apply to specific objects within the database).

Invocation

This statement can be embedded in an application program or issued by using dynamic SQL statements. It is an executable statement that can be dynamically prepared only if DYNAMICRULES run behavior is in effect for the package (SQLSTATE 42509).

Authorization

To grant ACCESSCTRL, CREATE_SECURE_OBJECT, DATAACCESS, DBADM, or SECADM authority, SECADM authority is needed.

Note: In Db2 11.5.7 and later, to grant CREATE_EXTERNAL_ROUTINE authority, SYSADM authority is needed. If the DB2_ALTERNATE_AUTHZ_BEHAVIOUR registry variable is set and contains the value EXTERNAL_ROUTINE_DBAUTH, then SYSADM, SECADM, or ACCESSCTRL authority is needed.
Also, in Db2 11.5.7 and later, to grant CREATE_NOT_FENCED_ROUTINE authority, SYSADM authority is needed. If the DB2_ALTERNATE_AUTHZ_BEHAVIOUR registry variable is set and contains the value NOT_FENCED_ROUTINE_DBAUTH, then SYSADM, SECADM, or ACCESSCTRL authority is needed.

To grant other authorities ACCESSCTRL or SECADM authority is needed.

Syntax

Read syntax diagramSkip visual syntax diagramGRANT,ACCESSCTRLBINDADDCONNECTCREATETABCREATE_EXTERNAL_ROUTINECREATE_NOT_FENCED_ROUTINECREATE_SECURE_OBJECTDATAACCESSDBADM•1WITHOUT DATAACCESSWITH DATAACCESS•WITHOUT ACCESSCTRLWITH ACCESSCTRL•EXPLAINIMPLICIT_SCHEMALOADQUIESCE_CONNECTSECADMSQLADMWLMADMON DATABASETO ,USERGROUPROLEauthorization-namePUBLIC 2
Notes:
  • 1 From Db2 12.1 DATAACCESS and ACCESSCTRL are no longer granted by default when granting DBADM. To grant DATAACCESS and ACCESSCTRL by default when granting DBADM, set the DB2_ALTERNATE_AUTHZ_BEHAVIOUR registry variable to DBADM_ADDTL_AUTHS. For more information, see Default privileges granted on creating a database.
  • 2 For Db2 12.1 and later, CONNECT, CREATETAB, BINDADD, IMPLICIT_SCHEMA on database, CREATEIN on SQLJ and NULLID schemas and USE of USERSPACE1 are no longer granted to PUBLIC by default when creating a database. To grant these authorities by default when creating a database, set the DB2_ALTERNATE_AUTHZ_BEHAVIOUR registry variable to PUBLIC_DBCREATE before creating the database. For more information, see Default privileges granted on creating a database.

Description

ACCESSCTRL
Grants the access control authority. The ACCESSCTRL authority allows the holder to:
  • Grant and revoke the following database authorities: BINDADD, CONNECT, CREATETAB, CREATE_EXTERNAL_ROUTINE, CREATE_NOT_FENCED_ROUTINE, EXPLAIN, IMPLICIT_SCHEMA, LOAD, QUIESCE_CONNECT, SQLADM, WLMADM
  • Grant and revoke all object level privileges.
The ACCESSCTRL authority cannot be granted to PUBLIC (SQLSTATE 42508).
BINDADD
Grants the authority to create packages. The creator of a package automatically has the CONTROL privilege on that package and retains this privilege even if the BINDADD authority is later revoked.
CONNECT
Grants the authority to access the database.
CREATETAB
Grants the authority to create base tables. The creator of a base table automatically has the CONTROL privilege on that table. The creator retains this privilege even if the CREATETAB authority is later revoked.

No explicit authority is needed for view creation. A view can be created at any time if the authorization ID of the statement that is used to create the view has either CONTROL or SELECT privilege on each base table of the view.

CREATE_EXTERNAL_ROUTINE
Grants the authority to register external routines. Care must be taken that routines so registered do not have adverse side effects. (For more information, see the description of the THREADSAFE clause in CREATE PROCEDURE (external) statement).

After an external routine has been registered, it continues to exist, even if CREATE_EXTERNAL_ROUTINE is later revoked.

CREATE_NOT_FENCED_ROUTINE
Grants the authority to register routines that run in the database manager's process. Care must be taken that routines so registered do not have adverse side effects. (For more information, see the description of the FENCED clause on the CREATE PROCEDURE (external) statement).

After a routine is registered as not fenced, it continues to run in this manner, even if CREATE_NOT_FENCED_ROUTINE is later revoked.

CREATE_EXTERNAL_ROUTINE is automatically granted to an authorization-name that is granted CREATE_NOT_FENCED_ROUTINE authority.

CREATE_SECURE_OBJECT
Grants the authority to create secure triggers and secure functions. Grants the authority to alter the secure attribute of such objects as well.
DATAACCESS
Grants the authority to access data. The DATAACCESS authority allows the holder to:
  • Select, insert, update, delete, and load data.
  • Run any package.
  • Run any routine (except audit routines).
The DATAACCESS authority cannot be granted to PUBLIC (SQLSTATE 42508).
DBADM
Grants the database administrator authority. A database administrator holds nearly all privileges on nearly all objects in the database. The only exceptions are those privileges that are part of the access control, data access, and security administrator authorities. DBADM cannot be granted to PUBLIC.
EXPLAIN
Grants the authority to explain statements. The EXPLAIN authority allows the holder to explain, prepare, and describe dynamic and static SQL statements without requiring access to data.
IMPLICIT_SCHEMA
Grants the authority to implicitly create a schema.
LOAD
Grants the authority to load in this database. This authority gives a user the right to use the LOAD utility in this database. DATAACCESS and DBADM also have this authority by default. However, if a user only has LOAD authority (not DATAACCESS), the user is also needs to have table-level privileges. In addition to LOAD privilege, the user needs to have:
  • INSERT privilege on the table for LOAD with mode INSERT, TERMINATE (to terminate a previous LOAD INSERT), or RESTART (to restart a previous LOAD INSERT).
  • INSERT and DELETE privilege on the table for LOAD with mode REPLACE, TERMINATE (to terminate a previous LOAD REPLACE), or RESTART (to restart a previous LOAD REPLACE).
  • INSERT privilege on the exception table, if such a table is used as part of LOAD.
QUIESCE_CONNECT
Grants the authority to access the database while it is quiesced.
SECADM
Grants the security administrator authority. The authority allows the holder to:
  • Create and drop security objects such as audit policies, roles, security labels, security label components, security policies, and trusted contexts.
  • Grant and revoke authorities, exemptions, privileges, roles, and security labels.
  • Grant and revoke the SETSESSIONUSER privilege.
  • Run TRANSFER OWNERSHIP on objects that are owned by others.
The SECADM authority cannot be granted to PUBLIC (SQLSTATE 42508).
SQLADM
Grants the authority to manage SQL statement execution. The SQLADM authority allows the holder to:
  • Create, drop, flush, and set event monitors.
  • Explain, prepare, and describe dynamic and static SQL statements without requiring access to data.
  • Flush optimization profile cache
  • Flush package cache
  • Execute the runstats utility.
  • Create, alter, drop, and set usage lists.
WLMADM
Grants the authority to manage workloads. The WLMADM authority allows the holder to:
  • Create, drop, and alter service classes, thresholds, work action sets, work class sets, or workloads.
TO
Specifies to whom the authorities are granted.
USER
Specifies that the authorization-name identifies a user.
GROUP
Specifies that the authorization-name identifies a group name.
ROLE
Specifies that the authorization-name identifies a role name. The role name must exist 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 authorities to a set of users (authorization IDs).

Rules

  • For each authorization-name specified, if neither USER, GROUP, or ROLE is specified, then:
    • 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 according to the security plug-in in effect, an error is returned (SQLSTATE 56092).
    • If the authorization-name is defined according to the security plug-in in effect as both USER and GROUP, an error is returned (SQLSTATE 56092).
    • If the authorization-name is defined according to the security plug-in in effect as USER only, or if it is undefined, USER is assumed.
    • If the authorization-name is defined according to the security plug-in in effect as GROUP only, GROUP is assumed.
    • If the authorization-name is defined in the database as ROLE only, ROLE is assumed.

Notes

  • ACCESSCTRL, CREATE_SECURE_OBJECT, DATAACCESS, DBADM, or SECADM authorities cannot be granted to the special group PUBLIC. Therefore, granting ACCESSCTRL, CREATE_SECURE_OBJECT, DBADM, DATAACCESS, or SECADM authority to a role role-name fails if role-name is granted to PUBLIC either directly or indirectly (SQLSTATE 42508).
    • Role role-name is granted directly to PUBLIC if the following statement has been issued:
         GRANT ROLE role-name TO PUBLIC
    • Role role-name is granted indirectly to PUBLIC if the following statements have been issued:
         GRANT ROLE role-name TO ROLE role-name2
   GRANT ROLE role-name2 TO PUBLIC
  • Syntax alternatives: The following are supported for compatibility with previous versions of Db2® and with other database products.
    • CREATE_NOT_FENCED can be specified in place of CREATE_NOT_FENCED_ROUTINE.
    • SYSTEM can be specified in place of DATABASE.
  • 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
  • WITH GRANT OPTION is ignored when granting database authorities.

Examples

  • Example 1: Give the users WINKEN, BLINKEN, and NOD the authority to connect to the database. 
       GRANT CONNECT ON DATABASE TO USER WINKEN, USER BLINKEN, USER NOD
  • Example 2: Grant BINDADD authority on the database to a group named D024. Both a group and a user called D024 exist in the system. 
       GRANT BINDADD ON DATABASE TO GROUP D024
    Observe that, the GROUP keyword must be specified; otherwise, an error will occur since both a user and a group named D024 exist. Any member of the D024 group will be allowed to bind packages in the database, but the D024 user will not be allowed (unless this user is also a member of the group D024, had been granted BINDADD authority previously, or BINDADD authority had been granted to another group of which D024 was a member).
  • Example 3: Give user Walid security administrator authority. 
       GRANT SECADM ON DATABASE TO USER Walid
  • Example 4: A user with SECADM authority grants the CREATE_SECURE_OBJECT authority to user Haytham.
       GRANT CREATE_SECURE_OBJECT ON DATABASE TO USER HAYTHAM