The Grant All Privileges Option
To grant a privilege on an object you do not own, you must have been granted the privilege WITH GRANT OPTION--only the privileges for which you have grant option are granted.
The results of granting all privileges on a view you do not own are determined as follows:
• Select—Granted if you can grant select privilege on all tables and views in the view definition.
• Update—Granted for all columns for which you can grant update privilege; if you were granted update...with grant option on a subset of the columns of a table, update is granted only for those columns.
• Insert—Granted if you can grant insert privilege on all tables and views in the view definition.
• Delete—Granted if you can grant delete privilege on all tables and views in the view definition.
• References—The references privilege is not valid for views.
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 character(25),
department character(5), salary decimal)...
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.
Last modified date: 01/30/2023