写这篇的起因很直接前阵子给一个团队做数据库规范评审翻了一圈发现好几个项目的MySQL账号居然还是清一色的root有些连接密码甚至直接写在代码仓库里。很多人并不是不想做用户隔离和权限管理而是没搞清楚“创建新用户”和“授予权限”到底怎么配合。更麻烦的是MySQL 8.0之后语法变了网上一堆老教程直接跑不通照着抄就报 ERROR 1410。所以这篇就把 MySQL 创建新用户及授予权限的完整流程讲透从最根本的安全理念、版本差异、SQL语法到常见的报错排查一步步给你完整可复现的命令。如果你是后端开发、运维或者自己买服务器折腾个人项目只要不是纯单机离线玩具库我都建议别再用 root 跑业务连接。账号按角色拆开权限按需分配看着多几步真出事时能救命的。1. 为什么必须把账号拆开1.1 root 一把梭方便是真的危险也是真的很多人刚开始用 MySQL 的时候都是 root 登录一条命令走天下因为安装完默认就这一个超级账号mysql -u root -p一看就懂。个人本地折腾随便但一旦进入“多服务共用一库”的阶段root 就等于把整个数据库的家门钥匙挂在大门口。我遇到的真实事故是这样的一个内部项目的配置中心泄露了 root 密码攻击者连上数据库直接执行了 DROP DATABASE。当时备份策略还不完善最后靠冷备恢复前前后后折腾两天。你说这是黑客多高明吗不是就是账号权限没有收口一个泄露点变成致命点。1.2 最小权限原则给账号刚刚好的权力数据库权限管理的核心说穿了就四个字最小权限。账号只拿完成任务必需的权限多一个都不给。比如报表服务只需要读取数据那就只给 SELECT应用写库需要增删改就只给 INSERT、UPDATE、DELETEDDL 这种建表、删表的操作只给到 DBA 和迁移专用的账号。最小权限不是给管理添麻烦而是给风险封顶。它有三个直接好处账号泄露时攻击者能破坏的范围被封死在授权边界内。多个应用共用一套库时不会因为某个服务的 bug 误操作到别的表。出问题时可以从 binlog 或审计日志里快速锁定是哪个账号、哪个主机动了手。提示权限管理的目标不是“防止所有人做事”而是“把每个账号的做事边界画清楚”。1.3 我的账号命名习惯人员、应用、工具分开我维护账号时有个习惯把账号按三类分人dba_zhang、dev_li主机限定为跳板机或本机。应用app_order_rw、app_order_ro按项目或服务命名主机是应用服务器网段。工具backup、repl用于备份和复制主机一般是固定的备份机或机房出口。这样命名的好处是SHOW PROCESSLIST 时一眼就能看清谁是谁。多人共用一个账号出了问题就只能靠猜排查成本非常高。2. 动手之前必须搞明白的版本差异2.1 MySQL 8.0 和老版本最大的语法坑在 MySQL 5.7 及更早版本可以用一条 GRANT 同时完成“创建用户 授权”GRANT ALL PRIVILEGES ON mydb.* TO devlocalhost IDENTIFIED BY secret;一条命令简洁到让人喜欢。但从 8.0 起这条语法被拿掉了。如果在 8.0 里执行MySQL 会直接拒绝并提示ERROR 1410 (42000): You are not allowed to create a user with GRANT正确姿势是先 CREATE USER 再 GRANT两步走。这个坑我亲眼见过好几个团队踩老安装脚本在 8.0 上跑挂就是因为这一点。2.2 认证插件为什么有的客户端连不上 8.0MySQL 8.0 默认认证插件是 caching_sha2_password加密强度更高但兼容性差一些。老版本的 PHP mysqli、老 JDBC 驱动、没升级过的 SQLyog、旧版 Navicat 都可能报认证失败。快速解决创建用户时显式指定老插件CREATE USER legacy_app% IDENTIFIED WITH mysql_native_password BY password;或者升级客户端驱动。我建议优先升级驱动因为 mysql_native_password 以后会被官方彻底移除靠它保兼容不是长久之计。2.3 主机地址白名单localhost、%、网段各有什么用创建用户时一定要想清楚写哪个 host这一项决定账号能从哪里连接Host 写法含义适用场景userlocalhost只允许本机连接本机备份、运维脚本user%任意主机可连云上跨机器访问但必须有强密码和防火墙配合user192.168.1.%只允许内网网段公司内部服务user203.0.113.5只允许指定IP固定出口的客户端这里有个容易混淆的点applocalhost 和 app% 是两个完全独立的账号密码和权限都可以不一样。MySQL 匹配账号时同时考虑 user 和 host 两个字段。我见过有人建了 app%却发现本机用 applocalhost 连不上就是没理解这一点。3. 第一步实操创建新用户3.1 CREATE USER 语法拆解完整的创建用户语法可以写成这样CREATE USER [IF NOT EXISTS] user_namehost_name IDENTIFIED BY password [PASSWORD EXPIRE ...] [ACCOUNT LOCK | UNLOCK];重点参数说明IF NOT EXISTS重复执行不报错适合写进初始化脚本。IDENTIFIED BY密码直接以明文写在 SQL 里写脚本时注意脱敏建议用环境变量注入。PASSWORD EXPIRE可设置密码过期天数比如 90 天。ACCOUNT LOCK刚创建时可以先用锁住状态等配置好授权再解锁。3.2 几个可以直接抄的建用户命令基础款本机专用CREATE USER opslocalhost IDENTIFIED BY Ops2024!StrongPwd;跨网段访问指定内网CREATE USER app_order_rw192.168.10.% IDENTIFIED BY AppOrder2024#Secret;指定老认证插件兼容旧客户端CREATE USER legacy_app% IDENTIFIED WITH mysql_native_password BY Legacy2024;先锁住再配置CREATE USER new_dev% IDENTIFIED BY Dev2024 ACCOUNT LOCK;3.3 查看用户、修改密码、锁与解锁查看当前库里的用户和插件SELECT user, host, plugin, password_expired, account_locked FROM mysql.user WHERE user NOT LIKE mysql.%;修改密码ALTER USER app_order_rw192.168.10.% IDENTIFIED BY NewPwd2024;锁定和解锁ALTER USER new_dev% ACCOUNT LOCK; ALTER USER new_dev% ACCOUNT UNLOCK;设置密码过期策略ALTER USER app_order_rw192.168.10.% PASSWORD EXPIRE INTERVAL 90 DAY;创建用户只是第一步密码和账号状态是后续持续管理的事。别图省事把 CREATE USER 一次写完后期轮转密码、离职封禁都会用到 ALTER 系列。另外注意MySQL 8.0 默认启用了 validate_password 组件密码必须满足一定复杂度否则会报 ERROR 1819。本地学习测试可以调整策略生产环境还是建议按安全基线设置长度和字符类型。4. 第二步实操授权、回收与查看4.1 GRANT 语法速记GRANT privilege_type [(column_list)] ON [object_type] privilege_level TO userhost [WITH GRANT OPTION];privilege_type权限类型。privilege_level授权范围。WITH GRANT OPTION允许该用户把已有权限再授予别人谨慎使用。4.2 授权范围层级别只会.MySQL 的授权范围有四种粒度从大到小层级写法典型用途全局ON.管理员账号一般不给业务数据库ON mydb.*最常见业务账号一个库一个权限表ON mydb.orders特定表操作列SELECT (order_id) ON mydb.orders敏感列可见控制常用权限列表DMLSELECT、INSERT、UPDATE、DELETEDDLCREATE、ALTER、DROP、INDEX、TRIGGER管理PROCESS、RELOAD 等其他EXECUTE存储过程/函数、REPLICATION SLAVE、REPLICATION CLIENT4.3 授权实例最常用的几种业务应用读写账号GRANT SELECT, INSERT, UPDATE, DELETE ON mydb.* TO app_order_rw192.168.10.%;只读报表账号GRANT SELECT ON mydb.* TO report_ro192.168.10.%;允许执行存储过程GRANT EXECUTE ON mydb.* TO app_order_rw192.168.10.%;给 DBA 一台机器上的全部权限GRANT ALL PRIVILEGES ON *.* TO dba_zhanglocalhost WITH GRANT OPTION;这里要注意ALL PRIVILEGES 不等于所有管理动作文件权限等一些特殊权限需要单独授权不要以为给了 ALL 就一劳永逸。4.4 查看权限、回收权限、删除用户查看某个账号权限SHOW GRANTS FOR app_order_rw192.168.10.%;回收权限收回 DELETE其余不变REVOKE DELETE ON mydb.* FROM app_order_rw192.168.10.%;完全回收全部权限REVOKE ALL PRIVILEGES, GRANT OPTION FROM app_order_rw192.168.10.%;删除用户DROP USER app_order_rw192.168.10.%;再聊一下 FLUSH PRIVILEGES。如果你是用 CREATE USER、GRANT、REVOKE 这些标准语句改的权限根本不用手动刷新MySQL 会自动生效。只有一种情况需要手动执行你直接往 mysql.user 表里 INSERT 或 UPDATE 了数据比如手工插了一个用户记录。绝大多数日常操作都不需要碰 FLUSH。5. 实战案例从开发到生产的三套配置5.1 开发环境一人一库一账号开发环境我一般给每个开发一个独立的库和账号CREATE USER dev_lilocalhost IDENTIFIED BY DevLi2024; GRANT ALL PRIVILEGES ON dev_li_db.* TO dev_lilocalhost;好处是各改各的互不干扰也不会因为某人误操作把公共库搞乱。如果有权限调整直接在授权语句里改。5.2 生产环境应用账号只给 DML生产环境坚决不给业务账号 DDL 权限。一个订单服务的标准配置CREATE USER app_order_rw192.168.10.% IDENTIFIED BY OrderApp#2024!; GRANT SELECT, INSERT, UPDATE, DELETE ON order_db.* TO app_order_rw192.168.10.%;这样即使程序有 SQL 注入漏洞攻击者也拿这个账号只能做 DML不能建表、删表也不能把权限传给其他账号。5.3 报表和备份账号只读与工具账号报表账号一般只给 SELECTCREATE USER report_bi192.168.10.% IDENTIFIED BY Report2024; GRANT SELECT ON order_db.* TO report_bi192.168.10.%;备份账号需要 SELECT、LOCK TABLES、RELOAD、PROCESS 等权限不同备份工具要求不同以 mysqldump 为例GRANT SELECT, LOCK TABLES, RELOAD, PROCESS ON *.* TO backup_userlocalhost;5.4 主从复制账号搭建主从复制时需要单独建一个复制账号CREATE USER repl192.168.10.% IDENTIFIED BY Repl2024; GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO repl192.168.10.%;这里要特别注意REPLICATION 是全局权限只能写在ON *.*上不能指定某个库。我第一次配主从的时候就在这栽过写成ON db.*直接报错。5.5 图形化工具怎么操作Navicat 里在左侧连接下找到“用户”你会看到类似 mysql.user 的列表。新建用户时填用户名、主机、密码然后切换到“权限”页勾选需要的权限最后保存。操作时自动生成的就是 CREATE USER GRANT 语句可以复制出来作为脚本记录。DBeaver 也类似不过我更推荐在“数据库”菜单下打开 SQL 编辑器直接执行命令。DBeaver 支持直接编辑 SQL 文件把权限管理做成版本化脚本方便评审和追溯。6. 踩坑记录常见报错与排查清单6.1 创建与授权阶段常见的报错报错信息原因解决办法ERROR 1410 (42000): You are not allowed to create a user with GRANT在 MySQL 8.0 里用 GRANT 创建用户先 CREATE USER再 GRANTERROR 1819 (HY000): Your password does not satisfy the current policy requirements密码不符合 validate_password 策略提高密码复杂度或按需调整 validate_password 策略ERROR 1396 (HY000): Operation CREATE USER failed for xx%用户已存在或残留未清理先查 mysql.user确认后 DROP USER 或使用 IF NOT EXISTS6.2 连接阶段最常见的两个错误ERROR 2002 (HY000): Cant connect to local MySQL server through socket /tmp/mysql.sock这个一般出现在本机连接MySQL 没启动或者 socket 文件路径不对。先用systemctl status mysqld或service mysql status看服务状态如果服务在再查 my.cnf 里的 socket 路径是否和连接时用的一致。ERROR 1045 (28000): Access denied for user xxlocalhost这个就是用户、主机或密码对不上。排查顺序看 mysql.user 里有没有这个 userhost 组合确认密码是否输错再确认客户端连接时用的 host 是否和授权 host 匹配。很多人建了 applocalhost然后从远程用 app% 连当然会被拒。6.3 远程连不上优先级从低到高排查远程连不上我习惯按优先级查这几项MySQL 是否监听外网检查 my.cnf 的 bind-address如果是 127.0.0.1远程肯定连不上改成 0.0.0.0 并重启注意只绑定内网 IP 更安全。账号 host 是否允许确认授权是 user% 还是 user内网网段。防火墙或安全组云主机查安全组入方向规则自建机器查 iptables 或 firewalld。SSL 连接问题如果客户端启用了严格 SSL 验证而服务端证书配置不对会报 SSL 相关错误。可以在连接参数里临时调整为不验证但生产建议配好正式证书。6.4 权限被改后不生效的检查思路如果你刚执行完 GRANT连接端还是报权限不足按这个顺序排查用 SHOW GRANTS FOR userhost 确认权限记录已经加上。检查当前连接是否复用了旧连接池。很多连接池框架会缓存连接需要重启应用或刷新连接池。检查 MySQL 是否开了 skip-grant-tables如果是所有权限检查都会失效这是应急模式生产禁止。提示任何权限变更都不会影响已经存在的历史连接。你 REVOKE 掉一个权限正在跑的会话仍然拥有旧权限直到这个会话结束。要立即生效只能杀掉相应连接。最后说点亲身感受创建用户和授权这套流程表面看就是几条 SQL但真正把它做好的团队通常会把这套 SQL 当作“数据库资产”来管理放在代码仓库、走评审、用脚本幂等执行。我刚开始也嫌麻烦root 一把梭确实省事直到线上出过一次权限过大导致的误删事件之后才把最小权限当成铁律。希望这篇能在你建立账号体系或排查权限问题时帮上一点忙有问题欢迎带着你的使用场景来交流。