選單
探索 Prisma

MySQL / 認證和授權

如何在 MySQL 中管理使用者和認證

分享到

簡介

使用者管理是希望管理 MySQL 資料庫系統的任何人的最重要的職責之一。建立、修改和刪除使用者賬戶以最好地代表環境中的使用者和服務，有助於為鎖定訪問、限制更改範圍以及實施修改的審計和問責制奠定基礎。

在本指南中，我們將討論如何在 MySQL 中管理使用者賬戶。首先，我們將討論 MySQL 中的有效使用者是什麼，並向您展示如何向系統新增其他使用者。這將引出關於如何為每個使用者配置認證以及系統如何從可能性列表中選擇認證選項的討論。我們將繼續討論更改現有使用者、如何使用您建立的賬戶登入以及如何刪除不再需要的使用者。

先決條件

要遵循本指南，您需要一個具有相應許可權的 MySQL 伺服器賬戶。

我們將使用的命令

要在 MySQL 中建立、修改和刪除使用者，您需要使用的核心命令是：

  • CREATE USER: 建立一個新的使用者賬戶
  • ALTER USER: 更改現有使用者賬戶
  • DROP USER: 刪除現有使用者賬戶

所需許可權

要執行上述命令，您需要使用具有 CREATE USER 許可權的賬戶登入 MySQL。CREATE USER 許可權允許您建立、修改、刪除和重新命名使用者，以及其他操作。我們還需要對 mysql 資料庫具有 SELECT 許可權，以檢視現有使用者的資訊。

按優先順序，您應該使用以下賬戶登入：

  • 一個受限賬戶，該賬戶對 mysql 資料庫具有 CREATE USER 許可權和 SELECT 許可權
  • 具有 MySQL 中所有許可權的 root 或管理使用者

瞭解 MySQL 如何定義和解釋使用者賬戶

在開始建立新賬戶之前，花一些時間熟悉 MySQL 用於建立和引用使用者的各種方式會很有幫助。您還需要熟悉 MySQL 的認證演算法，以瞭解它將使用哪個使用者賬戶來認證連線。

MySQL 使用者賬戶的語法是什麼？

在 MySQL 中，使用者賬戶由兩個獨立的資訊組成，透過一個“@”符號連線：

  • 使用者名稱
  • 使用者連線的主機

通常，系統上的使用者賬戶看起來像這樣：

'<user>'@'<host>'

如上所示，可以使用單引號單獨包裝使用者和主機元件。如果其中任何一個元件包含可能被誤解的字元，則有時是必要的。一般來說，新增它們始終是一個好主意，以明確表示。

因此，在 MySQL 中，完整的賬戶名不僅僅是 'john'，還需要某種主機，例如 'john'@'localhost'。這意味著系統上可能存在多個 'john' 賬戶，只要它們來自不同的域，MySQL 就會將它們視為唯一的賬戶。

儘管如此，還是可以定義沒有使用者或主機元件的使用者賬戶，但您必須瞭解其中重要的含義。

您可以使用空字串定義一個沒有使用者值的使用者賬戶：

''@'<host>'

例如，您可以建立一個使用者，如 ''@'localhost'。此使用者將匹配從本地計算機連線的 任何 使用者名稱。

同樣，您可以擁有一個可以匹配任何主機的使用者賬戶。對於主機值，您可以使用 % 萬用字元，如下所示：

'<user>'@'%'

例如，如果您建立 'john'@'%'，該賬戶將匹配從 任何 主機連線的 'john' 使用者。

MySQL 如何認證使用者？

瞭解 MySQL 實際如何處理每個認證請求對於避免一類常見的認證問題非常重要，這些問題是由於合理但不正確的假設導致的。這在我們的 MySQL 認證和授權簡介文章中已深入討論。

當認證連線請求時，MySQL 使用其內部 mysql 資料庫的 user 表中的多個欄位來決定是否允許連線。MySQL 最多 使用 一條 使用者賬戶記錄來嘗試認證連線。這意味著如果存在多條可以匹配連線的賬戶，MySQL 需要一種方法來決定使用哪個使用者賬戶。

