Grant Authority Implications
Go Up to Granting Users the Right to Grant Privileges
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, GALENA
and SUDHANSHU
, both issue the following statement:
GRANT INSERT ON DEPARTMENTS TO SPINOZA WITH GRANT OPTION;
Later, GALENA
revokes the privilege and grant authority for SPINOZA
:
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 SUDHANSHU
.
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
REVOKE
statement. - The table’s owner must issue a
REVOKE
statement for all users of the table, then issueGRANT
statements to reestablish access privileges for the users who should not lose their rights.
For more information about the REVOKE
statement, see Revoking User Access.