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 only if DYNAMICRULES run behavior is in effect for the package
(SQLSTATE 42509).
Authorization
To grant ACCESSCTRL, DATAACCESS,
DBADM, or SECADM authority,
SECADM authority is required. To grant other authorities ACCESSCTRL
or SECADM authority is required.
Syntax
![Read syntax diagram](../c.gif)
>>-GRANT-------------------------------------------------------->
.-,------------------------------------------------------------------.
V |
>----+-ACCESSCTRL-----------------------------------------------------+-+-->
+-BINDADD--------------------------------------------------------+
+-CONNECT--------------------------------------------------------+
+-CREATETAB------------------------------------------------------+
+-CREATE_EXTERNAL_ROUTINE----------------------------------------+
+-CREATE_NOT_FENCED_ROUTINE--------------------------------------+
+-DATAACCESS-----------------------------------------------------+
| .-WITH DATAACCESS----. .-WITH ACCESSCTRL----. |
+-DBADM--•--+--------------------+--•--+--------------------+--•-+
| '-WITHOUT DATAACCESS-' '-WITHOUT ACCESSCTRL-' |
+-EXPLAIN--------------------------------------------------------+
+-IMPLICIT_SCHEMA------------------------------------------------+
+-LOAD-----------------------------------------------------------+
+-QUIESCE_CONNECT------------------------------------------------+
+-SECADM---------------------------------------------------------+
+-SQLADM---------------------------------------------------------+
'-WLMADM---------------------------------------------------------'
.-,---------------------------------.
V |
>--ON DATABASE--TO----+-+-------+--authorization-name-+-+------><
| +-USER--+ |
| +-GROUP-+ |
| '-ROLE--' |
'-PUBLIC------------------------'
Description
- ACCESSCTRL
- Grants the access control authority. The ACCESSCTRL authority
allows the holder to:
Grant and revoke the following database authorities: BINDADD, CONNECT, CREATETAB,
CREATE_EXTERNAL_ROUTINE, CREATE_NOT_FENCED_ROUTINE, EXPLAIN, IMPLICIT_SCHEMA, LOAD, QUIESCE_CONNECT,
SQLADM, WLMADM![End of change](../deltaend.gif)
- Grant and revoke all object level privileges
The ACCESSCTRL authority cannot be granted to PUBLIC (SQLSTATE
42508).
- 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_EXTERNAL_ROUTINE
- Grants the authority to register external routines. Care must
be taken that routines so registered will not have adverse side effects.
(For more information, see the description of the THREADSAFE clause
on the CREATE or ALTER routine statements.)
Once an external routine
has been registered, it continues to exist, even if CREATE_EXTERNAL_ROUTINE
is subsequently revoked.
- CREATE_NOT_FENCED_ROUTINE
- Grants the authority to register routines that execute in the
database manager's process. Care must be taken that routines so registered
will not have adverse side effects. (For more information, see the
description of the FENCED clause on the CREATE or ALTER routine statements.)
Once a routine has been registered as not fenced, it continues
to run in this manner, even if CREATE_NOT_FENCED_ROUTINE is subsequently
revoked.
CREATE_EXTERNAL_ROUTINE is automatically granted
to an authorization-name that is granted
CREATE_NOT_FENCED_ROUTINE authority.
- DATAACCESS
- Grants the authority to access data. The DATAACCESS authority
allows the holder to:
- Select, insert, update, delete, and load data
- Execute any package
- Execute any routine (except audit routines)
The DATAACCESS authority cannot be granted to PUBLIC (SQLSTATE
42508).
- DBADM
- Grants
the database administrator authority. A database administrator holds
nearly all privileges on nearly all objects in the database.
The only exceptions are those privileges that are part of the access
control, data access, and security administrator authorities. DBADM
cannot be granted to PUBLIC.
- EXPLAIN
- Grants the authority to explain statements. The EXPLAIN authority
allows the holder to explain, prepare, and describe dynamic and static
SQL statements without requiring access to data.
- IMPLICIT_SCHEMA
- Grants the authority to implicitly create a schema.
- LOAD
- Grants the authority to load in this database. This authority
gives a user the right to use the LOAD utility in this database.
DATAACCESS and DBADM also have this authority by default. However,
if a user only has LOAD authority (not DATAACCESS), 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
- QUIESCE_CONNECT
- Grants the authority to access the database while it is quiesced.
- SECADM
- Grants the security administrator authority. The authority allows
the holder to:
- Create and drop security objects such as audit policies, roles, security
labels, security label components, security policies, and trusted
contexts
- Grant and revoke authorities, exemptions, privileges, roles,
and security labels
- Grant and revoke the SETSESSIONUSER privilege
- Execute TRANSFER OWNERSHIP on objects owned by others
The SECADM authority cannot be granted to PUBLIC (SQLSTATE 42508).
- SQLADM
- Grants the authority to manage SQL statement execution. The SQLADM
authority allows the holder to:
- Create, drop, flush, and set event monitors
- Explain, prepare, and describe dynamic and static SQL statements
without requiring access to data
- Flush optimization profile cache
- Flush package cache
- Execute the runstats utility
- WLMADM
- Grants the authority to manage workloads. The WLMADM authority
allows the holder to:
- Create, drop, and alter service classes, work action sets, work
class sets, or workloads.
- 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.
- ROLE
- Specifies that the authorization-name identifies
a role name. The role name must exist at the current server (SQLSTATE
42704).
- authorization-name,...
- Lists the authorization IDs of one or more users, groups, or
roles.
The list of authorization IDs cannot include the authorization
ID of the user issuing the statement (SQLSTATE 42502).
- PUBLIC
- Grants the authorities to a set of users (authorization IDs).
For more information, see "Authorization, privileges and object
ownership".
Rules
- For each authorization-name specified, if neither
USER, GROUP, nor ROLE is specified, then:
- If the security plug-in in effect for the instance cannot determine
the status of the authorization-name, an error
is returned (SQLSTATE 56092).
- If the authorization-name is defined as ROLE
in the database, and as either GROUP or USER according to the
security plug-in in effect, an error is returned (SQLSTATE 56092).
- If the authorization-name is defined according
to the security plug-in in effect as both USER and GROUP, an
error is returned (SQLSTATE 56092).
- If the authorization-name is defined according
to the security plug-in in effect as USER only, or if it is undefined,
USER is assumed.
- If the authorization-name is defined according
to the security plug-in in effect as GROUP only, GROUP is assumed.
- If the authorization-name is defined in
the database as ROLE only, ROLE is assumed.
Notes
- ACCESSCTRL,
DATAACCESS, DBADM, or SECADM authorities cannot be granted to the
special group PUBLIC. Therefore, granting ACCESSCTRL, DBADM, DATAACCESS,
or SECADM authority to a role role-name fails if role-name is
granted to PUBLIC either directly or indirectly (SQLSTATE 42508).
- Syntax alternatives: The following
are supported for compatibility with previous versions of DB2® and with other database products.
- CREATE_NOT_FENCED can be specified in place of CREATE_NOT_FENCED_ROUTINE
- SYSTEM can be specified in place of DATABASE
- Privileges granted to a group: A privilege
that is granted to a group is not used for authorization checking
on:
- Static DML statements in a package
- A base table while processing a CREATE VIEW statement
- A base table while processing a CREATE TABLE statement for a materialized
query table
- Create SQL routine
- Create trigger
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).
Example 3: Give user Walid security
administrator authority.
GRANT SECADM ON DATABASE TO USER Walid