MySQL 权限管理

权限管理是 MySQL 数据库安全的核心组成部分，它决定了哪些用户可以访问数据库，以及可以执行哪些操作。有效的权限管理可以防止未授权访问，保护敏感数据，并确保数据库的安全性和完整性。在生产环境中，合理的权限设计和严格的权限管控是保障数据库安全的重要手段。

权限管理基础

权限体系

MySQL 的权限体系分为以下几个层级，每个层级对应不同的权限范围和存储表：

全局级别：适用于整个 MySQL 实例，控制所有数据库和表的访问，权限存储在 mysql.user 表中
数据库级别：适用于指定数据库，控制特定数据库内所有对象的访问，权限存储在 mysql.db 和 mysql.schema_priv 表中
表级别：适用于指定表，控制特定表的访问，权限存储在 mysql.tables_priv 表中
列级别：适用于指定表的特定列，实现更细粒度的访问控制，权限存储在 mysql.columns_priv 表中
子程序级别：适用于存储过程和函数，控制存储程序的执行和创建，权限存储在 mysql.procs_priv 表中
代理级别：适用于用户代理，允许一个用户代理另一个用户的权限，权限存储在 mysql.proxies_priv 表中

权限检查顺序

当用户执行操作时，MySQL 按照以下顺序检查权限，只要在某个级别找到匹配的权限就会允许操作，否则继续检查下一级别，最终未找到匹配权限则拒绝操作：

检查全局权限
检查数据库权限
检查表权限
检查列权限
检查子程序权限

生产环境权限设计原则

在生产环境中，权限设计应遵循以下原则：

最小权限原则：只授予用户完成工作所需的最小权限
职责分离原则：不同角色的用户应具有不同的权限
权限过期原则：临时权限应设置过期时间
审计原则：所有权限变更应记录审计日志
定期 review 原则：定期检查和清理不必要的权限

权限管理命令

用户创建与授权

生产环境常用创建用户示例

sql

-- MySQL 5.6/5.7 创建用户
CREATE USER 'app_user'@'192.168.1.%' IDENTIFIED BY 'StrongPass@2023';
CREATE USER 'readonly_user'@'%' IDENTIFIED BY 'ReadonlyPass@2023';

-- MySQL 8.0 创建用户（支持角色管理）
CREATE USER 'app_user'@'192.168.1.%' IDENTIFIED BY 'StrongPass@2023';
CREATE USER 'readonly_user'@'%' IDENTIFIED BY 'ReadonlyPass@2023';
CREATE ROLE 'app_role', 'readonly_role';

生产环境常用授权示例

sql

-- 应用程序用户授权（最小权限原则）
GRANT SELECT, INSERT, UPDATE, DELETE ON app_db.* TO 'app_user'@'192.168.1.%';

-- 只读用户授权
GRANT SELECT ON app_db.* TO 'readonly_user'@'%';

-- 管理员用户授权（注意：生产环境应避免直接使用root）
GRANT ALL PRIVILEGES ON *.* TO 'admin_user'@'127.0.0.1' WITH GRANT OPTION;

-- MySQL 8.0 角色授权示例
GRANT SELECT, INSERT, UPDATE, DELETE ON app_db.* TO 'app_role';
GRANT SELECT ON app_db.* TO 'readonly_role';
GRANT 'app_role' TO 'app_user'@'192.168.1.%';
GRANT 'readonly_role' TO 'readonly_user'@'%';
SET DEFAULT ROLE ALL TO 'app_user'@'192.168.1.%', 'readonly_user'@'%';

-- 刷新权限（仅在直接修改权限表时需要，使用GRANT/REVOKE命令时不需要）
FLUSH PRIVILEGES;

权限查看

sql

-- 查看当前用户权限
SHOW GRANTS;

-- 查看指定用户权限
SHOW GRANTS FOR 'app_user'@'192.168.1.%';

-- 查看所有用户及主机
SELECT user, host FROM mysql.user;

-- 查看用户的具体权限（生产环境常用）
SELECT * FROM mysql.db WHERE user = 'app_user' AND host = '192.168.1.%';
SELECT * FROM mysql.tables_priv WHERE user = 'app_user' AND host = '192.168.1.%';

-- MySQL 8.0 查看角色权限
SHOW GRANTS FOR 'app_role';
SELECT * FROM mysql.role_edges;

权限撤销

sql

-- 撤销数据库级别权限
REVOKE DELETE ON app_db.* FROM 'app_user'@'192.168.1.%';

-- 撤销表级别权限
REVOKE SELECT ON app_db.sensitive_table FROM 'readonly_user'@'%';

-- 撤销所有权限
REVOKE ALL PRIVILEGES ON *.* FROM 'old_user'@'%';

