MySQL Modify the IP restrictions of the account

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