MySQL权限管理：层级化作用域、权限分类、操作命令

1、权限作用域与类型

1.1、权限作用域

MySQL 的权限体系基于层级化作用域设计，权限的生效范围从「全局」到「单一对象」逐步细化。理解作用域是管理权限的基础，共分为以下 5 个层级：

• 全局级（Global）
  • 作用范围：整个 MySQL 服务器实例（所有数据库、表、用户）。
  • 典型场景：用于数据库管理员（DBA）的全局管理操作，如创建用户、重启服务等。
• 数据库级（Database）
  • 作用范围：单个数据库（如 mydb）内的所有表、视图、索引等对象。
  • 典型场景：授予某用户对特定数据库的读写权限（如允许操作 orders_db 库下的所有表）。
• 表级（Table）
  • 作用范围：单个表（如 users 表）。
  • 典型场景：限制用户仅能操作某张表（如只允许读取 products 表，禁止访问同库下的其他表）。
• 列级（Column）
  • 作用范围：表中的特定列（如 users 表的 email 列）。
  • 典型场景：敏感数据控制（如允许用户查询 employees 表的 name 列，但禁止访问 salary 列）。
• 例程级（Routine）
  • 作用范围：存储过程、函数等数据库例程。
  • 典型场景：控制用户对存储过程的执行权限（如允许调用 calculate_tax() 函数）。

1.2、核心权限分类

1.2.1、管理权限（服务器级控制）

管理权限用于控制 MySQL 服务器的底层功能或用户体系，与具体数据库中的数据操作无关，分为用户管理和服务器管理两类。

1.2.1.1、用户管理权限

权限名称	作用域	说明
CREATE USER	全局级	创建、修改或删除用户账号（如 CREATE USER 'new_user'@'localhost'）。
GRANT OPTION	所有层级	允许用户将自己拥有的权限转授给其他用户（需显式授权，不包含在 ALL PRIVILEGES 中）。

1.2.1.2、服务器管理权限

权限名称	作用域	说明
PROCESS	全局级	查看服务器所有运行线程（如 SHOW PROCESSLIST），用于排查性能问题。
RELOAD	全局级	执行刷新操作（如 FLUSH PRIVILEGES 刷新权限表、FLUSH TABLES 刷新表缓存）。
SHUTDOWN	全局级	关闭 MySQL 服务器（高危权限，仅限超级管理员）。
SUPER	全局级	高级管理权限，允许终止线程（KILL THREAD）、设置全局变量（SET GLOBAL var）等。
FILE	全局级	允许读取或写入服务器文件系统（如 LOAD DATA INFILE 导入数据，存在文件系统安全风险）。

1.2.2、数据库权限

数据库权限直接作用于数据或数据库对象，分为 数据操作权限 和 对象定义权限 ，支持在全局、数据库、表、列、例程等多个作用域生效。

1.2.2.1、数据操作权限（CRUD 核心操作）

权限名称	作用域	说明
SELECT	全局/数据库/表/列	查询数据（列级权限可指定允许查询的列，如 SELECT(id, name) ON users）。
INSERT	全局/数据库/表	插入新数据行（表级权限仅作用于指定表）。
UPDATE	全局/数据库/表/列	更新数据（列级权限可指定允许更新的列，如 UPDATE(salary) ON employees）。
DELETE	全局/数据库/表	删除数据行（仅作用于表级及以上，无列级删除）。

1.2.2.2、对象定义权限（结构管理）

权限名称	作用域	说明
CREATE	全局/数据库/表	创建对象：全局级可创建数据库，数据库级可创建表/视图，表级无此权限（需数据库级权限）。
ALTER	数据库/表	修改表结构（如添加字段、重命名表，仅作用于指定数据库或表）。
DROP	全局/数据库/表	删除对象：全局级可删除数据库，数据库级可删除表，表级无此权限（需数据库级权限）。
INDEX	数据库/表	创建或删除表索引（需先拥有表的 ALTER 或 CREATE 权限）。
LOCK TABLES	数据库/表	手动锁定表（如 LOCK TABLES tbl READ，需配合 SELECT/INSERT 等权限使用）。

1.2.2.3、例程级权限（存储过程/函数）

权限名称	作用域	说明
EXECUTE	例程级	允许调用存储过程或函数（如 CALL db.pr_calculate()）。
ALTER ROUTINE	例程级	允许修改例程定义（如重命名存储过程、修改函数代码）。

---

1.2.3、特殊：ALL PRIVILEGES

ALL PRIVILEGES 是权限集合，非具体权限，授予 当前作用域下 的所有权限（管理权限 + 数据库级权限），但不包含 GRANT OPTION 和少数特殊权限（如 FILE 需单独授予）。

---

2、SHOW GRANTS查看权限

使用 SHOW GRANTS FOR 'user'@'host'; 列出该用户所有权限，格式为可重用的 GRANT 语句。

直接执行 SHOW GRANTS; 可查看当前用户的权限。

---

3、GRANT 分配权限

GRANT privilege_listON scopeTO 'user'@'host'[IDENTIFIED BY 'password'][WITH GRANT OPTION];

• privilege_list：指定授予的权限列表，多个权限用逗号分隔。
• scope：权限作用的区域，支持以下级别：
  • 全局级别：*.*（所有数据库的所有对象）
  • 数据库级别：db_name.*（指定数据库的所有对象）
  • 表级别：db_name.table_name（指定表）
  • 列级别：db_name.table_name(column1, column2)（指定表的特定列）
  • 存储过程 / 函数级别：PROCEDURE db_name.proc_name 或 FUNCTION db_name.func_name
• 'user'@'host'：
  • user是被授权的用户
  • host 支持 %（任意主机）、localhost（本地）、具体IP地址、域名。
• IDENTIFIED BY：可选参数，用于创建用户时设置密码。
• WITH GRANT OPTION：允许被授权用户继续授权。

示例：

-- 授予数据库级权限
GRANT SELECT, INSERT, UPDATE ON sales_db.* TO 'analyst'@'localhost';-- 授予表级权限
GRANT DELETE ON sales_db.orders TO 'manager'@'%';-- 授予列级权限
GRANT SELECT(customer_name, customer_email) ON sales_db.customers TO 'support'@'localhost';

---

4、REVOKE 撤销权限

REVOKE privilege_listON scopeFROM 'user'@'host';

示例：

REVOKE UPDATE, DELETE ON sales_db.* FROM 'analyst'@'localhost';
REVOKE 'read_only' FROM 'jane'@'localhost';

5、最佳实践与安全指南

• 最小权限原则 ：仅授予完成任务的最低权限和最小作用域（如仅 SELECT 单表，而非全局权限），对敏感数据列级控制，且仅授权给特定角色。
• 权限分层管理：分为 管理员账号 和 普通用户，仅管理员可拥有 GRANT OPTION、FILE 等权限。
• 限制访问来源：通过 'user'@'host' 限定登录IP（如 'app'@'192.168.1.%' 仅限指定网段）。
• 强密码策略：使用复杂密码（含大小写、数字、符号），定期更换。
• 加密连接（SSL/TLS）：GRANT ... REQUIRE SSL，防止中间人攻击。
• 定期审计清理：执行 SHOW GRANTS 检查权限，删除无效账号（DROP USER）。
• 监控高危操作：开启审计日志，追踪 GRANT、DROP、SHUTDOWN 等操作。