You can revoke authorization using the REVOKE statement. The authorization granted to a user can be revoked anytime. If more than one authorization are granted to a user, all or part of the authorization can be revoked. In addition, if authorization on multiple tables is granted to more than one user using one GRANT statement, the authorization can be selectively revoked for specific users and tables.
If the authorization (WITH GRANT OPTION) is revoked from the grantor, the authorization granted to the grantee by that grantor is also revoked.
REVOKE operation [ { , operation }_ ] ON table_name [ { , class_name }_ ]
FROM user [ { , user }_ ] [ ; ]
The following example shows how to grant SELECT, INSERT, UPDATE, and DELETE authorization to Fred and John so that they can perform on the nation and athlete tables.
GRANT SELECT, INSERT, UPDATE, DELETE ON nation, athlete TO Fred, John;
The following example shows how to execute the REVOKE statement; this allows John to have only SELECT authorization. If John has granted authorization to another user, the user is also allowed to execute SELECT only.
REVOKE INSERT, UPDATE, DELETE ON nation, athlete FROM John;
The following example shows how to execute the REVOKE statement revoking all authorization that has granted to Fred. Fred is not allowed to execute any operations on the nation and athlete tables once this statement is executed.
REVOKE ALL PRIVILEGES ON nation, athlete FROM Fred;