How to modify the IP restriction conditions of MySQL account

How to modify the IP restriction conditions of MySQL account

Preface

Recently, I encountered a requirement at work: modifying the permissions of MySQL users requires restricting access to specific IP addresses. This is the first time I have encountered such a requirement. As a result, during the test process, I found some problems when using the update system permissions report. The specific demonstration is as follows.

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

We first create a test user LimitIP, which only allows IP addresses in the 192.168 segment to access. 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 [email protected].% |
+----------------------------------------------------------------------------------------------------------------+
| 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>

Suppose now I receive a requirement: this user is only allowed to access 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 [email protected] |
+-------------------------------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'LimitIP'@'192.168.103.17' IDENTIFIED BY PASSWORD '*72DDE03E02CC55A9478A82F3F4EBE7F639249DEC' |
+-------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
 
mysql>

The above test found that if only the mysql.user table is modified, the previous permissions will be lost. As shown below, if you query mysql.db and mysql.tables_priv, you will find 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: 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 tested and verified that it was finally OK (please see the test steps below). Of course, if the account has more than these levels of permissions, you may also have to modify tables such as mysql.columns_priv, mysql.procs_priv, etc.

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 [email protected] |
+-------------------------------------------------------------------------------------------------------------------------------------+
| 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 the MySQL related permission table is not the best solution. In fact, there is a better way, which 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 [email protected] |
+-------------------------------------------------------------------------------------------------------------------------------------+
| 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>

Summarize

The above is the full content of this article. I hope that the content of this article can bring some help to your study or work. If you have any questions, you can leave a message to communicate. Thank you for your support of 123WORDPRESS.COM.

You may also be interested in:
  • MySQL index usage monitoring skills (worth collecting!)
  • Detailed explanation of MySQL row locks when encountering composite primary keys and multi-column indexes
  • Summary of situations where MySQL indexes will not be used
  • Will the index be used in the MySQL query condition?
  • Example analysis of the impact of MySQL index on sorting
  • Detailed explanation of the limitations and restrictions of MySQL partitioned tables
  • MySQL query statement uses limit to limit the number of rows queried
  • Solution to the problem of MySQL connection number exceeding the limit
  • Analysis of the Principle of MySQL Index Length Limit

<<:  CentOS method to modify the default ssh port number example

>>:  Several ways to run Python programs in the Linux background

Recommend

Detailed explanation of four solutions for implementing in-line scrolling on mobile devices

Discovering Needs If only part of an area is allo...

Implementation steps of mysql master-slave replication

Table of contents mysql master-slave replication ...

Solution to the problem of saving format in HTML TextArea

The format of textarea can be saved to the databas...

Vue implements the method of displaying percentage of echart pie chart legend

This article mainly introduces the pie chart data...

A brief discussion on the placement of script in HTML

I used to think that script could be placed anywh...

Tutorial on how to use profile in MySQL

What is a profile? We can use it when we want to ...

Detailed explanation of vite+ts to quickly build vue3 projects and introduce related features

Table of contents vite Build Configuration vite.c...

Detailed explanation of Apache SkyWalking alarm configuration guide

Apache SkyWalking Apache SkyWalking is an applica...

Linux uses NetworkManager to randomly generate your MAC address

Nowadays, whether you are on the sofa at home or ...

How to implement responsive layout in vue-cli

When we are doing front-end development, we will ...

Detailed introduction to nobody user and nologin in Unix/Linux system

What is the nobody user in Unix/Linux systems? 1....

Detailed explanation of using top command to analyze Linux system performance

Introduction to Linux top command The top command...

Detailed explanation of the use principle and calculation method of the umask command under Linux

Table of contents umask Umask usage principle 1. ...

How to Change Colors and Themes in Vim on Linux

Vim is a text editor that we use very often in Li...

Implementation of static website layout in docker container

Server placement It is recommended to use cloud s...