- Introduction
- 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
- Deploy
- Hardware Recommendations
- From Binary Tarball
- Orchestrated Deployment
- Geographic Redundancy
- Data Migration with Ansible
- Configure
- Secure
- Transport Layer Security (TLS)
- Generate Self-signed Certificates
- Monitor
- Migrate
- Maintain
- Common Ansible Operations
- Backup and Restore
- Use BR (recommended)
- Identify Abnormal Queries
- Scale
- Upgrade
- Troubleshoot
- Reference
- SQL
- MySQL Compatibility
- SQL Language Structure
- Attributes
- Data Types
- 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
- Window Functions
- Miscellaneous Functions
- Precision Math
- List of Expressions for Pushdown
- SQL Statements
ADD COLUMN
ADD INDEX
ADMIN
ADMIN CANCEL DDL
ADMIN CHECKSUM TABLE
ADMIN CHECK [TABLE|INDEX]
ADMIN SHOW DDL [JOBS|QUERIES]
ALTER DATABASE
ALTER INSTANCE
ALTER TABLE
ALTER USER
ANALYZE TABLE
BEGIN
CHANGE COLUMN
COMMIT
CREATE DATABASE
CREATE INDEX
CREATE ROLE
CREATE TABLE LIKE
CREATE TABLE
CREATE USER
CREATE VIEW
DEALLOCATE
DELETE
DESC
DESCRIBE
DO
DROP COLUMN
DROP DATABASE
DROP INDEX
DROP ROLE
DROP TABLE
DROP USER
DROP VIEW
EXECUTE
EXPLAIN ANALYZE
EXPLAIN
FLUSH PRIVILEGES
FLUSH STATUS
FLUSH TABLES
GRANT <privileges>
GRANT <role>
INSERT
KILL [TIDB]
LOAD DATA
LOAD STATS
MODIFY COLUMN
PREPARE
RECOVER TABLE
RENAME INDEX
RENAME TABLE
REPLACE
REVOKE <privileges>
REVOKE <role>
ROLLBACK
SELECT
SET DEFAULT ROLE
SET [NAMES|CHARACTER SET]
SET PASSWORD
SET ROLE
SET TRANSACTION
SET [GLOBAL|SESSION] <variable>
SHOW ANALYZE STATUS
SHOW CHARACTER SET
SHOW COLLATION
SHOW [FULL] COLUMNS FROM
SHOW CREATE TABLE
SHOW CREATE USER
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 STATUS
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
- Partitioning
- Character Set
- SQL Mode
- Views
- Configuration
- Security
- Transactions
- System Databases
- Errors Codes
- Supported Client Drivers
- Garbage Collection (GC)
- Performance
- Overview
- Understanding the Query Execution Plan
- The Blocklist of Optimization Rules and Expression Pushdown
- Introduction to Statistics
- TopN and Limit Push Down
- Optimizer Hints
- Follower Read
- Check the TiDB Cluster Status Using SQL Statements
- Execution Plan Binding
- Statement Summary Table
- Tune TiKV
- Operating System Tuning
- Column Pruning
- Key Monitoring Metrics
- Alert Rules
- Best Practices
- TiSpark
- TiKV
- TiFlash
- TiDB Binlog
- Tools
- Overview
- Use Cases
- Download
- TiDB Operator
- Table Filter
- Backup & Restore (BR)
- Mydumper
- Syncer
- Loader
- Data Migration
- TiDB Lightning
- sync-diff-inspector
- PD Control
- PD Recover
- TiKV Control
- TiDB Control
- TiDB in Kubernetes
- FAQs
- Support
- Contribute
- Releases
- All Releases
- v3.1
- v3.0
- v2.1
- v2.0
- v1.0
- Glossary
You are viewing the documentation of an older version of the TiDB database (TiDB v3.1).
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 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,password 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,password FROM mysql.user WHERE user='idontexist';
+------------+------+----------+
| user | host | password |
+------------+------+----------+
| idontexist | % | |
+------------+------+----------+
1 row in set (0.00 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';
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
+-------------------------------------------------------------+
| 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.%' |
+------------------------------------------------------------------+
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 |
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 |
Reload | ReloadPriv | Executes the FLUSH statement |
Config | ConfigPriv | Dynamically reloads configuration |
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 theALTER
privilege for the corresponding table. - For statements except
ALTER...DROP
andALTER...RENAME TO
, users must have theINSERT
andCREATE
privileges for the corresponding table. - For the
ALTER...DROP
statement, users must have theDROP
privilege for the corresponding table. - For the
ALTER...RENAME TO
statement, users must have theDROP
privilege for the table before renaming, and theCREATE
andINSERT
privileges for the table after renaming.
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.
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.
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.
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
privilege.
REVOKE
Requires the GRANT
privilege and those privileges targeted by the REVOKE
statement.
REVOKE ROLE
requires SUPER
privilege.
SET GLOBAL
Requires SUPER
privilege to set global variables.
ADMIN
Requires SUPER
privilege.
SET DEFAULT ROLE
Requires SUPER
privilege.
KILL
Requires SUPER
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.
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 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;