MySQL中限制用户对特定表修改权限的实现方法

在实际工作中踩过足够多的坑之后，你会发现，MySQL 中限制用户对某张表执行写操作，其实并没有什么复杂技巧——最直接、最可靠的方式，就是仅授予 SELECT 权限，不给 UPDATE 和 DELETE。

MySQL 的表级权限默认处于“全无”状态，如果你没有显式授予某个权限，用户就完全没有该权限。要想让某个用户只能查询而不能修改，核心指令就是：GRANT SELECT ON db_name.table_name TO 'user'@'host'，其他所有写权限一个都不给。

最容易翻车的操作是习惯性地写下 GRANT ALL PRIVILEGES，或者忘了检查 UPDATE、DELETE 是否被顺手带了出来。MySQL 没有“拒绝”某个权限的语法，只有“授予”和“未授予”两种状态——只要没授予，就是天然禁止。

这里有三个实践要点，建议牢记：

• 创建新用户后，立刻执行 SHOW GRANTS FOR 'user'@'host' 查看输出，确认里面不包含 UPDATE、DELETE 甚至 ALL PRIVILEGES 等字样。
• 如果旧用户曾经被误授予过权限，必须用 REVOKE UPDATE, DELETE ON db_name.table_name FROM 'user'@'host' 精确回收，不能指望“之后不再授予新权限”就万事大吉。
• 特别注意：INSERT 是一个独立权限，不授予就不会有插入操作。但很多人容易把 SELECT + INSERT 的组合当作“只读”，实际上这等于给了用户新增数据的权限——这个坑，踩的人真不少。

REVOKE 失效？检查作用域是否完全匹配

有时候你明明执行了 REVOKE UPDATE ON sales_db.orders FROM 'reporter'@'10.20.%'，但对方居然还能 UPDATE，大概率是四元组（库名、表名、用户名、host）没有对齐。

MySQL 的 REVOKE 采用精确匹配机制，它的逻辑很简单：但凡有一点差异，比如大小写不一致、少了一个反引号、host 写成 '%' 而实际是 '10.20.15.8'、库名多了一个空格或少了一个下划线——这些情况系统全都不报错，但权限纹丝不动。这种“静默失败”最磨人。

我的建议是：先用 SHOW GRANTS FOR 'user'@'host' 把原始授权语句拷贝出来，然后直接复制粘贴，把 GRANT 改成 REVOKE，这是最保险的做法。而且别信“我刚才 revoke 了”这个感觉，一定要再跑一次 SHOW GRANTS，亲眼确认那条权限已经消失。

还有一个细节：旧连接不会自动更新权限。你得 KILL 掉对应的线程，或者让应用重连，新权限才会生效。

字段级禁止修改？原生不支持，得绕道

MySQL 没有 UPDATE (col_a) 这种列级写权限。如果你想让 amount 字段不可改，但允许改 status，靠 GRANT 是搞不定的。

目前只有两个可行方案，而且各有明显缺陷：

• 建视图：CREATE VIEW orders_ro AS SELECT id, status, created_at FROM orders，然后把 UPDATE 权限授给这个视图，并加上 WITH CHECK OPTION。但代价是，所有写操作必须走视图，直接连基表就会绕开这个限制。
• 用触发器拦截：在 BEFORE UPDATE ON orders 里写一段判断逻辑，比如 IF OLD.amount != NEW.amount THEN SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'amount is read-only'; END IF;。但触发器没法阻止 UPDATE ... SET @var := amount 这类间接读取，而且批量更新时性能会明显下降。

权限生效后仍报错？先看连接上下文和缓存

权限已经改好了，用户连上来还是报 ERROR 1142 (42000): UPDATE command denied，或者反过来——明明不该操作的，居然能修改。问题往往不在语句本身，而在连接上下文。

第一步，确认客户端连接上的是目标账号：执行 SELECT USER(), CURRENT_USER();。前者是登录时的身份，后者才是权限匹配的依据，两者可能完全不一样。

第二步，检查 host 是否精确匹配：'appuser'@'localhost' 和 'appuser'@'127.0.0.1' 是两个不同的用户。如果开启了 DNS 反查，还可能因为域名解析延迟造成错配。

第三步，MySQL 8.0+ 在使用 mysql_native_password 插件时，某些连接池会缓存认证状态，重连也不一定刷新权限。这时候需要重启应用或者清空连接池。

一句话总结：真正卡住人的从来不是语法对不对，而是你改的是 'user'@'10.20.%'，而应用连的是 'user'@'10.20.30.5'——就差一个点，权限就完全不生效。