本文转载自微信公众号《DBA的杂想》，作者潇湘隐士。转载本文请联系DBA杂念公众号。在MySQL中，需要修改一个MySQL用户的权限，需要限制对特定IP地址的访问。下面通过一个简单的案例来进行演示。下面的测试环境是MySQL5.6.20。其他版本应该是一样的。如其他版本与以下测试结果有出入，请以实际环境为准。我们先创建一个测试用户LimitIP，只允许访问192.168段的IP地址。具体权限如下：mysql>GRANTSELECTONMyDB.*TOLimitIP@'192.168.%'IDENTIFIEDBY'LimitIP';QueryOK,0rowsaffected(0.01sec)mysql>GRANTINSERT,UPDATE,DELETEONMyDB.kkkTOLimitIP@'192.168.%';QueryOK,0rowsaffected(0.00sec)mysql>mysql>flushprivileges;QueryOK,0rowsaffected(0.00sec)mysql>mysql>showgrantsforLimitIP@'192.168.%';+---------------------------------------------------------------------------------------------------------------+|GrantsforLimitIP@192.168.%|+----------------------------------------------------------------------------------------------------------+|GRANTUSAGEON*.*TO'LimitIP'@'192.168.%'IDENTIFIEDBYPASSWORD'*72DDE03E02CC55A9478A82F3F4EBE7F639249DEC'||GRANTSELECTON`MyDB`.*TO'LimitIP'@'192.168.%'||GRANTINSERT,UPDATE,DELETEON`MyDB`.`kkk`TO'LimitIP'@'192.168.%'|+----------------------------------------------------------------------------------------------------------------+3rowsinset(0.00sec)mysql>假设现在收到一个请求：这个用户只允许访问这个IP地址192.168.103.17，所以我打算更新mysql.user表如下：mysql>selectuser,hostfrommysql.userwhereuser='LimitIP';+--------+------------+|用户|主机|+--------+----------+|LimitIP|192.168.%|+--------+------------+1rowinset(0.00sec)mysql>updatemysql.usersethost='192.168.103.17'whereuser='限制IP';QueryOK,1rowaffected(0.02sec)Rowsmatched:1Changed:1Warnings:0mysql>flushprivileges;QueryOK,0rowsaffected(0.01sec)mysql>selectuser,hostfromuserwhereuser='LimitIP';ERROR1046(3D000):Nodatabaseselectedmysql>usemysql;ReadingtableinformationforcompletionoftableandcolumnnamesYoucanturnoffthisfeaturetogetaquickerstartupwith-ADatabasechangedmysql>selectuser,hostfromuserwhereuser='限制IP';+--------+----------------+|用户|主机|+--------+---------------+|限制IP|192.168.103.17|+--------+----------------+1rowinset(0.00sec)mysql>showgrantsforLimitIP@'192.168.103.17';+----------------------------------------------------------------------------------------------------------------+|GrantsforLimitIP@192.168.103.17|+------------------------------------------------------------------------------------------------------------+|GRANTUSAGEON*.*TO'LimitIP'@'192.168.103.17'IDENTIFIEDBYPASSWORD'*72DDE03E02CC55A9478A82F3F4EBE7F639249DEC'|+------------------------------------------------------------------------------------------------------------------+1rowinset(0.00sec)mysql>上面测试发现如果只修改mysql.user表这样，那么之前的权限就没有了。如下图，如果查询mysql.db、mysql.tables_priv，发现Host的字段值还是192.168.%mysql>select*frommysql.dbwhereuser='LimitIP'\G;***************************1.row******************************主机:192.168.%Db:MyDBUser:LimitIPSelect_priv:YInsert_priv:NUpdate_priv:NDelete_priv:NCreate_priv:NDrop_priv:NGrant_priv:NReferences_priv:NIdex_priv:NAlter_priv:NCreate_tmp_table_priv:NLock_tables_priv:NCreate_view_priv:NShow_view_priv:NCreate_routine_priv:NGrant_priv:NCreate_routine_priv:NAlter_priv:NCreate_routine_priv:NAltprier_routine_routv:NEvent_priv:NTrigger_priv:N1rowinset(0.00sec)ERROR:Noqueryspecifiedmysql>select*frommysql.tables_privwhereuser='LimitIP'\G;********************************1.row******************************主机：192.168.%Db:MyDBUser:LimitIPTable_name:kkkGrantor:root@localhostTimestamp:0000-00-0000:00:00Table_priv:Insert,Update,DeleteColumn_priv:1rowinset(0.00sec)ERROR:Noqueryspecified于是继续修改mysql.db和mysql.tables_priv表，然后测试验证终于OK了（见下面的测试步骤），当然如果账户的权限超过这些级别，可能还得修改mysql.columns_priv、mysql.procs_priv等表mysql>showgrantsforLimitIP@'192.168.%';.%'mysql>mysql>mysql>updatemysql.dbsethost='192.168.103.17'whereuser='LimitIP';QueryOK,1rowaffected(0.00sec)Rowsmatched:1Changed:1Warnings:0mysql>updatemysql.tables_privsethost='192.168.103.17'whereuser='LimitIP';QueryOK,1rowsaffected(0.00sec)Rowsmatched:1Changed:1Warnings:0mysql>flushprivileges;QueryOK,0rowsaffected(0.00sec)mysql>showgrantsforLimitIP@'192.168.103.17';+----------------------------------------------------------------------------------------------------------------+|GrantsforLimitIP@192.168.103.17|+--------------------------------------------------------------------------------------------------------------+|GRANTUSAGEON*.*TO'LimitIP'@'192.168.103.17'IDENTIFIEDBYPASSWORD'*72DDE03E02CC55A9478A82F3F4EBE7F639249DEC'||GRANTSELECTON`MyDB`.*TO'LimitIP'@'192.168.103.`17'||kTERATO.'限制IP'@'192.168.103.17'|+-------------------------------------------------------------------------------------------------------------+3rowsinset(0.00sec)mysql>如果需要修改用户的IP限制，其实更新mysql相关的权限表不是最好的策略。其实还有更好的办法，那就是RENAMEUSERSyntaxmysql>RENAMEUSER'LimitIP'@'192.168.103.17'TO'LimitIP'@'192.168.103.18';QueryOK,0rowsaffected(0.00sec)mysql>FLUSHPRIVILEGES;QueryOK,0rowsaffected(0.00sec)mysql>showgrantsfor'LimitIP'@'192.168.103.18';+------------------------------------------------------------------------------------------------------------+|GrantsforLimitIP@192.168.103.18|+----------------------------------------------------------------------------------------------------------------+|GRANTUSAGEON*.*TO'LimitIP'@'192.168。103.18'IDENTIFIEDBYPASSWORD'*72DDE03E02CC55A9478A82F3F4EBE7F639249DEC'||GRANTSELECTON`MyDB`。*到'LimitIP'@'192.168.103.18'||GRANTINSERT，更新，删除`MyDB`。`kkk`TO'18.10.3'@6+----------------------------------------------------------------------------------------------------------------+3rowsinset(0.00sec)mysql>
