TiDB数据库锁定用户方法

概述

介绍TiDB数据库锁定用户方法。

问题描述

TiDB版本：v6.1.2

TiDB数据库目前不支持使用alter user命令锁定用户。

ALTER USER | PingCAP Docs

经测试，可以使用直接修改表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;