浅谈Mysql连接数据库时host和user的匹配规则

2023-07-07 05:13:03 211

--连接数据库时，host和user的匹配规则

官方文档：https://dev.mysql.com/doc/refman/5.7/en/connection-access.html

--host和user的匹配规则如下：

--是host为明确的最先匹配，host带%模糊的时候最后匹配，但host为''(空)位于%之后才匹配

--相同的host时候，比较user为明确的最先匹配，user为''(空)最后匹配

--相同的host和user时，排序是不确定的

Whenmultiplematchesarepossible,theservermustdeterminewhichofthemtouse.Itresolvesthisissueasfollows:
Whenevertheserverreadstheusertableintomemory,itsortstherows.
Whenaclientattemptstoconnect,theserverlooksthroughtherowsinsortedorder.
Theserverusesthefirstrowthatmatchestheclienthostnameandusername.
Theserverusessortingrulesthatorderrowswiththemost-specificHostvaluesfirst.LiteralhostnamesandIPaddressesarethemostspecific.(ThespecificityofaliteralIPaddressisnotaffectedbywhetherithasanetmask,so198.51.100.13and198.51.100.0/255.255.255.0areconsideredequallyspecific.)Thepattern'%'means“anyhost”andisleastspecific.Theemptystring''alsomeans“anyhost”butsortsafter'%'.RowswiththesameHostvalueareorderedwiththemost-specificUservaluesfirst(ablankUservaluemeans“anyuser”andisleastspecific).Forrowswithequally-specificHostandUservalues,theorderisnondeterministic.

--查看当前的host及用户信息匹配顺序，先host顺序匹配、后user顺序匹配

mysql>SELECTauthentication_string,host,user,account_lockedFROMmysql.USERORDERBYhostdesc,userdesc;
+-------------------------------------------+--------------+---------------+----------------+
|authentication_string|host|user|account_locked|
+-------------------------------------------+--------------+---------------+----------------+
|*511C0A408C5065XXEC90D60YYA1AB9437281AF28|localhost|root|N|
|*THISISNOTAVALIXXASSWORDYYATCANBEUSEDHERE|localhost|mysql.sys|Y|
|*THISISNOTAVALIXXASSWORDYYATCANBEUSEDHERE|localhost|mysql.session|Y|
|*485CE31BA547A4XXC047659YY10DF200F361CD4E|localhost|bkpuser|N|
|*7B502777D8FF69XX4B56BC2YY2867F4B47321BA8|192.168.56.%|repl|N|
|*AECCE73463829AXX3968838YYF6F85E43C3F169C|%|flyremote|N|
|*566AC8467DAAAEXXE247AE7YY0A770E9B97D9FB0||flylocal|N|
+-------------------------------------------+--------------+---------------+----------------+
8rowsinset(0.00sec)

--举个特殊例子

--建立两个特殊用户如下，一个用户名为''(空)、一个用户名和host都为''(空)

mysql>createuser''@'localhost'identifiedby"Kong123$";
QueryOK,0rowsaffected(0.00sec)
mysql>createuser''@''identifiedby"doubleKong123$";
QueryOK,0rowsaffected(0.00sec)

--查看当前的host及用户信息匹配顺序，先host顺序匹配、后user顺序匹配

