MySQL修改账号的IP限制条件

数据库 MySQL
在MySQL中,需要修改MySQL的一个用户的权限,需要限制特定IP地址才能访问,下面结合简单的案例,具体演示一下. 下面测试环境为MySQL 5.6.20. 其它版本应该是一样的。

[[385212]]

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

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

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

  1. mysql> GRANT SELECT ON MyDB.* TO LimitIP@'192.168.%' IDENTIFIED BY 'LimitIP'
  2. Query OK, 0 rows affected (0.01 sec) 
  3.   
  4. mysql> GRANT INSERT ,UPDATE,DELETE ON MyDB.kkk TO LimitIP@'192.168.%'
  5. Query OK, 0 rows affected (0.00 sec) 
  6.   
  7. mysql>  
  8. mysql> flush privileges
  9. Query OK, 0 rows affected (0.00 sec) 
  10.   
  11. mysql>  
  12.   
  13. mysql> show grants for LimitIP@'192.168.%'
  14. +----------------------------------------------------------------------------------------------------------------+ 
  15. | Grants for LimitIP@192.168.%                                                                                   | 
  16. +----------------------------------------------------------------------------------------------------------------+ 
  17. GRANT USAGE ON *.* TO 'LimitIP'@'192.168.%' IDENTIFIED BY PASSWORD '*72DDE03E02CC55A9478A82F3F4EBE7F639249DEC' | 
  18. GRANT SELECT ON `MyDB`.* TO 'LimitIP'@'192.168.%'                                                              | 
  19. GRANT INSERTUPDATEDELETE ON `MyDB`.`kkk` TO 'LimitIP'@'192.168.%'                                          | 
  20. +----------------------------------------------------------------------------------------------------------------+ 
  21. rows in set (0.00 sec) 
  22.   
  23. mysql> 

 

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

  1. mysql> select user, host from mysql.user where user='LimitIP'
  2. +---------+-----------+ 
  3. user    | host      | 
  4. +---------+-----------+ 
  5. | LimitIP | 192.168.% | 
  6. +---------+-----------+ 
  7. 1 row in set (0.00 sec) 
  8.   
  9. mysql> update mysql.user set host='192.168.103.17' where user='LimitIP'
  10. Query OK, 1 row affected (0.02 sec) 
  11. Rows matched: 1  Changed: 1  Warnings: 0 
  12.   
  13. mysql> flush privileges
  14. Query OK, 0 rows affected (0.01 sec) 
  15.   
  16. mysql> select user, host from user where user='LimitIP'
  17. ERROR 1046 (3D000): No database selected 
  18. mysql> use mysql; 
  19. Reading table information for completion of table and column names 
  20. You can turn off this feature to get a quicker startup with -A 
  21.   
  22. Database changed 
  23. mysql> select user, host from user where user='LimitIP'
  24. +---------+----------------+ 
  25. user    | host           | 
  26. +---------+----------------+ 
  27. | LimitIP | 192.168.103.17 | 
  28. +---------+----------------+ 
  29. 1 row in set (0.00 sec) 
  30.   
  31. mysql> show grants for LimitIP@'192.168.103.17'
  32. +---------------------------------------------------------------------------------------------------------------------+ 
  33. | Grants for LimitIP@192.168.103.17                                                                                   | 
  34. +---------------------------------------------------------------------------------------------------------------------+ 
  35. GRANT USAGE ON *.* TO 'LimitIP'@'192.168.103.17' IDENTIFIED BY PASSWORD '*72DDE03E02CC55A9478A82F3F4EBE7F639249DEC' | 
  36. +---------------------------------------------------------------------------------------------------------------------+ 
  37. 1 row in set (0.00 sec) 
  38.   
  39. mysql>  

 

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

  1. mysql>  select * from mysql.db where user='LimitIP'\G; 
  2. *************************** 1. row *************************** 
  3.                  Host: 192.168.% 
  4.                    Db: MyDB 
  5.                  User: LimitIP 
  6.           Select_priv: Y 
  7.           Insert_priv: N 
  8.           Update_priv: N 
  9.           Delete_priv: N 
  10.           Create_priv: N 
  11.             Drop_priv: N 
  12.            Grant_priv: N 
  13.       References_priv: N 
  14.            Index_priv: N 
  15.            Alter_priv: N 
  16. Create_tmp_table_priv: N 
  17.      Lock_tables_priv: N 
  18.      Create_view_priv: N 
  19.        Show_view_priv: N 
  20.   Create_routine_priv: N 
  21.    Alter_routine_priv: N 
  22.          Execute_priv: N 
  23.            Event_priv: N 
  24.          Trigger_priv: N 
  25. 1 row in set (0.00 sec) 
  26.   
  27. ERROR:  
  28. No query specified 
  29.   
  30. mysql> select * from mysql.tables_priv where user='LimitIP'\G; 
  31. *************************** 1. row *************************** 
  32.        Host: 192.168.% 
  33.          Db: MyDB 
  34.        User: LimitIP 
  35.  Table_name: kkk 
  36.     Grantor: root@localhost 
  37.   Timestamp: 0000-00-00 00:00:00 
  38.  Table_priv: Insert,Update,Delete 
  39. Column_priv:  
  40. 1 row in set (0.00 sec) 
  41.   
  42. ERROR:  
  43. No query specified 

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

  1. mysql> show grants for LimitIP@'192.168.%'
  2. ERROR 1141 (42000): There is no such grant defined for user 'LimitIP' on host '192.168.%' 
  3. mysql>  
  4. mysql>  
  5. mysql> update mysql.db set host='192.168.103.17' where user='LimitIP'
  6. Query OK, 1 row affected (0.00 sec) 
  7. Rows matched: 1  Changed: 1  Warnings: 0 
  8.   
  9. mysql> update mysql.tables_priv set host='192.168.103.17' where user='LimitIP'
  10. Query OK, 1 row affected (0.00 sec) 
  11. Rows matched: 1  Changed: 1  Warnings: 0 
  12.   
  13. mysql> flush privileges
  14. Query OK, 0 rows affected (0.00 sec) 
  15.   
  16. mysql> show grants for LimitIP@'192.168.103.17'
  17. +---------------------------------------------------------------------------------------------------------------------+ 
  18. | Grants for LimitIP@192.168.103.17                                                                                   | 
  19. +---------------------------------------------------------------------------------------------------------------------+ 
  20. GRANT USAGE ON *.* TO 'LimitIP'@'192.168.103.17' IDENTIFIED BY PASSWORD '*72DDE03E02CC55A9478A82F3F4EBE7F639249DEC' | 
  21. GRANT SELECT ON `MyDB`.* TO 'LimitIP'@'192.168.103.17'                                                              | 
  22. GRANT INSERTUPDATEDELETE ON `MyDB`.`kkk` TO 'LimitIP'@'192.168.103.17'                                          | 
  23. +---------------------------------------------------------------------------------------------------------------------+ 
  24. rows in set (0.00 sec) 
  25.   
  26. mysql>  

 

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

  1. mysql> RENAME USER 'LimitIP'@'192.168.103.17' TO 'LimitIP'@'192.168.103.18'
  2. Query OK, 0 rows affected (0.00 sec) 
  3.   
  4. mysql> FLUSH PRIVILEGES
  5. Query OK, 0 rows affected (0.00 sec) 
  6.   
  7. mysql> show grants for 'LimitIP'@'192.168.103.18'
  8. +---------------------------------------------------------------------------------------------------------------------+ 
  9. | Grants for LimitIP@192.168.103.18                                                                                   | 
  10. +---------------------------------------------------------------------------------------------------------------------+ 
  11. GRANT USAGE ON *.* TO 'LimitIP'@'192.168.103.18' IDENTIFIED BY PASSWORD '*72DDE03E02CC55A9478A82F3F4EBE7F639249DEC' | 
  12. GRANT SELECT ON `MyDB`.* TO 'LimitIP'@'192.168.103.18'                                                              | 
  13. GRANT INSERTUPDATEDELETE ON `MyDB`.`kkk` TO 'LimitIP'@'192.168.103.18'                                          | 
  14. +---------------------------------------------------------------------------------------------------------------------+ 
  15. rows in set (0.00 sec) 
  16.   
  17. mysql>  

 

责任编辑:武晓燕 来源: DBA闲思杂想录
相关推荐

2009-08-15 10:40:00

2010-05-31 13:38:17

2021-06-30 13:13:55

IP地址网络IP限制

2023-09-24 13:07:53

NginxMySQLIP

2013-11-18 10:35:31

2011-03-17 15:16:38

2010-10-21 12:03:35

SQL Server视

2010-09-30 10:45:28

DB2列的修改

2011-08-08 12:44:30

IP安全策略远程登录

2021-01-27 10:56:05

Linux运维Linux系统

2010-06-09 14:55:11

TCP IP协议限制

2011-01-21 13:29:03

Sendmail

2010-10-11 10:52:25

MySQL分区

2010-10-15 14:39:55

MySQL单表大小

2017-04-01 17:00:39

oracle监听IP地址

2011-03-21 10:26:10

SQL Server视图管理

2010-11-12 11:36:29

SQL Server视

2021-11-26 09:10:19

Linux文件命令

2010-07-13 14:22:47

SQL Server

2010-06-07 14:44:24

MySQL导入
点赞
收藏

51CTO技术栈公众号