如何在 MySQL 中管理使用者與身分驗證

我們將使用的指令

若要在 MySQL 中建立、修改和刪除使用者，您需要的核心指令如下：

• CREATE USER：建立新的使用者帳戶
• ALTER USER：變更現有使用者帳戶
• DROP USER：移除現有使用者帳戶

必要權限

若要執行上述指令，您需要使用具有 CREATE USER 權限的帳戶登入 MySQL。CREATE USER 權限允許您建立、修改、刪除和重新命名使用者，以及其他動作。我們也需要在 mysql 資料庫上擁有 SELECT 權限，才能查看有關現有使用者的資訊。

依偏好順序，您應該使用以下帳戶登入：

• 具有 CREATE USER 權限和 mysql 資料庫上的 SELECT 權限的受限帳戶
• 在 MySQL 中具有完整權限的 root 或管理員使用者

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

在 MySQL 中，使用者帳戶由兩個獨立的資訊組成，並以 at 符號 (@) 連接：

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

一般來說，系統上的使用者帳戶看起來會像這樣：

'<user>'@'<host>'

單引號可用於個別包裝使用者帳戶的使用者和主機元件，如上所示。如果其中一個元件包含其他情況下會被誤解的字元，則有時是必要的。一般來說，為了明確起見，始終建議新增它們。

因此，在 MySQL 中，完整帳戶名稱需要某種主機，例如 'john'@'localhost'，而不是只有一個名為 'john' 的帳戶。這表示系統上可能有多個 '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 將以下帳戶排序為以下順序：

基本語法

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

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

這將建立一個基本帳戶，而無需設定使用者和主機之外的任何詳細資訊。

如何建立具有密碼的使用者？

通常，您希望在建立使用者時設定身分驗證。您可以透過將選用的 IDENTIFIED BY 子句新增到 CREATE USER 陳述式來執行此操作：

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

這會像之前一樣建立新的使用者帳戶，並同時為該帳戶指派密碼。稍後我們將介紹如何在事後指派密碼，或如何變更使用者的密碼。

如何建立具有 Unix socket 身分驗證的使用者？

雖然密碼身分驗證是大多數使用者最常見的身分驗證方法，但它並不是唯一的選擇。MySQL 提供了許多不同的內部和外部身分驗證機制，您可以設定使用者帳戶以使用這些機制。作為範例，我們將使用 Unix socket 身分驗證設定一個新帳戶。

Unix socket 身分驗證可用於 Linux 或類 Unix 環境，以便作業系統上的帳戶可以存取 MySQL 中的相同帳戶名稱，而無需進一步身分驗證。在此組態中，MySQL 管理員知道作業系統上的使用者帳戶受到嚴格控制。

因此，如果作業系統上有 mary 使用者，如果 Unix socket 身分驗證是定義的身分驗證機制，他們將能夠登入 MySQL 中的 'mary'@'localhost' 帳戶。讓我們立即設定它。

Socket 身分驗證需要 auth_socket 外掛程式，因此首先輸入以下內容來載入外掛程式：

INSTALL PLUGIN auth_socket SONAME 'auth_socket.so';

接下來，建立一個與您作業系統上的使用者帳戶相符的使用者帳戶。在本範例中，我們將使用上面討論的 mary 帳戶。如果您不使用與您的作業系統名稱之一相符的名稱，您將無法使用此使用者進行身分驗證。

若要使用 socket 身分驗證建立使用者，我們需要使用 IDENTIFIED WITH 子句（與先前使用的 IDENTIFIED BY 子句不同）來指定要使用的身分驗證外掛程式：

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

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

mysql

您應該透過您設定的 Unix socket 身分驗證自動登入。

如何變更 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 socket 身分驗證。如果我們稍後想將該帳戶變更為使用傳統密碼身分驗證，我們可以再次使用 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 socket 身分驗證登入，但他們可以使用提供的密碼登入。

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

若要使用具有密碼的使用者帳戶登入本機託管的 MySQL 資料庫，基本語法如下：

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

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

mysql --user=kamal --password testing

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

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

如何使用 Unix socket 驗證登入本機資料庫？

若要使用 Unix socket 驗證登入本機 MySQL 伺服器，您需要以相符的帳戶名稱登入您的作業系統。因此，如果我們想要使用 Unix socket 驗證來驗證 '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'