如何在 MySQL 中创建新用户 – wiki基地

作者： /

MySQL 用户管理：创建新用户全面指南

数据库是应用程序的核心，存储着宝贵的数据。为了保障数据的安全性和系统的稳定性，合理的权限管理至关重要。在 MySQL 中，用户是访问和操作数据库的基本单位。通过创建不同的用户并赋予精细的权限，我们可以实现对数据访问的严格控制，遵循“最小权限原则”，降低安全风险。

本文将深入探讨如何在 MySQL 中创建新用户，从基础的 CREATE USER 语句到复杂的权限赋予 (GRANT)，涵盖用户身份验证、主机限制、密码管理、权限类型、版本差异以及最佳实践等多个方面，为您提供一份全面、详细的创建用户操作指南。

引言：为何需要创建新用户？

初次安装 MySQL 后，通常会有一个默认的 root 用户。这个 root 用户拥有对整个 MySQL 服务器的最高权限，可以执行任何操作。然而，在实际生产环境中，将所有应用程序或用户都使用 root 账户来访问数据库是非常危险的。一旦 root 账户的密码泄露，整个数据库服务器将完全暴露，面临数据被窃取、篡改甚至删除的风险。

创建新用户，并为不同的应用或人员分配仅满足其工作需求的最低权限，是保障数据库安全性的第一步，也是最重要的一步。这不仅限制了潜在攻击的损害范围，也便于审计和追踪操作行为。

前置条件

在开始创建新用户之前，您需要满足以下条件：

  1. 拥有 MySQL 数据库的访问权限： 您需要能够连接到目标 MySQL 服务器。
  2. 拥有足够的权限来创建用户和授权： 通常情况下，您需要使用具有 CREATE USER 权限的用户（如 root 用户）来执行用户创建操作，并拥有 GRANT OPTION 权限（或全局的 ALL PRIVILEGES）来执行授权操作。

创建新用户核心步骤

在 MySQL 中创建并配置一个可用的用户，通常需要完成以下三个核心步骤：

  1. 创建用户账户： 使用 CREATE USER 语句定义一个新的用户，并指定其用户名、允许连接的主机以及初始的身份验证方式（如密码）。
  2. 赋予权限： 使用 GRANT 语句为用户分配对特定数据库、表或其他对象的访问和操作权限。
  3. （可选）刷新权限： 在某些情况下（尤其是在旧版本的 MySQL 或直接修改权限表后），可能需要使用 FLUSH PRIVILEGES 命令使权限更改生效。但在 MySQL 8.0 及以上版本中，CREATE USER 和 GRANT 语句通常会立即生效，不再强制要求刷新。

下面我们将详细展开这三个步骤。

第一步：使用 CREATE USER 创建用户账户

CREATE USER 语句用于在 MySQL 服务器中创建一个新的用户账户。一个完整的用户账户由 用户名 (user) 和 允许连接的主机 (host) 两部分组成，格式通常是 'username'@'host'。这两部分共同唯一标识一个用户账户。这意味着，同一个用户名如果来自不同的主机，将被视为不同的用户。

基本语法：

sql
CREATE USER 'username'@'host' [IDENTIFIED BY 'password' | IDENTIFIED WITH authentication_plugin [AS 'plugin_data']];

