TiDB Self-Managed
Learning Center
Contact Us
Sign InStart for Free

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.

Grant privileges

The GRANT 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.

GRANT SELECT ON test.* TO 'xxx'@'%';

Use the following statement to grant the xxx user all privileges on all databases:

GRANT ALL PRIVILEGES ON *.* TO 'xxx'@'%';

By default, GRANT statements will return an error if the user specified does not exist. This behavior depends on if the SQL Mode NO_AUTO_CREATE_USER is specified:

mysql> SET sql_mode=DEFAULT;
Query OK, 0 rows affected (0.00 sec)

mysql> 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)

mysql> SELECT * FROM mysql.user WHERE user='idontexist';
Empty set (0.00 sec)

mysql> GRANT ALL PRIVILEGES ON test.* TO 'idontexist';
ERROR 1105 (HY000): You are not allowed to create a user with GRANT

mysql> 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:

mysql> 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)

mysql> 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)

mysql> SELECT * FROM mysql.user WHERE user='idontexist';
Empty set (0.00 sec)

mysql> GRANT ALL PRIVILEGES ON test.* TO 'idontexist';
Query OK, 1 row affected (0.05 sec)

mysql> 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 to grant privileges to databases.

mysql> GRANT ALL PRIVILEGES ON `te%`.* TO genius;
Query OK, 0 rows affected (0.00 sec)

mysql> 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 statement enables system administrators to revoke privileges from the user accounts.

The REVOKE statement corresponds with the REVOKE statement:

REVOKE ALL PRIVILEGES ON `test`.* FROM 'genius'@'localhost';

mysql> 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:

mysql> 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:

mysql> 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

mysql> 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:

mysql> 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:

SHOW GRANTS; -- show grants for the current user

+-------------------------------------------------------------+
| Grants for User                                             |
+-------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' WITH GRANT OPTION |
+-------------------------------------------------------------+
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.

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:

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
  • ROLE_ADMIN
  • CONNECTION_ADMIN
  • SYSTEM_VARIABLES_ADMIN

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.

Privileges required for TiDB operations

You can check privileges of TiDB users in the INFORMATION_SCHEMA.USER_PRIVILEGES table. For example:

mysql> 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.

BACKUP

Requires the SUPER or BACKUP_ADMIN privilege.

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.

DROP DATABASE

Requires the DROP privilege for the table.

DROP INDEX

Requires the INDEX privilege for the table.

DROP TABLES

Requires the DROP privilege for the table.

LOAD DATA

Requires the INSERT privilege for the table.

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.

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 SUPER to show connections belonging to other users.

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.

Implementation of the privilege system

Privilege table

The following system tables 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; not currently supported)

These tables contain the effective range and privilege information of the data. For example, in the mysql.user table:

mysql> 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.

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 following statement:

FLUSH PRIVILEGES;
Download PDF