MySQL changes the IP restriction conditions of the account to share examples

This article mainly introduces you to the relevant information on how to modify the IP restrictions of MySQL accounts. The article introduces it in detail through sample code. It has certain reference learning value for everyone's study or work. I hope it can help everyone.

Preface

I recently encountered a requirement at work: to modify the permissions of MySQL users, it is necessary to restrict access to specific IP addresses. This is the first time I encountered such a requirement, and the result was during the test. Some problems were found when updating system permissions. The specific demonstration is as follows.

Note: The test environment below is MySQL 5.6.20. If there are any discrepancies between other versions and the test results below, please refer to the actual environment.

We first create a test user LimitIP, which only allows access to IP addresses in the 192.168 segment. 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 now Received a requirement: This user only allows access to this IP address 192.168.103.17, so I plan to update the mysql.user table as follows:

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 selected
mysql> use mysql;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
 
Database changed
mysql> 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>

Tested above It is found that if you only modify the mysql.user table in this way, the previous permissions are gone, as shown below. If you query mysql.db and mysql.tables_priv, you will find that the field value of Host 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: N
Create_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: N
1 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,Delete
Column_priv: 
1 row in set (0.00 sec)
 
ERROR: 
No query specified

So I continued to modify the mysql.db and mysql.tables_priv tables, and then the test verification was finally OK (please see the test steps below). Of course, if the account has more than these levels, you may also have to modify, for example, 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 restrictions, updating mysql related permission tables is not the best way. In fact, there is a better way, then It’s 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>

Related recommendations:

Examples to explain mysql modification and enable remote connections

mysql modification database table Summary of usage examples

MySQL detailed examples of changing passwords and access restrictions

The above is the detailed content of MySQL changes the IP restriction conditions of the account to share examples. For more information, please follow other related articles on the PHP Chinese website!