| title | REVOKE <privileges> | TiDB SQL Statement Reference |
|---|---|
| summary | An overview of the usage of REVOKE <privileges> for the TiDB database. |
This statement removes privileges from an existing user. Executing this statement requires the GRANT OPTION privilege and all privileges you revoke.
Starting from TiDB v8.5.6 and subsequent 8.5.x versions, TiDB supports the MySQL-compatible column-level privilege management mechanism. You can specify a list of column names in REVOKE, for example REVOKE SELECT(col2) ON test.tbl FROM 'user'@'host';. For more information, see Column Privilege Management.
RevokeStmt ::=
'REVOKE' PrivElemList 'ON' ObjectType PrivLevel 'FROM' UserSpecList
PrivElemList ::=
PrivElem ( ',' PrivElem )*
PrivElem ::=
PrivType ( '(' ColumnNameList ')' )?
PrivType ::=
'ALL' 'PRIVILEGES'?
| 'ALTER' 'ROUTINE'?
| 'CREATE' ( 'USER' | 'TEMPORARY' 'TABLES' | 'VIEW' | 'ROLE' | 'ROUTINE' )?
| 'TRIGGER'
| 'DELETE'
| 'DROP' 'ROLE'?
| 'PROCESS'
| 'EXECUTE'
| 'INDEX'
| 'INSERT'
| 'SELECT'
| 'SUPER'
| 'SHOW' ( 'DATABASES' | 'VIEW' )
| 'UPDATE'
| 'GRANT' 'OPTION'
| 'REFERENCES'
| 'REPLICATION' ( 'SLAVE' | 'CLIENT' )
| 'USAGE'
| 'RELOAD'
| 'FILE'
| 'CONFIG'
| 'LOCK' 'TABLES'
| 'EVENT'
| 'SHUTDOWN'
ObjectType ::=
'TABLE'?
PrivLevel ::=
'*' ( '.' '*' )?
| Identifier ( '.' ( '*' | Identifier ) )?
UserSpecList ::=
UserSpec ( ',' UserSpec )*
mysql> CREATE USER 'newuser' IDENTIFIED BY 'mypassword';
Query OK, 1 row affected (0.02 sec)
mysql> GRANT ALL ON test.* TO 'newuser';
Query OK, 0 rows affected (0.03 sec)
mysql> SHOW GRANTS FOR 'newuser';
+-------------------------------------------------+
| Grants for newuser@% |
+-------------------------------------------------+
| GRANT USAGE ON *.* TO 'newuser'@'%' |
| GRANT ALL PRIVILEGES ON test.* TO 'newuser'@'%' |
+-------------------------------------------------+
2 rows in set (0.00 sec)
mysql> REVOKE ALL ON test.* FROM 'newuser';
Query OK, 0 rows affected (0.03 sec)
mysql> SHOW GRANTS FOR 'newuser';
+-------------------------------------+
| Grants for newuser@% |
+-------------------------------------+
| GRANT USAGE ON *.* TO 'newuser'@'%' |
+-------------------------------------+
1 row in set (0.00 sec)
mysql> DROP USER 'newuser';
Query OK, 0 rows affected (0.14 sec)
mysql> SHOW GRANTS FOR 'newuser';
ERROR 1141 (42000): There is no such grant defined for user 'newuser' on host '%'- In TiDB, after the
REVOKE <privileges>statement is executed successfully, the execution result takes effect immediately on the current connection. Whereas in MySQL, for some privileges, the execution results take effect only on subsequent connections. See TiDB #39356 for details.