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'——就差一个点，权限就完全不生效。