MySQL 認證使用者的演算法在伺服器啟動時開始。啟動時，MySQL 將整個 mysql.user 表載入到記憶體中。每當使用常規 MySQL 命令建立使用者賬戶時，它也會這樣做。在載入表時，它會將條目從最高優先順序排序到最低優先順序。

MySQL 使用 Host 列作為主要排序欄位，並優先選擇具有更具體值的結果。因此，文字值被排序到頂部作為最高優先順序，而使用萬用字元（如 %）的值則排序到底部。最後的條目是那些只包含 % 沒有其他字元的，其次是主機完全為空的條目。

對於具有相同 Host 值的任何條目，User 列用作輔助排序欄位。同樣，更精確的匹配項會得到優先排序。由於 User 列不能使用萬用字元，因此除那些具有空白 User 值的條目外，所有條目都處於平等地位。這些條目被排序到底部。如果選擇了任何具有空白 User 值的條目，則該使用者將作為“匿名使用者”進行身份驗證，這通常等同於沒有許可權。

現在，每當發出連線請求時，MySQL 都會從上到下遍歷其記憶體中的排序表。它使用找到的 第一個 條目來認證使用者，無論是否存在其他也匹配的條目。如果客戶端未能使用該條目定義的方法進行認證，連線將失敗，並且不會檢查其他條目。

在 MySQL 使用者賬戶定義中不包含使用者或主機的含義是什麼？

由於 MySQL 的認證演算法，如果您在建立不包含使用者或主機元件的使用者賬戶時不小心，可能會出現問題。這是因為 MySQL 決定使用哪條記錄來認證您的方式可能不直觀且令人驚訝。

例如，如果使用者使用空白字串作為使用者部分進行 MySQL 認證，MySQL 會在會話的其餘部分將其視為“匿名使用者”。通常，匿名使用者幾乎沒有許可權，連線後能做的事情非常少。甚至在嘗試使用其他使用者賬戶進行認證時，也可能不小心認證為匿名使用者。

使用萬用字元 主機 的使用者賬戶的挑戰在於，其他 包含 主機值的使用者賬戶可以輕鬆地遮蔽或使使用萬用字元的使用者賬戶不可用。

例如，如果您有一個使用者賬戶定義為 'emily'@'%'，您可能期望能夠從任何主機認證到 'emily'。但是，如果您有一個使用者賬戶使用者為空但主機值與 'emily' 連線的主機匹配，MySQL 將改用該賬戶進行認證（導致如上所述的匿名使用者登入）。

因此，例如，MySQL 會將以下賬戶按以下順序排序：

優先順序MySQL 賬戶註釋
1'emily'@'localhost' 和 'emily'@'example.com'這些優先順序相同，這沒問題，因為它們中只有一個可能匹配一個連線。
2''@'localhost' 和 ''@'example.com'這兩個條目再次具有相同的優先順序。由於它們沒有使用者元件，但它們 確實 具有字面主機元件，因此它們被放置在具有精確主機值的條目底部。
3'emily'@'%.example.com'此條目的主機元件中包含萬用字元，因此它的優先順序低於具有精確主機值的條目。
4''@'%.example.com'此條目與主機值中包含萬用字元的條目歸為一組。由於它沒有使用者元件，因此它位於此組的底部。
5'emily'@'%'此條目的主機值僅由萬用字元組成。由於它匹配任何主機，因此優先順序非常低。
7''@'%'此條目可用於將任何使用者從任何主機認證為匿名使用者。它的優先順序極低，因為它匹配任何連線。
6'emily'@''此條目的主機值完全為空，其優先順序甚至低於僅包含萬用字元主機的主機。
8''@''這是最低優先順序的使用者。它不包含主機資訊，因此在主機排序時被放置在最後。由於它還包含空白使用者，因此它被放置在該組中的其他條目下方。與所有沒有使用者的條目一樣，使用此條目認證的連線將以匿名使用者身份登入。

如何建立使用者？

既然您已經瞭解了 MySQL 處理使用者賬戶的方式，我們就可以開始建立一些新使用者了。請記住使用 先決條件 中所述許可權的使用者登入。

