PingCAP
  • Cloud
  • TiDB Academy
  • Docs
  • Success Stories
  • Blog
  • Free Download
PingCAP
  • Cloud
  • TiDB Academy
  • Docs
  • Success Stories
  • Blog
  • Free Download

Contact

中文
文档
v3.0 (stable) dev v2.1
  • Introduction
    • TiDB Introduction
    • Benchmarks
      • How to Test TiDB Using Sysbench
      • Sysbench Performance Test - v2.1 vs. v2.0
      • TPC-H 50G Performance Test - v2.1 vs. v2.0
      • DM Benchmark Report
  • Concepts
    • Architecture
    • Key Features
      • Horizontal Scalability
      • MySQL Compatible Syntax
      • Replicate from and to MySQL
      • Distributed Transactions with Strong Consistency
      • Cloud Native Architecture
      • Minimize ETL with HTAP
      • Fault Tolerance & Recovery with Raft
      • Automatic Rebalancing
      • Deployment and Orchestration with Ansible, Kubernetes, Docker
      • JSON Support
      • Spark Integration
      • Read Historical Data Without Restoring from Backup
      • Fast Import and Restore of Data
      • Hybrid of Column and Row Storage
      • SQL Plan Management
      • Open Source
      • Online Schema Changes
  • How-to
    • Get Started
      • Start a Local Cluster
        • From Binary
      • Explore SQL with TiDB
      • Import Example Database
      • Read Historical Data
      • TiDB Binlog Tutorial
      • TiDB Data Migration Tutorial
      • TiDB Lightning Tutorial
      • TiSpark Quick Start Guide
    • Deploy
      • Hardware Recommendations
      • From Binary Tarball
        • For Testing Environments
        • For Production Environments
      • Orchestrated Deployment
        • Ansible Deployment (Recommended)
        • Ansible Offline Deployment
        • Docker Deployment
      • Geographic Redundancy
        • Overview
        • Configure Location Awareness
      • Data Migration with Ansible
    • Configure
      • Time Zone
      • Memory Control
    • Secure
      • Transport Layer Security (TLS)
        • Enable TLS For MySQL Clients
        • Enable TLS Between TiDB Components
      • Generate Self-signed Certificates
    • Monitor
      • Overview
      • Monitor a TiDB Cluster
    • Migrate
      • Overview
      • Migrate from MySQL
        • Migrate the Full Data
        • Migrate the Incremental Data
      • Migrate from Aurora
      • Migrate from CSV
    • Maintain
      • Common Ansible Operations
      • Backup and Restore
      • Identify Slow Queries
    • Scale
      • Scale using Ansible
      • Scale a TiDB Cluster
    • Upgrade
      • Upgrade to TiDB 2.1
    • Troubleshoot
      • Troubleshoot Cluster Setup
      • Troubleshoot TiDB Lightning
  • Reference
    • SQL
      • MySQL Compatibility
      • SQL Language Structure
        • Literal Values
        • Schema Object Names
        • Keywords and Reserved Words
        • User-Defined Variables
        • Expression Syntax
        • Comment Syntax
      • Data Types
        • Overview
        • Default Values
        • Numeric Types
          • `BIT`
          • `BOOL|BOOLEAN`
          • `TINYINT`
          • `SMALLINT`
          • `MEDIUMINT`
          • `INT|INTEGER`
          • `BIGINT`
          • `DECIMAL`
          • `FLOAT`
          • `DOUBLE`
        • Date and Time Types
          • `DATE`
          • `DATETIME`
          • `TIMESTAMP`
          • `TIME`
          • `YEAR`
        • String Types
          • `CHAR`
          • `VARCHAR`
          • `TEXT`
          • `LONGTEXT`
          • `BINARY`
          • `VARBINARY`
          • `TINYBLOB`
          • `BLOB`
          • `MEDIUMBLOB`
          • `LONGBLOB`
          • `ENUM`
          • `SET`
        • JSON Type
      • Functions and Operators
        • Function and Operator Reference
        • Type Conversion in Expression Evaluation
        • Operators
        • Control Flow Functions
        • String Functions
        • Numeric Functions and Operators
        • Date and Time Functions
        • Bit Functions and Operators
        • Cast Functions and Operators
        • Encryption and Compression Functions
        • Information Functions
        • JSON Functions
        • Aggregate (GROUP BY) Functions
        • Miscellaneous Functions
        • Precision Math
      • SQL Statements
        • `ADD COLUMN`
        • `ADD INDEX`
        • `ADMIN`
        • `ALTER DATABASE`
        • `ALTER TABLE`
        • `ALTER USER`
        • `ANALYZE TABLE`
        • `BEGIN`
        • `COMMIT`
        • `CREATE DATABASE`
        • `CREATE INDEX`
        • `CREATE TABLE LIKE`
        • `CREATE TABLE`
        • `CREATE USER`
        • `DEALLOCATE`
        • `DELETE`
        • `DESC`
        • `DESCRIBE`
        • `DO`
        • `DROP COLUMN`
        • `DROP DATABASE`
        • `DROP INDEX`
        • `DROP TABLE`
        • `DROP USER`
        • `EXECUTE`
        • `EXPLAIN ANALYZE`
        • `EXPLAIN`
        • `FLUSH PRIVILEGES`
        • `FLUSH STATUS`
        • `FLUSH TABLES`
        • `GRANT <privileges>`
        • `INSERT`
        • `KILL [TIDB]`
        • `LOAD DATA`
        • `MODIFY COLUMN`
        • `PREPARE`
        • `RENAME INDEX`
        • `RENAME TABLE`
        • `REPLACE`
        • `REVOKE <privileges>`
        • `ROLLBACK`
        • `SELECT`
        • `SET [NAMES|CHARACTER SET]`
        • `SET PASSWORD`
        • `SET TRANSACTION`
        • `SET [GLOBAL|SESSION] <variable>`
        • `SHOW CHARACTER SET`
        • `SHOW COLLATION`
        • `SHOW [FULL] COLUMNS FROM`
        • `SHOW CREATE TABLE`
        • `SHOW DATABASES`
        • `SHOW ENGINES`
        • `SHOW ERRORS`
        • `SHOW [FULL] FIELDS FROM`
        • `SHOW GRANTS`
        • `SHOW INDEXES [FROM|IN]`
        • `SHOW INDEX [FROM|IN]`
        • `SHOW KEYS [FROM|IN]`
        • `SHOW PRIVILEGES`
        • `SHOW [FULL] PROCESSSLIST`
        • `SHOW SCHEMAS`
        • `SHOW [FULL] TABLES`
        • `SHOW TABLE REGIONS`
        • `SHOW TABLE STATUS`
        • `SHOW [GLOBAL|SESSION] VARIABLES`
        • `SHOW WARNINGS`
        • `SPLIT REGION`
        • `START TRANSACTION`
        • `TRACE`
        • `TRUNCATE`
        • `UPDATE`
        • `USE`
      • Constraints
      • Generated Columns
      • Character Set
    • Configuration
      • tidb-server
        • MySQL System Variables
        • TiDB Specific System Variables
        • Configuration Flags
        • Configuration File
      • pd-server
        • Configuration Flags
      • tikv-server
        • Configuration Flags
    • Security
      • Security Compatibility with MySQL
      • The TiDB Access Privilege System
      • TiDB User Account Management
    • Transactions
      • Overview
      • Transaction Model
      • Isolation Levels
    • System Databases
      • `mysql`
      • `information_schema`
    • Errors Codes
    • Supported Client Drivers
    • Garbage Collection (GC)
    • Performance
      • Overview
      • Understanding the Query Execution Plan
      • Introduction to Statistics
      • Optimizer Hints
      • Tune TiKV
    • Key Monitoring Metrics
      • Overview
      • TiDB
      • PD
      • TiKV
    • Alert Rules
    • Best Practices
      • HAProxy Best Practices
      • PD Scheduling Best Practices
    • TiSpark
    • TiDB Binlog
      • Overview
      • Deploy
      • Maintain
      • Monitor
      • Upgrade
      • Reparo
      • Binlog Slave Client
      • FAQ
    • Tools
      • Mydumper
      • Syncer
      • Loader
      • TiDB Data Migration
        • Overview
          • DM Overview
          • Restrictions
          • DM-worker
          • DM Relay Log
        • Features
          • Table Routing
          • Black and White Lists
          • Binlog Event Filter
          • Replication Delay Monitoring
          • Sharding Support
            • Introduction
            • Restrictions
            • Handle Sharding DDL Locks Manually
        • Usage Scenarios
          • Simple Scenario
          • Shard Merge Scenario
          • Shard Merge Best Practices
        • Deploy
        • Configure
          • Overview
          • Task Configuration
        • Manage the DM Cluster
          • Cluster Operations
          • Cluster Upgrade
        • Manage Replication Tasks
          • Manage Tasks
          • Precheck Tasks
          • Query Task Status
          • Skip or Replace Abnormal SQL Statements
        • Monitor
        • Migrate from MySQL compatible database
          • Migrate from Aurora
        • Troubleshoot
          • DM Troubleshooting
          • Error Description
          • Error Handling
        • FAQ
      • TiDB Lightning
        • Overview
        • Deployment
        • Checkpoints
        • Table Filter
        • CSV Support
        • Monitor
        • Troubleshoot
        • FAQ
      • sync-diff-inspector
      • PD Control
      • PD Recover
      • TiKV Control
      • TiDB Control
      • Download
  • FAQs
    • TiDB FAQs
    • TiDB Lightning FAQs
    • Upgrade FAQs
  • Support
    • Support Resources
    • Report an Issue
  • Contribute
    • Contribute to TiDB
    • Improve the Docs
  • Adopters
  • Roadmap
  • Releases
    • v2.1
      • 2.1.18
      • 2.1.17
      • 2.1.16
      • 2.1.15
      • 2.1.14
      • 2.1.13
      • 2.1.12
      • 2.1.11
      • 2.1.10
      • 2.1.9
      • 2.1.8
      • 2.1.7
      • 2.1.6
      • 2.1.5
      • 2.1.4
      • 2.1.3
      • 2.1.2
      • 2.1.1
      • 2.1 GA
      • 2.1 RC5
      • 2.1 RC4
      • 2.1 RC3
      • 2.1 RC2
      • 2.1 RC1
      • 2.1 Beta
    • v2.0
      • 2.0.11
      • 2.0.10
      • 2.0.9
      • 2.0.8
      • 2.0.7
      • 2.0.6
      • 2.0.5
      • 2.0.4
      • 2.0.3
      • 2.0.2
      • 2.0.1
      • 2.0
      • 2.0 RC5
      • 2.0 RC4
      • 2.0 RC3
      • 2.0 RC1
      • 1.1 Beta
      • 1.1 Alpha
    • v1.0
      • 1.0.8
      • 1.0.7
      • 1.0.6
      • 1.0.5
      • 1.0.4
      • 1.0.3
      • 1.0.2
      • 1.0.1
      • 1.0
      • Pre-GA
      • RC4
      • RC3
      • RC2
      • RC1

