This
form of the GRANT statement grants privileges on a table, view, or
nickname.
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
The privileges held by the
authorization ID of the statement must include at least one of the
following authorities:
- CONTROL privilege on the referenced table, view, or nickname
- The WITH GRANT OPTION for each identified privilege. If ALL is
specified, the authorization ID must have some grantable privilege
on the identified table, view, or nickname.
- ACCESSCTRL or SECADM authority
ACCESSCTRL or SECADM authority is required
to grant the CONTROL privilege, or to grant privileges on catalog
tables and views.
Syntax
.-PRIVILEGES-.
>>-GRANT--+-ALL--+------------+---------------------------+----->
| .-,-----------------------------------------. |
| V | |
'---+-ALTER---------------------------------+-+-'
+-CONTROL-------------------------------+
+-DELETE--------------------------------+
+-INDEX---------------------------------+
+-INSERT--------------------------------+
+-REFERENCES--+-----------------------+-+
| | .-,-----------. | |
| | V | | |
| '-(----column-name-+--)-' |
+-SELECT--------------------------------+
'-UPDATE--+-----------------------+-----'
| .-,-----------. |
| V | |
'-(----column-name-+--)-'
.-TABLE-.
>--ON--+-------+--+-table-name----+----------------------------->
| (1) |
+-view-name-----+
'-nickname------'
.-,---------------------------------.
V |
>--TO----+-+-------+--authorization-name-+-+-------------------->
| +-USER--+ |
| +-GROUP-+ |
| '-ROLE--' |
'-PUBLIC------------------------'
>--+-------------------+---------------------------------------><
'-WITH GRANT OPTION-'
Notes:
- ALTER, INDEX, and REFERENCES privileges are not applicable
to views.
Description
- ALL or ALL PRIVILEGES
- Grants all the appropriate privileges, except CONTROL, on the
base table, view, or nickname named in the ON clause.
If the authorization ID of the statement has CONTROL
privilege on the table, view, or nickname, or ACCESSCTRL or SECADM
authority, then all the privileges applicable to the object (except
CONTROL) are granted. Otherwise, the privileges granted are all those
grantable privileges that the authorization ID of the statement has
on the identified table, view, or nickname.
If ALL is not specified,
one or more of the keywords in the list of privileges must be specified.
- ALTER
- Grants the privilege to:
- CONTROL
- Grants:
The definer of a base table, materialized query table, staging
table, or nickname automatically receives the CONTROL privilege.
The
definer of a view automatically receives the CONTROL privilege if
the definer holds the CONTROL privilege on all tables, views, and
nicknames identified in the fullselect.
- DELETE
- Grants the privilege to delete rows from the table or updatable
view.
- INDEX
- Grants the privilege to create an index on a table, or an index
specification on a nickname. This privilege cannot be granted on a
view. The creator of an index or index specification automatically
has the CONTROL privilege on the index or index specification (authorizing
the creator to drop the index or index specification). In addition,
the creator retains the CONTROL privilege even if the INDEX privilege
is revoked.
- INSERT
- Grants the privilege to insert rows into the table or updatable
view and to run the IMPORT utility.
- REFERENCES
- Grants the privilege to create and drop a foreign key referencing
the table as the parent.
If the authorization ID of the statement
has one of:
- ACCESSCTRL or SECADM authority
- CONTROL privilege on the table
- REFERENCES WITH GRANT OPTION on the table
then the grantee(s) can create referential constraints using
all columns of the table as parent key, even those added later using
the ALTER TABLE statement. Otherwise, the privileges granted are all
those grantable column REFERENCES privileges that the authorization
ID of the statement has on the identified table.
The privilege
can be granted on a nickname, although foreign keys cannot be defined
to reference nicknames.
- REFERENCES (column-name,...)
- Grants the privilege to create and drop a foreign key using only
those columns specified in the column list as a parent key. Each column-name must
be an unqualified name that identifies a column of the table identified
in the ON clause. Column level REFERENCES privilege cannot be granted
on typed tables, typed views, or nicknames (SQLSTATE 42997).
- SELECT
- Grants the privilege to:
- Retrieve rows from the table or view.
- Create views on the table.
- Run the EXPORT utility against the table or view.
- UPDATE
- Grants the privilege to use the UPDATE statement on the table
or updatable view identified in the ON clause.
If the authorization
ID of the statement has one of:
- ACCESSCTRL or SECADM authority
- CONTROL privilege on the table or view
- UPDATE WITH GRANT OPTION on the table or view
then the grantee(s) can update all updatable columns of the
table or view on which the grantor has with grant privilege as well
as those columns added later using the ALTER TABLE statement. Otherwise,
the privileges granted are all those grantable column UPDATE privileges
that the authorization ID of the statement has on the identified table
or view.
- UPDATE (column-name,...)
- Grants the privilege to use the UPDATE statement to update only
those columns specified in the column list. Each column-name must
be an unqualified name that identifies a column of the table or view
identified in the ON clause. Column level UPDATE privilege cannot
be granted on typed tables, typed views, or nicknames (SQLSTATE 42997).
- ON TABLE table-name or view-name or nickname
- Specifies the table, view, or nickname on which privileges are
to be granted.
No privileges may be granted on an inoperative
view or an inoperative materialized query table (SQLSTATE 51024).
No privileges may be granted on a declared temporary table (SQLSTATE
42995).
- 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.
- 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.
A
privilege that is granted to a group is not used for authorization
checking:
- On static DML statements in a package
- On a base table while processing a CREATE VIEW statement
- On a base table while processing a CREATE TABLE statement for
a materialized query table
Table
privileges granted to groups only apply to statements that are dynamically
prepared. For example, if the INSERT privilege on the PROJECT table
has been granted to group D204 but not UBIQUITY (a member of D204)
UBIQUITY could issue the statement:
EXEC SQL EXECUTE IMMEDIATE :INSERT_STRING;
where
the content of the string is:
INSERT INTO PROJECT (PROJNO, PROJNAME, DEPTNO, RESPEMP)
VALUES ('AD3114', 'TOOL PROGRAMMING', 'D21', '000260');
but
could not precompile or bind a program with the statement:
EXEC SQL INSERT INTO PROJECT (PROJNO, PROJNAME, DEPTNO, RESPEMP)
VALUES ('AD3114', 'TOOL PROGRAMMING', 'D21', '000260');
- PUBLIC
- Grants the privileges to a set of users (authorization IDs). For
more information, see "Authorization, privileges and object ownership".
(Previous restrictions on the use of privileges granted to PUBLIC
for static SQL statements and the CREATE VIEW statement have been
removed.)
- WITH GRANT OPTION
- Allows the specified authorization-names
to GRANT the privileges to others.
If the specified privileges
include CONTROL, the WITH GRANT OPTION applies to all the applicable
privileges except for CONTROL (SQLSTATE 01516).
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.
- 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). (If the package used for processing
the statement was precompiled with LANGLEVEL set to SQL92E or MIA,
a warning is returned (SQLSTATE 01007), unless the grantor has no
privileges on the object of the grant operation.) If CONTROL privilege
is specified, privileges will only be granted if the authorization
ID of the statement has ACCESSCTRL or SECADM authority (SQLSTATE 42501).
Notes
- Privileges may be granted independently at every level of a table
hierarchy. A user with a privilege on a supertable may affect the
subtables. For example, an update specifying the supertable T may
show up as a change to a row in the subtable S of T
done by a user with UPDATE privilege on T but
without UPDATE privilege on S. A user can
only operate directly on the subtable if the necessary privilege is
held on the subtable.
- Granting nickname privileges has no effect on data source object
(table or view) privileges. Typically, data source privileges are
required for the table or view that a nickname references when attempting
to retrieve data.
- Syntax alternatives:
The following syntax alternatives are supported for compatibility with previous versions of DB2® and with other database products. The following syntax is
tolerated and ignored:
Examples
- Grant all privileges on the table WESTERN_CR to PUBLIC.
GRANT ALL ON WESTERN_CR
TO PUBLIC
- Grant the appropriate privileges on the CALENDAR table so that
users PHIL and CLAIRE can read it and insert new entries into it.
Do not allow them to change or remove any existing entries.
GRANT SELECT, INSERT ON CALENDAR
TO USER PHIL, USER CLAIRE
- Grant all privileges on the COUNCIL table to user FRANK and the
ability to extend all privileges to others.
GRANT ALL ON COUNCIL
TO USER FRANK WITH GRANT OPTION
- GRANT SELECT privilege on table CORPDATA.EMPLOYEE to a user named
JOHN. There is a user called JOHN and no group called JOHN.
GRANT SELECT ON CORPDATA.EMPLOYEE TO JOHN
or
GRANT SELECT
ON CORPDATA.EMPLOYEE TO USER JOHN
- GRANT SELECT privilege on table CORPDATA.EMPLOYEE to a group named
JOHN. There is a group called JOHN and no user called JOHN.
GRANT SELECT ON CORPDATA.EMPLOYEE TO JOHN
or GRANT SELECT ON CORPDATA.EMPLOYEE TO GROUP JOHN
- GRANT INSERT and SELECT on table T1 to both a group named D024
and a user named D024.
GRANT INSERT, SELECT ON TABLE T1
TO GROUP D024, USER D024
In this case,
both the members of the D024 group and the user D024 would be allowed
to INSERT into and SELECT from the table T1. Also, there would be
two rows added to the SYSCAT.TABAUTH catalog view.
- GRANT INSERT, SELECT, and CONTROL on the CALENDAR table to user
FRANK. FRANK must be able to pass the privileges on to others.
GRANT CONTROL ON TABLE CALENDAR
TO FRANK WITH GRANT OPTION
The result
of this statement is a warning (SQLSTATE 01516) that CONTROL was not
given the WITH GRANT OPTION. Frank now has the ability to grant any
privilege on CALENDAR including INSERT and SELECT as required. FRANK
cannot grant CONTROL on CALENDAR to other users unless he has ACCESSCTRL
or SECADM authority.
- User JON created a nickname for an
Oracle table that had no index. The nickname is ORAREM1. Later, the Oracle DBA defined an index for
this table. User SHAWN now wants DB2 to know that this index
exists, so that the optimizer can devise strategies to access the table more efficiently. SHAWN can
inform DB2 of the index by creating an index specification for
ORAREM1. Give SHAWN the index privilege on this nickname, so that he can create the index
specification.
GRANT INDEX ON NICKNAME ORAREM1
TO USER SHAWN