SQL Reference Guide > SQL Reference Guide > SQL Statements > GRANT (privilege) > GRANT ALL PRIVILEGES Option
Was this helpful?
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 follows:
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.
Last modified date: 04/26/2024