感谢 @tracy0984 的贡献
TiDB版本：v6.1.2
TiDB数据库目前不支持使用alter user命令锁定用户。
经测试，可以使用直接修改表mysql.user的account_lock列值为Y的方式实现锁定用户功能。
测试结果
-- 使用alter user命令锁定用户，SQL执行不会报错，但是数据库汇产生warning信息，提示不知此锁定用户。再进行连接测试，确认用户并没有成功锁定。
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>
-- 使用直接修改表mysql.user的account_lock列值为Y的方式可以实现锁定用户功能。再进行连接测试，确认用户连接数据库会报错。
[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)
总结
TiDB数据库锁定用户方法：
UPDATA MYSQL.USER SET ACCOUNT_LOCKED = 'Y' where USER='username';
FLUSH PRIVILEGES;
解锁用户方法也需要直接修改表：
UPDATA MYSQL.USER SET ACCOUNT_LOCKED = 'N' where USER='username';
FLUSH PRIVILEGES;
