TiDB User Account Management
This document describes how to manage a TiDB user account.
User names and passwords
TiDB stores the user accounts in the table of the
mysql.user system database. Each account is identified by a user name and the client host. Each account may have a password.
You can connect to the TiDB server using the MySQL client, and use the specified account and password to login. For each user name, make sure that it contains no more than 32 characters.
mysql --port 4000 --user xxx --password
Or use the abbreviation of command line parameters:
mysql -P 4000 -u xxx -p
Add user accounts
You can create TiDB accounts in two ways:
- By using the standard account-management SQL statements intended for creating accounts and establishing their privileges, such as
- By manipulating the privilege tables directly with statements such as
DELETE. It is not recommended to use this method to create accounts, because it might lead to incomplete updates.
You can also create accounts by using third party GUI tools.
CREATE USER [IF NOT EXISTS] user [IDENTIFIED BY 'auth_string'];
After you assign the password, TiDB encrypts and stores the
auth_string in the
CREATE USER 'test'@'127.0.0.1' IDENTIFIED BY 'xxx';
The name of a TiDB account consists of a user name and a hostname. The syntax of the account name is 'user_name'@'host_name'.
user_nameis case sensitive.
host_nameis a hostname or IP address, which supports the wild card
_. For example, the hostname
'%'matches all hosts, and the hostname
'192.168.1.%'matches all hosts in the subnet.
The host supports fuzzy matching:
CREATE USER 'test'@'192.168.10.%';
test user is allowed to log in from any hosts on the
If the host is not specified, the user is allowed to log in from any IP. If no password is specified, the default is empty password:
CREATE USER 'test';
CREATE USER 'test'@'%' IDENTIFIED BY '';
If the specified user does not exist, the behavior of automatically creating users depends on
sql_mode. If the
GRANT statement will not create users with an error returned.
For example, assume that the
sql_mode does not include
NO_AUTO_CREATE_USER, and you use the following
CREATE USER and
GRANT statements to create four accounts:
CREATE USER 'finley'@'localhost' IDENTIFIED BY 'some_pass';
GRANT ALL PRIVILEGES ON *.* TO 'finley'@'localhost' WITH GRANT OPTION;
CREATE USER 'finley'@'%' IDENTIFIED BY 'some_pass';
GRANT ALL PRIVILEGES ON *.* TO 'finley'@'%' WITH GRANT OPTION;
CREATE USER 'admin'@'localhost' IDENTIFIED BY 'admin_pass';
GRANT RELOAD,PROCESS ON *.* TO 'admin'@'localhost';
CREATE USER 'dummy'@'localhost';
To see the privileges granted for an account, use the
SHOW GRANTS statement:
SHOW GRANTS FOR 'admin'@'localhost';
+-----------------------------------------------------+ | Grants for admin@localhost | +-----------------------------------------------------+ | GRANT RELOAD, PROCESS ON *.* TO 'admin'@'localhost' | +-----------------------------------------------------+
Remove user accounts
To remove a user account, use the
DROP USER statement:
DROP USER 'test'@'localhost';
This operation clears the user's records in the
mysql.user table and the related records in the privilege table.
Reserved user accounts
TiDB creates the
'root'@'%' default account during the database initialization.
Set account resource limits
Currently, TiDB does not support setting account resource limits.
Assign account passwords
TiDB stores passwords in the
mysql.user system database. Operations that assign or update passwords are permitted only to users with the
CREATE USER privilege, or, alternatively, privileges for the
mysql database (
INSERT privilege to create new accounts,
UPDATE privilege to update existing accounts).
To assign a password when you create a new account, use
CREATE USERand include an
CREATE USER 'test'@'localhost' IDENTIFIED BY 'mypass';
To assign or change a password for an existing account, use
SET PASSWORD FORor
SET PASSWORD FOR 'root'@'%' = 'xxx';
ALTER USER 'test'@'localhost' IDENTIFIED BY 'mypass';
Modify the configuration file:
Log in to the machine where one of the tidb-server instances is located.
confdirectory under the TiDB node deployment directory, and find the
Add the configuration item
securitysection of the configuration file. If there is no
securitysection, add the following two lines to the end of the tidb.toml configuration file:
[security] skip-grant-table = true
Stop the tidb-server process:
View the tidb-server process:
ps aux | grep tidb-server
Find the process ID (PID) corresponding to tidb-server and use the
killcommand to stop the process:
kill -9 <pid>
Start TiDB using the modified configuration:
scriptsdirectory under the TiDB node deployment directory.
Switch to the
rootaccount of the operating system.
run_tidb.shscript in the directory in the foreground.
Log in as
rootin a new terminal window and change the password.
mysql -h 127.0.0.1 -P 4000 -u root
Stop running the
run_tidb.shscript, remove the content added in the TiDB configuration file in step 1, and wait for tidb-server to start automatically.
Information related to users and privileges is stored in the TiKV server, and TiDB caches this information inside the process. Generally, modification of the related information through
GRANT, and other statements takes effect quickly within the entire cluster. If the operation is affected by some factors such as temporarily unavailable network, the modification will take effect in about 15 minutes because TiDB will periodically reload the cache information.
If you modified the privilege tables directly, run the following command to apply changes immediately:
For details, see Privilege Management.