Grant All Privileges Option

The following sections describe the results of the grant all privileges option.

Installation and Database Privileges

If grant all privileges on database or grant all privileges on current installation is specified, the grantees receive the following database privileges:

noquery_io_limit
noquery_row_limit
create_table
create_procedure
lockmode
raise dbevent
register dbevent

Privileges granted on a specific database override privileges granted on current installation.

Other Privileges

The requirements for granting all privileges on tables, views, database procedures, and database events depend on the type of object and the owner. To grant a privilege on an object owned by another user, the grantor or public must have been granted the privilege with grant option. Only the privileges for which the grantor or public has grant option are granted.

The following example illustrates the results of the grant all privileges option. The accounting_mgr user creates the following employee table:

create table employee (name char(25), department char(5),
salary money)...

and, using the following grant statement, grants the accounting_supervisor user the ability to select all columns but only allows accounting_supervisor to update the department column (to prevent unauthorized changes of the salary column):

grant select, update (department) on table employees to accounting_supervisor with grant option;

If the accounting_supervisor user issues the following grant statement:

grant all privileges on table employees to accounting_clerk;

the accounting_clerk user receives select and update(department) privileges.

Granting All Privileges on Views

The results of granting all privileges on a view you do not own are determined as shown in this table:

Privilege	Results
Select	Granted if the grantor can grant select privilege on all tables and views in the view definition.
Update	Granted for all columns for which the grantor can grant update privilege. If the grantor was granted update...with grant option on a subset of the columns of a table, update is granted only for those columns.
Insert	Granted if the grantor can grant insert privilege on all tables and views in the view definition.
Delete	Granted if the grantor can grant delete privilege on all tables and views in the view definition.
References	The references privilege is not valid for views.