MySQL賬號登錄ip驗證機制

我們通過Mysql客戶端登錄MySQL服務器的時候，基本都是使用的命令 mysql -hxxx.xxx.xxx.xxx -uxxx -pxxxxxx 進行訪問的。

但是我們可能會遇到這樣的情況，我們明明輸入的是正確的密碼。但是就是登錄不上，服務端卻返回了

ERROR 1045 (28000): Access denied for user xxx

之類的報錯，但是換一個機器使用mysql客戶端就可以登錄上mysql服務器。

前后兩次輸入的命令都是一樣的，用戶名和密碼都是一樣的。為什么一個機器可以登錄MySQL服務器，另外一個卻不可以呢。

唯一的區別就是使用不同的機器，難道訪問MySQL服務器還是認機器的嗎？

說對了，訪問MySQL服務器還真的是認機器的。MySQL的賬號信息是存放在mysql.user這個系統表里面的。以mysql5.7為例，查看一下mysql.user這張系統表。

發現它是一張有主鍵表，主鍵列有兩個User和Host，是復合主鍵。因此mysql.user表里面可以是有多條記錄的User列是相同。

猜測MySQL的登錄認證的時候是User 和 Host 兩個維度的，但是我們使用mysql客戶端的時候我們只通過 -u和-p參數輸入了User和Password信息。

在登錄的時候，MySQL到底是使用mysql.user表的哪一行記錄進行認證的呢？對應的優先級又是怎樣的呢？做一個簡單的試驗驗證一下。

在MySQL服務器（ip為 10.xxx.xxx.133）上新建多個用戶，用戶名相同，host和密碼不同；語句如下。(注，xxx.xxx.是固定的ip前綴，如192.168.)

create user 'drs_user' identified by 'drs_pwd_0'; create user 'drs_user'@'127.0.0.1' identified by 'drs_pwd_1'; create user 'drs_user'@'localhost' identified by 'drs_pwd_2'; create user 'drs_user'@'xxx.xxx.218.181' identified by 'drs_pwd_3' create user 'drs_user'@'xxx.xxx.218.%' identified by 'drs_pwd_4'

查看mysql.user系統表如下

mysql> select user, host from mysql.user where user = 'drs_user'; +----------+-----------------+ | user | host | +----------+-----------------+ | drs_user | % | | drs_user | xxx.xxx.218.% | | drs_user | xxx.xxx.218.181 | | drs_user | 127.0.0.1 | | drs_user | localhost | +----------+-----------------+

準備三個vm作為訪問MySQL的客戶端，ip分別為’xxx.xxx.218.181’，‘xxx.xxx.218.42’和’xxx.xxx.219.30’.分別使用drs_user進行登錄驗證

在’xxx.xxx.218.181’上

> mysql -h10.xxx.xxx.133 -udrs_user -pdrs_pwd_0 mysql: [Warning] Using a password on the command line interface can be insecure. ERROR 1045 (28000): Access denied for user 'drs_user'@'xxx.xxx.218.181' (using password: YES) > > mysql -h10.xxx.xxx.133 -udrs_user -pdrs_pwd_1 mysql: [Warning] Using a password on the command line interface can be insecure. ERROR 1045 (28000): Access denied for user 'drs_user'@'xxx.xxx.218.181' (using password: YES) > > mysql -h10.xxx.xxx.133 -udrs_user -pdrs_pwd_2 mysql: [Warning] Using a password on the command line interface can be insecure. ERROR 1045 (28000): Access denied for user 'drs_user'@'xxx.xxx.218.181' (using password: YES) > > mysql -h10.xxx.xxx.133 -udrs_user -pdrs_pwd_3 mysql: [Warning] Using a password on the command line interface can be insecure. Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 17545 Server version: 5.7.26-log MySQL Community Server (GPL) Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql>

此時MySQL服務器使用的是 (drs_user | xxx.xxx.218.181)這一行進行登錄認證的

在’xxx.xxx.218.42’上

> mysql -h10.xxx.xxx.133 -udrs_user -pdrs_pwd_0 mysql: [Warning] Using a password on the command line interface can be insecure. ERROR 1045 (28000): Access denied for user 'drs_user'@'xxx.xxx.218.42' (using password: YES) > > mysql -h10.xxx.xxx.133 -udrs_user -pdrs_pwd_1 mysql: [Warning] Using a password on the command line interface can be insecure. ERROR 1045 (28000): Access denied for user 'drs_user'@'xxx.xxx.218.42' (using password: YES) > > mysql -h10.xxx.xxx.133 -udrs_user -pdrs_pwd_2 mysql: [Warning] Using a password on the command line interface can be insecure. ERROR 1045 (28000): Access denied for user 'drs_user'@'xxx.xxx.218.42' (using password: YES) > > mysql -h10.xxx.xxx.133 -udrs_user -pdrs_pwd_3 mysql: [Warning] Using a password on the command line interface can be insecure. ERROR 1045 (28000): Access denied for user 'drs_user'@'xxx.xxx.218.42' (using password: YES) > > mysql -h10.xxx.xxx.133 -udrs_user -pdrs_pwd_4 mysql: [Warning] Using a password on the command line interface can be insecure. Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 17545 Server version: 5.7.26-log MySQL Community Server (GPL) Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql>

此時MySQL服務器使用的是 (drs_user | xxx.xxx.218.%)這一行進行登錄認證的

在’xxx.xxx.219.30’上

>mysql -hxxx.xxx.xxx..133 -udrs_user -pdrs_pwd_0 mysql: [Warning] Using a password on the command line interface can be insecure. Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 17694 Server version: 5.7.26-log MySQL Community Server (GPL) Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql>

此時MySQL服務器使用的是 (drs_user | %)這一行進行登錄認證的

參考官方文檔(https://dev.mysql.com/doc/refman/5.7/en/connection-access.html)的描述：

簡而言之：MySQL 對于同一個User，會根據 Host 的匹配精度按降序排列，當客戶端嘗試登錄 MySQL 的時候，會按照順序依次這個 User下面所有的 Host 規則，采用最小匹配直到匹配成功。

MySQL

版權聲明：本文內容由網絡用戶投稿，版權歸原作者所有，本站不擁有其著作權，亦不承擔相應法律責任。如果您發現本站中有涉嫌抄襲或描述失實的內容，請聯系我們jiasou666@gmail.com 處理，核實后本網站將在24小時內刪除侵權內容。