Purpose
You can use this statement to revoke privileges or roles granted to a user or role.
Limitations and considerations
- To revoke the
ALL PRIVILEGESandGRANT OPTIONprivileges, you must have the globalGRANT OPTIONprivilege or theUPDATEandDELETEprivileges on the table. - Privilege revocation does not cascade to dependent users or roles. For example, if
user1has granted some privileges touser2, when the privileges ofuser1are revoked, the privileges granted touser2are not revoked. - The
REVOKEstatement takes effect immediately for the currently logged-on user. - Roles defined in the system variable
mandatory_rolescannot be revoked.
Required privileges
Privileges required for revoking privileges from a user or role
You must have the privileges to be revoked and the GRANT OPTION privilege. For example, if you want to revoke the SELECT privilege on table t1 from the user user2 as the user user1, you must have the SELECT privilege on table t1 and the GRANT OPTION privilege.
Privileges required for revoking roles from a user or role
You must have the roles to be revoked and the ADMIN OPTION privilege.
Syntax
Revoke privileges from a user or role
REVOKE priv_type
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
user_or_role:
user_name | role_name
Revoke roles 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 | The type of the privilege to be revoked. For information about the specific privilege types and their description, see the following table. Multiple privileges must be separated with commas (,). |
| database.table_name | The name of the target table in the database. If you replace database or table_name with an asterisk (*), operation privileges on all tables in the database will be revoked. |
| user_name | The name of the user from which the privilege is to be revoked. Multiple usernames must be separated with commas (,). |
| IF EXISTS | IGNORE UNKNOWN USER | Optional. If you specify IF EXISTS and IGNORE UNKNOWN USER, a warning instead of an error message is generated when the role to be revoked has not been granted or the user or role from which the role is revoked does not exist. |
| role_name | The name of the role. |
The following table lists the types of privileges that can be revoked.
| Privilege type | 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 SET GLOBAL privilege for modifying global system parameters. |
| 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, all users in the sys tenant can execute the CHANGE EFFECTIVE TENANT statement to revoke privileges of specified users in user tenants.
Examples
Example 1: Revoke a privilege
Revoke the SELECT privilege from user u1 and then check whether the SELECT privilege is revoked from the privileges that were granted by using the GRANT ALL PRIVILEGES statement.
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
Revoke the
role001role from therole002role.obclient [test]> REVOKE role001 FROM role002;Revoke the
role001role from theuser001user.obclient [test]> REVOKE role001 FROM user001;