MySQL权限

MySQL 权限管理详解：从系统表到用户授权

MySQL 的权限管理是保障数据库安全的核心机制，通过精细化控制用户对数据库的操作权限，可防止未授权访问和误操作。MySQL 的权限信息存储在 mysql 系统库的 5 个专用表中，权限控制粒度从全局到列级不等。本文详细解析权限存储机制、授权方法及生效规则。

权限存储的系统表

MySQL 的权限信息按粒度分层存储在以下 5 个系统表中，权限范围从大到小依次为：全局 → 数据库 → 表 → 列 → 存储过程 / 函数。

1. `user` 表：全局级别权限

作用：存储用户账户信息及全局权限（对所有数据库生效）。
核心字段：
- Host：允许访问的主机（支持 % 通配符，如 192.168.1.% 表示 192.168.1 网段）。
- User：用户名（与 Host 共同构成唯一用户标识 User@Host）。
- 权限字段：如 Select_priv（查询权限）、Insert_priv（插入权限）等，值为 Y 表示拥有该权限。
特点：若用户在 user 表中有某权限，则对所有数据库默认拥有该权限（除非被更细粒度的权限限制）。

2. `db` 表：数据库级别权限

作用：控制用户对特定数据库的操作权限（仅对指定数据库生效）。
核心字段：
- Db：数据库名（指定权限生效的数据库）。
- Host、User：与 user 表一致，标识用户及来源主机。
- 权限字段：与 user 表类似（如 Select_priv、Create_priv 等），但仅作用于 Db 字段指定的数据库。
特点：数据库级权限优先级高于全局权限（若用户对某数据库有更严格的权限限制，以 db 表为准）。

3. `tables_priv` 表：表级别权限

作用：控制用户对特定表的操作权限（仅对指定表生效）。
核心字段：
- Table_name：表名（指定权限生效的表）。
- Table_priv：表级权限集合（如 Select、Insert、Update、Delete、Create 等）。
- Column_priv：预留字段，实际列级权限由 columns_priv 表管理。
特点：表级权限优先级高于数据库级和全局权限，适合限制用户仅能操作某张表。

4. `columns_priv` 表：列级别权限

作用：控制用户对特定表中某列的操作权限（粒度最细）。
核心字段：
- Column_name：列名（指定权限生效的列）。
- Column_priv：列级权限（仅支持 Select、Insert、Update、References）。
特点：列级权限优先级最高，例如可允许用户仅查询表中 name 列，而无权查看 password 列。

5. `procs_priv` 表：存储过程 / 函数级别权限

作用：控制用户对存储过程和函数的操作权限。
核心字段：
- Routine_name：存储过程 / 函数名。
- Routine_type：类型（FUNCTION 或 PROCEDURE）。
- Proc_priv：权限集合（Execute 执行、Alter Routine 修改、Grant 授权）。
特点：专门针对存储过程 / 函数的权限控制，与表级权限独立。

用户授权的核心概念

1. 用户标识：`User@Host`

MySQL 中用户的唯一标识是 用户名@主机（User@Host），其中：

User：登录用户名（如 'app_user'）。
Host：允许登录的主机（支持通配符）：
- %：匹配所有主机（如 'app_user'@'%' 允许从任何主机登录）。
- 192.168.1.%：匹配 192.168.1 网段的所有主机。
- localhost：仅允许本地登录（通过 127.0.0.1 或 socket 连接）。

2. 权限级别与优先级

权限按 “粒度从细到粗” 的优先级生效：
列级权限 → 表级权限 → 数据库级权限 → 全局权限

例如：若用户在 user 表中有全局 SELECT 权限，但在 db 表中对 test 库无 SELECT 权限，则该用户无法查询 test 库的任何表。

用户授权与管理操作

1. 创建用户

-- 语法：创建用户并指定密码（MySQL 5.7+ 推荐使用）
CREATE USER '用户名'@'主机' IDENTIFIED BY '密码';

-- 示例 1：允许本地登录的用户（仅 localhost 可访问）
CREATE USER 'local_user'@'localhost' IDENTIFIED BY 'Local@123';

