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

Content-type description, that is, the type of HTTP request header

To learn content-type, you must first know what i...

Quickly install MySQL5.7 compressed package on Windows

This article shares with you how to install the M...

Detailed explanation of NodeJS modularity

Table of contents 1. Introduction 2. Main text 2....

Application of Beautiful Style Sheets in XHTML+CSS Web Page Creation

This is an article written a long time ago. Now it...

Detailed explanation of the process of using docker to build minio and java sdk

Table of contents 1minio is simple 2 Docker build...

Use MySQL master-slave configuration to achieve read-write separation and reduce database pressure

In order to handle a large number of concurrent v...

Windows 10 1903 error 0xc0000135 solution [recommended]

Windows 10 1903 is the latest version of the Wind...

Detailed explanation of setting resource cache in nginx

I have always wanted to learn about caching. Afte...

Use Smart CSS to apply styles based on the user's scroll position

By adding the current scroll offset to the attrib...

Attributes in vue v-for loop object

Table of contents 1. Values ​​within loop objects...

Functions in TypeScript

Table of contents 1. Function definition 1.1 Func...

Detailed explanation of the data responsiveness principle of Vue

This article is mainly for those who do not under...

Detailed explanation of hosts file configuration on Linux server

Linux server hosts file configuration The hosts f...

Detailed explanation of the execution differences between count(1), count(*) and count(column name)

Implementation effect: 1. count(1) and count(*) W...

How to simply encapsulate axios in vue

Inject axios into Vue import axios from 'axio...