MySQL创建只读账号的权限设置方法

MySQL权限体系基于白名单机制，创建只读账号的核心在于明确允许用户读取哪些内容，而非限制其写入操作。掌握这一原则，众多权限配置难题将迎刃而解。

值得注意的是，GRANT SELECT ON *.*看似授予全局读权限，实则暗藏风险——它将暴露mysql、performance_schema等系统库的表结构。真正的只读账号应精确限定库表范围，例如使用GRANT SELECT ON mydb.*。这个细节不容忽视。

GRANT SELECT必须明确指定库表范围，不能仅依赖未授予写权限来确保安全

几个看似简单、却经常翻车的坑值得警惕：

• 当库名包含特殊字符（如my-db）时，使用单引号包裹的'my-db'.*会导致语法错误。正确的做法是采用反引号，即`my-db`.*，或直接避免使用带特殊字符的库名。
• 跨库JOIN查询是常见的隐藏陷阱。例如，执行SELECT * FROM db1.t1 JOIN db2.t2时，若仅对db1授予SELECT权限而忽略db2，查询将报错。必须对两个库均显式执行GRANT SELECT。
• 部分管理员误以为通过REVOKE INSERT可以强化只读安全，实则多余——未授予的权限原本就不存在，且回收操作容易遗漏TRUNCATE、LOCK TABLES等隐式写操作。权限管理中，采用加法原则（只授予必要权限）比减法更可靠。

USAGE权限必须单独授予，否则用户无法建立连接

GRANT SELECT仅控制可执行的操作，并不决定用户能否登录。这好比给了你钥匙，却未告知门的位置。

若缺少USAGE权限，即使密码验证通过，连接也会立即断开，并报错ERROR 1045 (28000): Access denied。在MySQL 8.0及以上版本中，USAGE不会自动随GRANT SELECT授予，因此必须显式添加。

• 补充授权语句：GRANT USAGE ON *.* TO 'ro_user'@'192.168.10.%'
• USAGE是纯连接权限，不赋予任何实际操作能力，需与SELECT分开授予
• 主机名请勿使用'%'开放所有IP，生产环境应限定内网网段，例如'192.168.10.%'

SHOW CREATE TABLE失败？需显式授予INFORMATION_SCHEMA权限

许多ORM框架、监控工具或DBA在查询表结构时会调用SHOW CREATE TABLE t1。该语句底层依赖于information_schema.TABLES和information_schema.COLUMNS的SELECT权限。MySQL 5.7默认允许此操作，但8.0及以上版本已严格收紧。

若仅授予业务库权限，执行SHOW CREATE TABLE将返回错误：ERROR 1142 (42000): SELECT command denied。

• 解决方案：GRANT SELECT ON INFORMATION_SCHEMA.* TO 'ro_user'@'192.168.10.%'
• 无需开放performance_schema或sys的全部权限，除非确实需要访问其视图。

CREATE USER与GRANT需分两步执行，不可合并为一条语句

从MySQL 5.7开始，CREATE USER语句不再支持同时携带GRANT子句。合并书写会直接触发语法错误：ERROR 1064 (42000): You have an error in your SQL syntax。

• 正确操作分为两步：首先执行CREATE USER 'ro_user'@'192.168.10.%' IDENTIFIED BY 'strong-pass-2024';，随后执行GRANT SELECT ON myapp_db.* TO 'ro_user'@'192.168.10.%';
• 密码需用单引号包裹，且不能为纯数字或过于简单的字符串。MySQL 8.0+默认启用密码强度校验，弱密码会被拒绝创建。
• 执行完GRANT后无需手动运行FLUSH PRIVILEGES，因为GRANT语句会自动刷新权限缓存，除非你直接修改了mysql.user表。

最后，还有一个易被忽略的要点：只读账号能否执行SELECT ... FOR UPDATE？答案是不能——该语句会触发锁机制，实际需要SELECT与LOCK TABLES双重权限，而只读账号缺少后者，执行将直接报错ERROR 1142。但需要警惕的是，如果用户持有EXECUTE权限，或能够访问SQL SECURITY DEFINER视图，仍可能间接触发写操作。权限的边界，远比表面上复杂。