数据库 MySQL

如何在 MySQL 中创建用户并管理权限

本文详细介绍了如何在 MySQL 数据库中创建新用户，授予和撤销不同权限，以及管理数据库访问。内容涵盖 SQL 命令、各种认证插件、安全最佳实践，并提供常见错误解决方案，助你高效安全地管理 MySQL 用户和权限。

阅读时长: 9 分钟
共 4209字
作者: eimoon.com
#MySQL #数据库 #SQL #安全 #运维

简介

MySQL 是一款广受欢迎的开源关系型数据库管理系统（RDBMS），常作为 LAMP 堆栈（Linux、Apache、MySQL、PHP/Python/Perl）的核心组成部分。无论是小型项目还是大型企业级应用，MySQL 都因其高性能、可靠性和灵活性而备受青睐。

本文将提供一份详细的指南，指导你如何在 MySQL 数据库中创建新的用户账户，并根据实际需求授予其细粒度的操作权限。此外，我们还将探讨如何撤销权限、删除用户以及解决常见的用户权限问题，帮助你更安全、高效地管理 MySQL 数据库访问。

前提条件

在开始之前，请确保满足以下条件：

  • 访问 MySQL 数据库实例：本指南假设你拥有一个可访问的 MySQL 数据库实例。示例命令基于 Ubuntu 20.04 系统上的 MySQL 部署，但所涉及的 SQL 原理和命令通常适用于任何 MySQL 环境（包括云提供商托管的 MySQL 数据库，如 DigitalOcean Managed Database）。
  • MySQL 安装：如果你尚未安装 MySQL，可以参考相关教程进行安装。
  • 命令占位符：示例命令中需要替换或自定义的部分将以 your_value 的形式进行标记。

创建新用户

在 MySQL 安装完成后，系统会默认创建一个 root 用户账户，该账户拥有 MySQL 服务器的全部管理权限。出于安全考虑，强烈建议避免在日常操作中使用 root 用户。相反，你应该创建具有最小必要权限的独立用户账户。

在运行 MySQL 5.7 及更高版本的 Ubuntu 系统中，root MySQL 用户通常默认通过 auth_socket 插件进行身份验证，这意味着它无需密码即可通过 sudo 命令访问。你可以使用以下命令进入 MySQL 客户端：

sudo mysql

注意： 如果你的 root MySQL 用户配置为需要密码进行身份验证，则需要使用以下命令并输入密码来访问 MySQL shell：

mysql -u root -p

进入 MySQL 提示符后，你可以使用 CREATE USER 语句来创建新的用户账户。其通用语法如下：

CREATE USER 'username'@'host' IDENTIFIED WITH authentication_plugin BY 'password';
  • username: 你要创建的用户名。
  • host: 允许用户从哪个主机连接到 MySQL 服务器。
    • localhost: 表示用户只能从运行 MySQL 服务器的本地机器连接。
    • %: 表示用户可以从任何主机连接（适用于远程访问）。
    • 特定的 IP 地址或主机名：限制用户只能从指定的 IP 或主机连接。
    • 建议用单引号 ' 包裹用户名和主机名，以避免潜在的语法错误。
  • authentication_plugin: 用于用户身份验证的插件。
    • auth_socket: 提供强大的安全性，无需密码即可认证，但通常阻止远程连接。
    • caching_sha2_password: MySQL 8.0 及更高版本的默认插件，推荐用于需要密码登录的用户，因为它提供了强大的安全功能。
    • mysql_native_password: 较旧但仍安全的插件，适用于与某些旧版本 PHP 应用程序（例如 phpMyAdmin）存在兼容性问题的情况。
  • BY 'password': 为用户设置密码。

示例：创建使用 caching_sha2_password 认证的用户

如果你需要为应用程序或特定服务创建用户，并希望它使用强加密的密码认证，可以使用 caching_sha2_password：

CREATE USER 'sammy'@'localhost' IDENTIFIED BY 'your_strong_password';

