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

Docker Modify Docker storage location Modify container image size limit operation

This seems to be no longer possible with the new ...

Detailed examples of variable and function promotion in JavaScript

js execution Lexical analysis phase: includes thr...

Vue3.0 implements the magnifying glass effect case study

The effect to be achieved is: fixed zoom in twice...

Vue3 encapsulates its own paging component

This article example shares the specific code of ...

HTML form tag tutorial (5): text field tag

<br />This tag is used to create a multi-lin...

Example of using negative margin to achieve average layout in CSS

For evenly distributed layouts, we generally use ...

Implementing a web calculator based on JavaScript

This article shares the specific code of JavaScri...

Several methods for js to determine the horizontal and vertical screen viewport detection of mobile terminals

Table of contents 1. How to obtain different view...

Detailed graphic instructions for downloading and installing the unzipped version of MySQL 5.7.18 and starting the MySQL service

Because the distribution package of MySQL Communi...

Linux kernel device driver proc file system notes

/***************** * proc file system************...

Import CSS files using judgment conditions

Solution 1: Use conditional import in HTML docume...

SQL Server database error 5123 solution

Because I have a database tutorial based on SQL S...

The whole process of configuring reverse proxy locally through nginx

Preface Nginx is a lightweight HTTP server that u...

WeChat applet to determine whether the mobile phone number is legal example code

Table of contents Scenario Effect Code Summarize ...

MySQL 8.0.11 MacOS 10.13 installation and configuration method graphic tutorial

The process of installing MySQL database and conf...