mysql>SELECTauthentication_string,host,user,account_lockedFROMmysql.USERORDERBYhostdesc,userdesc;
+-------------------------------------------+--------------+---------------+----------------+
|authentication_string|host|user|account_locked|
+-------------------------------------------+--------------+---------------+----------------+
|*511C0VVV8C5065CBEC90D6TTTT1AB9437281AF28|localhost|root|N|
|*THISIVVVTAVALIDPASSWORTTTTTCANBEUSEDHERE|localhost|mysql.sys|Y|
|*THISIVVVTAVALIDPASSWORTTTTTCANBEUSEDHERE|localhost|mysql.session|Y|
|*485CEVVVA547A48CC04765TTTT0DF200F361CD4E|localhost|bkpuser|N|
|*256D7VVV91F7363EBDADEFTTTTB74B2B318746FC|localhost||N|
|*7B502VVVD8FF69164B56BCTTTT867F4B47321BA8|192.168.56.%|repl|N|
|*AECCEVVV63829A5F396883TTTT6F85E43C3F169C|%|flyremote|N|
|*566ACVVV7DAAAE79E247AETTTTA770E9B97D9FB0||flylocal|N|
|*AE162VVV68403D1D98A4C9TTTT50A508B8C56F3F|||N|
+-------------------------------------------+--------------+---------------+----------------+
9rowsinset(0.00sec)

--这样本地登录flyremote用户时会报错，因为按以上的顺序优先匹配到了host为localhost、user为''(空)的用户，而不是flyremote用户(因为user为''(空)的用户可以匹配任意用户名)

[root@hostmysql-mmysql]#mysql-uflyremote-pFlyremote123$
mysql:[Warning]Usingapasswordonthecommandlineinterfacecanbeinsecure.
ERROR1045(28000):Accessdeniedforuser'flyremote'@'localhost'(usingpassword:YES)

--那就是说本地登录flyremote用户时，用匹配到的host为localhost、user为''(空)的密码Kong123$，就可以正常登陆了

[root@hostmysql-mmysql]#mysql-uflyremote-pKong123$
mysql:[Warning]Usingapasswordonthecommandlineinterfacecanbeinsecure.
WelcometotheMySQLmonitor.Commandsendwith;or\g.
YourMySQLconnectionidis15
Serverversion:5.7.23-logMySQLCommunityServer(GPL)
Copyright(c)2000,2018,Oracleand/oritsaffiliates.Allrightsreserved.
OracleisaregisteredtrademarkofOracleCorporationand/orits
affiliates.Othernamesmaybetrademarksoftheirrespective
owners.
Type'help;'or'\h'forhelp.Type'\c'toclearthecurrentinputstatement.

--查看当前用户连接方式和当前用户认证方式

mysql>selectuser(),CURRENT_USER();
+---------------------+----------------+
|user()|CURRENT_USER()|
+---------------------+----------------+
|flyremote@localhost|@localhost|
+---------------------+----------------+
1rowinset(0.06sec)

--用带入ip的方式登录flyremote用户时无问题，ip匹配到了%，user匹配到了flyremote

[root@hostmysql-mmysql]#mysql-uflyremote-pFlyremote123$-h127.11.22.33
mysql:[Warning]Usingapasswordonthecommandlineinterfacecanbeinsecure.
WelcometotheMySQLmonitor.Commandsendwith;or\g.
YourMySQLconnectionidis12
Serverversion:5.7.23-logMySQLCommunityServer(GPL)
Copyright(c)2000,2018,Oracleand/oritsaffiliates.Allrightsreserved.
OracleisaregisteredtrademarkofOracleCorporationand/orits
affiliates.Othernamesmaybetrademarksoftheirrespective
owners.
Type'help;'or'\h'forhelp.Type'\c'toclearthecurrentinputstatement.
mysql>

--查看当前用户连接方式和当前用户认证方式

mysql>selectuser(),CURRENT_USER();
+------------------------+----------------+
|user()|CURRENT_USER()|
+------------------------+----------------+
|flyremote@127.11.22.33|flyremote@%|
+------------------------+----------------+
1rowinset(0.00sec)

--任意用户、任意host，只要密码和建立的第二个空用户空host的密码"doubleKong123$"匹配了，就可以进入mysql

--测试一个不存在的用户hahaha

