MySQL权限详解

在MySQL中，权限管理是保障数据安全和合理使用的重要手段。MySQL提供了丰富的权限控制机制，允许管理员对不同用户授予不同级别的操作权限。本文将会对MySQL中的权限管理，以及内核如何实现权限控制进行介绍。

一、权限级别

MySQL 的权限是分层的，权限可以授予不同级别的对象：

全局权限： 作用于整个 MySQL 服务器实例的所有数据库，授予全局权限的用户可以对服务器上的任何数据库和对象进行操作，存储在 mysql.user 表。

1. ALL PRIVILEGES：授予用户所有可用的权限。
2. CREATE：允许用户创建新的数据库和表。
3. DROP：允许用户删除数据库和表。
4. ALTER：允许用户修改表结构，如添加、删除列等。
5. INDEX：允许用户创建和删除索引。
6. GRANT OPTION：允许用户将自己拥有的权限授予其他用户。

数据库权限： 作用于特定数据库（或匹配模式的所有数据库）中的所有对象，存储在 mysql.db 和 mysql.host 表（后者很少用）。

1. CREATE TEMPORARY TABLES：允许用户在指定数据库中创建临时表。
2. SHOW VIEW：允许用户查看指定数据库中视图的定义。
3. CREATE ROUTINE：允许用户在指定数据库中创建存储过程和函数。
4. ALTER ROUTINE：允许用户修改指定数据库中存储过程和函数的定义。
5. EXECUTE：允许用户执行指定数据库中的存储过程和函数。

表权限： 作用于特定表（或匹配模式的所有表）中的所有列，存储在 mysql.tables_priv 表。

1. SELECT：允许用户从表中查询数据。
2. INSERT：允许用户向表中插入新数据。
3. UPDATE：允许用户修改表中的数据。
4. DELETE：允许用户删除表中的数据。
5. REFERENCES：允许用户创建外键关联。

列权限： 作用于特定表中的特定列，存储在 mysql.columns_priv 表。

1. 可以针对表中的特定列授予 SELECT、INSERT、UPDATE 等权限，限制用户只能对指定列进行操作。

程序权限： 作用于存储过程、存储函数，存储在 mysql.procs_priv 表。

1. PROCESS：允许用户查看所有正在执行的线程信息。
2. SHOW DATABASES：允许用户查看所有数据库的列表。
3. RELOAD：允许用户重新加载权限表和刷新日志。
4. SHUTDOWN：允许用户关闭 MySQL 服务器。

global、database、table、column、routines，控制层级示意图：

对权限进行授权管理时，有以下的规则：

• 授予高层级的某个权限，下一个层级的权限同样会获得。

例如：如果授予 user_1 在 全局 的select 权限，那么 user_1 在所有database的所有table 都具有select 权限。

• 部分权限是专属于某一个层级的，不可以在其他层级授权。

例如：create routine权限，仅可以在global 级别以及 database级别授权，不可以在table级别授权。

二、权限表详解

MySQL 的权限信息存储在几个系统表中，主要包括：

• mysql.user：存储全局级别的权限。
• mysql.db：存储数据库级别的权限。
• mysql.tables_priv：存储表级别的权限。
• mysql.columns_priv：存储列级别的权限。

权限表： 位于 mysql 数据库中（user, db, tables_priv, columns_priv, procs_priv），这些表定义了用户账户及其权限。修改权限后，通常需要执行 FLUSH PRIVILEGES; 命令或重启服务器（不推荐）来重新加载权限表，使更改生效。 但使用标准 GRANT 和 REVOKE 语句通常会自动刷新权限。

mysql.user (用户账户和全局权限)

