GRANT (schema privileges)

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

Invocation

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

Authorization

The privileges held by the authorization ID of the statement must include at least one of the following:

For each schema identified in the statement:
- Every privilege specified in the statement
- The system authority of *OBJMGT on the schema
Database administrator authority
Security administrator authority

Syntax

Description

ALL or ALL PRIVILEGES

Grants one or more privileges. The privileges granted are all those grantable privileges that the authorization ID of the statement has on the specified schemas. Note that granting ALL PRIVILEGES on a schema is not the same as granting the system authority of *ALL.

If you do not use ALL, you must use one or more of the keywords listed below. Each keyword grants the privilege described.

CREATEIN

Grants the privilege to create objects in the schema. Other authorities or privileges required to create the object are still required.

USAGE

Grants the privilege to use the schema. USAGE privilege is required to reference any objects that exist in the schema.

ON SCHEMA schema-name

Identifies the schemas on which the privilege is granted.

TO

Indicates to whom the privileges are granted.

USER: Specifies that the authorization-name identifies a user profile. If USER is specified, authorization-name must be a user profile.
GROUP: Specifies that the authorization-name identifies a group profile. If GROUP is specified, authorization-name must be a group profile.
authorization-name,…: Lists one or more authorization IDs.
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 privileges on the schemas specified in the ON clause to other users.

Notes

Corresponding System Authorities: GRANT and REVOKE statements assign and remove system authorities for SQL objects. The following table describes the system authorities that correspond to the SQL privileges when granting to a schema. The left column lists the SQL privilege. The right column lists the equivalent system authorities that are granted or revoked.

Table 1. Privileges Granted to or Revoked from Schemas
SQL Privilege	Corresponding System Authorities when Granting to or Revoking from a Schema
ALL (Grant or revoke of ALL grants or revokes only those privileges the authorization ID of the statement has)	OBJMGT OBJOPR READ EXECUTE *ADD
CREATEIN	OBJOPR READ EXECUTE ADD^¹
USAGE	OBJOPR READ *EXECUTE
WITH GRANT OPTION	*OBJMGT

Corresponding System Authorities When Checking Privileges to a Schema: The following table describes the system authorities that correspond to the SQL privileges when checking privileges to a schema. The left column lists the SQL privilege. The right column lists the equivalent system authorities.

Table 2. Corresponding System Authorities When Checking Privileges to a Schema
SQL Privilege	Corresponding System Authorities
CREATEIN	OBJOPR, READ, EXECUTE, and ADD^²
USAGE	*EXECUTE^²

GRANT rules: The GRANT statement will grant only those privileges that the authorization ID of the statement is allowed to grant. If no privileges were granted, an error is returned.

Example

Example 1: Grant the CREATEIN privilege on schema T_SCORES to user JONES.

GRANT CREATEIN
  ON SCHEMA T_SCORES
  TO JONES;

¹ Only *ADD is revoked when CREATEIN is revoked.

² *OBJOPR and *READ are only checked for certain statements (for example CREATEs).