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

我们将使用的命令

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

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

所需权限

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

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

• 具有 CREATE USER 权限和 mysql 数据库上的 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 会将他们视为会话其余部分的“匿名用户”。通常，匿名用户几乎没有任何权力，并且一旦连接就几乎无法执行任何操作。甚至可能在尝试使用其他用户帐户进行身份验证时意外地以匿名用户身份进行身份验证。

为用户帐户使用通配符主机的挑战在于，包含主机值的其他用户帐户可以轻松地掩盖或使使用通配符的用户帐户不可用。

例如，如果您有一个定义为 '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'