让我们详细解析这个语法：

  • 'username':

    • 指定新用户的名称。用户名是字符串，需要用单引号括起来。
    • 用户名可以包含字母、数字、下划线 (_)、美元符号 ($) 等字符。
    • 用户名有最大长度限制，通常为 32 个字符，但取决于具体的 MySQL 版本和配置。
    • 避免使用保留字作为用户名。

  • 'host':

    • 指定允许用户从哪个主机连接到 MySQL 服务器。主机是字符串，需要用单引号括起来。
    • 这是用户身份识别中非常关键的一部分，决定了用户从哪里可以登录。

    • 常见的主机指定方式：

      • 'localhost': 只允许从运行 MySQL 服务器的本机连接。这是最安全和推荐的设置，尤其对于应用程序在数据库服务器本地的情况。
      • '%': 允许从任何主机连接。这是最不安全的设置，应该尽量避免在生产环境中使用，除非有充分的理由和额外的安全措施（如防火墙）。使用 % 相当于开放了该用户从全球任何地方连接的可能。
      • '192.168.1.100': 允许从特定的 IPv4 地址连接。
      • '192.168.1.%': 允许从一个 IPv4 地址范围（子网）连接。
      • 'hostname.example.com': 允许从特定的主机名连接。MySQL 会解析主机名对应的 IP 地址。需要注意的是，主机名解析可能会带来额外的开销和潜在的安全风险（如 DNS 欺骗），因此通常更推荐使用 IP 地址。
      • '::1': 允许从 IPv6 的本机地址连接。
      • '2001:db8::/32': 允许从一个 IPv6 地址范围连接。

    • 重要概念： MySQL 按照权限表中用户记录的顺序来匹配连接请求。通常，更具体的主机规则（如 192.168.1.100 或 localhost）会排在更宽泛的规则（如 192.168.1.% 或 %）之前被匹配。因此，如果您为同一个用户创建了 'myuser'@'localhost' 和 'myuser'@'%' 两个账户，当从本机连接时，会优先匹配 'myuser'@'localhost' 的权限。这允许您为来自不同位置的同一用户名设置不同的权限策略。

  • IDENTIFIED BY 'password':

    • 指定用户的密码。密码是字符串，需要用单引号括起来。
    • MySQL 并不存储明文密码，而是存储密码的哈希值。
    • 密码策略（如长度、复杂度）可以在 MySQL 服务器配置中设置。
    • 安全警告： 在 CREATE USER 语句中直接写明文密码存在风险，特别是在日志文件或历史命令中可能被记录。更安全的做法是使用 IDENTIFIED BY 子句，但在实际操作中，如果是在安全的终端会话中执行，这种方式是常见的。

  • IDENTIFIED WITH authentication_plugin [AS 'plugin_data']:

    • 从 MySQL 5.6 版本引入，用于指定用户使用的身份验证插件。这是更现代和灵活的方式。
    • authentication_plugin 是插件的名称，例如：
      • mysql_native_password: MySQL 传统的、基于 SHA1 哈希的密码验证方法。
      • caching_sha2_password: MySQL 8.0 的默认身份验证插件，使用 SHA256 哈希，并带有客户端缓存，提高了安全性。
      • sha256_password: 使用 SHA256 哈希，但不带客户端缓存。
      • 其他插件如 PAM（可插入身份验证模块）、LDAP 等，用于集成企业级身份验证系统。

    • AS 'plugin_data': 可选，为插件提供额外的数据，例如对于 PAM 插件可能是服务名称。对于密码插件，通常不需要 AS 子句。

    • MySQL 8.0 的重要变化： 在 MySQL 8.0 中，默认的身份验证插件从 mysql_native_password 更改为 caching_sha2_password。这提高了安全性，但也可能导致使用旧版本客户端库或连接器的应用程序无法连接，因为它们可能不支持新的插件。如果在创建用户时遇到兼容性问题，可以考虑显式地使用 IDENTIFIED WITH mysql_native_password 创建用户，或者升级客户端库。

CREATE USER 示例：

  1. 创建本地用户，使用传统密码认证：
    sql
    CREATE USER 'app_user'@'localhost' IDENTIFIED BY 'secure_password';
    这个用户只能从 MySQL 服务器所在的机器连接，使用指定的密码和 mysql_native_password（如果这是服务器默认插件或显式指定）。

  2. 创建允许任意主机连接的用户，使用 MySQL 8.0 默认认证：
    sql
    CREATE USER 'remote_user'@'%' IDENTIFIED BY 'another_strong_password';
    警告： '%' 不安全，除非有必要且配合其他安全措施。在 MySQL 8.0 中，默认会使用 caching_sha2_password 进行密码哈希。

  3. 创建允许特定 IP 地址连接的用户，显式指定传统认证：
    sql
    CREATE USER 'dev_user'@'192.168.1.200' IDENTIFIED WITH mysql_native_password BY 'dev_password123';
    这个用户只能从 IP 地址为 192.168.1.200 的主机连接，使用指定的密码和传统的 mysql_native_password 认证。

  4. 创建允许某个子网连接的用户：
    sql
    CREATE USER 'monitor'@'192.168.1.%' IDENTIFIED BY 'monitor_pass';
    这个用户可以从 192.168.1.0/24 这个子网内的任何主机连接。

  5. 创建不带密码的用户 (不推荐用于普通用户)：
    sql
    CREATE USER 'nopass_user'@'localhost';
    这样的用户没有密码。通常只用于特定的身份验证插件（如 PAM）或作为临时用途，不推荐用于需要密码保护的场景。