Privilege Management

TiDB supports MySQL 5.7’s privilege management system, including the syntax and privilege types. Starting with TiDB 3.0, support for SQL Roles is also available.

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 statement grants privileges to user accounts. It is recommended to first create a user, and then grant privileges. For example, use the following statement to grant the developer user the privilege to read the test database:

CREATE USER developer IDENTIFIED BY 'mypassword';
GRANT SELECT ON test.* TO 'developer';

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

GRANT ALL PRIVILEGES ON *.* TO 'developer';

Warning:

TiDB 2.1 does not support the NO_AUTO_CREATE_USER SQL Mode. This means that TiDB will automatically create a new user if one does not already exist. This is particularly risky, since typos can lead to users created with an empty password. While this behavior is compatible with earlier releases of MySQL, it is recommended to upgrade to TiDB 3.0 to prevent this issue.

Note:

Granting privileges to a database or table does not check if the database or table exists.

mysql> SELECT * FROM test.xxxx;
ERROR 1146 (42S02): Table 'test.xxxx' doesn't exist

mysql> GRANT ALL PRIVILEGES ON test.xxxx TO xxxx;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT user,host FROM mysql.tables_priv WHERE user='xxxx';
+------|------+
| user | host |
+------|------+
| xxxx | %    |
+------|------+
1 row in set (0.00 sec)

