精通 DevOps 工具与最佳实践 - DevOps 知识中心 精通 DevOps 工具与最佳实践 - DevOps 知识中心

授予和撤销MySQL用户权限的最佳实践

使用MySQL的GRANT、REVOKE、SHOW GRANTS和DROP USER安全地管理权限，并提供应用程序和管理员账户的最小权限示例。

授予和撤销MySQL用户权限的最佳实践

MySQL权限决定了您的应用程序、工具和管理员在连接后可以执行的操作。一个拥有广泛权限的账户可能会将一个小错误或密码泄露转变为完整的数据库暴露。

使用GRANT、REVOKE和SHOW GRANTS遵循最小权限原则：每个账户只应拥有其所需的最小权限，并且仅从其使用的特定主机连接。

理解MySQL权限

在深入了解GRANT和REVOKE之前，理解MySQL中不同范围和类型的权限至关重要。权限定义了用户可以对哪些数据库对象执行哪些操作。

MySQL权限可以根据其范围进行分类：

  • 全局权限（*.*）：适用于MySQL服务器上的所有数据库和表。例如SUPER、PROCESS、RELOAD、CREATE USER。
  • 数据库权限（database_name.*）：适用于特定数据库中的所有表和对象。例如SELECT、INSERT、UPDATE、DELETE、CREATE、DROP。
  • 表权限（database_name.table_name）：适用于特定表中的所有列。例如SELECT、INSERT、UPDATE、DELETE、ALTER。
  • 列权限（database_name.table_name.column_name）：适用于表中的特定列。这种情况较少见，但对于高度细粒度的控制很有用。
  • 例程权限（database_name.routine_name）：适用于存储过程和函数，控制EXECUTE和ALTER ROUTINE。
  • 代理权限：允许一个用户充当另一个用户，适用于管理用户身份的应用程序。

一些常见的特定权限包括：

  • SELECT：从表中读取数据。
  • INSERT：向表中添加新行。
  • UPDATE：修改表中的现有行。
  • DELETE：从表中删除行。
  • CREATE：创建数据库、表或索引。
  • DROP：删除数据库、表或索引。
  • ALTER：修改表结构。
  • INDEX：创建或删除索引。
  • REFERENCES：建立外键约束。
  • CREATE VIEW、SHOW VIEW：管理视图。
  • CREATE ROUTINE、ALTER ROUTINE、EXECUTE：管理和执行存储过程和函数。
  • FILE：在服务器主机上读取或写入文件（非常强大，需极其谨慎使用）。
  • GRANT OPTION：允许用户将自己的权限授予其他用户。这是一个非常强大的权限，应谨慎授予。

GRANT命令：安全地授予权限

GRANT命令用于向MySQL用户分配权限。授予权限时，务必考虑最小权限原则——只授予绝对必要的权限。

基本语法

GRANT命令的一般语法是：

GRANT privileges ON object TO 'user'@'host' [WITH GRANT OPTION];
  • privileges：以逗号分隔的权限列表（例如SELECT, INSERT）。
  • object：指定范围（例如全局使用*.*，数据库使用database_name.*，表使用database_name.table_name）。
  • 'user'@'host'：用户账户，包括用户名和允许连接的主机。host可以是IP地址、主机名或通配符（%表示任何主机，localhost表示本地连接）。
  • WITH GRANT OPTION：（可选）允许用户将指定的权限授予其他用户。

使用CREATE USER和ALTER USER创建或更改密码。较旧的MySQL版本允许GRANT ... IDENTIFIED BY，但现代MySQL语法将账户创建分开。

实际示例

让我们逐步介绍一些常见场景。

  1. 创建新用户并授予全局只读访问权限（强烈不推荐）

    CREATE USER 'global_reader'@'localhost' IDENTIFIED BY 'StrongPass123!';
GRANT SELECT ON *.* TO 'global_reader'@'localhost';

    警告：授予SELECT on *.*将允许访问所有数据库和表。这对于应用程序用户来说通常过于宽泛，除非绝对必要用于特定的管理任务，否则应避免。

  2. 为应用程序用户授予对特定数据库的完全访问权限

    一个常见场景是应用程序用户需要在其自己的数据库中管理数据。

    CREATE USER 'app_user'@'localhost' IDENTIFIED BY 'AppPassSecure!';
GRANT SELECT, INSERT, UPDATE, DELETE ON `myapp_db`.* TO 'app_user'@'localhost';

    这里，app_user只能在myapp_db数据库中执行基本的CRUD操作，但不能创建新表或修改模式。

  3. 授予对特定表的只读访问权限

    对于一个只需要读取特定表的报表工具。

    CREATE USER 'report_tool'@'%' IDENTIFIED BY 'ReportSecret!';
GRANT SELECT ON `sales_db`.`orders` TO 'report_tool'@'%';

    '%'主机允许report_tool从任何主机连接，但只能对sales_db中的orders表进行SELECT操作。

  4. 授予GRANT OPTION（极其谨慎使用）

    如果管理员需要委托特定数据库的权限管理。

    CREATE USER 'db_admin'@'localhost' IDENTIFIED BY 'AdminPass#456';
GRANT ALL PRIVILEGES ON `inventory_db`.* TO 'db_admin'@'localhost' WITH GRANT OPTION;

    现在，db_admin可以将inventory_db上的任何权限授予其他用户。这是一个绕过中央控制的强大权限，只有在无法避免时才应使用。

授予权限的技巧

  • 最小权限原则：始终授予用户或应用程序运行所需的最小权限集。除非是专门的数据库管理员账户，否则避免使用ALL PRIVILEGES。
  • 特定主机：尽可能将用户连接限制到特定的IP地址或主机名（'user'@'192.168.1.10'或'user'@'appserver.example.com'），而不是使用'%'。
  • 分离用户：为不同的应用程序或服务创建单独的用户账户，即使它们访问同一个数据库。这样可以隔离潜在的安全漏洞。
  • 应用程序不使用root：切勿为应用程序使用root用户账户。创建专用的、最小权限的用户。