-- MySQL 8.0 撤销角色
REVOKE 'app_role' FROM 'app_user'@'192.168.1.%';
DROP ROLE 'old_role';

用户删除

sql

-- 删除单个用户
DROP USER 'old_user'@'%';

-- 批量删除用户
DROP USER 'user1'@'%', 'user2'@'localhost', 'user3'@'192.168.1.100';

-- 注意：DROP USER命令不需要FLUSH PRIVILEGES，MySQL会自动刷新

权限管理最佳实践

最小权限原则

生产环境中，严格遵循最小权限原则是保障数据库安全的基石：

应用程序用户：只授予特定数据库的必要权限（如 SELECT, INSERT, UPDATE, DELETE），避免授予 DROP, ALTER 等危险权限
只读用户：只授予 SELECT 权限，禁止授予任何写权限
管理员用户：避免直接使用 root 用户，创建专门的管理员用户并限制其只能从本地连接
开发测试用户：限制其只能访问测试数据库，禁止访问生产数据库
避免使用 WITH GRANT OPTION：除非绝对必要，否则不要授予用户授予权限的权限

密码与认证策略

MySQL 5.6/5.7

sql

-- 设置密码过期策略
SET GLOBAL default_password_lifetime = 90;
-- 强制用户下次登录修改密码
ALTER USER 'user'@'host' PASSWORD EXPIRE;

MySQL 8.0

sql

-- 设置密码过期策略
SET GLOBAL default_password_lifetime = 90;
-- 设置密码复杂度要求
INSTALL COMPONENT 'file://component_validate_password';
SET GLOBAL validate_password.policy = 'STRONG';
SET GLOBAL validate_password.length = 12;
-- 强制用户下次登录修改密码
ALTER USER 'user'@'host' PASSWORD EXPIRE;

主机限制与连接控制

严格限制主机：避免使用通配符 %，尽量使用具体的 IP 地址或 IP 段（如 192.168.1.%）
本地管理：管理员用户应限制为只能从本地连接（localhost 或 127.0.0.1）
远程连接加密：对于必须远程连接的用户，强制使用 SSL 加密
连接控制：MySQL 5.7+ 支持连接控制插件，可限制失败登录尝试次数

权限审计与监控

定期权限审计

sql

-- 检查具有全局权限的用户
SELECT user, host FROM mysql.user WHERE Super_priv = 'Y' OR Process_priv = 'Y';

-- 检查具有 WITH GRANT OPTION 权限的用户
SELECT user, host FROM mysql.user WHERE Grant_priv = 'Y';

-- 检查长期未使用的用户（MySQL 5.7+）
SELECT user, host, password_last_changed FROM mysql.user WHERE password_last_changed < NOW() - INTERVAL 180 DAY;

权限变更监控

MySQL 5.6/5.7：开启 general_log 或 audit_log 插件监控权限变更
MySQL 8.0：使用审计日志插件或 Performance Schema 监控权限变更

角色管理（MySQL 8.0+）

角色管理是 MySQL 8.0 的重要新特性，可大幅简化权限管理：

sql

-- 创建应用角色
CREATE ROLE 'app_read', 'app_write', 'app_admin';

-- 为角色授予权限
GRANT SELECT ON app_db.* TO 'app_read';
GRANT INSERT, UPDATE, DELETE ON app_db.* TO 'app_write';
GRANT ALTER, CREATE, DROP ON app_db.* TO 'app_admin';

-- 组合角色
GRANT 'app_read', 'app_write' TO 'app_full_access';

-- 为用户分配角色
CREATE USER 'app_user'@'192.168.1.%' IDENTIFIED BY 'StrongPass@2023';
GRANT 'app_full_access' TO 'app_user'@'192.168.1.%';

-- 设置默认角色
SET DEFAULT ROLE ALL TO 'app_user'@'192.168.1.%';

-- 激活角色（会话级别）
SET ROLE 'app_read';

动态权限管理（MySQL 8.0+）

MySQL 8.0 引入了动态权限，允许更细粒度的权限控制：

sql

-- 授予监控权限（无需全局 SELECT 权限）
GRANT PERFORMANCE_SCHEMA ON *.* TO 'monitor_user'@'localhost';

-- 授予备份权限
GRANT BACKUP_ADMIN ON *.* TO 'backup_user'@'localhost';

-- 授予复制管理权限
GRANT REPLICATION_ADMIN ON *.* TO 'repl_admin'@'localhost';

权限回收与清理

定期清理过期权限：对于临时授予的权限，设置过期时间并定期清理
移除不再使用的用户：定期检查并删除长期未使用的用户
撤销不必要的权限：定期审查用户权限，撤销不再需要的权限
禁用默认用户：删除或重命名默认的测试用户和数据库

