How to modify the IP address restrictions of an account in MySQL

How to modify the IP address restrictions of an account in MySQL

Preface

Recently, I encountered a requirement in my work: To modify the permissions of MySQL users, you must restrict specific IP addresses for access. The first requirement was met, an error occurred when updating system permissions. The following is an example.

Note:The following test environment is MySQL 5.6.20. If other versions differ from the following test results, refer to the actual environment.

First, create a test user LimitIP, which only allows access from the IP address segment 192.168. The specific permissions are as follows:

mysql> GRANT SELECT ON MyDB.* TO LimitIP@'192.168.%' IDENTIFIED BY 'LimitIP';Query OK, 0 rows affected (0.01 sec) mysql> GRANT INSERT ,UPDATE,DELETE ON MyDB.kkk TO LimitIP@'192.168.%';Query OK, 0 rows affected (0.00 sec) mysql> mysql> flush privileges;Query OK, 0 rows affected (0.00 sec) mysql>  mysql> show grants for LimitIP@'192.168.%';+----------------------------------------------------------------------------------------------------------------+| Grants for LimitIP@192.168.%                     |+----------------------------------------------------------------------------------------------------------------+| GRANT USAGE ON *.* TO 'LimitIP'@'192.168.%' IDENTIFIED BY PASSWORD '*72DDE03E02CC55A9478A82F3F4EBE7F639249DEC' || GRANT SELECT ON `MyDB`.* TO 'LimitIP'@'192.168.%'                || GRANT INSERT, UPDATE, DELETE ON `MyDB`.`kkk` TO 'LimitIP'@'192.168.%'           |+----------------------------------------------------------------------------------------------------------------+3 rows in set (0.00 sec) mysql>

Assume that the user can only access the IP address 192.168.103.17, so I plan to update the mysql. user table, as shown below:

mysql> select user, host from mysql.user where user='LimitIP';+---------+-----------+| user | host  |+---------+-----------+| LimitIP | 192.168.% |+---------+-----------+1 row in set (0.00 sec) mysql> update mysql.user set host='192.168.103.17' where user='LimitIP';Query OK, 1 row affected (0.02 sec)Rows matched: 1 Changed: 1 Warnings: 0 mysql> flush privileges;Query OK, 0 rows affected (0.01 sec) mysql> select user, host from user where user='LimitIP';ERROR 1046 (3D000): No database selectedmysql> use mysql;Reading table information for completion of table and column namesYou can turn off this feature to get a quicker startup with -A Database changedmysql> select user, host from user where user='LimitIP';+---------+----------------+| user | host   |+---------+----------------+| LimitIP | 192.168.103.17 |+---------+----------------+1 row in set (0.00 sec) mysql> show grants for LimitIP@'192.168.103.17';+---------------------------------------------------------------------------------------------------------------------+| Grants for LimitIP@192.168.103.17                     |+---------------------------------------------------------------------------------------------------------------------+| GRANT USAGE ON *.* TO 'LimitIP'@'192.168.103.17' IDENTIFIED BY PASSWORD '*72DDE03E02CC55A9478A82F3F4EBE7F639249DEC' |+---------------------------------------------------------------------------------------------------------------------+1 row in set (0.00 sec) mysql> 

The test above found that if only mysql is modified in this way. user table, the previous permission is no longer available, as shown below, if you query mysql. db, mysql. tables_priv found that the Host field value is still 192.168.%

mysql> select * from mysql.db where user='LimitIP'\G;*************************** 1. row ***************************     Host: 192.168.%     Db: MyDB     User: LimitIP   Select_priv: Y   Insert_priv: N   Update_priv: N   Delete_priv: N   Create_priv: N   Drop_priv: N   Grant_priv: N  References_priv: N   Index_priv: N   Alter_priv: NCreate_tmp_table_priv: N  Lock_tables_priv: N  Create_view_priv: N  Show_view_priv: N Create_routine_priv: N Alter_routine_priv: N   Execute_priv: N   Event_priv: N   Trigger_priv: N1 row in set (0.00 sec) ERROR: No query specified mysql> select * from mysql.tables_priv where user='LimitIP'\G;*************************** 1. row ***************************  Host: 192.168.%   Db: MyDB  User: LimitIP Table_name: kkk Grantor: root@localhost Timestamp: 0000-00-00 00:00:00 Table_priv: Insert,Update,DeleteColumn_priv: 1 row in set (0.00 sec) ERROR: No query specified

So I continue to modify mysql. db, mysql. tables_priv table, and test and verification are finally OK (see the test procedure below). Of course, if your account has more than these permissions, you may have to modify such permissions as mysql. columns_priv, mysql. procs_priv and other tables

mysql> show grants for LimitIP@'192.168.%';ERROR 1141 (42000): There is no such grant defined for user 'LimitIP' on host '192.168.%'mysql> mysql> mysql> update mysql.db set host='192.168.103.17' where user='LimitIP';Query OK, 1 row affected (0.00 sec)Rows matched: 1 Changed: 1 Warnings: 0 mysql> update mysql.tables_priv set host='192.168.103.17' where user='LimitIP';Query OK, 1 row affected (0.00 sec)Rows matched: 1 Changed: 1 Warnings: 0 mysql> flush privileges;Query OK, 0 rows affected (0.00 sec) mysql> show grants for LimitIP@'192.168.103.17';+---------------------------------------------------------------------------------------------------------------------+| Grants for LimitIP@192.168.103.17                     |+---------------------------------------------------------------------------------------------------------------------+| GRANT USAGE ON *.* TO 'LimitIP'@'192.168.103.17' IDENTIFIED BY PASSWORD '*72DDE03E02CC55A9478A82F3F4EBE7F639249DEC' || GRANT SELECT ON `MyDB`.* TO 'LimitIP'@'192.168.103.17'                || GRANT INSERT, UPDATE, DELETE ON `MyDB`.`kkk` TO 'LimitIP'@'192.168.103.17'           |+---------------------------------------------------------------------------------------------------------------------+3 rows in set (0.00 sec) mysql> 

If you need to modify the USER's IP address limit, updating the mysql-related permission table is not the best practice. In fact, there is a better method, that is, rename user Syntax.

mysql> RENAME USER 'LimitIP'@'192.168.103.17' TO 'LimitIP'@'192.168.103.18';Query OK, 0 rows affected (0.00 sec) mysql> FLUSH PRIVILEGES;Query OK, 0 rows affected (0.00 sec) mysql> show grants for 'LimitIP'@'192.168.103.18';+---------------------------------------------------------------------------------------------------------------------+| Grants for LimitIP@192.168.103.18                     |+---------------------------------------------------------------------------------------------------------------------+| GRANT USAGE ON *.* TO 'LimitIP'@'192.168.103.18' IDENTIFIED BY PASSWORD '*72DDE03E02CC55A9478A82F3F4EBE7F639249DEC' || GRANT SELECT ON `MyDB`.* TO 'LimitIP'@'192.168.103.18'                || GRANT INSERT, UPDATE, DELETE ON `MyDB`.`kkk` TO 'LimitIP'@'192.168.103.18'           |+---------------------------------------------------------------------------------------------------------------------+3 rows in set (0.00 sec) mysql> 

Summary

The above is all the content of this article. I hope the content of this article will help you in your study or work. If you have any questions, please leave a message, thank you for your support.