You can use fuzzy matching to grant privileges to databases and tables.

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';

Note:

To revoke privileges, you need the exact match. If the matching result cannot be found, an error will be displayed:

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
SHOW GRANTS FOR 'root'@'%'; -- show grants for a specific user

To be more precise, you can check the privilege information in the Grant table. For example, you can use the following steps to check if the test@% user has the Insert privilege on db1.t:

  1. Check if test@% has global Insert privilege:

    SELECT Insert_priv FROM mysql.user WHERE user='test' AND host='%';
  2. If not, check if test@% has database-level Insert privilege at db1:

    SELECT Insert_priv FROM mysql.db WHERE user='test' AND host='%';
  3. If the result is still empty, check whether test@% has table-level Insert privilege at db1.t:

    SELECT table_priv FROM mysql.tables_priv WHERE user='test' AND host='%' AND db='db1';

Privileges required for TiDB operations

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

Privilege type Privilege variable Privilege description
ALL AllPriv All the privileges
Drop DropPriv Deletes a schema or table
Index IndexPriv Creates or deletes an index
Alter AlterPriv Executes the ALTER statement
Super SuperPriv All the privileges
Grant GrantPriv Grants another user a privilege
Create CreatePriv Creates a schema or table
Select SelectPriv Reads the table data
Insert InsertPriv Inserts data to a table
Update UpdatePriv Updates the table data
Delete DeletePriv Deleted the table data
Trigger TriggerPriv /
Process ProcessPriv Displays the running task
Execute ExecutePriv Executes the EXECUTE statement
Drop Role DropRolePriv Executes DROP ROLE
Show View ShowViewPriv Executes SHOW CREATE VIEW
References ReferencesPriv /
Create View CreateViewPriv Creates a View
Create User CreateUserPriv Creates a user
Create Role CreateRolePriv Executes CREATE ROLE
Show Databases ShowDBPriv Shows the table status in the database

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.

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

DROP INDEX

Requires the INDEX privilege for the table.

DROP TABLES

Requires the DROP 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.

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.

REVOKE

Requires the SUPER privilege.

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.

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 automatially 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 Password.

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;

Limitations and constraints

Currently, the following privileges are not checked yet because they are less frequently used:

  • FILE
  • USAGE
  • SHUTDOWN
  • EXECUTE
  • PROCESS
  • INDEX
  • …

Note:

Column-level privileges are not implemented at this stage.

"Privilege Management" was last updated Jun 26 2019: dev, v3, v2.1: update privilege table wording (#1272) (a28dde5)
Edit this page Request docs changes

What’s on this page

Product

  • TiDB
  • TiSpark
  • Roadmap

Docs

  • Quick Start
  • Best Practices
  • FAQ
  • TiDB Tools
  • Release Notes

Resources

  • Blog
  • Weekly
  • GitHub
  • TiDB Community

Company

  • About
  • Careers
  • News
  • Contact Us
  • Privacy Policy
  • Terms of Service

Connect

  • Twitter
  • LinkedIn
  • Reddit
  • Google Group
  • Stack Overflow

© 2019 PingCAP. All Rights Reserved.

中文