MySQL如何创建只读权限账号_通过GRANT SELECT设置只读用户

MySQL只读账号创建指南：避开那些“想当然”的坑

在MySQL中创建只读账号，许多人的第一想法是“这还不简单？不给写权限就行了”。然而实际操作时，从连接失败到权限绕过，处处是细节。下图清晰展示了通过GRANT SELECT设置只读用户的核心流程，但流程之外，仍有不少关键点需要厘清。

GRANT SELECT 是创建只读账号的正确方式

首先要明确：MySQL并没有一个名为“READ ONLY”的用户类型。我们所说的只读账号，本质上是一个只被授予了SELECT权限的账号。因此，最直接可靠的方法是使用GRANT SELECT。这比事后用REVOKE INSERT, UPDATE...禁用一堆写权限要稳妥得多——MySQL的权限机制是白名单，没给的权限就是无法执行。

一个常见误区是先创建用户，再试图用REVOKE收回所有写权限。这种做法极易遗漏，比如忘记TRUNCATE或LOCK TABLES，反而留下安全隐患。从一开始就只授予SELECT，才能一劳永逸。

• 范围是关键：GRANT SELECT必须指定数据库和表范围。GRANT SELECT ON *.*是全局只读，而GRANT SELECT ON mydb.*则仅限于某个库。
• 别忘了元数据：如果用户需要查询INFORMATION_SCHEMA（例如执行SHOW CREATE TABLE查看表结构），必须额外授权：GRANT SELECT ON INFORMATION_SCHEMA.*，否则操作会失败。
• 注意锁的边界：即使用户只有SELECT权限，如果他执行的语句是SELECT ... FOR UPDATE，也会触发错误。因为FOR UPDATE涉及行锁机制，这需要额外的LOCK TABLES权限。所以，错误提示ERROR 1142 (42000): SELECT command denied可能会让人困惑，其实问题出在“锁”上。

CREATE USER 和 GRANT 要分两步写，别合在一条语句里

从MySQL 5.7开始，已经不支持在一条语句里同时完成创建用户和授权。如果强行写成CREATE USER ... GRANT ...，将导致语法错误。正确的做法永远是两步走：

CREATE USER 'ro_user'@'%' IDENTIFIED BY 'strong-pass-2024';
GRANT SELECT ON myapp_db.* TO 'ro_user'@'%';

这里有三个细节值得强调：

• 主机名别太随意：生产环境强烈不建议使用'%'允许所有IP连接。最好限定在内网网段，例如'192.168.10.%'。
• 密码强度是硬性要求：密码必须用单引号包裹，且不能过于简单。MySQL 8.0及以上版本默认启用了密码强度校验，弱密码会被直接拒绝。
• 无需手动刷新：执行完GRANT语句后，权限会立即生效，通常不需要再执行FLUSH PRIVILEGES——除非你手动修改了底层的mysql.user系统表。

只读账号连不上？检查是否漏了 USAGE 权限

这个问题坑过不少人：明明用GRANT SELECT给了权限，账号却死活连不上，提示“Access denied”。原因在于，新建的账号必须拥有USAGE权限才能建立连接。USAGE可以理解为“登录许可证”，而SELECT是“操作许可证”。GRANT SELECT并不会自动附带USAGE。

解决方法很直接，补上就行：

GRANT USAGE ON *.* TO 'ro_user'@'%';

不过，这里要特别警惕一个“好心办坏事”的操作：绝对不要在只读账号上使用WITH GRANT OPTION。这个选项意味着该账号可以将自己的权限授予他人，这完全违背了只读的初衷。对于只读账号，确保它有USAGE权限就足够了。实际上，当你执行GRANT SELECT ON *.*时，MySQL会隐式地加上USAGE；但如果你授权范围精确到某个库或某张表（如GRANT SELECT ON mydb.t1），则必须显式补充USAGE授权。

MySQL 8.0 的角色机制让只读管理更清晰，但别滥用

对于MySQL 8.0的用户，角色（Role）功能能让只读权限管理变得井井有条。你可以创建一个名为role_readonly的角色，将所有SELECT权限授予它，然后再把需要只读权限的用户加入这个角色。未来权限变更时，只需修改角色，所有相关用户会自动生效。

听起来很完美，但有几个陷阱需要注意：

• 角色不会自动生效：角色本身不能登录，必须通过SET DEFAULT ROLE语句将角色赋予用户，否则用户登录后无法行使角色的权限。
• 权限不自动继承：新建一个数据库newdb后，现有的role_readonly角色对它没有任何权限。你必须手动执行GRANT SELECT ON newdb.* TO role_readonly。
• 备份脚本的权限陷阱：使用mysqldump --single-transaction进行备份时，只读账号通常可以胜任。但如果备份脚本中包含SHOW MASTER STATUS这类语句，就需要额外授予REPLICATION CLIENT权限。这属于复制相关权限，与“只读”是两码事，不要混淆。

最后，还有一个高阶但至关重要的点：视图和存储函数的权限穿透问题。当只读账号查询一个视图（VIEW）或调用存储函数时，权限检查可能发生在定义者（DEFINER）的上下文中。如果这个视图是用DEFINER = 'root'@'localhost'创建的，那么即使用户只有SELECT权限，他通过视图访问底层表时，实际行使的却是root的权限——这无疑彻底绕过了只读限制。因此，务必确保视图和函数的SQL SECURITY属性设置为INVOKER（调用者），这样权限检查才会基于当前执行用户的权限。