2020-05-01 16:30:47
今天遇到一个需求:修改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>
假设现在收到需求:这个用户只允许这个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>
上面测试发现,如果这样只修改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
所以我继续修改 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 (http://dev.mysql.com/doc/refman/5.6/en/rename-user.html)
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>
Newifi新路由R6830如何修改内网的IP地址
02-24
联想SureFibre400盘柜,默认控制器管理IP地址是什么?
02-21
MySQL笔记之数据备份与还原的使用详解
07-07
MySQL线程处于Waiting for table flush状态的分析
07-05
CentOS使用MySQLbinlog恢复MySQL数据库
05-07
Docker安装部署MySQL5.7
05-26
Ubuntu下迁移通过apt安装的MySQL数据库文件目录
07-18
『浅入浅出』MySQL及InnoDB存储引擎
07-16
如何实现MySQL的一次一密登录
05-22
更改MySQL数据文件目录位置
06-05
未经授权修改 UEFI 系统中的 UEFI 变量
04-08
修改注册表扩大常用网站数量
06-14
MySQL(mariadb)新建用户及用户授权管理
06-01
Apache优化:修改最大并发连接数
05-01
CentOS 7 修改内核启动顺序
04-23
Ideapad Y650修改触控板默认设定值
04-20
Linux ar建立或修改备存文件命令详解
06-23
M7650DNF如何将扫描的图片保存为PDF文档,如何修改保存路径?
07-02
PostgreSQL数据库修改表增加主键
07-12
Redis未授权访问漏洞说明及利用
03-18
EasyTool(IP搜索修改升级工具) v3.2.1.6469 官方版
1.95MB
下载PhotoLightning(照片信息修改工具) v5.52 官方版
7.85M
下载ce修改器下载
22.2M
下载ce修改器下载
22.3M
下载game master(游戏修改器) V1.0.0.0 官方版
2.5M
下载ip修改器(电脑IP修改工具)v1.0 绿色版
486KB
下载pdf修改器(PDF文件编辑软件) V2.5 免费版
3.26MB
下载无敌账号备忘录下载
423.28K
下载金山游侠(游戏修改工具) V4.0.1.0 最新版
2.93 MB
下载3dsmax下载
3.82G
下载HeidiSQL下载
10.1M
下载NewFileTime下载
186KB
下载PolyUnwrapper下载
1.8M
下载vistalizator下载
1.03MB
下载wpe三件套下载
2.9M
下载