关于用户创建的其他选项：

CREATE USER 语句还可以包含一些高级选项，用于限制用户的资源使用或要求特定的连接方式：

sql
CREATE USER 'user'@'host' ...
[REQUIRE {NONE | SSL | X509}]
[WITH {MAX_QUERIES_PER_HOUR count |
MAX_UPDATES_PER_HOUR count |
MAX_CONNECTIONS_PER_HOUR count |
MAX_USER_CONNECTIONS count}];

  • REQUIRE {NONE | SSL | X509}: 要求用户使用加密连接。SSL 需要 SSL 证书，X509 需要客户端提供有效的 X.509 证书进行身份验证。这对于提高连接安全性非常有用。
  • WITH MAX_..._PER_HOUR count: 限制用户每小时可以执行的查询、更新或连接次数。
  • WITH MAX_USER_CONNECTIONS count: 限制用户可以同时建立的最大连接数。

这些选项可以帮助您更好地管理和限制用户的行为，提升系统的稳定性和安全性。

执行 CREATE USER 语句后，如果成功，用户账户就被创建了。但此时用户还没有任何权限，无法执行任何有意义的操作（除了连接服务器并执行少数基本命令）。

第二步：使用 GRANT 赋予权限

创建用户后，下一步是使用 GRANT 语句为用户分配操作数据库对象的权限。权限可以应用于不同的级别：全局级别、数据库级别、表级别、列级别、存储过程/函数级别等。遵循“最小权限原则”意味着只授予用户完成其任务所需的最少权限。

基本语法：

sql
GRANT privilege_type [(column_list)] [, privilege_type [(column_list)]] ...
ON object_type object_name
TO 'username'@'host' [IDENTIFIED BY 'password']
[REQUIRE {NONE | SSL | X509}]
[WITH {GRANT OPTION | resource_options}];