不同版本的权限差异

MySQL 5.6 权限特性

MySQL 5.6 是一个稳定的版本，但权限管理功能相对基础：

权限表结构：相对简单，主要包括 user、db、tables_priv、columns_priv、procs_priv 等表
密码管理：仅支持 mysql_native_password 哈希算法，不支持密码过期策略
权限检查：相对宽松，存在一些权限绕过漏洞（已在后续版本修复）
监控能力：缺乏有效的权限监控机制
角色管理：不支持角色管理，所有权限需直接授予用户
动态权限：不支持动态权限

MySQL 5.7 权限特性

MySQL 5.7 对权限管理进行了显著增强：

密码管理增强：
- 引入 password_expired 列，支持密码过期
- 引入 password_last_changed 和 password_lifetime 列
- 支持密码强度验证插件
权限检查增强：
- 增强了权限检查的严格性，修复了多个权限绕过漏洞
- 引入了 connection_control 插件，可限制失败登录尝试次数
监控能力增强：
- performance_schema 中新增了权限相关的监控表
- 支持审计日志插件
角色管理：仍不支持角色管理
权限回收：支持基本的权限回收功能

MySQL 8.0 权限特性

MySQL 8.0 对权限管理进行了全面重构和增强：

角色管理：
- 原生支持角色管理，可创建、授权、分配和撤销角色
- 支持默认角色和角色激活
- 支持角色继承
密码管理增强：
- 默认密码哈希算法改为 caching_sha2_password
- 引入密码验证组件，可配置密码复杂度要求
- 支持密码过期和密码历史记录
动态权限：
- 引入动态权限，允许更细粒度的权限控制
- 支持 PERFORMANCE_SCHEMA、BACKUP_ADMIN、REPLICATION_ADMIN 等动态权限
权限检查优化：
- 改进了权限检查性能
- 增强了权限检查的严格性
审计能力增强：
- 增强了审计日志插件
- 支持更详细的权限变更审计
权限回收：支持更灵活的权限回收功能

常见权限问题排查

权限不足

错误信息：

ERROR 1044 (42000): Access denied for user 'username'@'host' to database 'database_name'

排查步骤：

检查用户是否存在：SELECT user, host FROM mysql.user WHERE user = 'username' AND host = 'host';
检查用户权限：SHOW GRANTS FOR 'username'@'host';
检查数据库是否存在：SHOW DATABASES LIKE 'database_name';
检查主机匹配规则：确保用户的主机匹配规则正确，避免 IP 地址不匹配
检查权限表是否需要刷新：如果直接修改了权限表，执行 FLUSH PRIVILEGES;

密码错误

错误信息：

ERROR 1045 (28000): Access denied for user 'username'@'host' (using password: YES)

排查步骤：

检查密码是否正确：使用正确的密码尝试连接
检查用户是否存在：SELECT user, host FROM mysql.user WHERE user = 'username' AND host = 'host';
检查密码是否过期：SELECT user, host, password_expired FROM mysql.user WHERE user = 'username' AND host = 'host';
检查密码哈希算法：不同版本的 MySQL 可能使用不同的密码哈希算法，确保客户端支持
检查主机限制：确保用户被允许从当前主机连接

权限未生效

问题现象：执行授权命令后，用户仍然无法访问

排查步骤：

检查授权命令是否正确：确保授权命令中的用户名、主机、数据库和权限都正确
检查权限是否需要刷新：如果直接修改了权限表，执行 FLUSH PRIVILEGES;
检查用户是否重新连接：权限变更需要用户重新连接才能生效
检查角色是否激活：MySQL 8.0 中，确保角色已被激活或设置为默认角色

访问被拒绝（无具体错误信息）

排查步骤：

检查 MySQL 错误日志：查看 MySQL 错误日志，可能包含更详细的错误信息
检查防火墙设置：确保防火墙允许 MySQL 端口的访问
检查 SELinux/AppArmor 设置：如果启用了 SELinux 或 AppArmor，确保其允许 MySQL 访问
检查 MySQL 绑定地址：确保 MySQL 绑定到了正确的 IP 地址

企业级权限管理方案

集中式权限管理

企业环境中，建议采用集中式权限管理方案：

LDAP/AD 集成：
- 将 MySQL 用户认证与 LDAP 或 Active Directory 集成
- 实现统一的身份认证和权限管理
- 支持单点登录
权限同步机制：
- 定期从 LDAP/AD 同步用户和权限到 MySQL
- 实现自动化的权限生命周期管理
- 支持权限的自动过期和回收

自动化权限管理

