SQL Reference

GRANT (Database Authorities)

This form of the GRANT statement grants authorities that apply to the entire database (rather than privileges that apply to specific objects within the database).

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

To grant DBADM authority, SYSADM authority is required. To grant other authorities, either DBADM or SYSADM authority is required.

Refer to GRANT (Index Privileges), GRANT (Package Privileges), GRANT (Schema Privileges), GRANT (Server Privileges) and GRANT (Table, View, or Nickname Privileges) for details on other GRANT statements.

Syntax

            .-,------------------------.
            V                          |
>>-GRANT--------+-BINDADD-----------+--+--ON DATABASE----------->
                +-CONNECT-----------+
                +-CREATETAB---------+
                +-CREATE_NOT_FENCED-+
                +-IMPLICIT_SCHEMA---+
                +-DBADM-------------+
                '-LOAD--------------'
 
         .-,------------------------------------.
         V                                      |
>----TO----+-+-------+---authorization-name--+--+--------------><
           | +-USER--+                       |
           | '-GROUP-'                       |
           '-PUBLIC--------------------------'
 

Description

BINDADD

Grants the authority to create packages. The creator of a package automatically has the CONTROL privilege on that package and retains this privilege even if the BINDADD authority is subsequently revoked.

CONNECT

Grants the authority to access the database.

CREATETAB

Grants the authority to create base tables. The creator of a base table automatically has the CONTROL privilege on that table. The creator retains this privilege even if the CREATETAB authority is subsequently revoked.

There is no explicit authority required for view creation. A view can be created at any time if the authorization ID of the statement used to create the view has either CONTROL or SELECT privilege on each base table of the view.

CREATE_NOT_FENCED

Grants the authority to register functions that execute in the database manager's process. Care must be taken that functions so registered will not have adverse side effects (see the FENCED or NOT FENCED clause on page *** for more information).

Once a function has been registered as not fenced, it continues to run in this manner even if CREATE_NOT_FENCED is subsequently revoked.

IMPLICIT_SCHEMA

Grants the authority to implicitly create a schema.

DBADM

Grants the database administrator authority. A database administrator has all privileges against all objects in the database and may grant these privileges to others.

BINDADD, CONNECT, CREATETAB, CREATE_NOT_FENCED and IMPLICIT_SCHEMA are automatically granted to an authorization-name that is granted DBADM authority.

LOAD

Grants the authority to load in this database. This authority gives a user the right to use the LOAD utility in this database. SYSADM and DBADM also have this authority by default. However, if a user only has LOAD authority (not SYSADM or DBADM), the user is also required to have table-level privileges. In addition to LOAD privilege, the user is required to have:

• INSERT privilege on the table for LOAD with mode INSERT, TERMINATE (to terminate a previous LOAD INSERT), or RESTART (to restart a previous LOAD INSERT)
• INSERT and DELETE privilege on the table for LOAD with mode REPLACE, TERMINATE (to terminate a previous LOAD REPLACE), or RESTART (to restart a previous LOAD REPLACE)
• INSERT privilege on the exception table, if such a table is used as part of LOAD

TO

Specifies to whom the authorities 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 authorities to all users. DBADM cannot be granted to PUBLIC.

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.

Examples

Example 1:  Give the users WINKEN, BLINKEN, and NOD the authority to connect to the database.

   GRANT CONNECT ON DATABASE TO USER WINKEN, USER BLINKEN, USER NOD

Example 2:  GRANT BINDADD authority on the database to a group named D024. There is both a group and a user called D024 in the system.

  GRANT BINDADD ON DATABASE TO GROUP D024

Observe that, the GROUP keyword must be specified; otherwise, an error will occur since both a user and a group named D024 exist. Any member of the D024 group will be allowed to bind packages in the database, but the D024 user will not be allowed (unless this user is also a member of the group D024, had been granted BINDADD authority previously, or BINDADD authority had been granted to another group of which D024 was a member).