mysql 修改ip权限,MySQL修改账号的IP限制条件

Bertha 。 2023-01-13 14:00 493阅读 0赞

![2455abb4d5d061d311480900161cd832.png][]

本文转载自微信公众号「DBA闲思杂想录」，作者潇湘隐者。转载本文请联系DBA闲思杂想录公众号。

在MySQL中，需要修改MySQL的一个用户的权限，需要限制特定IP地址才能访问，下面结合简单的案例，具体演示一下. 下面测试环境为MySQL 5.6.20. 其它版本应该是一样的。如有其它版本与下面测试结果有出入，请以实际环境为准。

我们先创建一个测试用户LimitIP，只允许192.168段的IP地址访问，具体权限如下所示：

mysql>GRANTSELECTONMyDB.\*TOLimitIP@'192.168.%'IDENTIFIEDBY'LimitIP';

Query OK, 0 rowsaffected (0.01 sec)

mysql> GRANTINSERT,UPDATE,DELETEONMyDB.kkkTOLimitIP@'192.168.%';

Query OK, 0 rowsaffected (0.00 sec)

mysql>

mysql> flush privileges;

Query OK, 0 rowsaffected (0.00 sec)

mysql>

mysql> show grants forLimitIP@'192.168.%';

\+----------------------------------------------------------------------------------------------------------------+

| Grants forLimitIP@192.168.%                                                                                   |

\+----------------------------------------------------------------------------------------------------------------+

| GRANTUSAGEON\*.\*TO'LimitIP'@'192.168.%'IDENTIFIEDBYPASSWORD'\*72DDE03E02CC55A9478A82F3F4EBE7F639249DEC'|

