Purpose
This statement is used to revoke privileges previously granted to a user or role, or to revoke a role from a user or role.
Limitations and considerations
- When revoking the
ALL PRIVILEGESandGRANT OPTIONprivileges, the current user must have the globalGRANT OPTIONprivilege, or theUPDATEandDELETEprivileges on the privilege table. - Revoking object privileges does not cascade. For example, if user
user1grants certain privileges to useruser2, revokinguser1's privileges will not automatically revoke the corresponding privileges fromuser2. - The
REVOKEstatement takes effect immediately for the currently logged-in user. - Roles specified in the
mandatory_rolessystem variable cannot be revoked.
Privilege requirements
Privilege requirements for revoking object privileges from a user or role
The user must have the privilege being revoked (e.g., user1 must have the SELECT privilege on table t1 to revoke it from user2), and the GRANT OPTION privilege.
Privilege requirements for revoking role privileges from a user or role
To revoke a role, the current user must have the role being revoked and the ADMIN OPTION privilege.
Syntax
Revoking object privileges from a user or role
REVOKE priv_type[(column_name_list)] [, priv_type[(column_name_list)] ...]
ON database.table_name
FROM 'user_or_role';
priv_type:
ALTER
| CREATE
| CREATE USER
| CREATE VIEW
| DELETE
| DROP
| GRANT OPTION
| INDEX
| INSERT
| PROCESS
| SELECT
| SHOW DATABASES
| SHOW VIEW
| SUPER
| UPDATE
| USAGE
column_name_list:
column_name [, column_name ...]
user_or_role:
user_name | role_name
Revoking a role from a user or role
REVOKE [IF EXISTS] role_name [, role_name ...]
FROM user_or_role [, user_or_role ...]
[IGNORE UNKNOWN USER];
user_or_role:
user_name | role_name
Parameters
| Parameter | Description |
|---|---|
| priv_type | Specifies the type of privilege to revoke. For more information about the privilege types, see the table below. When revoking multiple privileges from a user, separate the privilege types with commas. Note: The PROXY parameter is only recorded in the syntax for this version and does not take effect. |
| column_name_list | Optional. Specifies the list of column-level privileges to revoke. If not specified, the privileges for the entire table or object are revoked. |
| database.table_name | Specifies the table in the database. Use \* to replace database or table_name to revoke global privileges, i.e., the operation privileges on all tables in the database. |
| user_name | Specifies the user from whom to revoke privileges. When revoking privileges from multiple users, separate the usernames with commas. |
| IF EXISTS | IGNORE UNKNOWN USER | Optional. If the specified role or grantee does not exist, the system will issue a warning instead of an error message when IF EXISTS or IGNORE UNKNOWN USER is used. |
| role_name | Specifies the name of the role. |
| proxy | The PROXY parameter is only recorded in the syntax for this version and does not take effect. |
The following table lists the revocable privilege types.
| Privilege | Description |
|---|---|
| ALL PRIVILEGES | All privileges except GRANT OPTION. |
| ALTER | The ALTER TABLE privilege. |
| CREATE | The CREATE TABLE privilege. |
| CREATE USER | The CREATE USER, DROP USER, RENAME USER, and REVOKE ALL PRIVILEGES privileges. |
| CREATE TABLEGROUP | The global CREATE TABLEGROUP privilege. |
| DELETE | The DELETE privilege. |
| DROP | The DROP privilege. |
| GRANT OPTION | The GRANT OPTION privilege. |
| INSERT | The INSERT privilege. |
| SELECT | The SELECT privilege. |
| UPDATE | The UPDATE privilege. |
| SUPER | The privilege to modify global system parameters using SET GLOBAL. |
| SHOW DATABASES | The global SHOW DATABASES privilege. |
| INDEX | The CREATE INDEX and DROP INDEX privileges. |
| CREATE VIEW | The CREATE VIEW and DROP VIEW privileges. |
| SHOW VIEW | The SHOW CREATE VIEW privilege. |
Note
Currently, there is no privilege control for changing the effective tenant, so users in the sys tenant can revoke privileges.
Examples
Example 1: Revoke object privileges
Revoke the SELECT privilege from user u1 and view the privileges granted by ALL PRIVILEGES.
obclient> GRANT ALL PRIVILEGES ON *.* TO u1;
Query OK, 0 rows affected
obclient> SHOW GRANTS FOR u1;
+-------------------------------------+
| Grants for u1@% |
+-------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'u1' |
+-------------------------------------+
1 row in set
obclient> REVOKE SELECT ON *.* FROM u1;
Query OK, 0 rows affected
obclient> SHOW GRANTS FOR u1;
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Grants for u1@%
|
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| GRANT ALTER, CREATE, CREATE USER, DELETE, DROP, INSERT, UPDATE, INDEX, CREATE VIEW, SHOW VIEW, SHOW DB, SUPER, PROCESS, FILE, ALTER TENANT, ALTER SYSTEM, CREATE RESOURCE POOL, CREATE RESOURCE UNIT ON *.* TO 'u1' |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set
Example 2: Revoke a role from a role
Revoke role
role001from rolerole002.obclient [test]> REVOKE role001 FROM role002;Revoke role
role001from useruser001.obclient [test]> REVOKE role001 FROM user001;