基本語法

建立新使用者 的基本語法相對簡單。您使用 CREATE USER 命令，然後指定新賬戶的使用者和主機：

CREATE USER '<user>'@'<host>';

這將建立一個基本賬戶，在建立時除了其使用者和主機之外不配置任何詳細資訊。

如何建立帶密碼的使用者？

通常，您希望在建立使用者時配置認證。您可以透過在 CREATE USER 語句中新增可選的 IDENTIFIED BY 子句來實現：

CREATE USER '<user>'@'<host>' IDENTIFED BY '<password>';

這會像以前一樣建立一個新使用者賬戶，並同時為該賬戶分配密碼。我們稍後將介紹如何在事後分配密碼或如何更改使用者的密碼。

如何建立具有 Unix 套接字認證的使用者？

雖然密碼認證是大多數使用者最常見的認證方法，但它不是唯一的選項。MySQL 提供了許多不同的內部和外部認證機制，您可以配置它們供您的使用者賬戶使用。例如，我們將使用 Unix 套接字認證配置一個新賬戶。

Unix 套接字認證可以在 Linux 或類 Unix 環境中使用，以便作業系統上的賬戶無需進一步認證即可訪問 MySQL 中同名的賬戶。在此配置中，MySQL 管理員知道作業系統上的使用者賬戶受到嚴格控制。

因此，如果作業系統上有一個 mary 使用者，如果 Unix 套接字認證是定義的認證機制，他們將能夠登入到 MySQL 中的 'mary'@'localhost' 賬戶。讓我們現在配置這個。

套接字認證需要 auth_socket 外掛，所以首先透過鍵入以下命令載入外掛：

INSTALL PLUGIN auth_socket SONAME 'auth_socket.so';

接下來，建立一個與您作業系統上的使用者賬戶匹配的使用者賬戶。在此示例中，我們將使用上面討論的 mary 賬戶。如果您不使用與您作業系統名稱之一匹配的名稱，您將無法使用此使用者進行認證。

要建立具有套接字認證的使用者，我們需要使用 IDENTIFIED WITH 子句（與前面使用的 IDENTIFIED BY 子句不同）來指定要使用的認證外掛：

CREATE USER 'mary'@'localhost' IDENTIFIED WITH auth_socket;

現在，您應該能夠從作業系統上的 mary 使用者認證到 MySQL 的 'mary'@'localhost' 使用者。以 mary 身份登入時，無需提供任何使用者名稱或密碼即可連線到資料庫：

mysql

您應該透過您配置的 Unix 套接字認證自動登入。

如何顯示現有使用者？

接下來，讓我們看看如何查詢現有使用者的資訊。

要顯示所有現有 MySQL 使用者（包括其使用者和主機元件以及當前使用的認證外掛），您可以從 mysql.user 資料庫中 SELECT 這些欄位：

SELECT user,host,plugin FROM mysql.user
+------------------+-----------+-----------------------+
  user             | host      | plugin                |
+------------------+-----------+-----------------------+
  mary             | localhost | auth_socket           |
  mysql.infoschema | localhost | caching_sha2_password |
  mysql.session    | localhost | caching_sha2_password |
  mysql.sys        | localhost | caching_sha2_password |
  root             | localhost | caching_sha2_password |
  useradmin        | localhost | caching_sha2_password |
+------------------+-----------+-----------------------+
6 rows in set (0.00 sec)

在這裡，我們可以看到系統上定義了六個使用者，所有這些使用者都只能本地登入。其中五個賬戶配置為使用密碼認證。'mary'@'localhost' 賬戶配置為使用 Unix 套接字認證。

我們可以透過使用 SHOW CREATE USER 命令查詢有關使用者屬性的更多資訊。儘管它的名稱如此，但它顯示的是使用者賬戶的所有當前屬性，不一定是最初建立賬戶時使用的屬性。

SHOW CREATE USER 命令接受賬戶名稱作為引數：

SHOW CREATE USER '<user>'@'<host>'\G

通常最好用 \G 語句終止符而不是通常的冒號（;）來結束命令，這樣您就可以更清晰地檢視結果。