兼容性提示：

如果你计划将此数据库与一些较旧的 PHP 应用程序（如某些版本的 phpMyAdmin）一起使用，由于 caching_sha2_password 在某些 PHP 版本中可能存在兼容性问题，你可能需要使用 mysql_native_password 插件创建用户：

CREATE USER 'sammy'@'localhost' IDENTIFIED WITH mysql_native_password BY 'your_strong_password';

如果不确定，你可以先创建 caching_sha2_password 用户，之后再使用 ALTER USER 命令修改其认证插件：

ALTER USER 'sammy'@'localhost' IDENTIFIED WITH mysql_native_password BY 'your_strong_password';

创建新用户后，下一步就是授予其必要的权限。

授予用户权限

授予用户权限的通用语法如下：

GRANT PRIVILEGE ON database.table TO 'username'@'host';
  • PRIVILEGE: 定义用户允许在指定数据库和表上执行的操作。常见的权限包括：
    • SELECT: 读取数据。
    • INSERT: 插入新数据。
    • UPDATE: 修改现有数据。
    • DELETE: 删除数据。
    • CREATE: 创建数据库或表。
    • ALTER: 修改数据库或表结构。
    • DROP: 删除数据库或表。
    • INDEX: 创建或删除索引。
    • REFERENCES: 创建外键约束。
    • RELOAD: 执行 FLUSH 操作（例如刷新权限）。
    • ALL PRIVILEGES: 授予所有可用权限（谨慎使用）。
  • database.table: 指定权限所针对的数据库和表范围：
    • *.*: 表示所有数据库中的所有表，授予全局权限。
    • database_name.*: 表示指定数据库中的所有表。
    • database_name.table_name: 表示指定数据库中的指定表。

你可以在一条 GRANT 命令中，通过逗号分隔的方式，为同一用户授予多个权限。

示例：授予用户细粒度权限

以下命令授予用户 sammy 对所有数据库和表的 CREATE、ALTER、DROP（数据库/表/用户）、INSERT、UPDATE、DELETE、SELECT、REFERENCES 和 RELOAD 权限。

GRANT CREATE, ALTER, DROP, INSERT, UPDATE, DELETE, SELECT, REFERENCES, RELOAD on *.* TO 'sammy'@'localhost';

WITH GRANT OPTION： 如果你想允许该 MySQL 用户将其自身拥有的任何权限授予系统上的其他用户，可以在 GRANT 语句末尾添加 WITH GRANT OPTION：

GRANT SELECT, INSERT ON your_database.* TO 'sammy'@'localhost' WITH GRANT OPTION;

警告：关于 ALL PRIVILEGES

某些情况下，你可能会看到使用 GRANT ALL PRIVILEGES 授予用户所有权限的示例：

GRANT ALL PRIVILEGES ON *.* TO 'sammy'@'localhost' WITH GRANT OPTION;

这种广泛的权限不应轻易授予，因为它会赋予该用户几乎与 root 用户相同的超级用户权限。任何能够访问此 MySQL 用户的人都将对服务器上的每个数据库拥有完全控制权，这带来了巨大的安全风险。始终遵循最小权限原则。

刷新权限（FLUSH PRIVILEGES）

根据 MySQL 官方文档，当你使用 GRANT 等账户管理语句间接修改授权表时，数据库会立即将授权表重新加载到内存中，因此通常不需要在 CREATE USER 或 GRANT 语句后立即运行 FLUSH PRIVILEGES 命令。然而，运行它也不会产生任何负面影响，有时可以作为一种保险措施：

FLUSH PRIVILEGES;

撤销权限（REVOKE）

如果你需要撤销用户的特定权限，其语法与 GRANT 语句类似，但使用 FROM 替代 TO：

REVOKE type_of_permission ON database_name.table_name FROM 'username'@'host';

例如，撤销 sammy 用户在 your_database 上的 INSERT 权限：

REVOKE INSERT ON your_database.* FROM 'sammy'@'localhost';

