MySQL 权限管理完整详解

MySQL 权限体系基于用户账号 + 权限粒度 + 授权语句 + 权限生效机制设计，核心作用：控制谁能连接数据库、能做哪些操作、能访问哪些库 / 表 / 字段。

一、核心基础：MySQL 用户账号格式

MySQL 用户不是单纯用户名，格式：'用户名'@'访问主机'

1. 主机匹配规则（核心）

主机写法	含义
`localhost`	仅本地客户端（socket 连接，127.0.0.1 不算）
`127.0.0.1`	IPv4 本地回环
`::1`	IPv6 本地回环
`192.168.1.%`	通配符 %，192.168.1 网段所有机器
`%`	任意 IP、任意主机（生产慎用，风险极高）

2. 用户存储位置

所有用户、权限信息存在系统库 mysql 下三张核心表：

mysql.user：全局权限（所有库通用权限，如 CREATE USER、RELOAD）
mysql.db：库级别权限（指定数据库的操作权限）
mysql.tables_priv / columns_priv：表级、字段级精细化权限

修改这三张表不会立即生效，必须 FLUSH PRIVILEGES; 重载权限。

二、MySQL 权限分级（从粗到细）

权限分为 4 个粒度，粒度越细管控越精准：

1. 全局权限（.）

作用于所有数据库所有表，存储在 mysql.user

常见全局权限：

ALL PRIVILEGES：全部权限
CREATE USER：创建 / 删除 / 修改用户
RELOAD：刷新权限、重置日志
SHUTDOWN：关闭数据库服务
PROCESS：查看所有连接进程
FILE：读写服务器本地文件（高危）

授权示例：给用户全局查询权限

sql

GRANT SELECT ON *.* TO 'user'@'%';

2. 数据库级权限（db_name.*）

仅作用于指定数据库全部表，存储在 mysql.db

日常业务最常用，例如业务库 biz_db：

sql

GRANT SELECT,INSERT,UPDATE,DELETE ON biz_db.* TO 'biz_user'@'192.168.1.%';

3. 表级权限（db_name.table_name）

仅作用于单张表，适合多租户、分库分表精细化管控

sql

-- 仅允许查询 biz_db.order 表
GRANT SELECT ON biz_db.order TO 'order_user'@'localhost';

4. 列级权限（字段粒度）

只允许操作表中指定字段，敏感数据隔离（手机号、身份证）

sql

-- 仅允许读取 id、name，不能读取 phone
GRANT SELECT(id,name) ON biz_db.user TO 'read_user'@'%';

三、常用权限分类说明

1. DML 数据操作权限（业务用户必备）

SELECT 查询数据
INSERT 插入数据
UPDATE 修改数据
DELETE 删除数据

2. DDL 结构操作权限（开发 / 运维）

CREATE 创建库 / 表
DROP 删除库 / 表
ALTER 修改表结构
INDEX 创建 / 删除索引

3. 管理类高危权限（仅 DBA 使用，禁止业务账号分配）

CREATE USER 用户管理
GRANT OPTION 授权传递（拥有该权限的用户可以把自己权限分给别人，高危）
FILE 读写服务器文件，可拖库、读取系统文件
SUPER 超级权限，能终止任意连接、修改全局参数
REPLICATION SLAVE/MASTER 主从复制权限

四、核心操作语句：创建用户、授权、回收、删除

1. 创建用户 `CREATE USER`

sql

-- 创建仅本地登录用户，密码123456
CREATE USER 'local_user'@'localhost' IDENTIFIED BY '123456';

-- 创建网段访问用户，新版推荐caching_sha2_password加密
CREATE USER 'biz'@'192.168.1.%' IDENTIFIED WITH caching_sha2_password BY 'Biz@123456';

2. 授权 `GRANT`

语法：GRANT 权限列表 ON 范围 TO 用户@主机 [WITH GRANT OPTION];