让我们解析这个语法的重要部分：

  • privilege_type [(column_list)]:

    • 指定要授予的权限类型。MySQL 提供了非常丰富的权限列表。常见的权限类型包括：
      • 数据操作权限： SELECT, INSERT, UPDATE, DELETE, TRUNCATE
      • 数据结构操作权限 (DDL)： CREATE, ALTER, DROP, INDEX
      • 管理权限： ALL PRIVILEGES (所有权限，非常危险，应谨慎使用), RELOAD (允许执行 FLUSH 操作), SHUTDOWN (允许关闭服务器), SUPER (超级用户权限，可以执行许多维护和管理操作), PROCESS (允许查看服务器进程), FILE (允许使用 SELECT ... INTO OUTFILE 和 LOAD DATA INFILE), CREATE USER (允许创建、删除、重命名用户和撤销所有权限), GRANT OPTION (允许用户授予他自己拥有的权限给其他用户)。
      • 复制权限： REPLICATION SLAVE (用于配置复制从库), REPLICATION CLIENT (用于执行 SHOW MASTER STATUS 等命令)。
      • 存储过程/函数权限： CREATE ROUTINE, ALTER ROUTINE, EXECUTE
      • 视图权限： CREATE VIEW, SHOW VIEW
    • ALL PRIVILEGES 授予指定对象上的所有权限。
    • 对于 SELECT, INSERT, UPDATE, REFERENCES 权限，可以在括号中指定 column_list，将权限限制在表的特定列上。例如：GRANT SELECT (col1, col2) ON my_table TO 'user'@'host';

  • ON object_type object_name:

    • 指定权限作用的对象。object_type 是对象类型（如 TABLE, FUNCTION, PROCEDURE），通常可以省略，直接指定 object_name。
    • object_name 指定具体的对象，格式通常是 database_name.table_name。
    • 常见的对象指定方式：
      • *.*: 全局级别。权限应用于服务器上的所有数据库和所有对象。例如：GRANT SELECT ON *.* TO 'monitor'@'%'; (允许从任何主机查询所有数据库)。
      • database_name.*: 数据库级别。权限应用于指定数据库中的所有对象。例如：GRANT SELECT, INSERT, UPDATE, DELETE ON app_db.* TO 'app_user'@'localhost'; (允许用户在 app_db 数据库中进行基本的 CRUD 操作)。
      • database_name.table_name: 表级别。权限应用于指定数据库中的指定表。例如：GRANT SELECT ON financial_db.sales_table TO 'report_user'@'192.168.1.10';
      • database_name.procedure_name 或 database_name.function_name: 存储过程或函数级别。需要指定 ON PROCEDURE 或 ON FUNCTION。例如：GRANT EXECUTE ON PROCEDURE my_db.my_procedure TO 'proc_exec'@'localhost';

  • TO 'username'@'host':

    • 指定要授予权限的用户账户。这里的 'username'@'host' 必须是您在第一步中已经使用 CREATE USER 创建的账户。

  • [IDENTIFIED BY 'password']:

    • 注意： 在较新的 MySQL 版本（如 8.0+）中，在 GRANT 语句中使用 IDENTIFIED BY 子句来创建用户或修改密码已经被弃用 (deprecated)。推荐的做法是先使用 CREATE USER 创建用户并设置密码/认证方式，然后再使用 GRANT 赋予权限。虽然某些旧版本可能仍然支持，但为了兼容性和最佳实践，请避免在 GRANT 中使用它。

  • [REQUIRE {NONE | SSL | X509}]:

    • 与 CREATE USER 中的 REQUIRE 子句相同，用于指定用户连接时必须满足的连接方式。

  • [WITH {GRANT OPTION | resource_options}]:

    • WITH GRANT OPTION: 非常重要的选项！ 如果授予了这个选项，用户就可以将他自己拥有的 当前被授予的这些权限 再授予给其他用户。这提供了权限的传递能力，但同时也增加了安全风险。通常应该谨慎授予这个选项，只给那些需要管理用户权限的用户（如管理员或特定的应用程序）。例如：GRANT ALL PRIVILEGES ON app_db.* TO 'admin_user'@'localhost' WITH GRANT OPTION; (这个用户可以在 app_db 数据库上做任何事，并且可以将这些权限分发给其他用户)。
    • resource_options: 与 CREATE USER 中的资源限制选项相同。

GRANT 示例：

  1. 授予用户在特定数据库上的所有权限（无 GRANT OPTION）：
    sql
    GRANT ALL PRIVILEGES ON my_application_db.* TO 'app_user'@'localhost';
    app_user 现在可以在 my_application_db 数据库中执行所有操作，但不能将这些权限授予其他人。

  2. 授予用户在特定表上的读写权限：
    sql
    GRANT SELECT, INSERT, UPDATE, DELETE ON my_application_db.user_data TO 'data_writer'@'192.168.1.50';
    data_writer 只能在 user_data 表上执行查询、插入、更新和删除操作。

  3. 授予用户全局的查询权限（通常用于监控工具）：
    sql
    GRANT SELECT ON *.* TO 'monitor'@'192.168.1.%';
    monitor 用户可以查询服务器上的所有数据（如果 SELECT 权限不是列级别的），但不能修改数据或结构。

  4. 授予用户执行存储过程的权限：
    sql
    GRANT EXECUTE ON PROCEDURE report_db.generate_report TO 'report_exec'@'localhost';

  5. 授予用户创建其他用户的权限 (需要全局 CREATE USER 权限)：
    sql
    GRANT CREATE USER ON *.* TO 'user_manager'@'localhost';
    注意：CREATE USER 是全局权限，因此对象必须是 *.*。拥有此权限的用户可以创建、删除、重命名用户。结合 GRANT OPTION 才能授予其他权限。