• 存储所有 MySQL 用户账户信息，核心字段：
• Host: 允许连接的主机名/IP/通配符 (如 '%', 'localhost', '192.168.1.%')。
• User: 用户名。
• Password / authentication_string: 加密后的密码 (取决于 MySQL 版本和认证插件)。
• [Privilege]_priv: 一系列的 Y/N 字段，代表全局权限 (如 Select_priv, Insert_priv, Update_priv, Delete_priv, Create_priv, Drop_priv, Reload_priv, Shutdown_priv, Process_priv, File_priv, Grant_priv, References_priv, Index_priv, Alter_priv, Show_db_priv, Super_priv, Create_tmp_table_priv, Lock_tables_priv, Execute_priv, Repl_slave_priv, Repl_client_priv, Create_view_priv, Show_view_priv, Create_routine_priv, Alter_routine_priv, Create_user_priv, Event_priv, Trigger_priv, Create_tablespace_priv, ssl_type, ssl_cipher, x509_issuer, x509_subject, max_questions, max_updates, max_connections, max_user_connections, plugin, authentication_string, password_expired, password_last_changed, password_lifetime, account_locked, Create_role_priv, Drop_role_priv, Password_reuse_history, Password_reuse_time, Password_require_current, User_attributes)。(MySQL 版本不同，字段会略有增减)

作用： 定义谁能连接服务器，以及他们拥有哪些服务器范围的权限（例如管理用户、关闭服务器、查看所有数据库列表等）。如果这里的 Select_priv 是 Y，则该用户可以在任何数据库的任何表上执行 SELECT。

mysql.db (数据库级权限)

• 存储用户对特定数据库的权限，核心字段：
• Host: 主机限制。
• Db: 数据库名。
• User: 用户名。
• Table_name: 表名。
• Grantor: 授予该权限的用户。
• Timestamp: 权限设置时间。
• Table_priv: 一个 SET 类型的字段，包含该表上的权限列表 (如 'Select,Insert,Update,Delete,Create,Drop,Grant,References,Index,Alter,Create View,Show view,Trigger')。
• Column_priv: 一个 SET 类型的字段，表示该用户在该表上拥有哪些列级权限 ('Select,Insert,Update,References')。注意：这里只是声明有列权，具体列权定义在 columns_priv 表。

作用： 定义用户对特定表的权限，权限粒度比数据库级更细。

mysql.columns_priv (列级权限)

• 存储用户对表中特定列的权限，核心字段：
• Host: 主机限制。
• Db: 数据库名。
• User: 用户名。
• Table_name: 表名。
• Column_name: 列名。
• Timestamp: 权限设置时间。
• Column_priv: 一个 SET 类型的字段，包含该列上的权限列表 ('Select,Insert,Update,References')。

作用： 定义用户对特定列的权限（主要是 SELECT, INSERT, UPDATE, REFERENCES），这是最细粒度的权限控制。

mysql.procs_priv (存储过程和函数权限)

