Syntax
┌◄────── , ──────┐
── REVOKE ───────┬─────────────────────┬──────┬─┴─┬── INSERT ──┬─┴──────┬────────────►
└─ GRANT OPTION FOR ─┘ │ ├── UPDATE ──┤ │
│ ├── SELECT ──┤ │
│ └── DELETE ──┘ │
│ │
└─ ALL ─┬──────────────┬──┘
└─ PRIVILEGES ─┘
┌◄───── , ────┐
►──── ON ───────┬─────────┬───────┬──<table name>──┬── FROM ──┬───┴──<usercode>─┴───┬─────►
└─ TABLE ─┘ ├──<view name>───┤ └────── PUBLIC ──────┘
└────── * ───────┘
Explanation
The REVOKE command enables grantors to remove access rights that were previously defined for other users. You can revoke a specific privilege or simply the ability to grant the specified privilege to other users.
GRANT OPTION FOR
Specifies that only the ability to grant the specified privilege to other users is being revoked. The user is still able to perform the specified privilege.
Restrictions
The REVOKE command has the following restrictions:
-
The REVOKE command cannot exceed 65535 characters.
-
Only one table (or all tables if specified using the * (asterisk) option) can be specified in a REVOKE statement.
-
The PUBLIC usercode and usercodes named using the <usercode> element cannot be used in the same statement.
-
Only the grantor of a permission can revoke the specified permission from the grantee.
Note: The owner of the database can revoke any permission from any user in the database. For example, if USER1 grants permissions to USER2 and USER2 grants permissions to USER3, USER1 cannot revoke the permission from USER3.
-
If a user has been granted permissions for all tables or views using the * (asterisk) option, you cannot revoke the permissions for an individual table or view. Instead, you must revoke all permissions and grant individual permissions as necessary.
-
If all users have been granted permissions for a table using the PUBLIC option, you cannot revoke the permission for an individual user. Instead, you must revoke permissions from PUBLIC and grant individual permissions as necessary.
-
If a user is granted all query privileges for a table using the ALL [PRIVILEGES] option, you can revoke an individual privilege.
Examples
Example 1
The following example removes the SELECT privilege for USER1 for the table CUSTOMERS.
REVOKE SELECT ON CUSTOMERS FROM USER1
Example 2
The following example removes the GRANT option from all privileges for ADMIN for the table ORDERS:
REVOKE GRANT OPTION FOR ALL ON ORDERS FROM ADMIN
Example 3
The following command removes the INSERT and DELETE command for OP2 for the table PRODUCTS:
REVOKE INSERT, DELETE ON PRODUCTS FROM OP2
Example 4
The following command removes the SELECT command from all users for the table SALES:
REVOKE SELECT ON SALES TO PUBLIC
After you perform a REVOKE command, you receive a response detailing the status of the command. The following examples are responses that a user might receive after issuing a REVOKE command.
Example 5
The following response is returned when a REVOKE is successfully performed for one user:
1 row affected.
Example 6
The following response is returned when a REVOKE is successfully performed for three users in the same command:
3 rows affected.
Example 7
The following response is returned when the user attempting to perform the REVOKE command does not have the ability to grant or revoke this permission to others:
The Requestor is not permitted to perform the action.
