GRANT (schema privileges)
This form of the GRANT statement grants privileges on a schema.
This statement can be embedded in an application program or issued interactively. It is an executable statement that can be dynamically prepared.
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
- 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.
- Grants the privilege to create objects in the schema. Other authorities or privileges required to create the object are still required.
- 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.
- Indicates to whom the privileges are granted.
- Specifies that the authorization-name identifies a user profile. If USER is specified, authorization-name must be a user profile.
- Specifies that the authorization-name identifies a group profile. If GROUP is specified, authorization-name must be a group profile.
- Lists one or more authorization IDs.
- 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.
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.
|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)||
|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.
|SQL Privilege||Corresponding System Authorities|
|CREATEIN||*OBJOPR, *READ, *EXECUTE, and *ADD2|
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 1: Grant the CREATEIN privilege on schema T_SCORES to user JONES.
GRANT CREATEIN ON SCHEMA T_SCORES TO JONES;