撤销权限 (REVOKE)：

如果需要移除用户的权限，可以使用 REVOKE 语句，其语法与 GRANT 类似，但功能相反。

“`sql
REVOKE privilege_type [(column_list)] [, privilege_type [(column_list)]] …
ON object_type object_name
FROM ‘username’@’host’;

— 撤销 GRANT OPTION 需要单独指定
REVOKE GRANT OPTION ON database_name.* FROM ‘username’@’host’;
“`

例如：
sql
REVOKE INSERT, DELETE ON my_application_db.user_data FROM 'data_writer'@'192.168.1.50';

第三步：刷新权限（在多数新版本中不再强制）

在旧版本的 MySQL 中（如 5.6 之前的版本），或者当您直接修改了 mysql 系统数据库中的权限表（如 mysql.user, mysql.db, mysql.tables_priv 等）时，需要运行 FLUSH PRIVILEGES; 命令来重新加载权限到内存中，使更改生效。

然而，在 MySQL 8.0 及以上版本中，CREATE USER, DROP USER, RENAME USER, GRANT, REVOKE, SET PASSWORD 等语句在执行成功后，权限更改通常会立即生效，不再需要手动执行 FLUSH PRIVILEGES;。这是因为这些语句会直接操作内存中的权限缓存并使其失效，MySQL 会自动重新加载受影响的权限。

何时可能还需要 FLUSH PRIVILEGES？

  • 如果您连接的是非常老的 MySQL 版本。
  • 如果您通过直接修改 mysql 数据库中的权限表来更改权限（强烈不推荐这种做法）。
  • 执行了某些特定的非权限相关的 SET 语句可能需要刷新其他配置。

总结： 对于使用 CREATE USER 和 GRANT 创建和授权的典型场景，如果您使用的是 MySQL 8.0 或更高版本，通常不需要执行 FLUSH PRIVILEGES;。但了解它的作用和历史原因是有益的。

验证用户和权限

创建用户并赋予权限后，您应该验证这些操作是否成功，以及用户是否拥有预期的权限。

  1. 查看所有用户：
    sql
    SELECT user, host FROM mysql.user;
    这个查询会列出 mysql.user 表中所有的用户账户及其允许连接的主机。确认您创建的用户是否在列表中。

  2. 查看特定用户的权限：
    sql
    SHOW GRANTS FOR 'username'@'host';
    例如：
    sql
    SHOW GRANTS FOR 'app_user'@'localhost';
    这个命令会显示该用户被直接授予的所有 GRANT 语句。它会列出全局权限、数据库权限、表权限等。
    如果您想看用户实际有效的权限（包括通过角色继承的权限，如果使用角色的话），可以使用：
    sql
    SHOW GRANTS FOR 'username'@'host' USING role1, role2; -- 如果使用了角色
    -- 或者查看所有可能的权限路径（较复杂）
    对于大多数情况，SHOW GRANTS FOR 'username'@'host' 已经足够检查直接授予的权限。

  3. 测试连接和操作：
    使用新创建的用户账户从允许的主机尝试连接到 MySQL 服务器，并执行一些需要权限的操作，例如：

    • 连接到数据库：mysql -u app_user -h localhost -p my_application_db
    • 执行查询：SELECT * FROM your_table LIMIT 1;
    • 尝试执行不允许的操作：DROP TABLE your_table; (如果用户没有 DROP 权限，应该会报错)。

通过实际测试，可以确保用户能够连接并且权限设置符合预期。

删除用户账户

如果一个用户不再需要，应该及时将其删除，以减少不必要的账户，降低安全风险。使用 DROP USER 语句可以删除用户。

语法：

sql
DROP USER 'username'@'host' [, 'username'@'host'] ...;

可以一次删除一个或多个用户。

示例：
sql
DROP USER 'old_user'@'localhost';
DROP USER 'temp_user'@'%', 'another_temp'@'192.168.1.10';

执行 DROP USER 后，该用户账户将被删除，所有直接授予给该用户的权限也会被自动撤销。

最佳实践与安全建议