检查用户权限（SHOW GRANTS）

要查看用户的当前权限，可以使用 SHOW GRANTS 命令：

SHOW GRANTS FOR 'username'@'host';

例如：

SHOW GRANTS FOR 'sammy'@'localhost';

完成 MySQL 用户的创建和权限授予后，你可以退出 MySQL 客户端：

exit

将来，要以新创建的 MySQL 用户身份登录，可以使用以下命令：

mysql -u sammy -p

-p 标志将提示你输入 MySQL 用户的密码以进行身份验证。

删除 MySQL 用户

如果某个用户账户不再需要，你可以使用 DROP USER 命令将其从 MySQL 服务器中删除。此命令的语法如下：

DROP USER 'username'@'host';

将 username 替换为要删除的实际用户名，将 host 替换为用户可以连接的主机名或 IP 地址。例如，要删除名为 sammy 的用户，该用户可以从 localhost 连接，你可以使用：

DROP USER 'sammy'@'localhost';

执行此命令后，指定的用户将从 MySQL 服务器中永久删除。请注意，此操作是不可逆的，因此请务必谨慎使用，并且仅在你确定要删除用户时才执行。

另外，请注意，你不能删除当前连接到 MySQL 服务器的用户。如果你尝试这样做，你将收到一条错误消息。你需要先断开该用户的所有连接，然后才能尝试删除他们。

常见错误与调试

在使用 MySQL 用户和权限管理时，可能会遇到一些常见问题。本节将介绍这些问题及其解决方案。

1. “Access denied for user” 错误

当用户尝试使用不正确的凭据或权限不足连接到 MySQL 数据库时，通常会发生“Access denied for user”（用户访问被拒绝）错误。此错误通常很容易通过检查和调整用户的凭据和权限来解决。

要修复此错误，请按照以下步骤操作：

  • 验证用户凭据：确保用户名、密码和主机正确。仔细检查用户名和密码是否拼写正确，并且主机是否设置为正确的值（例如，localhost、% 或特定的 IP 地址）。

  • 检查权限：确保用户已获得访问数据库所需的权限。你可以通过运行 SHOW GRANTS 命令来查看用户的当前权限：

    SHOW GRANTS FOR 'username'@'localhost';

    这将显示授予用户的当前权限。如果用户缺少必要的权限，可以使用 GRANT 命令授予它们。

  • 特定数据库或表访问：如果用户尝试访问特定的数据库或表，请确保用户已获得该特定数据库或表的权限。例如，要授予特定数据库的所有权限：

    GRANT ALL PRIVILEGES ON database_name.* TO 'username'@'localhost';

    或者，要授予特定表的 SELECT、INSERT、UPDATE、DELETE 权限：

    GRANT SELECT, INSERT, UPDATE, DELETE ON database_name.table_name TO 'username'@'localhost';

通过执行这些步骤，你应该能够解决“Access denied for user”错误，并确保用户拥有访问 MySQL 数据库所需的权限。

2. 用户无法远程连接

如果用户配置为只能从 localhost 连接，但你尝试从远程主机连接，则会失败。要为用户启用远程连接，请确保用户账户配置为允许来自特定主机或 IP 地址的连接。这可以通过使用正确的主机名（通常是 %）向用户授予权限来完成。例如：

GRANT ALL PRIVILEGES ON *.* TO 'username'@'%';

这将授予来自任何主机（%）的用户“username”所有权限。请根据需要调整权限和主机名。

3. Error 1396: Operation CREATE USER failed

错误 1396 通常发生在尝试创建已存在的用户时。MySQL 不允许创建同名且来自同一主机的重复用户。要修复此错误，请确保用户在 MySQL 数据库中尚不存在。

如果你想创建一个同名的新用户，可以先删除现有用户，然后再创建新用户。例如，如果你尝试创建名为 newuser 的用户，但遇到错误 1396，你可以首先使用以下命令检查用户是否已存在：

