Note:
This topic has been translated from a Chinese forum by GPT and might contain errors.Original topic: 【SOP系列 38】锁定用户方法
|
username: Billmay表妹
Thanks to @tracy0984 for the contribution
TiDB Version: v6.1.2
Currently, TiDB does not support using the ALTER USER command to lock users.
Based on tests, you can lock a user by directly modifying the account_lock column value to ‘Y’ in the mysql.user table.
Test Results
-- Using the alter user command to lock a user, the SQL execution will not report an error, but the database will generate a warning indicating that it does not recognize the user lock. Further connection tests confirm that the user was not successfully locked.
mysql> create user test identified by "test";
Query OK, 0 rows affected (0.10 sec)
mysql> select user,account_locked from mysql.user;
+------+----------------+
| user | account_locked |
+------+----------------+
| root | N |
| test | N |
+------+----------------+
2 rows in set (0.00 sec)
mysql> alter user test account lock;
Query OK, 0 rows affected, 1 warning (0.05 sec)
mysql> show warnings;
+---------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message |
+---------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Warning | 1064 | You have an error in your SQL syntax; check the manual that corresponds to your TiDB version for the right syntax to use line 1 column 28 near ""TiDB does not support PASSWORD EXPIRE and ACCOUNT LOCK now, they would be parsed but ignored. |
+---------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)
[tidb@sjzx-test-moban ~]$ mysql -utest -p -P4000 -h 10.0.32.6
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 437
Server version: 5.7.25-TiDB-v6.1.2 TiDB Server (Apache License 2.0) Community Edition, MySQL 5.7 compatible
Copyright (c) 2000, 2022, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql>
-- Using the method of directly modifying the account_lock column value to 'Y' in the mysql.user table can lock the user. Further connection tests confirm that the user will receive an error when trying to connect to the database.
[tidb@sjzx-test-moban ~]$ mysql -uroot -p -P4000 -h 10.0.32.6
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 435
Server version: 5.7.25-TiDB-v6.1.2 TiDB Server (Apache License 2.0) Community Edition, MySQL 5.7 compatible
Copyright (c) 2000, 2022, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> update mysql.user set account_locked ='Y' where user='test';
Query OK, 1 row affected (0.02 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.04 sec)
mysql> exit
Bye
[tidb@sjzx-test-moban ~]$ mysql -utest -p -P4000 -h 10.0.32.6
Enter password:
ERROR 1045 (28000): Access denied for user 'test'@'10.0.32.4' (using password: YES)
Summary
Methods to lock a user in TiDB:
UPDATE MYSQL.USER SET ACCOUNT_LOCKED = 'Y' WHERE USER='username';
FLUSH PRIVILEGES;
To unlock a user, you also need to directly modify the table:
UPDATE MYSQL.USER SET ACCOUNT_LOCKED = 'N' WHERE USER='username';
FLUSH PRIVILEGES;
|
username: 
|
username: 