Grant Authority Implications
Consider every extension of grant authority with care. Once other users are permitted grant authority on a table, they can grant those same privileges, as well as grant authority for them, to other users.
As the number of users with privileges and grant authority for a table increases, the likelihood that different users can grant the same privileges and grant authority to any single user also increases.
SQL permits duplicate privilege and authority assignment under the assumption that it is intentional. Duplicate privilege and authority assignments to a single user have implications for subsequent revocation of that user’s privileges and authority. For more information about revoking privileges, see Revoking User Access.
For example, suppose two users to whom the appropriate privileges and grant authority have been extended,
SUDHANSHU, both issue the following statement:
GRANT INSERT ON DEPARTMENTS TO SPINOZA WITH GRANT OPTION;
GALENA revokes the privilege and grant authority for
REVOKE INSERT ON DEPARTMENTS FROM SPINOZA;
GALENA now believes that
SPINOZA no longer has
INSERT privilege and grant authority for the
DEPARTMENTS table. The immediate net effect of the statement is negligible because
SPINOZA retains the
INSERT privilege and grant authority assigned by
When full control of access privileges on a table is desired, grant authority should not be assigned indiscriminately. In cases where privileges must be universally revoked for a user who might have received rights from several users, there are two options:
- Each user who assigned rights must issue an appropriate
- The table’s owner must issue a
REVOKEstatement for all users of the table, then issue
GRANTstatements to reestablish access privileges for the users who should not lose their rights.
For more information about the
REVOKE statement, see Revoking User Access.