要顯示 'useradmin'@'localhost' 賬戶的屬性，您可以鍵入：

SHOW CREATE USER 'useradmin'@'localhost'\G
*************************** 1. row ***************************
CREATE USER for useradmin@localhost: CREATE USER 'useradmin'@'localhost' IDENTIFIED WITH 'caching_sha2_password' AS '$A$005$O
                                              Rl7lM;@Gt{roB4EWchqDdYM142Lq7pfzcCNiK4yUxnRBlrAgr0sE3' REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK PASSWORD HISTORY DEFAULT PASSWORD REUSE INTERVAL DEFAULT PASSWORD REQUIRE CURRENT DEFAULT
1 row in set (0.00 sec)

如何更改現有 MySQL 使用者？

您可以使用 ALTER USER 命令更改 MySQL 中的現有使用者。此命令可用於更改賬戶的大多數使用者相關屬性，但賬戶許可權除外，許可權由 GRANT 和 REVOKE 命令控制。

ALTER USER 的基本語法如下：

ALTER USER <user> <properties_to_change>;

如何更改 MySQL 使用者的密碼？

對於大多數人來說，ALTER USER 最常見的用途是修改密碼。

例如，您可以透過鍵入以下內容修改 'kamal'@'localhost' 的密碼：

ALTER USER 'kamal'@'localhost' IDENTIFIED BY '<new_password>';

如果您想為使用者設定一個他們必須立即替換的臨時密碼，您可以同時設定和過期密碼：

ALTER USER 'kamal'@'localhost' IDENTIFIED BY '<new_password>' PASSWORD EXPIRE;

您總是可以更改自己的密碼，即使沒有 CREATE USER 許可權。最簡單的方法是使用 USER() 函式自動填寫您自己的使用者名稱：

ALTER USER USER() IDENTIFIED BY '<new_password>';

如何更改 MySQL 使用者的認證外掛？

您還可以更改用於認證賬戶的機制或外掛。

在前面的示例中，我們配置了一個名為 'mary'@'localhost' 的賬戶使用 Unix 套接字認證。如果以後我們想將該賬戶更改為使用常規密碼認證，我們可以再次使用 ALTER USER 命令。

首先，確定伺服器的預設認證外掛。如果它是基於密碼的認證方法，最好重新使用預設選擇：

SHOW VARIABLES LIKE 'default_authentication_plugin';

在這種情況下，預設的認證外掛是 caching_sha2_password，因此在切換到密碼認證時我們將使用它。

現在，將 'mary'@'localhost' 更改為使用 caching_sha2_password 外掛並設定新密碼：

ALTER USER 'mary'@'localhost' IDENTIFIED WITH 'caching_sha2_password' BY '<marys_password>';

'mary'@'localhost' 使用者將不再能夠使用 Unix 套接字認證登入，但他們可以使用提供的密碼登入。

如何登入 MySQL？

我們已經討論瞭如何建立和修改 MySQL 使用者賬戶，包括認證。但是，我們還沒有討論如何實際使用這些認證方法登入。

mysql 客戶端是一個功能強大的命令列客戶端，可用於連線本地和遠端資料庫。我們將使用它來討論如何使用我們上面配置的方法進行認證。

如何使用密碼登入本地資料庫？

要使用帶密碼的使用者賬戶登入本地託管的 MySQL 資料庫，基本語法如下：

mysql --user=<username> --password <dbname>

因此，如果 'kamal'@'localhost' 使用者想登入 MySQL 並從託管系統的計算機連線到 testing 資料庫，他們可以鍵入：

mysql --user=kamal --password testing

mysql 客戶端將提示輸入 'kamal'@'localhost' 的密碼。如果您提供正確的憑據，您將連線到 testing 資料庫。

在命令列上指定資料庫是可選的。如果未指定，您將連線到伺服器，但不會連線到特定的資料庫。

如何使用 Unix 套接字認證登入本地資料庫？

要使用 Unix 套接字認證登入本地 MySQL 伺服器，您需要以匹配的賬戶名稱登入到您的作業系統。因此，如果我們想使用 Unix 套接字認證來認證 'mary'@'localhost'，我們首先必須以名為 mary 的使用者名稱登入到我們的計算機。

一旦您使用正確的作業系統賬戶，您可以透過執行客戶端，無需任何選項，直接連線到本地資料庫。

mysql

和以前一樣，您可以選擇追加一個數據庫名稱以連線到您想要的特定資料庫。

如何使用密碼登入遠端資料庫？

如果您的 MySQL 伺服器不在本地伺服器上執行，您將不得不指定客戶端應嘗試連線的主機。您可以透過新增 --host 選項來做到這一點。

大多數情況下，您將使用密碼認證遠端 MySQL 伺服器，因此命令看起來像這樣：

mysql --user=<username> --password --host=<host> <dbname>

因此 'tanya'@'<tanyas_domain>' 可以透過鍵入以下命令連線到位於 example.com 的 MySQL 伺服器：

mysql --user='tanya' --password --host='example.com'

如何刪除 MySQL 使用者？

保留不再有用的使用者賬戶存在安全風險。您可以使用 DROP USER 命令輕鬆刪除賬戶。

基本語法如下：

DROP USER '<user>'@'<host>';

因此，要刪除 'mary'@'localhost' 使用者，您可以鍵入：

DROP USER 'mary'@'localhost';

如果您嘗試刪除不存在的使用者，您將收到錯誤：

ERROR 1396 (HY000): Operation DROP USER failed for 'mary'@'localhost'

為了避免這種情況，您可以在賬戶名稱前新增 IF EXISTS 子句。如果使用者存在，它將被刪除。如果不存在，則只給出警告：

Query OK, 0 rows affected, 1 warning (0.00 sec)

總結

MySQL 的使用者賬戶管理和認證配置非常靈活。學習如何在 MySQL 中建立、修改和獲取使用者資訊將幫助您更有效地管理您的資料庫系統。

安全最佳實踐要求您為每個獨特的用例建立賬戶，並僅授予執行其範圍所需的訪問級別。賬戶建立和認證是此過程的第一個階段。在另一份指南中，我們將討論授予和撤銷許可權以實現該策略的另一部分。

常見問題解答

更改使用者密碼最常見的方法是使用 ALTER USER。例如，您可以使用以下命令修改 'kamal'@'localhost' 的密碼：

ALTER USER 'kamal'@'localhost' IDENTIFIED BY '<new_password>';

您還可以使用以下命令設定臨時密碼：

ALTER USER 'kamal'@'localhost' IDENTIFIED BY '<new_password>' PASSWORD EXPIRE;

嘗試以 root 使用者身份登入 MySQL 資料庫時，您可能會收到錯誤，原因可能有多種。MySQL 在其文件中闡述了您可以進行的故障排除檢查。

一些常見情況包括需要重置 root 使用者密碼或編輯配置檔案。

您可以使用 DROP USER 命令在 MySQL 中刪除使用者。基本語法如下：

DROP USER '<user>'@'<host>';

為避免嘗試刪除不存在的使用者，您可以在賬戶名稱前包含 IF EXISTS 子句。

要列出 MySQL 例項中的所有使用者，您可以使用 mysql.user 資料庫上的 SELECT 語句。當同時包含 host 和 plugin 時，語法如下：

SELECT user,host,plugin FROM mysql.user;

要顯示當前使用者的許可權，您可以使用以下語法：

SHOW GRANTS;

對於其他使用者，您可以使用：

SHOW GRANTS FOR '<user>'@'<host>';
關於作者
Justin Ellingwood

賈斯汀·埃林伍德 (Justin Ellingwood)

賈斯汀自 2013 年以來一直在撰寫有關資料庫、Linux、基礎設施和開發者工具的文章。他目前與妻子和兩隻兔子住在柏林。他通常不必以第三人稱寫作，這對所有相關方來說都是一種解脫。
在 GitHub 上編輯此頁面

Prisma 的資料指南

一個不斷增長的文章庫，旨在使資料庫更易於理解。

由 Prisma 用 ❤️ 製作
© . This site is unofficial and not affiliated with Prisma Data, Inc.