MySQL限制账号权限的实践指南_Mysql

前言

数据库安全最重要的一条准则就是最小权限原则：一个业务账号只分配它必须用到的权限，不要直接授予all privileges。很多人图方便新建用户直接给全部权限，一旦程序出现漏洞，攻击者拿到数据库账号后可以拖库、删库，风险极高。
本文将从创建账号、精细化授权、回收权限、账号锁定、权限查看、常见坑几个方面讲解mysql账号权限管控，覆盖mysql5.7与mysql8.0。

核心需求场景：

只允许账号访问指定数据库，不能看到其他库
只给查询/写入权限，禁止删除、修改表结构
保留账号可登录，但收回所有操作权限（仅保留登录能力）
限制账号只能从指定ip连接，杜绝任意地址访问

一、先搞懂mysql账号的组成

mysql账号格式为 '用户名'@'登录主机'，用户名+主机是一个整体。

user@localhost：仅本地本机登录
user@192.168.1.100：仅指定ip登录
user@'%'：允许任意ip远程登录（生产环境尽量避免）

重点：test@localhost 和 test@% 是两个完全独立账号，权限互不影响，操作权限时需要分别处理。

二、创建账号（不授权，仅能登录）

创建账号后默认只有usage权限，仅可以连接登录，没有任何数据库操作权限。

-- mysql8.0
create user 'appuser'@'192.168.1.20' identified by 'strongpass@123';
-- mysql5.7
create user 'appuser'@'192.168.1.20' identified by 'strongpass@123';

此时执行show grants for 'appuser'@'192.168.1.20';，结果只有：

grant usage on *.* to `appuser`@`192.168.1.20`

usage是mysql内置基础登录权限，无法被revoke回收。

三、精细化授权，按需分配权限

1. 常用权限清单

select：查询数据
insert：新增数据
update：修改数据
delete：删除数据
create / drop：创建、删除表
alter：修改表结构
all privileges：全部权限（生产谨慎使用）

2. 给账号指定库的查询+写入权限（推荐业务账号用法）

只允许操作business_db库下所有表，不能访问其他数据库：

grant select,insert,update on business_db.* to 'appuser'@'192.168.1.20';
flush privileges;

business_db.*代表该库全部表；如果只授权单张表可以写business_db.table1

3. 禁止授予管理员权限

不要执行下面这种危险语句：

-- ❌不推荐，拥有全部权限
grant all privileges on *.* to 'appuser'@'%';

*.*代表所有数据库所有表，等同于超级账号，一旦泄露后果严重。

四、回收账号权限：保留账号可登录，清空所有操作权限

这是很多运维人员需要的场景：账号保留、密码不变，可以正常登录，但是不能增删改查任何数据。

正确回收全部权限命令

revoke all privileges, grant option from 'appuser'@'192.168.1.20';
flush privileges;

⚠关键点：

该命令由root等管理员账号执行，不需要填写被操作账号的密码
只会收回所有数据库操作权限，账号本身不会删除，密码不变
回收完成后账号仅剩usage登录权限，登录后无法操作任何库表
不要遗漏grant option，否则账号还保留转授权限

和删除用户的区别：

revoke：账号存在，能登录，无操作权限
drop user：直接删除账号，无法登录

-- 彻底删除账号（慎用）
drop user 'appuser'@'192.168.1.20';

五、账号锁定：禁止登录，但保留账号（mysql8.0特有）

如果不想删除账号，又希望不让账号登录数据库，单纯revoke做不到，可以使用账号锁定：

--锁定账号，无法登录
alter user 'appuser'@'192.168.1.20' account lock;
--解锁恢复登录
alter user 'appuser'@'192.168.1.20' account unlock;

mysql5.7不支持account lock语法。

六、查看账号现有权限（验证权限是否生效）

show grants for 'appuser'@'192.168.1.20';

执行这条sql可以快速确认授权/回收是否成功，日常权限变更后建议校验。

七、生产环境最佳实践与避坑指南

禁止业务账号使用%任意ip访问
尽量固定应用服务器ip，缩小访问来源范围。
区分多个同名不同主机账号
test@localhost和test@%是两个账号，回收权限必须分别执行revoke。
不要混用revoke与锁定功能
- 需要账号保留登录能力，只是剥夺操作权限 → 使用revoke
- 需要账号完全不能登录，临时冻结 → 使用account lock
- 账号不再使用 → drop user删除
定期清理闲置账号
定期查询mysql.user表，找出长期不用的账号，及时回收或删除，减少安全隐患。
flush privileges什么时候加？
mysql5.7修改权限后建议加上flush privileges;刷新权限缓存；mysql8.0执行grant/revoke自动生效，加上兼容性更好。

八、完整操作演示流程

--1.创建账号，仅可登录
create user 'appuser'@'192.168.1.20' identified by 'pass@2026';
--2.分配业务所需权限
grant select,insert,update on business_db.* to 'appuser'@'192.168.1.20';
flush privileges;
--3.查看权限
show grants for 'appuser'@'192.168.1.20';
--4.回收全部权限，保留登录能力
revoke all privileges, grant option from 'appuser'@'192.168.1.20';
flush privileges;
--5.再次校验，仅剩登录权限
show grants for 'appuser'@'192.168.1.20';