今天遇到一個需求：修改MySQL用戶的權限，需要限制特定IP地址才能訪問，第一次遇到這類需求，結果在測試過程，使用更新系統(tǒng)權限報發(fā)現(xiàn)出現(xiàn)了一些問題， 具體演示如下. 下面測試環(huán)境為MySQL 5.6.20. 如有其它版本與下面測試結果有出入，請以實際環(huán)境為準。

?

? 我們先創(chuàng)建一個測試用戶LimitIP，只允許192.168段的IP地址訪問，具體權限如下所示：

?

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 LimitIP@192.168.%?????????????????????????????????????????????????????????????????????????????????? |

+----------------------------------------------------------------------------------------------------------------+

| 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>

?

?

?

?

假設現(xiàn)在收到需求：這個用戶只允許這個IP地址192.168.103.17訪問，于是我打算更新mysql.user表，如下所示：

?

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 LimitIP@192.168.103.17?????????????????????????????????????????????????????????????????????????????????? |

+---------------------------------------------------------------------------------------------------------------------+

| GRANT?USAGE?ON *.* TO?'LimitIP'@'192.168.103.17' IDENTIFIED BY PASSWORD '*72DDE03E02CC55A9478A82F3F4EBE7F639249DEC' |

+---------------------------------------------------------------------------------------------------------------------+

1 row?in?set (0.00 sec)

?

mysql> 

?

?

?

上面測試發(fā)現(xiàn)，如果這樣只修改mysql.user表， 那么之前的權限沒有了，如下所示，如果你查詢mysql.db、 mysql.tables_priv 發(fā)現(xiàn)Host的字段值依然為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

?

?

所以我繼續(xù)修改 mysql.db、 mysql.tables_priv 表，然后測試驗證終于OK了（請見下面測試步驟），當然如果賬戶的權限不止這幾個層面，你可能還必須修改例如mysql.columns_priv、mysql.procs_priv等表

?

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 LimitIP@192.168.103.17?????????????????????????????????????????????????????????????????????????????????? |

+---------------------------------------------------------------------------------------------------------------------+

| 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> 

?

?

?

如果需要修改用戶的IP限制，其實更新mysql相關權限表不是上上策，其實有更好的方法，那就是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 LimitIP@192.168.103.18?????????????????????????????????????????????????????????????????????????????????? |

+---------------------------------------------------------------------------------------------------------------------+

| 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> 

轉載于:https://www.cnblogs.com/kerrycode/p/7428800.html

總結

以上是生活随笔為你收集整理的MySQL 修改账号的IP限制条件的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。