REVOKE命令：有效地撤销权限

REVOKE命令用于从MySQL用户中移除权限。它与GRANT同样重要，用于维护安全的数据库环境，尤其是在角色变更或应用程序退役时。

基本语法

REVOKE命令的一般语法是：

REVOKE privileges ON object FROM 'user'@'host';
  • privileges：要撤销的权限列表，以逗号分隔。
  • object：要撤销的范围（必须与授予权限时的范围匹配）。
  • 'user'@'host'：要撤销权限的用户账户。

实际示例

  1. 从应用程序用户撤销DELETE权限

    如果应用程序不再需要删除数据，或者您想降低其权限。

    REVOKE DELETE ON `myapp_db`.* FROM 'app_user'@'localhost';

    现在，app_user仍然可以SELECT、INSERT和UPDATE，但不能在myapp_db中执行DELETE。

  2. 从委托管理员撤销GRANT OPTION

    如果db_admin不再需要管理其他用户在inventory_db上的权限。

    REVOKE GRANT OPTION ON `inventory_db`.* FROM 'db_admin'@'localhost';

    注意：要撤销GRANT OPTION，您必须在REVOKE语句中明确指定GRANT OPTION。

  3. 撤销对特定数据库的所有权限

    要移除用户对某个数据库的所有权限。

    REVOKE ALL PRIVILEGES ON `old_db`.* FROM 'old_app'@'%';

    警告：在*.*上执行REVOKE ALL PRIVILEGES将撤销所有全局权限，可能包括SUPER、CREATE USER等。在全局范围内使用时要小心。

  4. 删除用户账户

    当用户或应用程序不再需要时，最好完全删除用户。

    DROP USER 'report_tool'@'%';

    此命令将删除用户及其所有关联的权限。

撤销权限的技巧

  • 匹配范围：撤销时，确保对象范围（*.*、database_name.*等）与最初授予权限的方式完全匹配。如果您在database_name.*上授予了SELECT，则必须从database_name.*撤销，而不是database_name.table_name。
  • 验证：在授予或撤销权限后，始终使用SHOW GRANTS FOR 'user'@'host';来确认更改。
  • 跳过不必要的刷新：在GRANT、REVOKE、CREATE USER或DROP USER之后不需要FLUSH PRIVILEGES；MySQL会立即应用这些语句。
  • 考虑级联效应：如果拥有GRANT OPTION的用户已将权限授予其他人，撤销其GRANT OPTION不会自动撤销他们授予的权限。您需要单独撤销这些权限。

MySQL用户权限管理的最佳实践

实施稳健的权限管理策略对于数据库安全至关重要。

1. 最小权限原则（PoLP）

这是黄金法则。只授予用户或应用程序执行其预期功能所需的最小权限。例如：

  • 报表工具需要SELECT。
  • Web应用程序通常需要SELECT、INSERT、UPDATE、DELETE。
  • ETL过程可能需要INSERT、UPDATE、DELETE、CREATE TABLE、DROP TABLE（但仅限于特定的暂存模式）。

2. 专用用户账户

  • 避免共享账户：每个应用程序、服务或管理用户应拥有自己唯一的MySQL用户账户。这有助于审计和跟踪活动。
  • 应用程序不使用root：切勿将应用程序配置为以root用户身份连接。root用户拥有无限制的访问权限，只应由人类管理员用于关键的管理任务。

3. 强密码和密码轮换

  • 为所有MySQL用户账户强制使用强且唯一的密码。如果可用，利用MySQL的密码验证插件。
  • 实施定期的密码轮换策略，特别是对于高权限账户。

4. 主机限制

  • 尽可能将用户连接限制到特定的IP地址或主机名。将'%'替换为localhost、应用程序服务器的IP或网络子网（'user'@'192.168.1.%'）。这可以防止来自未知位置的未授权访问尝试。

5. 定期审计和审查

  • 定期审查所有用户账户及其关联的权限。移除任何过时的账户或不必要的权限。
  • 使用SHOW GRANTS FOR 'user'@'host';检查权限。
  • 考虑使用自动化工具审计大型环境。

6. 记录权限

  • 维护清晰的文档，记录数据库用户、他们的角色以及授予每个用户的权限。这有助于保持一致性并促进安全审计。

7. 分离开发、暂存和生产环境

  • 切勿在开发或暂存环境中使用生产凭据。每个环境应拥有自己的一组不同的用户和权限。

8. 除非绝对必要，否则避免使用GRANT OPTION

  • 授予WITH GRANT OPTION会将权限管理委托给该用户，这可能会绕过中央安全策略。仅将其保留给高度信任的管理用户，并在尽可能最严格的范围上使用。

查看当前权限

要验证分配给用户的权限，请使用SHOW GRANTS命令：

SHOW GRANTS FOR 'username'@'host';

示例：

SHOW GRANTS FOR 'app_user'@'localhost';

输出可能如下所示：

+-------------------------------------------------------------+
| Grants for app_user@localhost                               |
+-------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'app_user'@'localhost'                |
| GRANT SELECT, INSERT, UPDATE ON `myapp_db`.* TO 'app_user'@'localhost' |
+-------------------------------------------------------------+

GRANT USAGE ON *.*行表示该用户没有全局权限，只有连接的能力。

要点

保持MySQL权限管理狭窄且可审查。创建专用账户，限制主机，在最小有用范围内只授予所需操作，并使用SHOW GRANTS检查每次更改。当应用程序或个人不再需要访问权限时，撤销权限或删除账户，而不是留下过时的凭据。