配置管理工具：
- 使用 Ansible、Puppet 等配置管理工具管理 MySQL 权限
- 实现权限配置的版本控制
- 支持一键部署和回滚
权限申请与审批流程：
- 建立规范化的权限申请和审批流程
- 实现权限变更的自动化执行和审计
- 支持临时权限的自动过期
自动化权限审计：
- 定期自动审计用户权限
- 识别和清理不必要的权限
- 生成权限审计报告

权限监控与告警

实时监控：
- 使用 Prometheus + Grafana 监控 MySQL 权限相关指标
- 监控异常权限访问和权限变更
- 设置权限相关的告警规则
审计日志：
- 启用 MySQL 审计日志插件
- 将审计日志发送到集中式日志管理系统（如 ELK Stack）
- 实现审计日志的实时分析和告警
异常检测：
- 使用机器学习算法检测异常权限访问模式
- 识别潜在的权限滥用和安全威胁

总结

MySQL 权限管理是数据库安全的核心组成部分，合理的权限设计和严格的权限管控是保障数据库安全的重要手段。在生产环境中，DBA 应遵循以下原则：

严格遵循最小权限原则：只授予用户完成工作所需的最小权限
根据 MySQL 版本选择合适的权限管理策略：
- MySQL 5.6：基础权限管理，重点关注密码安全和权限检查
- MySQL 5.7：利用增强的密码管理和权限检查功能，启用 connection_control 插件
- MySQL 8.0：充分利用角色管理、动态权限和增强的密码管理功能
建立完善的权限管理流程：
- 规范化的权限申请和审批流程
- 定期的权限审计和清理
- 实时的权限监控和告警
结合企业实际情况：
- 采用集中式权限管理方案
- 实现自动化的权限管理
- 建立完善的权限审计机制

通过合理的权限管理，可以有效地保护 MySQL 数据库的安全性和完整性，防止未授权访问和权限滥用，保障业务系统的稳定运行。

版本差异总结

特性	MySQL 5.6	MySQL 5.7	MySQL 8.0
角色管理	❌	❌	✅
密码过期	❌	✅	✅
密码哈希算法	mysql_native_password	mysql_native_password	caching_sha2_password (默认)
动态权限	❌	❌	✅
密码验证组件	❌	❌	✅
performance_schema 权限监控	❌	✅	✅
连接控制插件	❌	✅	✅
审计日志插件	❌	✅	✅
密码历史记录	❌	❌	✅
角色继承	❌	❌	✅
默认角色	❌	❌	✅
权限检查性能优化	❌	❌	✅

MySQL 权限管理 ​

权限管理基础 ​

权限体系 ​

权限检查顺序 ​

生产环境权限设计原则 ​

权限管理命令 ​

用户创建与授权 ​

生产环境常用创建用户示例 ​

生产环境常用授权示例 ​

权限查看 ​

权限撤销 ​

用户删除 ​

权限管理最佳实践 ​

最小权限原则 ​

密码与认证策略 ​

MySQL 5.6/5.7 ​

MySQL 8.0 ​

主机限制与连接控制 ​

权限审计与监控 ​

定期权限审计 ​

权限变更监控 ​

角色管理（MySQL 8.0+） ​

动态权限管理（MySQL 8.0+） ​

权限回收与清理 ​

不同版本的权限差异 ​

MySQL 5.6 权限特性 ​

MySQL 5.7 权限特性 ​

MySQL 8.0 权限特性 ​

常见权限问题排查 ​

权限不足 ​

密码错误 ​

权限未生效 ​

访问被拒绝（无具体错误信息） ​

企业级权限管理方案 ​

集中式权限管理 ​

自动化权限管理 ​

权限监控与告警 ​

总结 ​

版本差异总结 ​

参考资料 ​

MySQL 权限管理

权限管理基础

权限体系

权限检查顺序

生产环境权限设计原则

权限管理命令

用户创建与授权

生产环境常用创建用户示例

生产环境常用授权示例

权限查看

权限撤销

用户删除

权限管理最佳实践

最小权限原则

密码与认证策略

MySQL 5.6/5.7

MySQL 8.0

主机限制与连接控制

权限审计与监控

定期权限审计

权限变更监控

角色管理（MySQL 8.0+）

动态权限管理（MySQL 8.0+）

权限回收与清理

不同版本的权限差异

MySQL 5.6 权限特性

MySQL 5.7 权限特性

MySQL 8.0 权限特性

常见权限问题排查

权限不足

密码错误

权限未生效

访问被拒绝（无具体错误信息）

企业级权限管理方案

集中式权限管理

自动化权限管理

权限监控与告警

总结

版本差异总结

参考资料