SELECT User, Host FROM mysql.user WHERE User = 'newuser';

如果用户存在，你可以使用 DROP USER 命令删除现有用户账户：

DROP USER 'newuser'@'%'; -- 替换为实际的主机名

删除现有用户后，你就可以使用以下命令创建新用户账户：

CREATE USER 'newuser'@'%' IDENTIFIED BY 'password';

常见问题 (FAQs)

1. 如何创建权限有限的 MySQL 用户？

要创建权限有限的 MySQL 用户，你需要在创建用户账户后，使用 GRANT 语句指定要授予用户的特定权限。例如，如果你只想授予用户对特定数据库的 SELECT、INSERT、UPDATE 和 DELETE 权限，你可以使用以下命令：

GRANT SELECT, INSERT, UPDATE, DELETE ON database_name.* TO 'username'@'localhost';

这种方法确保用户只能对指定数据库执行指定的动作，从而限制了他们的权限范围。

2. 如何检查 MySQL 用户权限？

要检查 MySQL 用户的权限，你可以使用 SHOW GRANTS 命令。此命令会显示授予指定用户的具体权限列表。语法如下：

SHOW GRANTS FOR 'username'@'localhost';

此命令将显示授予指定用户的所有权限条目。

3. GRANT ALL PRIVILEGES 和特定权限之间有什么区别？

GRANT ALL PRIVILEGES 授予用户对数据库或表的所有可用权限，使其拥有完全的控制权。而授予特定权限则将用户的访问权限限制为仅允许执行指定的动作（例如，只允许 SELECT 和 INSERT）。

授予所有权限可能存在巨大的安全风险，因为它赋予用户对数据库或表的完全控制权。另一方面，授予特定权限遵循最小权限原则，确保用户只能执行其角色所需的动作，从而降低未经授权访问或更改数据的风险。在生产环境中，应尽可能避免使用 ALL PRIVILEGES。

4. 如何允许 MySQL 用户进行远程访问？

要允许 MySQL 用户进行远程访问，你需要在创建或修改用户时，将其主机名设置为 %（表示任何主机）或特定的远程 IP 地址。例如：

CREATE USER 'username'@'%' IDENTIFIED BY 'your_password';
GRANT ALL PRIVILEGES ON *.* TO 'username'@'%';
FLUSH PRIVILEGES;

这会将权限授予来自任何主机的用户，从而允许远程访问。同时，请确保 MySQL 服务器的网络配置（如防火墙、bind-address 设置）允许远程连接。

5. 如何安全地删除 MySQL 用户？

要安全地删除 MySQL 用户，你应遵循以下步骤：

  1. 撤销权限：首先使用 REVOKE 命令撤销授予该用户的所有权限，这是一种良好的实践，尽管 DROP USER 也会删除相关权限，但先撤销有助于清晰管理。
  2. 删除用户：然后，使用 DROP USER 命令删除用户账户。

语法如下：

REVOKE ALL PRIVILEGES ON *.* FROM 'username'@'localhost'; -- 如果之前授予了ALL PRIVILEGES
DROP USER 'username'@'localhost';

这种方法可确保安全地删除用户账户，不会留下任何可能被利用的残留权限。

总结

通过本教程，你已经全面学会了如何在 MySQL 数据库中创建新用户、授予和管理各种权限，以及处理常见的权限问题。掌握这些技能对于维护数据库的安全性和稳定性至关重要。

你可以继续探索和尝试不同 MySQL 用户的权限设置，以适应你的具体应用场景。为了进一步提升你的 MySQL 知识，建议查阅以下相关教程：

关于

关注我获取更多资讯

公众号
📢 公众号
个人号
💬 个人号

本文链接地址：https://blog.eimoon.com/p/mysql-create-user-manage-permissions/

作者：eimoon.com

分享转载说明：本文由作者原创，转载请注明出处。

© 2023 - 2025 longlikun
使用 Hugo 构建
主题 Stack 由 Jimmy 设计