-- 示例 2：允许任意主机登录的用户（生产环境谨慎使用）
CREATE USER 'app_user'@'%' IDENTIFIED BY 'App@123';

2. 授权操作（`GRANT`）

GRANT 命令用于为用户分配权限，语法如下：

1	GRANT 权限列表 ON 权限范围 TO '用户名'@'主机' [WITH GRANT OPTION];

（1）权限列表

单权限：如 SELECT、INSERT、UPDATE、DELETE 等。
多权限：用逗号分隔，如 SELECT, INSERT, UPDATE。
所有权限：ALL PRIVILEGES（不包含 GRANT OPTION，需单独指定）。

（2）权限范围

全局：*.*（对所有数据库的所有表生效）。
数据库级：数据库名.*（如 test.* 对 test 库的所有表生效）。
表级：数据库名.表名（如 test.users 仅对 test 库的 users 表生效）。
列级：数据库名.表名(列1, 列2)（如 test.users(name, age) 仅对 name 和 age 列生效）。
存储过程 / 函数级：数据库名.存储过程名（如 test.my_proc）。

（3）示例

-- 示例 1：授予全局所有权限（包含授权给其他用户的权限）
GRANT ALL PRIVILEGES ON *.* TO 'admin'@'localhost' WITH GRANT OPTION;

-- 示例 2：授予对 test 库所有表的查询和插入权限
GRANT SELECT, INSERT ON test.* TO 'app_user'@'%';

-- 示例 3：授予对 test.users 表的 name 和 age 列的查询权限
GRANT SELECT (name, age) ON test.users TO 'read_user'@'192.168.1.%';

-- 示例 4：授予执行 test 库中存储过程的权限
GRANT EXECUTE ON test.my_proc TO 'proc_user'@'localhost';

3. 撤销权限（`REVOKE`）

-- 语法：撤销指定权限
REVOKE 权限列表 ON 权限范围 FROM '用户名'@'主机';

-- 示例：撤销 app_user 对 test 库的插入权限
REVOKE INSERT ON test.* FROM 'app_user'@'%';

4. 修改用户密码

-- 方法 1：MySQL 5.7+ 推荐（ALTER USER）
ALTER USER '用户名'@'主机' IDENTIFIED BY '新密码';

-- 方法 2：SET PASSWORD（兼容旧版本）
SET PASSWORD FOR '用户名'@'主机' = PASSWORD('新密码');  -- MySQL 5.7 及之前
SET PASSWORD FOR '用户名'@'主机' = '新密码';  -- MySQL 8.0+（默认 caching_sha2_password 加密）

-- 示例：修改 app_user 的密码
ALTER USER 'app_user'@'%' IDENTIFIED BY 'NewApp@456';

5. 删除用户

DROP USER '用户名'@'主机';

-- 示例：删除本地用户 local_user
DROP USER 'local_user'@'localhost';

权限生效规则

权限修改后，生效时机因操作方式和权限级别而异：

自动生效的情况：
使用 GRANT、REVOKE、ALTER USER、DROP USER 等命令修改权限时，MySQL 会自动将权限同步到内存，无需额外操作。
需手动刷新的情况：
若直接通过 INSERT/UPDATE/DELETE 修改权限系统表（如 mysql.user），需执行以下命令刷新权限：
1
FLUSH PRIVILEGES; -- 重新加载权限表到内存
不同级别权限的生效条件：
- 全局权限：修改后需重新连接才会生效。
- 数据库级权限：执行 USE 数据库名 后生效。
- 表级 / 列级 / 存储过程权限：下次操作时立即生效。

权限管理最佳实践

最小权限原则：仅授予用户完成工作必需的权限（如查询用户仅给 SELECT，避免 ALL PRIVILEGES）。
限制主机范围：尽量指定具体的主机（如 192.168.1.100），避免使用 % 允许任意主机登录。
定期审计权限：通过查询权限表（如 SELECT User, Host FROM mysql.user;）清理冗余用户和权限。
使用强密码：结合 validate_password 插件强制密码复杂度（如长度 ≥8，包含大小写、数字和特殊字符）。
避免直接修改系统表：优先使用 GRANT、REVOKE 等命令管理权限，减少手动操作风险。