Grant Authority Restrictions
Go Up to Granting Users the Right to Grant Privileges
There are only three conditions under which a user can grant access privileges (SELECT, DELETE, INSERT, UPDATE, and REFERENCES) for tables to other users or objects:
- Users can grant privileges to any table or view that they own.
- Users can grant any privileges on another user’s table or view when they have been assigned those privileges
WITH GRANT OPTION. - Users can grant privileges that they have acquired by being granted a role
WITH ADMIN OPTION.
For example, in an earlier GRANT statement, EMIL was granted SELECT access to the DEPARTMENTS table WITH GRANT OPTION. EMIL can grant SELECT privilege to other users. Suppose EMIL is now given INSERT access as well, but without the WITH GRANT OPTION:
GRANT INSERT ON DEPARTMENTS TO EMIL;
EMIL can SELECT from and INSERT to the DEPARTMENTS table. He can grant SELECT privileges to other users, but cannot assign INSERT privileges.
To change a user’s existing privileges to include grant authority, issue a second GRANT statement that includes the WITH GRANT OPTION clause. For example, to allow EMIL to grant INSERT privileges on DEPARTMENTS to others, reissue the GRANT statement and include the WITH GRANT OPTION clause:
GRANT INSERT ON DEPARTMENTS TO EMIL WITH GRANT OPTION;