• 存储用户对存储过程 (PROCEDURE) 和存储函数 (FUNCTION) 的权限，核心字段：
• Host: 主机限制。
• Db: 数据库名。
• User: 用户名。
• Routine_name: 存储过程或函数的名称。
• Routine_type: 'PROCEDURE' 或 'FUNCTION'。
• Grantor: 授予者。
• Proc_priv: 一个 SET 类型的字段，包含权限 ('Execute,Alter Routine,Grant'）。
• Timestamp: 权限设置时间。

作用： 定义用户是否可以执行 (EXECUTE)、修改 (ALTER ROUTINE) 或授予他人 (GRANT OPTION) 特定存储过程或函数的权限。

三、权限生效顺序与计算

当用户尝试执行一个操作时，MySQL 服务器按以下顺序检查权限（从最具体到最宽泛）：

1. 列级权限 (columns_priv)： 检查是否有针对该操作涉及的具体列的权限。如果有且允许，则执行；如果明确拒绝，则拒绝；如果没有记录，则进入下一步。
2. 表级权限 (tables_priv)： 检查是否有针对该操作涉及的表的权限。如果有且允许，则执行；如果明确拒绝，则拒绝；如果没有记录，则进入下一步。
3. 数据库级权限 (db)： 检查是否有针对该操作涉及的数据库的权限。如果有且允许，则执行；如果明确拒绝，则拒绝；如果没有记录，则进入下一步。
4. 全局权限 (user)： 检查用户的全局权限。如果有且允许，则执行；否则拒绝。

重要规则：

• 权限是叠加的 (OR 逻辑)： 用户最终拥有的权限是他在所有层级 (user, db, tables_priv, columns_priv, procs_priv) 上被授予权限的并集。例如，user 表有全局 SELECT 权限，或者 db 表有某个库的 SELECT 权限，或者 tables_priv 有某个表的 SELECT 权限，或者 columns_priv 有某个列的 SELECT 权限，都意味着用户有权限执行相应的 SELECT。
• 拒绝优先： 虽然权限是 OR 叠加，但 REVOKE 操作可以移除之前授予的权限。此外，在 user 表中显式设置为 'N' 的全局权限会覆盖下层更具体的 'Y' 权限（例如，user.Select_priv='N' 会使用户在任何地方都不能 SELECT，即使 db 或 tables_priv 里有 SELECT 权限）。这是权限生效顺序决定的，user 表是最后检查但具有最高“否定权”。实践中更常见的是通过 REVOKE 语句来精确移除权限，而不是直接去 user 表把全局权限设为 N。
• GRANT OPTION 的特殊性： 拥有 GRANT OPTION 权限的用户可以传播权限，即使他们自己是通过 GRANT OPTION 获得的该权限（但仅限于他们自己拥有的权限）。

权限验证流程：

1. 连接验证： 服务器检查 mysql.user 表，验证提供的 'username'@'host' 是否存在，密码是否正确，账户是否被锁定 (account_locked)，密码是否过期 (password_expired)。验证通过才允许连接。
2. 语句解析： 服务器解析用户提交的 SQL 语句，确定它要访问哪些数据库、表、列或程序。
3. 权限检查： 对于语句中涉及的每一个对象（数据库、表、列、过程），服务器按照 权限层级（列 -> 表 -> 数据库 -> 全局） 依次检查对应的权限表 (columns_priv -> tables_priv -> db -> user)。如果在该层级找到匹配的记录且权限允许 (Y 或 SET 中包含所需权限)，则对该对象的权限检查通过。如果在任何层级找到匹配记录但权限明确拒绝 (例如 user 表中全局权限为 N)，则整个操作被拒绝。如果一直到 user 表都没有找到允许该操作的权限，则操作被拒绝。
4. 执行或拒绝： 如果对所有涉及对象的权限检查都通过，则执行语句；否则返回权限错误 (如 ERROR 1142 (42000): SELECT command denied to user ...)。

四、权限管理操作

创建用户：

-- 创建一个新用户，用户名为 'test_user'，密码为 'test_password'
CREATE USER 'test_user'@'localhost' IDENTIFIED BY 'test_password';

授予权限：

GRANT privilege_type [(column_list)] [, privilege_type [(column_list)] ...]
ON [object_type] privilege_level
TO user_or_role [, user_or_role ...]
[WITH GRANT OPTION]; -- 谨慎使用

• privilege_type: SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, ALTER, INDEX, EXECUTE, TRIGGER, CREATE VIEW, SHOW VIEW, ALL [PRIVILEGES] (不推荐) 等。
• column_list: 可选，指定列名 (用于 SELECT, INSERT, UPDATE)。
• object_type: 可选，TABLE (默认) 或 FUNCTION 或 PROCEDURE。
• privilege_level:*.* (全局权限)database_name.* (数据库权限)database_name.table_name (表权限)table_name (需在当前数据库下)
• user_or_role: 'username'@'host' 或 角色名 (MySQL 8.0+).

示例:

GRANT SELECT, INSERT ON app_db.* TO 'app_user'@'%'; -- 数据库级权限
GRANT UPDATE (email, phone) ON app_db.customers TO 'support_user'@'localhost'; -- 列级权限
GRANT EXECUTE ON PROCEDURE app_db.calculate_report TO 'report_user'@'10.0.0.%'; -- 存储过程权限
GRANT ALL ON *.* TO 'admin_user'@'localhost' WITH GRANT OPTION; -- 全局权限 (极度危险，仅用于示例)

-- 授予用户 'test_user' 在所有数据库的所有表上的 SELECT 权限
GRANT SELECT ON *.* TO 'test_user'@'localhost';
-- 授予用户 'test_user' 在 'test_db' 数据库的 'test_table' 表上的 INSERT 和 UPDATE 权限
GRANT INSERT, UPDATE ON test_db.test_table TO 'test_user'@'localhost';

查看权限：

SHOW GRANTS; -- 查看当前用户权限
SHOW GRANTS FOR 'username'@'host'; -- 查看指定用户权限
SELECT * FROM mysql.user WHERE User='username' AND Host='host'\G -- 查看 user 表信息 (谨慎)
SELECT * FROM mysql.db WHERE User='username' AND Host='host'\G -- 查看 db 表权限
-- 类似可以查看 tables_priv, columns_priv, procs_priv

撤销权限：

REVOKE [GRANT OPTION FOR] privilege_type [(column_list)] [, privilege_type [(column_list)] ...]
ON [object_type] privilege_level
FROM user_or_role [, user_or_role ...];

• 语法结构与 GRANT 高度相似，用于移除权限。
• REVOKE ALL PRIVILEGES, GRANT OPTION FROM user_or_role; 可以移除用户所有权限（包括 GRANT OPTION）。

示例:

-- 撤销用户 'test_user' 在所有数据库的所有表上的 SELECT 权限
REVOKE SELECT ON *.* FROM 'test_user'@'localhost';
-- 撤销用户 'test_user' 在 'test_db' 数据库的 'test_table' 表上的 INSERT 和 UPDATE 权限
REVOKE INSERT, UPDATE ON test_db.test_table FROM 'test_user'@'localhost';

删除用户：

-- 删除用户 'test_user'
DROP USER 'test_user'@'localhost';

刷新权限：

-- 刷新权限表，使权限更改立即生效
FLUSH PRIVILEGES;

注意事项：

• 权限继承：全局权限会影响所有数据库和表，数据库级权限会影响该数据库下的所有表。
• 权限范围：授予权限时要谨慎，避免给予用户过高的权限，遵循最小权限原则。
• 权限刷新：在修改权限后，需要执行 FLUSH PRIVILEGES 语句使权限更改立即生效。

五、最佳实践与注意事项

1. 最小权限原则： 永远只授予用户完成其任务所必需的最小权限。避免使用 GRANT ALL 或全局权限 (ON *.*)，尤其是在生产环境。
2. 使用角色 (MySQL 8.0+)： 将一组权限定义为一个角色 (CREATE ROLE)，然后将角色授予用户 (GRANT role TO user)。这大大简化了权限管理，尤其是对于具有相同权限需求的多个用户。
3. 主机限制： 尽可能使用严格的主机限制 ('localhost', '192.168.1.100', 'appserver.example.com')，避免使用通配符 '%'，尤其是在公网环境。
4. 强密码策略： 使用强密码并定期更换。考虑使用 caching_sha2_password (MySQL 8.0+ 默认)。
5. 定期审计： 使用 SHOW GRANTS 或直接查询权限表定期审查用户权限。
6. 谨慎使用 WITH GRANT OPTION： 仅授予绝对可信且需要委派权限的用户。失控的 GRANT OPTION 可能导致权限泛滥。
7. 避免直接修改权限表： 尽量使用标准的 GRANT 和 REVOKE 语句来管理权限。直接修改 mysql 数据库中的表容易出错且需要手动 FLUSH PRIVILEGES。
8. 理解通配符 % 和 _：在 GRANT ... ON db_name.* 中，db_name 可以包含 % 和 _ (需转义 \_) 进行模式匹配。在 Host 字段中，% 匹配任意主机，_ 匹配任意单个字符。'%' 非常开放。
9. 匿名用户 (''@'host')： 默认安装可能创建匿名账户。这些账户权限通常很低，但存在安全隐患。建议检查并删除不需要的匿名账户 (DROP USER ''@'localhost'; 等)。
10. FLUSH PRIVILEGES： 只有在直接修改权限表 (INSERT/UPDATE/DELETE on mysql.user, mysql.db etc.) 后才需要运行此命令。使用 GRANT/REVOKE/CREATE USER/DROP USER 语句时，权限更改通常是即时生效的，因为这些语句内部会处理权限表的更新和刷新。

总结：

MySQL 权限系统是一个强大但层级化的机制，通过 mysql.user, mysql.db, mysql.tables_priv, mysql.columns_priv, mysql.procs_priv 五个核心表实现从全局到列级的精细控制。理解权限的层级、生效顺序 (具体到宽泛) 以及权限叠加/拒绝的规则至关重要。始终遵循最小权限原则，优先使用 GRANT/REVOKE 语句进行管理，并在 MySQL 8.0+ 中积极使用角色来提升安全性和管理效率。