SQL Reference

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 through the use of dynamic SQL statements. It is an executable statement that can be dynamically prepared. However, if the bind option DYNAMICRULES BIND applies, the statement cannot be dynamically prepared (SQLSTATE 42509).

Authorization

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

• WITH GRANT OPTION for each identified privilege on schema-name
• SYSADM or DBADM authority

Privileges cannot be granted on schema names SYSIBM, SYSCAT, SYSFUN and SYSSTAT by any user.

Refer to GRANT (Database Authorities), GRANT (Index Privileges), GRANT (Package Privileges), GRANT (Server Privileges), GRANT (Table Space Privileges) and GRANT (Table, View, or Nickname Privileges) for other GRANT statements.

Syntax

            .-,---------------.
            V                 |
>>-GRANT--------+-ALTERIN--+--+--ON SCHEMA--schema-name--------->
                +-CREATEIN-+
                '-DROPIN---'
 
         .-,------------------------------------.
         V                                      |
>----TO----+-+-------+---authorization-name--+--+--------------->
           | +-USER--+                       |
           | '-GROUP-'                       |
           '-PUBLIC--------------------------'
 
>----+-------------------+-------------------------------------><
     '-WITH GRANT OPTION-'
 

Description

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

Identifies the schema on which the privileges are to be granted.

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.

authorization-name,...

Lists the authorization IDs of one or more users or groups.

The list of authorization IDs cannot include the authorization ID of the user issuing the statement (SQLSTATE 42502).

PUBLIC

Grants the privileges to all users.

WITH GRANT OPTION

Allows the specified authorization-names to GRANT the privileges to others.

If the WITH GRANT OPTION is omitted, the specified authorization-names can only grant the privileges to others if they:

• have DBADM authority or
• received the ability to grant privileges from some other source.

Rules

• If neither USER nor GROUP is specified, then
  • If the authorization-name is defined in the operating system only as GROUP, then GROUP is assumed.
  • If the authorization-name is defined in the operating system only as USER or if it is undefined, USER is assumed.
  • If the authorization-name is defined in the operating system as both, or DCE authentication is used, an error (SQLSTATE 56092) is raised.
• 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). ⁹⁸

Examples

Example 1:  Grant USER2 to the ability to create objects in schema CORPDATA.

   GRANT CREATEIN ON SCHEMA CORPDATA TO USER2

Example 2:  Grant user BIGGUY the ability to create and drop objects in schema CORPDATA.

   GRANT CREATEIN, DROPIN ON SCHEMA CORPDATA TO BIGGUY

⁹⁸

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.