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 --user=<username> --password <dbname>

因此，如果 'kamal'@'localhost' 用户想登录 MySQL 并从托管系统的计算机连接到 testing 数据库，他们可以键入：

mysql --user=kamal --password testing

mysql 客户端将提示输入 'kamal'@'localhost' 的密码。如果您提供正确的凭据，您将连接到 testing 数据库。

在命令行上指定数据库是可选的。如果未指定，您将连接到服务器，但不会连接到特定的数据库。

要使用 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 中创建、修改和获取用户信息将帮助您更有效地管理您的数据库系统。

安全最佳实践要求您为每个独特的用例创建账户，并仅授予执行其范围所需的访问级别。账户创建和认证是此过程的第一个阶段。在另一份指南中，我们将讨论授予和撤销权限以实现该策略的另一部分。

常见问题解答

如何在 MySQL 中更改用户密码？

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

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

您还可以使用以下命令设置临时密码：

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

如何修复 MySQL 中的 root 用户访问错误？

尝试以 root 用户身份登录 MySQL 数据库时，您可能会收到错误，原因可能有多种。MySQL 在其文档中阐述了您可以进行的故障排除检查。

一些常见情况包括需要重置 root 用户密码或编辑配置文件。

如何在 MySQL 中删除用户？

您可以使用 DROP USER 命令在 MySQL 中删除用户。基本语法如下：

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

为避免尝试删除不存在的用户，您可以在账户名称前包含 IF EXISTS 子句。

如何显示 MySQL 中的所有用户？

要列出 MySQL 实例中的所有用户，您可以使用 mysql.user 数据库上的 SELECT 语句。当同时包含 host 和 plugin 时，语法如下：

SELECT user,host,plugin FROM mysql.user;

如何在 MySQL 中显示用户的权限？

要显示当前用户的权限，您可以使用以下语法：

SHOW GRANTS;

对于其他用户，您可以使用：

SHOW GRANTS FOR '<user>'@'<host>';

关于作者

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

贾斯汀自 2013 年以来一直在撰写有关数据库、Linux、基础设施和开发者工具的文章。他目前与妻子和两只兔子住在柏林。他通常不必以第三人称写作，这对所有相关方来说都是一种解脱。

MySQL 中授权和认证简介

如何在 MySQL 中使用 `GRANT` 和 `REVOKE` 管理权限

在 GitHub 上编辑此页面

MySQL / 认证和授权

如何在 MySQL 中管理用户和认证

目录

简介

先决条件

我们将使用的命令

所需权限

了解 MySQL 如何定义和解释用户账户

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

MySQL 如何认证用户？

在 MySQL 用户账户定义中不包含用户或主机的含义是什么？

如何创建用户？

基本语法

如何创建带密码的用户？

如何创建具有 Unix 套接字认证的用户？

如何显示现有用户？

如何更改现有 MySQL 用户？

如何更改 MySQL 用户的密码？

如何更改 MySQL 用户的认证插件？

如何删除 MySQL 用户？

总结

常见问题解答

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

Prisma 的数据指南

MySQL / 认证和授权

如何在 MySQL 中管理用户和认证

目录

简介

先决条件

我们将使用的命令

所需权限

了解 MySQL 如何定义和解释用户账户

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

MySQL 如何认证用户？

在 MySQL 用户账户定义中不包含用户或主机的含义是什么？

如何创建用户？

基本语法

如何创建带密码的用户？

如何创建具有 Unix 套接字认证的用户？

如何显示现有用户？

如何更改现有 MySQL 用户？

如何更改 MySQL 用户的密码？

如何更改 MySQL 用户的认证插件？

如何登录 MySQL？

如何使用密码登录本地数据库？

如何使用 Unix 套接字认证登录本地数据库？

如何使用密码登录远程数据库？

如何删除 MySQL 用户？

总结

常见问题解答

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

Prisma 的数据指南