This form of the REVOKE statement removes privileges on a table or view.
This statement can be embedded in an application program or issued interactively. It is an executable statement that can be dynamically prepared.
The privileges held by the authorization ID of the statement must include at least one of the following:
.-PRIVILEGES-. >>-REVOKE--+-ALL--+------------+---------------------------+----> | .-,-----------------------------------------. | | V | | '---+-ALTER---------------------------------+-+-' +-DELETE--------------------------------+ +-INDEX---------------------------------+ +-INSERT--------------------------------+ +-REFERENCES--+-----------------------+-+ | | .-,-----------. | | | | V | | | | '-(----column-name-+--)-' | +-SELECT--------------------------------+ '-UPDATE--+-----------------------+-----' | .-,-----------. | | V | | '-(----column-name-+--)-' .-,--------------. .-TABLE-. V | >--ON--+-------+----+-table-name-+-+--FROM----------------------> '-view-name--' .-,----------------------. V | >----+-authorization-name-+-+---------------------------------->< '-PUBLIC-------------'
If you do not use ALL, you must use one or more of the keywords listed below. Each keyword revokes the privilege described, but only as it applies to the tables and views named in the ON clause.
Multiple grants: If the same privilege is granted to the same user more than once, revoking that privilege from that user nullifies all those grants.
If you revoke a privilege, it nullifies any grant of that privilege, regardless of who granted it.
Revoking WITH GRANT OPTION: The only way to revoke the WITH GRANT OPTION is to revoke ALL.
Privilege warning: Revoking a specific privilege from a user does not necessarily prevent that user from performing an action that requires that privilege. For example, the user may still have the privilege through PUBLIC or administrative privileges.
If more than one system authority will be revoked with an SQL privilege, and any one of the authorities cannot be revoked, then a warning occurs and no authorities will be revoked for that privilege.
Corresponding system authorities: When a table privilege is revoked, the corresponding system authorities are revoked, except:
For information about the system authorities that correspond to SQL privileges see GRANT (Table or View Privileges).
Revoking either the INDEX or ALTER privilege, revokes the system authority *OBJALTER.
Example 1: Revoke SELECT privileges on table EMPLOYEE from user ENGLES.
Example 2: Revoke update privileges on table EMPLOYEE previously granted to all users. Note that grants to specific users are not affected.
Example 3: Revoke all privileges on table EMPLOYEE from users PELLOW and ANDERSON.
Example 4: Revoke the privilege to update column_1 in VIEW1 from FRED.