GRANT (schema privileges) statement

This form of the GRANT statement grants privileges on a schema.

Invocation

This statement can be embedded in an application program or issued through the use of 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

The privileges held by the authorization ID of the statement must include at least one of the following authorities:
  • The WITH GRANT OPTION for each identified privilege on schema-name
  • ACCESSCTRL or SECADM authority
Note: No user can grant privileges on schema names starting with SYSIBM, SYSCAT, SYSFUN, or SYSSTAT (SQLSTATE 42501).

Syntax

Read syntax diagramSkip visual syntax diagram GRANT ALLPRIVILEGES,ALTERINCREATEINDROPIN ON SCHEMAschema-nameCURRENT SCHEMA TO ,USERGROUPROLEauthorization-namePUBLIC WITH GRANT OPTION

Description

ALL or ALL PRIVILEGES
Grants all schema privileges on the schema named in the ON clause.

If ALL is not specified, one or more of the keywords in the list of privileges must be specified.

ALTERIN
Grants the privilege to alter or comment on all objects in the schema. The owner of an explicitly created schema automatically receives ALTERIN privilege.
CREATEIN
Grants the privilege to create objects in the schema. Other authorities or privileges required to create the object (such as CREATETAB) are still required. The owner of an explicitly created schema automatically receives CREATEIN privilege. An implicitly created schema has CREATEIN privilege automatically granted to PUBLIC.
DROPIN
Grants the privilege to drop all objects in the schema. The owner of an explicitly created schema automatically receives DROPIN privilege.
ON
SCHEMA schema-name
Specifies the name of the schema on which the authorities are to be granted. Authorities cannot be granted on any schema beginning with the SYS prefix (SQLSTATE 42501).
CURRENT SCHEMA
Specifies that the authorities will be granted on the schema described by the DB2®® special register CURRENT SCHEMA. Authorities cannot be granted on any schema beginning with the SYS prefix (SQLSTATE 42501).
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 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 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 the privileges to others.

Rules

  • For each authorization-name specified, if neither USER, GROUP, nor 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.
  • In general, the GRANT statement will process the granting of privileges that the authorization ID of the statement is allowed to grant, returning a warning (SQLSTATE 01007) if one or more privileges was not granted. If no privileges were granted, an error is returned (SQLSTATE 42501). (If the package used for processing the statement was precompiled with LANGLEVEL set to SQL92E for MIA, a warning is returned (SQLSTATE 01007), unless the grantor has no privileges on the object of the grant operation.)

Notes

  • Grant on SYSPUBLIC: You can grant privileges on the reserved schema SYSPUBLIC. Granting CREATEIN privilege allows you to create a public alias, and granting DROPIN privilege allows you to drop a public alias. Granting SELECTIN allows you to select from tables defined in the schema.
  • SCHEMAADM, schema ACCESSCTRL, schema DATAACCESS, or schema LOAD authorities cannot be granted to the special group PUBLIC. Therefore, granting schema ACCESSCTRL, SCHEMAADM, schema DATAACCESS, or schema LOAD 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
  • Privileges granted to a group: An authority 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 schema authorities (SCHEMAADM, ACCESSCTRL, DATAACCESS, LOAD)

Examples

  • Example 1:  Grant user JSINGLETON to the ability to create objects in schema CORPDATA. 
       GRANT CREATEIN ON SCHEMA CORPDATA TO JSINGLETON
  • Example 2:  Grant user IHAKES the ability to create and drop objects in schema CORPDATA. 
       GRANT CREATEIN, DROPIN ON SCHEMA CORPDATA TO IHAKES