9. Extended Statements : Grant : The Grant All Privileges Option
 
Share this page                  
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.