MySQL Modify the IP restrictions of the account

Source: Internet
Author: User
Tags flush

Meet a demand today: Modify the permissions of the MySQL user, you need to restrict the specific IP address to access, the first encounter such a requirement, the results in the test process, the use of update system permission to find some problems, the following specific demonstration. The following test environment is for MySQL 5.6.20. If there is any discrepancy between the other versions and the test results below, please take the actual circumstances as the subject.

We first create a test user Limitip, which allows only 192.168 segments of IP address access, with the following specific permissions:

Mysql> GRANT SELECT on mydb.* to [e-mail protected] ' 192.168.% ' identified by ' limitip ';
Query OK, 0 rows affected (0.01 sec)
Mysql> GRANT INSERT, Update,delete on MYDB.KKK to [email protected] ' 192.168.% ';
Query OK, 0 rows Affected (0.00 sec)
mysql> flush Privileges;
Query OK, 0 rows Affected (0.00 sec)
Mysql> Show grants for [email protected] ' 192.168.% ';
+-------------------------------------------------------------------------------------------------------------- --+
| Grants for [email protected]% |
+-------------------------------------------------------------------------------------------------------------- --+
| GRANT USAGE on *. 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 you receive the demand now: This user only allows this IP address 192.168.103.17 access, so I intend 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
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 [email protected] ' 192.168.103.17 ';
+-------------------------------------------------------------------------------------------------------------- -------+
| Grants for [email protected] |
+-------------------------------------------------------------------------------------------------------------- -------+
| GRANT USAGE on *. limitip ' @ ' 192.168.103.17 ' identified by PASSWORD ' *72dde03e02cc55a9478a82f3f4ebe7f639249dec ' |
+-------------------------------------------------------------------------------------------------------------- -------+
1 row in Set (0.00 sec)

The above test found that if this only modifies the Mysql.user table, then the previous permissions are not, as shown below, if you query mysql.db, Mysql.tables_priv found the field value of the 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)
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: [Email protected]
timestamp:0000-00-00 00:00:00
Table_priv:insert,update,delete
1 row in Set (0.00 sec)
No query specified

So I continue to modify the mysql.db, Mysql.tables_priv table, and then the test verification finally OK (see the test steps below), of course, if the account has more permissions than these levels, you may also have to modify such as Mysql.columns_priv, Mysql.procs_priv and other tables

Mysql> Show grants for [email protected] ' 192.168.% ';
ERROR 1141 (42000): There is no such grant defined for user ' limitip ' on Host ' 192.168.% '
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 [email protected] ' 192.168.103.17 ';
+-------------------------------------------------------------------------------------------------------------- -------+
| Grants for [email protected] |
+-------------------------------------------------------------------------------------------------------------- -------+
| GRANT USAGE on *. 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)

If you need to modify the user's IP restrictions, in fact, update the MySQL related permissions table is not the best policy, there is a better way, 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 [email protected] |
+-------------------------------------------------------------------------------------------------------------- -------+
| GRANT USAGE on *. 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 Modify the IP restrictions of the account

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.