今天遇到一个需求:修改MySQL用户的权限,需要限制特定IP地址才能访问,第一次遇到这类需求,结果在测试过程,使用更新系统权限报发现出现了一些问题, 具体演示如下. 下面测试环境为MySQL 5.6.20. 如有其它版本与下面测试结果有出入,请以实际环境为准。
我们先创建一个测试用户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>