--- title: Privilege Management summary: Learn how to manage the privilege. --- # Privilege Management TiDB supports MySQL 5.7's privilege management system, including the syntax and privilege types. The following features from MySQL 8.0 are also supported: * SQL Roles, starting with TiDB 3.0. * Dynamic privileges, starting with TiDB 5.1. This document introduces privilege-related TiDB operations, privileges required for TiDB operations and implementation of the privilege system. ## Privilege-related operations ### Grant privileges The [`GRANT`](/sql-statements/sql-statement-grant-privileges.md) statement grants privileges to the user accounts. For example, use the following statement to grant the `xxx` user the privilege to read the `test` database. ```sql GRANT SELECT ON test.* TO 'xxx'@'%'; ``` Use the following statement to grant the `xxx` user all privileges on all databases: ```sql GRANT ALL PRIVILEGES ON *.* TO 'xxx'@'%'; ``` Starting from v8.5.6, TiDB supports a MySQL-compatible column-level privilege management mechanism. You can grant or revoke `SELECT`, `INSERT`, `UPDATE`, and `REFERENCES` privileges on specific columns in a specified table. For more information, see [Column-Level Privilege Management](/column-privilege-management.md). By default, [`GRANT`](/sql-statements/sql-statement-grant-privileges.md) statements will return an error if the user specified does not exist. This behavior depends on if the [SQL mode](/system-variables.md#sql_mode) `NO_AUTO_CREATE_USER` is specified: ```sql SET sql_mode=DEFAULT; ``` ``` Query OK, 0 rows affected (0.00 sec) ``` ```sql SELECT @@sql_mode; ``` ``` +-------------------------------------------------------------------------------------------------------------------------------------------+ | @@sql_mode | +-------------------------------------------------------------------------------------------------------------------------------------------+ | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION | +-------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) ``` ```sql SELECT * FROM mysql.user WHERE user='idontexist'; ``` ``` Empty set (0.00 sec) ``` ```sql GRANT ALL PRIVILEGES ON test.* TO 'idontexist'; ``` ``` ERROR 1105 (HY000): You are not allowed to create a user with GRANT ``` ```sql SELECT user,host,authentication_string FROM mysql.user WHERE user='idontexist'; ``` ``` Empty set (0.00 sec) ``` In the following example, the user `idontexist` is automatically created with an empty password because the SQL Mode `NO_AUTO_CREATE_USER` was not set. This is **not recommended** since it presents a security risk: miss-spelling a username will result in a new user created with an empty password: ```sql SET @@sql_mode='ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION'; ``` ``` Query OK, 0 rows affected (0.00 sec) ``` ```sql SELECT @@sql_mode; ``` ``` +-----------------------------------------------------------------------------------------------------------------------+ | @@sql_mode | +-----------------------------------------------------------------------------------------------------------------------+ | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION | +-----------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) ``` ```sql SELECT * FROM mysql.user WHERE user='idontexist'; ``` ``` Empty set (0.00 sec) ``` ```sql GRANT ALL PRIVILEGES ON test.* TO 'idontexist'; ``` ``` Query OK, 1 row affected (0.05 sec) ``` ```sql SELECT user,host,authentication_string FROM mysql.user WHERE user='idontexist'; ``` ``` +------------+------+-----------------------+ | user | host | authentication_string | +------------+------+-----------------------+ | idontexist | % | | +------------+------+-----------------------+ 1 row in set (0.01 sec) ``` You can use fuzzy matching in [`GRANT`](/sql-statements/sql-statement-grant-privileges.md) to grant privileges to databases. ```sql GRANT ALL PRIVILEGES ON `te%`.* TO genius; ``` ``` Query OK, 0 rows affected (0.00 sec) ``` ```sql SELECT user,host,db FROM mysql.db WHERE user='genius'; ``` ``` +--------|------|-----+ | user | host | db | +--------|------|-----+ | genius | % | te% | +--------|------|-----+ 1 row in set (0.00 sec) ``` In this example, because of the `%` in `te%`, all the databases starting with `te` are granted the privilege. ### Revoke privileges The [`REVOKE`](/sql-statements/sql-statement-revoke-privileges.md) statement enables system administrators to revoke privileges from the user accounts. The `REVOKE` statement corresponds with the `GRANT` statement: ```sql REVOKE ALL PRIVILEGES ON `test`.* FROM 'genius'@'localhost'; ``` > **Note:** > > To revoke privileges, you need the exact match. If the matching result cannot be found, an error will be displayed: ```sql REVOKE ALL PRIVILEGES ON `te%`.* FROM 'genius'@'%'; ``` ``` ERROR 1141 (42000): There is no such grant defined for user 'genius' on host '%' ``` About fuzzy matching, escape, string and identifier: ```sql GRANT ALL PRIVILEGES ON `te\%`.* TO 'genius'@'localhost'; ``` ``` Query OK, 0 rows affected (0.00 sec) ``` This example uses exact match to find the database named `te%`. Note that the `%` uses the `\` escape character so that `%` is not considered as a wildcard. A string is enclosed in single quotation marks(''), while an identifier is enclosed in backticks (``). See the differences below: ```sql GRANT ALL PRIVILEGES ON 'test'.* TO 'genius'@'localhost'; ``` ``` ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''test'.* to 'genius'@'localhost'' at line 1 ``` ```sql GRANT ALL PRIVILEGES ON `test`.* TO 'genius'@'localhost'; ``` ``` Query OK, 0 rows affected (0.00 sec) ``` If you want to use special keywords as table names, enclose them in backticks (``). For example: ```sql CREATE TABLE `select` (id int); ``` ``` Query OK, 0 rows affected (0.27 sec) ``` ### Check privileges granted to users You can use the `SHOW GRANTS` statement to see what privileges are granted to a user. For example: ```sql SHOW GRANTS; -- show grants for the current user ``` ``` +-------------------------------------------------------------+ | Grants for User | +-------------------------------------------------------------+ | GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' WITH GRANT OPTION | +-------------------------------------------------------------+ ``` ```sql SHOW GRANTS FOR 'root'@'%'; -- show grants for a specific user ``` For example, create a user `rw_user@192.168.%` and grant the user with write privilege on the `test.write_table` table and global read privilege. ```sql CREATE USER `rw_user`@`192.168.%`; GRANT SELECT ON *.* TO `rw_user`@`192.168.%`; GRANT INSERT, UPDATE ON `test`.`write_table` TO `rw_user`@`192.168.%`; ``` Show granted privileges of the `rw_user@192.168.%` user: ```sql SHOW GRANTS FOR `rw_user`@`192.168.%`; ``` ``` +------------------------------------------------------------------+ | Grants for rw_user@192.168.% | +------------------------------------------------------------------+ | GRANT Select ON *.* TO 'rw_user'@'192.168.%' | | GRANT Insert,Update ON test.write_table TO 'rw_user'@'192.168.%' | +------------------------------------------------------------------+ ``` ### Dynamic privileges Since v5.1, TiDB features support dynamic privileges, a feature borrowed from MySQL 8.0. Dynamic privileges are intended to replace the `SUPER` privilege by implementing more fine-grained access to certain operations. For example, using dynamic privileges, system administrators can create a user account that can only perform `BACKUP` and `RESTORE` operations. Dynamic privileges include: * `BACKUP_ADMIN` * `RESTORE_ADMIN` * `SYSTEM_USER` * `SYSTEM_VARIABLES_ADMIN` * `ROLE_ADMIN` * `CONNECTION_ADMIN` * `PLACEMENT_ADMIN` allows privilege owners to create, modify, and remove placement policies. * `DASHBOARD_CLIENT` allows privilege owners to log in to TiDB Dashboard. * `RESTRICTED_TABLES_ADMIN` allows privilege owners to view system tables when SEM is enabled. * `RESTRICTED_STATUS_ADMIN` allows privilege owners to view all status variables in [`SHOW [GLOBAL|SESSION] STATUS`](/sql-statements/sql-statement-show-status.md) when SEM is enabled. * `RESTRICTED_VARIABLES_ADMIN` allows privilege owners to view all system variables when SEM is enabled. * `RESTRICTED_USER_ADMIN` prohibits privilege owners to have their access revoked by SUPER users when SEM is enabled. * `RESTRICTED_CONNECTION_ADMIN` allows privilege owners to kill connections of `RESTRICTED_USER_ADMIN` users. This privilege affects `KILL` and `KILL TIDB` statements. * `RESTRICTED_REPLICA_WRITER_ADMIN` allows privilege owners to perform write or update operations without being affected when the read-only mode is enabled in the TiDB cluster. For details, see [`tidb_restricted_read_only`](/system-variables.md#tidb_restricted_read_only-new-in-v520). To see the full set of dynamic privileges, execute the `SHOW PRIVILEGES` statement. Because plugins are permitted to add new privileges, the list of privileges that are assignable might differ based on your TiDB installation. ## `SUPER` privilege - The `SUPER` privilege allows the user to perform almost any operation. By default, only the `root` user is granted with this privilege. Be careful when granting this privilege to other users. - The `SUPER` privilege is considered [deprecated in MySQL 8.0](https://dev.mysql.com/doc/refman/8.0/en/privileges-provided.html#dynamic-privileges-migration-from-super) and can be replaced by [dynamic privileges](#dynamic-privileges) to provide more fine-grained access control. ## Privileges required for TiDB operations You can check privileges of TiDB users in the `INFORMATION_SCHEMA.USER_PRIVILEGES` table. For example: ```sql SELECT * FROM INFORMATION_SCHEMA.USER_PRIVILEGES WHERE grantee = "'root'@'%'"; ``` ``` +------------+---------------+-------------------------+--------------+ | GRANTEE | TABLE_CATALOG | PRIVILEGE_TYPE | IS_GRANTABLE | +------------+---------------+-------------------------+--------------+ | 'root'@'%' | def | Select | YES | | 'root'@'%' | def | Insert | YES | | 'root'@'%' | def | Update | YES | | 'root'@'%' | def | Delete | YES | | 'root'@'%' | def | Create | YES | | 'root'@'%' | def | Drop | YES | | 'root'@'%' | def | Process | YES | | 'root'@'%' | def | References | YES | | 'root'@'%' | def | Alter | YES | | 'root'@'%' | def | Show Databases | YES | | 'root'@'%' | def | Super | YES | | 'root'@'%' | def | Execute | YES | | 'root'@'%' | def | Index | YES | | 'root'@'%' | def | Create User | YES | | 'root'@'%' | def | Create Tablespace | YES | | 'root'@'%' | def | Trigger | YES | | 'root'@'%' | def | Create View | YES | | 'root'@'%' | def | Show View | YES | | 'root'@'%' | def | Create Role | YES | | 'root'@'%' | def | Drop Role | YES | | 'root'@'%' | def | CREATE TEMPORARY TABLES | YES | | 'root'@'%' | def | LOCK TABLES | YES | | 'root'@'%' | def | CREATE ROUTINE | YES | | 'root'@'%' | def | ALTER ROUTINE | YES | | 'root'@'%' | def | EVENT | YES | | 'root'@'%' | def | SHUTDOWN | YES | | 'root'@'%' | def | RELOAD | YES | | 'root'@'%' | def | FILE | YES | | 'root'@'%' | def | CONFIG | YES | | 'root'@'%' | def | REPLICATION CLIENT | YES | | 'root'@'%' | def | REPLICATION SLAVE | YES | +------------+---------------+-------------------------+--------------+ 31 rows in set (0.00 sec) ``` ### ALTER - For all `ALTER` statements, users must have the `ALTER` privilege for the corresponding table. - For statements except `ALTER...DROP` and `ALTER...RENAME TO`, users must have the `INSERT` and `CREATE` privileges for the corresponding table. - For the `ALTER...DROP` statement, users must have the `DROP` privilege for the corresponding table. - For the `ALTER...RENAME TO` statement, users must have the `DROP` privilege for the table before renaming, and the `CREATE` and `INSERT` privileges for the table after renaming. > **Note:** > > In MySQL 5.7 documentation, users need `INSERT` and `CREATE` privileges to perform the `ALTER` operation on a table. But in reality for MySQL 5.7.25, only the `ALTER` privilege is required in this case. Currently, the `ALTER` privilege in TiDB is consistent with the actual behavior in MySQL. ### BACKUP Requires the `SUPER` or `BACKUP_ADMIN` privilege. ### CANCEL IMPORT JOB Requires the `SUPER` privilege to cancel jobs created by other users. Otherwise, only jobs created by the current user can be canceled. ### CREATE DATABASE Requires the `CREATE` privilege for the database. ### CREATE INDEX Requires the `INDEX` privilege for the table. ### CREATE TABLE Requires the `CREATE` privilege for the table. To execute the `CREATE TABLE...LIKE...` statement, the `SELECT` privilege for the table is required. ### CREATE VIEW Requires the `CREATE VIEW` privilege. > **Note:** > > If the current user is not the user that creates the View, both the `CREATE VIEW` and `SUPER` privileges are required. ### DROP DATABASE Requires the `DROP` privilege for the database. ### DROP INDEX Requires the `INDEX` privilege for the table. ### DROP TABLES Requires the `DROP` privilege for the table. ### IMPORT INTO Requires the `SELECT`, `UPDATE`, `INSERT`, `DELETE`, and `ALTER` privileges for the target table. To import files stored locally in TiDB, the `FILE` privilege is also required. ### LOAD DATA Requires the `INSERT` privilege for the table. When you use `REPLACE INTO`, the `DELETE` privilege is also required. ### TRUNCATE TABLE Requires the `DROP` privilege for the table. ### RENAME TABLE Requires the `ALTER` and `DROP` privileges for the table before renaming and the `CREATE` and `INSERT` privileges for the table after renaming. ### ANALYZE TABLE Requires the `INSERT` and `SELECT` privileges for the table. ### LOCK STATS Requires the `INSERT` and `SELECT` privileges for the table. ### UNLOCK STATS Requires the `INSERT` and `SELECT` privileges for the table. ### SHOW `SHOW CREATE TABLE` requires any single privilege to the table. `SHOW CREATE VIEW` requires the `SHOW VIEW` privilege. `SHOW GRANTS` requires the `SELECT` privilege to the `mysql` database. If the target user is current user, `SHOW GRANTS` does not require any privilege. `SHOW PROCESSLIST` requires the `PROCESS` privilege to show connections belonging to other users. `SHOW IMPORT JOB` requires the `SUPER` privilege to show connections belonging to other users. Otherwise, it only shows jobs created by the current user. `SHOW STATS_LOCKED` requires the `SELECT` privilege to the `mysql.stats_table_locked` table. ### CREATE ROLE/USER `CREATE ROLE` requires the `CREATE ROLE` privilege. `CREATE USER` requires the `CREATE USER` privilege. ### DROP ROLE/USER `DROP ROLE` requires the `DROP ROLE` privilege. `DROP USER` requires the `CREATE USER` privilege. ### ALTER USER Requires the `CREATE USER` privilege. ### GRANT Requires the `GRANT` privilege with the privileges granted by `GRANT`. Requires additional `CREATE USER` privilege to create a user implicitly. `GRANT ROLE` requires `SUPER` or `ROLE_ADMIN` privilege. ### REVOKE Requires the `GRANT` privilege and those privileges targeted by the `REVOKE` statement. `REVOKE ROLE` requires `SUPER` or `ROLE_ADMIN` privilege. ### SET GLOBAL Requires `SUPER` or `SYSTEM_VARIABLES_ADMIN` privilege to set global variables. ### ADMIN Requires `SUPER` privilege. ### SET DEFAULT ROLE Requires `SUPER` privilege. ### KILL Requires `SUPER` or `CONNECTION_ADMIN` privilege to kill other user sessions. ### CREATE RESOURCE GROUP Requires `SUPER` or `RESOURCE_GROUP_ADMIN` privilege. ### ALTER RESOURCE GROUP Requires `SUPER` or `RESOURCE_GROUP_ADMIN` privilege. ### DROP RESOURCE GROUP Requires `SUPER` or `RESOURCE_GROUP_ADMIN` privilege. ### CALIBRATE RESOURCE Requires `SUPER` or `RESOURCE_GROUP_ADMIN` privilege. ### SET RESOURCE GROUP When the system variable [`tidb_resource_control_strict_mode`](/system-variables.md#tidb_resource_control_strict_mode-new-in-v820) is set to `ON`, you need to have the `SUPER` or `RESOURCE_GROUP_ADMIN` or `RESOURCE_GROUP_USER` privilege to execute this statement. ## Implementation of the privilege system ### Privilege table The following [`mysql` system tables](/mysql-schema/mysql-schema.md) are special because all the privilege-related data is stored in them: - `mysql.user` (user account, global privilege) - `mysql.db` (database-level privilege) - `mysql.tables_priv` (table-level privilege) - `mysql.columns_priv` (column-level privilege; supported starting from v8.5.6) These tables contain the effective range and privilege information of the data. For example, in the `mysql.user` table: ```sql SELECT User,Host,Select_priv,Insert_priv FROM mysql.user LIMIT 1; ``` ``` +------|------|-------------|-------------+ | User | Host | Select_priv | Insert_priv | +------|------|-------------|-------------+ | root | % | Y | Y | +------|------|-------------|-------------+ 1 row in set (0.00 sec) ``` In this record, `Host` and `User` determine that the connection request sent by the `root` user from any host (`%`) can be accepted. `Select_priv` and `Insert_priv` mean that the user has global `Select` and `Insert` privilege. The effective range in the `mysql.user` table is global. `Host` and `User` in `mysql.db` determine which databases users can access. The effective range is the database. > **Note:** > > It is recommended to only update the privilege tables via the supplied syntax such as `GRANT`, `CREATE USER` and `DROP USER`. Making direct edits to the underlying privilege tables will not automatically update the privilege cache, leading to unpredictable behavior until `FLUSH PRIVILEGES` is executed. ### Connection verification When the client sends a connection request, TiDB server will verify the login operation. TiDB server first checks the `mysql.user` table. If a record of `User` and `Host` matches the connection request, TiDB server then verifies the `authentication_string`. User identity is based on two pieces of information: `Host`, the host that initiates the connection, and `User`, the user name. If the user name is not empty, the exact match of user named is a must. `User`+`Host` may match several rows in `user` table. To deal with this scenario, the rows in the `user` table are sorted. The table rows will be checked one by one when the client connects; the first matched row will be used to verify. When sorting, Host is ranked before User. ### Request verification When the connection is successful, the request verification process checks whether the operation has the privilege. For database-related requests (`INSERT`, `UPDATE`), the request verification process first checks the user's global privileges in the `mysql.user` table. If the privilege is granted, you can access directly. If not, check the `mysql.db` table. The `user` table has global privileges regardless of the default database. For example, the `DELETE` privilege in `user` can apply to any row, table, or database. In the `db` table, an empty user is to match the anonymous user name. Wildcards are not allowed in the `User` column. The value for the `Host` and `Db` columns can use `%` and `_`, which can use pattern matching. Data in the `user` and `db` tables is also sorted when loaded into memory. The use of `%` in `tables_priv` and `columns_priv` is similar, but column value in `Db`, `Table_name` and `Column_name` cannot contain `%`. The sorting is also similar when loaded. ### Time of effect When TiDB starts, some privilege-check tables are loaded into memory, and then the cached data is used to verify the privileges. Executing privilege management statements such as `GRANT`, `REVOKE`, `CREATE USER`, `DROP USER` will take effect immediately. Manually editing tables such as `mysql.user` with statements such as `INSERT`, `DELETE`, `UPDATE` will not take effect immediately. This behavior is compatible with MySQL, and privilege cache can be updated with the [`FLUSH PRIVILEGES`](/sql-statements/sql-statement-flush-privileges.md) statement.