创建和管理用户是数据库安全的关键环节。遵循以下最佳实践可以大大提高数据库的安全性：

  1. 遵循最小权限原则 (Principle of Least Privilege)： 永远只授予用户执行其任务所需的最低权限。例如，一个只读报表应用只需要 SELECT 权限，不需要 INSERT, UPDATE, DELETE 或 DROP 权限。
  2. 为不同的应用和目的创建独立的用户： 不要让多个应用或不同的功能模块共享同一个数据库用户。独立的账户便于追踪每个用户/应用的活动，并且在一个账户泄露时，只影响使用该账户的部分功能或应用。
  3. 限制主机 (Host)： 尽可能精确地指定允许用户连接的主机。避免使用 %，除非绝对必要且配合强大的防火墙规则。将应用程序用户限制在应用服务器的 IP 地址，将管理用户限制在管理主机的 IP 地址。
  4. 使用强密码： 为用户设置复杂、难以猜测的密码，并定期更换。利用 MySQL 的密码策略功能（如 validate_password 组件）来 enforce 密码强度要求。
  5. 不要使用 ‘root’ 账户进行日常应用连接： root 账户只应用于数据库的管理和维护工作。应用程序应该使用权限受限的专门账户。
  6. 谨慎使用 GRANT OPTION： GRANT OPTION 允许用户将权限传递。除非明确需要赋予用户管理其他用户的权限，否则不要授予 GRANT OPTION。
  7. 定期审查用户账户和权限： 定期检查数据库中的用户列表，删除不再需要的账户。审查现有用户的权限设置，确保它们仍然符合最小权限原则。
  8. 考虑使用角色 (Roles)： 在 MySQL 8.0+ 中引入了角色功能。角色是一组命名好的权限集合，可以将角色授予用户，从而简化权限管理。当需要修改一组用户的权限时，只需修改角色的权限即可。
  9. 了解并配置身份验证插件： 特别是对于 MySQL 8.0，理解 caching_sha2_password 和 mysql_native_password 的区别和兼容性影响。根据客户端环境选择合适的插件，或配置客户端支持新的插件。
  10. 利用 SSL/TLS 加密连接： 对于敏感数据或在不受信任的网络上连接时，强制用户使用 SSL/TLS 加密连接（使用 REQUIRE SSL 或 REQUIRE X509），防止数据在传输过程中被截获。

MySQL 版本差异重点回顾

  • MySQL 5.7 及更早版本：

    • CREATE USER ... IDENTIFIED BY ... 是主要方式。
    • GRANT ... IDENTIFIED BY ... 可以同时创建用户并授权（不推荐）。
    • 默认身份验证插件通常是 mysql_native_password。
    • 执行 CREATE USER 和 GRANT 后，有时需要 FLUSH PRIVILEGES; 使更改生效。

  • MySQL 8.0 及更高版本：

    • CREATE USER ... IDENTIFIED BY ... 和 CREATE USER ... IDENTIFIED WITH ... 都是常用的方式，推荐后者以明确指定认证插件。
    • GRANT ... IDENTIFIED BY ... 已被弃用。
    • 默认身份验证插件改为 caching_sha2_password，需要客户端支持。
    • CREATE USER 和 GRANT 语句通常会立即生效，不再强制要求 FLUSH PRIVILEGES;。
    • 引入了角色 (Roles) 功能，简化权限管理。

总结

创建新用户是 MySQL 数据库管理的基础操作，也是保障数据库安全性的基石。本文详细介绍了从 CREATE USER 创建账户，到 GRANT 赋予权限，再到验证和删除用户的全过程。理解用户与主机结合的身份标识、各种权限的含义及作用域、不同身份验证方式以及版本间的差异，对于建立健壮安全的数据库访问控制体系至关重要。

始终坚持最小权限原则，为不同用途创建独立账户，并对用户和权限进行定期审查，这些都是构建安全数据库环境不可或缺的环节。希望这篇指南能帮助您更好地理解和实践 MySQL 用户管理。

发表评论

您的邮箱地址不会被公开。 必填项已用 * 标注

滚动至顶部