Purpose
You can use this statement as the system administrator to revoke privileges from a user.
The considerations are as follows:
The user must be granted the privileges to be revoked and the
GRANT OPTIONprivilege. For example, ifuser1wants to revoke theSELECTprivilege on tablet1fromuser2,user1must have theSELECTprivilege on tablet1.To revoke the
ALL PRIVILEGESandGRANT OPTIONprivileges, you must have the globalGRANT OPTIONprivilege or theUPDATEandDELETEprivileges on the table.The revocation does not extend to dependent users. For example, if
user1has granted some privileges touser2, when the privileges ofuser1are revoked, the privileges granted touser2are not revoked.
Syntax
REVOKE priv_type
ON database.table_name
FROM 'user_name';
priv_type:
ALTER
| CREATE
| CREATE USER
| CREATE VIEW
| DELETE
| DROP
| GRANT OPTION
| INDEX
| INSERT
| PROCESS
| SELECT
| SHOW DATABASES
| SHOW VIEW
| SUPER
| UPDATE
| USAGE
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 table in the database on which the privilege is to be revoked. 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 (,). |
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, DROPUSER, 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
Revoke the SELECT privilege from user u1 and then execute the SHOW GRANTS statement to 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