如何在 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>'

可以使用单引号（如上所示）分别包装用户和主机组件的用户名。如果其中任何一个组件包含可能被错误解释的字符，则有时需要它们。通常，添加它们始终是一个好主意，以明确表达。

因此，与其只拥有一个名为 'john' 的帐户，在 MySQL 中，完整的帐户名称将需要某种主机，例如 '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 会将以下帐户按以下顺序排序

基本语法

创建新用户的基本语法比较简单。您使用 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 用户登录到 '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'