如何在 MySQL 中管理用户和身份验证

我们将使用的命令

要在 MySQL 中创建、修改和删除用户，您需要的核心命令是

• CREATE USER：创建一个新的用户帐户
• ALTER USER：更改现有用户帐户
• DROP USER：删除现有用户帐户

所需权限

要执行上述命令，您需要使用具有 CREATE USER 权限的帐户登录 MySQL。CREATE USER 权限允许您创建、修改、删除和重命名用户等操作。我们还需要在 mysql 数据库上具有 SELECT 权限，才能查看有关现有用户的信息。

按优先级顺序，您应该使用以下帐户登录：

• 在 mysql 数据库上具有 CREATE USER 权限和 SELECT 权限的受限帐户
• 在 MySQL 中具有完全权限的 root 或管理用户

MySQL 的用户帐户语法是什么？

在 MySQL 中，用户帐户由两个独立的信息部分组成，用 at 符号 (@) 连接

• 用户名
• 用户连接的主机

通常，系统上的用户帐户看起来像这样

'<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 列作为主排序字段，并优先考虑具有更具体值的结果。因此，文字值被排序到顶部作为最高优先级，而那些使用通配符（如 %）的值被排序到底部。最后的条目是那些仅包含 % 而没有其他字符的条目，其次是那些具有完全空白主机的条目。

User 列用作具有相同 Host 值的任何条目的辅助排序字段。再次，更精确的匹配项被优先考虑。由于 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 套接字身份验证的用户？

虽然密码身份验证是大多数用户最常见的身份验证方法，但它不是唯一的选择。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 用户验证到 'mary'@'localhost' MySQL 用户。以 mary 身份登录后，连接到数据库，无需提供任何用户名或密码

mysql

您应该通过您配置的 Unix 套接字身份验证自动登录。

如何更改 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 --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'