示例 1：业务普通读写账号

sql

GRANT SELECT,INSERT,UPDATE,DELETE ON biz_db.* TO 'biz'@'192.168.1.%';

示例 2：开发账号，可建表改结构

sql

GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,ALTER,DROP ON dev_db.* TO 'dev'@'localhost';

示例 3：DBA 超级账号（生产尽量避免 *@%）

sql

GRANT ALL PRIVILEGES ON *.* TO 'dba'@'127.0.0.1' WITH GRANT OPTION;

WITH GRANT OPTION：该用户可以将自身拥有的权限授予其他用户，仅管理员分配。

3. 回收权限 `REVOKE`

回收已授予的权限，不会删除用户：

sql

-- 回收biz用户的删除权限
REVOKE DELETE ON biz_db.* FROM 'biz'@'192.168.1.%';

-- 回收授权传递权限
REVOKE GRANT OPTION ON biz_db.* FROM 'biz'@'192.168.1.%';

-- 回收全部权限
REVOKE ALL PRIVILEGES,GRANT OPTION FROM 'biz'@'192.168.1.%';

4. 删除用户 `DROP USER`

sql

DROP USER IF EXISTS 'local_user'@'localhost';

5. 刷新权限（关键）

两种场景需要刷新权限：

直接操作 mysql.user/db 系统表修改权限
低版本 MySQL 执行完 GRANT/REVOKE 后（8.0 自动刷新，但手动刷新无副作用）

sql

FLUSH PRIVILEGES;

五、查看权限相关命令

1. 查看当前登录用户权限

sql

SHOW GRANTS;

2. 查看指定用户权限

sql

SHOW GRANTS FOR 'biz'@'192.168.1.%';

3. 查看所有用户

sql

SELECT user,host FROM mysql.user;

4. 查看库级权限

sql

SELECT * FROM mysql.db WHERE user='biz';

六、MySQL 8.0 权限管理重要变化

取消隐式创建用户
5.7 执行 GRANT ... TO user 用户不存在会自动创建；8.0 必须先 CREATE USER 再授权。
默认密码插件变更
8.0 默认 caching_sha2_password，5.7 是 mysql_native_password；旧客户端连接报错时可修改加密方式：
sql
```
ALTER USER 'biz'@'%' IDENTIFIED WITH mysql_native_password BY '密码';
```
不支持 GRANT ALL 创建用户，严格区分创建和授权两步。

七、生产环境权限最佳实践（安全规范）

最小权限原则
业务账号只分配业务必需权限：仅 SELECT/INSERT/UPDATE/DELETE，禁止 DROP/ALTER/CREATE、禁止 ALL PRIVILEGES。
严格限制访问主机
杜绝 % 任意主机，精确限定应用服务器 IP 段 192.168.1.%；DBA 账号仅允许 127.0.0.1/localhost。
权限分层隔离
- 应用业务账号：仅 DML 读写
- 开发测试账号：DDL+DML，仅限测试库
- DBA 管理员账号：全局权限，仅本地登录
禁止业务账号分配高危权限
FILE、SUPER、RELOAD、CREATE USER、WITH GRANT OPTION 一律不分配给应用。
定期清理闲置账号
定期查询 mysql.user，删除离职人员、废弃应用的数据库账号。
密码复杂度规范
密码包含大小写、数字、特殊符号，禁止弱密码，定期轮换密码。

八、常见问题

授权后权限不生效
原因：直接修改系统表未执行 FLUSH PRIVILEGES;；或者登录的账号主机匹配错误（localhost 和 127.0.0.1 是两个独立账号）。
远程连接报错 Access denied
检查三点：用户名密码、访问主机 host 是否匹配、是否授予对应库的访问权限。
WITH GRANT OPTION 风险
拥有该权限的账号可把读写权限泄露给任意用户，一旦应用被入侵，攻击者可新建账号长期拖库，生产业务账号严禁添加。

分享