[root@hostmysql-m~]#mysql-uhahaha-pdoubleKong123$-h127.11.22.33
mysql:[Warning]Usingapasswordonthecommandlineinterfacecanbeinsecure.
WelcometotheMySQLmonitor.Commandsendwith;or\g.
YourMySQLconnectionidis6
Serverversion:5.7.23-logMySQLCommunityServer(GPL)
Copyright(c)2000,2018,Oracleand/oritsaffiliates.Allrightsreserved.
OracleisaregisteredtrademarkofOracleCorporationand/orits
affiliates.Othernamesmaybetrademarksoftheirrespective
owners.
Type'help;'or'\h'forhelp.Type'\c'toclearthecurrentinputstatement.
mysql>

--查看当前用户连接方式和当前用户认证方式

mysql>selectuser(),CURRENT_USER();
+---------------------+----------------+
|user()|CURRENT_USER()|
+---------------------+----------------+
|hahaha@127.11.22.33|@|
+---------------------+----------------+
1rowinset(0.01sec)

--解决方案：

1、手工删除空用户和空host用户确保安全

或者

2、使用mysql_secure_installation来进行安全配置

--安全配置如下，其中有删除匿名用户的操作

ThisprogramenablesyoutoimprovethesecurityofyourMySQLinstallationinthefollowingways:
Youcansetapasswordforrootaccounts.
Youcanremoverootaccountsthatareaccessiblefromoutsidethelocalhost.
Youcanremoveanonymous-useraccounts.
Youcanremovethetestdatabase(whichbydefaultcanbeaccessedbyallusers,evenanonymoususers),andprivilegesthatpermitanyonetoaccessdatabaseswithnamesthatstartwithtest_.

--删除匿名用户的源码mysql_secure_installation.cc如下：

//Removeanonymoususers
remove_anonymous_users();
/**
Removesalltheanonymoususersforbettersecurity.
*/
voidremove_anonymous_users()
{
intreply;
reply=get_response((constchar*)"Bydefault,aMySQLinstallationhasan"
"anonymoususer,\nallowinganyonetolog"
"intoMySQLwithouthavingtohave\nauser"
"accountcreatedforthem.Thisisintended"
"onlyfor\ntesting,andtomakethe"
"installationgoabitsmoother.\nYoushould"
"removethembeforemovingintoaproduction\n"
"environment.\n\nRemoveanonymoususers?"
"(Pressy|YforYes,anyotherkeyforNo):",'y');

if(reply==(int)'y'||reply==(int)'Y')
{
constchar*query;
query="SELECTUSER,HOSTFROMmysql.userWHEREUSER=''";
if(!execute_query(&query,strlen(query)))
DBUG_PRINT("info",("querysuccess!"));
MYSQL_RES*result=mysql_store_result(&mysql);
if(result)
drop_users(result);
mysql_free_result(result);
fprintf(stdout,"Success.\n\n");
}
else
fprintf(stdout,"\n...skipping.\n\n");
}

补充：mysql用户表中多个host时的匹配规则

mysql数据库中user表的host字段，是用来控制用户访问数据库“权限”的。

可以使用“%”，表示所有的网段；

也可以使用具体的ip地址，表示只有该ip的客户端才可以登录到mysql服务器；

也可以使用“_”进行模糊匹配，表示某个网段的客户端可以登录到mysql服务器。

如果在user表中存在一个用户两条不同host值的记录，那么mysql服务器该如何匹配该用户的权限呢？

mysql采用的策略是：当服务器读取user表时，它首先以最具体的Host值排序（主机名和IP号是最具体的）。有相同Host值的条目首先以最具体的User匹配。

举例：

如下，有两条root用户，那么只有localhost的root客户端可以登录到mysql服务器。

|root|localhost|*81F5E21E35407D884A6CD4A731AEBFB6AF209E1B|
|root|%|*81F5E21E35407D884A6CD4A731AEBFB6AF209E1B|

以上为个人经验，希望能给大家一个参考，也希望大家多多支持毛票票。如有错误或未考虑完全的地方，望不吝赐教。

浅谈Mysql连接数据库时host和user的匹配规则

--host和user的匹配规则如下：

--解决方案：

举例：

热门推荐

随机推荐