GRANT (schema privileges)

This form of the GRANT statement grants privileges on schemas.

Syntax

          .-,------------.               .-,-----------.     
          V              |               V             |     
>>-GRANT----+-ALTERIN--+-+--ON SCHEMA--+---schema-name-+-+------>
            +-CREATEIN-+               '-*---------------'   
            '-DROPIN---'                                     

       .-,----------------------.                          
       V                        |                          
>--TO----+-authorization-name-+-+--+-------------------+-------><
         +-ROLE--role-name----+    '-WITH GRANT OPTION-'   
         '-PUBLIC-------------'

Description

ALTERIN: Grants the privilege to alter stored procedures and user-defined functions, or specify a comment for distinct types, cast functions that are generated for distinct types, sequences, stored procedures, triggers, and user-defined functions in the designated schemas.
CREATEIN: Grants the privilege to create distinct types, sequences, stored procedures, triggers, and user-defined functions in the designated schemas.
DROPIN: Grants the privilege to drop distinct types, sequences, stored procedures, triggers, and user-defined functions in the designated schemas.
SCHEMA schema-name: Identifies the schemas on which the privilege is granted. The schemas do not need to exist when the privilege is granted.
SCHEMA *: Indicates that the specified privilege is granted on all schemas including those that do not currently exist.
TO: Refer to GRANT for a description of the TO clause.
WITH GRANT OPTION: Refer to GRANT for a description of the WITH GRANT OPTION clause.

Examples

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

   GRANT CREATEIN ON SCHEMA T_SCORES TO JONES;

Example 2: Grant the CREATEIN privilege on schema VAC to all users at the current server.

   GRANT CREATEIN ON SCHEMA VAC TO PUBLIC;

Example 3: Grant the ALTERIN privilege on schema DEPT to the administrative assistant and give the grantee the ability to grant ALTERIN privileges on this schema to others.

   GRANT ALTERIN ON SCHEMA DEPT TO ADMIN_A
         WITH GRANT OPTION;

Example 4: Grant the CREATEIN, ALTERIN, and DROPIN privileges on schemas NEW_HIRE, PROMO, and RESIGN to HR (Human Resources).

   GRANT CREATEIN, ALTERIN, DROPIN ON SCHEMA NEW_HIRE, PROMO, RESIGN TO HR;

Example 5: Grant the ALTERIN privileges on the EMPLOYEE schema to role ROLE1:

GRANT ALTERIN ON SCHEMA EMPLOYEE TO ROLE ROLE1;