| GRANTSELECTON\`MyDB\`.\*TO'LimitIP'@'192.168.%'|

| GRANTINSERT,UPDATE,DELETEON\`MyDB\`.\`kkk\`TO'LimitIP'@'192.168.%'|

\+----------------------------------------------------------------------------------------------------------------+

3 rowsinset(0.00 sec)

mysql>

![8dc2c636b912a0cddc268184bee7f683.png][]

假设现在收到一个需求：这个用户只允许这个IP地址192.168.103.17访问，于是我打算更新mysql.user表，如下所示：

mysql>selectuser, hostfrommysql.userwhereuser='LimitIP';

\+---------+-----------+

| user| host      |

\+---------+-----------+

| LimitIP | 192.168.% |

\+---------+-----------+

1 row inset(0.00 sec)

mysql> updatemysql.usersethost='192.168.103.17'whereuser='LimitIP';

Query OK, 1 row affected (0.02 sec)

Rowsmatched: 1  Changed: 1  Warnings: 0

mysql> flush privileges;

Query OK, 0 rowsaffected (0.01 sec)

mysql> selectuser, hostfromuserwhereuser='LimitIP';

ERROR 1046 (3D000): Nodatabaseselected

mysql> use mysql;

Reading tableinformationforcompletionoftableandcolumnnames

You can turn offthis featuretoget a quicker startupwith-A

Databasechanged

mysql> selectuser, hostfromuserwhereuser='LimitIP';

\+---------+----------------+

| user| host           |

\+---------+----------------+

| LimitIP | 192.168.103.17 |

\+---------+----------------+

1 row inset(0.00 sec)

mysql> show grants forLimitIP@'192.168.103.17';

\+---------------------------------------------------------------------------------------------------------------------+

| Grants forLimitIP@192.168.103.17                                                                                   |

\+---------------------------------------------------------------------------------------------------------------------+

| GRANTUSAGEON\*.\*TO'LimitIP'@'192.168.103.17'IDENTIFIEDBYPASSWORD'\*72DDE03E02CC55A9478A82F3F4EBE7F639249DEC'|

\+---------------------------------------------------------------------------------------------------------------------+

1 row inset(0.00 sec)

mysql>

![f48cc06dda43dba0614c1f5730283501.png][]

上面测试发现，如果这样只修改mysql.user表， 那么之前的权限没有了，如下所示，如果你查询mysql.db、 mysql.tables\_priv 发现Host的字段值依然为192.168.%

mysql>select\*frommysql.dbwhereuser='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 inset(0.00 sec)

ERROR:

Noquery specified

mysql> select\*frommysql.tables\_privwhereuser='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 inset(0.00 sec)

ERROR:

Noquery specified

所以我继续修改 mysql.db、 mysql.tables\_priv 表，然后测试验证终于OK了(请见下面测试步骤)，当然如果账户的权限不止这几个层面，你可能还必须修改例如mysql.columns\_priv、mysql.procs\_priv等表

mysql> show grantsforLimitIP@'192.168.%';

ERROR 1141 (42000): There isnosuchgrantdefinedforuser'LimitIP'onhost'192.168.%'

mysql>

mysql> updatemysql.dbsethost='192.168.103.17'whereuser='LimitIP';

Query OK, 1 row affected (0.00 sec)

Rowsmatched: 1  Changed: 1  Warnings: 0

mysql> updatemysql.tables\_privsethost='192.168.103.17'whereuser='LimitIP';

Query OK, 1 row affected (0.00 sec)

Rowsmatched: 1  Changed: 1  Warnings: 0

mysql> flush privileges;

Query OK, 0 rowsaffected (0.00 sec)

mysql> show grants forLimitIP@'192.168.103.17';

\+---------------------------------------------------------------------------------------------------------------------+

| GRANTUSAGEON\*.\*TO'LimitIP'@'192.168.103.17'IDENTIFIEDBYPASSWORD'\*72DDE03E02CC55A9478A82F3F4EBE7F639249DEC'|

| GRANTSELECTON\`MyDB\`.\*TO'LimitIP'@'192.168.103.17'|

| GRANTINSERT,UPDATE,DELETEON\`MyDB\`.\`kkk\`TO'LimitIP'@'192.168.103.17'|

\+---------------------------------------------------------------------------------------------------------------------+

3 rowsinset(0.00 sec)

mysql>

![8b0c18bf6b3557b39f9a696b97544336.png][]

如果需要修改用户的IP限制，其实更新mysql相关权限表不是上上策，其实有更好的方法，那就是RENAME USER Syntax

mysql> RENAMEUSER'LimitIP'@'192.168.103.17'TO'LimitIP'@'192.168.103.18';

Query OK, 0 rowsaffected (0.00 sec)

mysql> FLUSH PRIVILEGES;

Query OK, 0 rowsaffected (0.00 sec)

mysql> show grants for'LimitIP'@'192.168.103.18';

\+---------------------------------------------------------------------------------------------------------------------+

| Grants forLimitIP@192.168.103.18                                                                                   |

\+---------------------------------------------------------------------------------------------------------------------+

| GRANTUSAGEON\*.\*TO'LimitIP'@'192.168.103.18'IDENTIFIEDBYPASSWORD'\*72DDE03E02CC55A9478A82F3F4EBE7F639249DEC'|

| GRANTSELECTON\`MyDB\`.\*TO'LimitIP'@'192.168.103.18'|

| GRANTINSERT,UPDATE,DELETEON\`MyDB\`.\`kkk\`TO'LimitIP'@'192.168.103.18'|

\+---------------------------------------------------------------------------------------------------------------------+

3 rowsinset(0.00 sec)

mysql>

【编辑推荐】

【责任编辑：武晓燕 TEL：(010)68476606】

[2455abb4d5d061d311480900161cd832.png]: /images/20221022/fc2e2bd3f6b34ea8bb088cecdd0c48fb.png
[8dc2c636b912a0cddc268184bee7f683.png]: /images/20221022/dc7946394ce14327bb5ca6cb56bc7085.png
[f48cc06dda43dba0614c1f5730283501.png]: /images/20221022/196fc542ae7e4fc7b3730002bb9c990c.png
[8b0c18bf6b3557b39f9a696b97544336.png]: /images/20221022/8b1fcbf7c50849c1b67785e7db487f1a.png