新浦京81707con > 注册购买 > MySQL如何修改账号的IP限制条件详解,修改账号的

原标题:MySQL如何修改账号的IP限制条件详解,修改账号的

浏览次数:147 时间:2019-07-24

前言

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

明日高出三个须求:修改MySQL用户的权位,供给限制特定IP地址能力访谈,第贰次遭逢那类须求,结果在测量试验进程,使用更新系统权限报开掘出现了一部分主题材料, 具体演示如下. 上面测验景况为MySQL 5.6.20. 如有别的版本与下部测量试验结果有出入,请以实际条件为准。

 

  大家先创立一个测量检验用户LimitIP,只允许192.168段的IP地址访谈,具体权限如下所示:

 

mysql> GRANT SELECT ON MyDB.* TO [email 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> 

mysql> flush privileges;

Query OK, 0 rows affected (0.00 sec)

 

mysql> 

 

mysql> show grants for [email protected]'192.168.%';

 ---------------------------------------------------------------------------------------------------------------- 

| Grants for [email protected]%                                                                                   |

 ---------------------------------------------------------------------------------------------------------------- 

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

 

 

 

 

若是今后接收供给:那些用户只同意这些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 [email protected]'192.168.103.17';

 --------------------------------------------------------------------------------------------------------------------- 

| Grants for [email protected]                                                                                   |

 --------------------------------------------------------------------------------------------------------------------- 

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

 --------------------------------------------------------------------------------------------------------------------- 

1 row in set (0.00 sec)

 

mysql> 

 

 

 

上边测量检验开掘,如若那样只修改mysql.user表, 那么此前的权柄没有了,如下所示,纵然你询问mysql.db、 mysql.tables_priv 开掘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: [email protected]

  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

 

 

据此笔者延续修改 mysql.db、 mysql.tables_priv 表,然后测量检验评释终于OK了(请见上面测量试验步骤),当然假若账户的权力不只有那多少个范畴,你或者还非得修改举例mysql.columns_priv、mysql.procs_priv等表

 

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> 

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 [email protected]'192.168.103.17';

 --------------------------------------------------------------------------------------------------------------------- 

| Grants for [email protected]                                                                                   |

 --------------------------------------------------------------------------------------------------------------------- 

| 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 USEWrangler 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 *.* 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> 

修改账号的IP限制条件,mysql账号ip限制 明天超过三个供给:修改MySQL用户的权力,须求限制特定IP地址才干访谈,第三回蒙受那类须求...

今日遇见贰个需要:修改MySQL用户的权能,须要限制特定IP地址技巧访谈,第三遍遇上那类须求,结果在测量检验进程,使用更新系统权限报发现并发了有个别难点, 具体演示如下. 下边测验蒙受为MySQL 5.6.20. 如有别的版本与下部测量检验结果有出入,请以实际条件为准。

近些日子在职业中碰着一个须要:修改MySQL用户的权柄,必要限制特定IP地址技能访谈,第二回遭逢那类必要,结果在测量检验进程,使用更新系统权限报发掘现身了一部分问题, 具体演示如下.

 

注意:下边测量试验情形为MySQL 5.6.20. 如有其余版本与下部测验结果有出入,请以实际条件为准。

  大家先成立贰个测试用户LimitIP,只同意192.168段的IP地址访谈,具体权限如下所示:

咱俩先创设八个测验用户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>
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>

图片 1

 

假诺今后接收供给:这么些用户只允许那些IP地址192.168.103.17拜访,于是笔者盘算更新mysql.user表,如下所示:

图片 2

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> 

 

图片 3

 

地点测量试验开采,若是如此只修改mysql.user表, 那么在此之前的权力没有了,如下所示,如若您询问mysql.db、 mysql.tables_priv 开掘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

假设现在吸收接纳供给:这些用户只同意那个IP地址192.168.103.17拜谒,于是本身打算更新mysql.user表,如下所示:

本文由新浦京81707con发布于注册购买,转载请注明出处:MySQL如何修改账号的IP限制条件详解,修改账号的

关键词: 新浦京81707con

上一篇:Shell脚本之自动修改IP,详解CentOs设置静态IP的方

下一篇:没有了