做后端开发久了你会发现MySQL 里最频繁的翻车场景往往不在 SQL 本身而是在用户创建和授权这一步。不是连不上数据库就是权限给得太宽被 DBA 约谈不是新环境里忘了限制主机就是明明执行了 GRANT 却依然报 Access denied。这套逻辑其实并不复杂但坑确实密集。今天我就完整地把MySQL 如何创建用户并且授权这件事讲透从底层权限模型到每一步实操再到我这些年踩过的各种坑一次性整理清楚适合刚接触 MySQL 的新手也适合那些被权限问题折磨到怀疑人生的开发、运维和测试同学。1. 先搞清楚 MySQL 的用户和权限到底是怎么一回事1.1 为什么不能一直拿 root 开整很多人在本地开发的时候习惯直接用 root 连接数据库图省事。这个习惯一旦带到生产环境基本就是事故预定。root 拥有 MySQL 实例的全部权限包括创建用户、修改全局配置、读取所有库表、甚至关闭服务。假如你的业务代码里数据库账号是 root一旦代码被注入或者配置泄露攻击者拿到的就是整个数据库的钥匙而不是某个库某张表的钥匙。用最小权限原则来设计账号是数据库安全的基本功。我见过不少团队线上应用账号用 root连 binlog 都能随便看数据被删了都不知道是哪一步出的问题。正确的做法是给每个应用、每个场景单独建用户按需授权宁可多建几个账号也不要一个超级账号打通所有环境。1.2 一个用户由三个要素决定用户名、主机、密码初学 MySQL 的人最容易懵的一个概念是MySQL 里的一个用户不是单纯由用户名决定的而是由用户名 主机共同决定密码只是附加的认证凭据。比如applocalhost和app%是两个完全不同的账号可以有不同的密码和权限。这里的主机host表示允许从哪里连接进来applocalhost只允许本机通过本地 socket 或回环地址连接。app127.0.0.1只允许从本机 IPv4 回环地址连接。app192.168.1.%允许整个192.168.1.x网段的机器连接。app%允许从任意主机连接最宽松也最需要谨慎。为什么明明创建了app%本机用 Navicat 连却提示连不上因为%并不一定覆盖本机通过 socket 发起的连接本机走 socket 时主机名会被解析成localhost。这属于后文要讲的典型坑这里先记住一个结论生产环境里如果应用需要远程连库通常建议明确指定应用服务器的 IP 或网段而不是无脑%如果应用和数据库在同一台机器上localhost往往更合适也更安全。权限的判断顺序是这样的客户端带着用户名和来源 IP 连接时MySQL 先在mysql.user表里匹配user host记录用最精确的那条规则做身份认证登录成功后再根据这条记录关联的全局权限、库级权限、表级权限逐层校验操作。理解了这个顺序后面所有授权和排错都会变得非常清晰。2. 第一步创建用户把这些坑一次排干净2.1 CREATE USER 的完整语法MySQL 从 5.7 开始推荐用CREATE USER语句创建账号而不是往mysql.user表里直接 INSERT。原因很简单直接改系统表很容易绕过密码哈希和权限校验逻辑产生一堆诡异问题。最基础的创建语句长这样CREATE USER applocalhost IDENTIFIED BY StrongPass2024;语句执行成功后这个用户就存在于mysql.user表里了但此时它没有任何访问业务库的权限连SELECT都做不了。这也符合最小权限的原则先有账号再按需授权。如果你想一步到位指定密码加密插件可以这样写CREATE USER applocalhost IDENTIFIED WITH caching_sha2_password BY StrongPass2024;IDENTIFIED WITH后面跟的是认证插件名。MySQL 8.0 默认用的是caching_sha2_password5.7 及更早版本默认是mysql_native_password。这个差异直接影响你能不能拿老版本客户端连上数据库后面第 4 节会专门讲。还有一个经常被忽略的选项是PASSWORD EXPIRE可以设置密码过期策略CREATE USER report% IDENTIFIED BY Report2024 PASSWORD EXPIRE INTERVAL 90 DAY;生产环境里让报表账号每 90 天强制改一次密码既满足安全审计要求又避免一个账号用到天长地久。MySQL 8.0 还支持CREATE USER ... PASSWORD HISTORY、FAILED_LOGIN_ATTEMPTS这些高级选项需要的话可以查官方文档日常使用频率不高。2.2 主机限制的几种写法与真实影响主机这一项值得专门花一节聊因为它是权限体系里最容易出问题的部分。很多 DBA 和开发沟通的时候最常说的一句话是你到底从哪里连给我具体 IP。常见的几种写法我整理成下表主机写法含义使用场景localhost仅本机走本地 socket 或回环地址应用与数据库同机部署127.0.0.1仅本机 IPv4 回环明确按 IP 访问的本机调试::1本机 IPv6 回环本机 IPv6 环境192.168.1.10指定某台机器应用服务器 IP 固定192.168.1.%指定网段内网多台应用服务器%任意主机最不推荐除非网络隔离充分这里有个实际经验如果应用是通过内网域名访问数据库而 MySQL 开启了反向解析它的 host 匹配可能和你预期的不一样。为了避免这种不确定性很多生产环境会开启skip-name-resolve让 MySQL 直接用 IP 做匹配这种情况下主机字段写成具体 IP 或网段才可靠。还有一个容易误会的点app%并不等于applocalhost。如果你只创建了%账号应用从本机通过 socket 连接时会因为匹配不到localhost账号而收到 Access denied。所以很多人在本机测试时发现明明建了%用户为什么还连不上原因就在这。要么把localhost账号也建上要么让客户端明确用 TCP IP 连接不要走 socket。2.3 MySQL 8.0 与 5.7 的密码插件差异以前从 5.7 升到 8.0 之后很多人第一反应是我密码没错啊为什么连不上。最常见的元凶就是认证插件从mysql_native_password切换到了默认的caching_sha2_password。老一点的客户端、旧版 JDBC 驱动、某些早期版本的 Navicat、以及一些自研的 ORM 工具对caching_sha2_password支持得并不好。报错通常是这样的Authentication plugin caching_sha2_password cannot be loaded解决办法有两种思路。一种是升级客户端驱动这是最推荐的方向毕竟caching_sha2_password在安全性上更胜一筹而且支持 RSA 公钥传输加密密码。另一种是给特定用户指定回mysql_native_passwordCREATE USER legacy% IDENTIFIED WITH mysql_native_password BY Legacy2024; -- 或者修改已有用户 ALTER USER legacy% IDENTIFIED WITH mysql_native_password BY Legacy2024;不过要提醒一句MySQL 官方在 8.0 里已经标记mysql_native_password为弃用未来版本会移除。新建项目尽量选择兼容新插件的客户端别为了省事让新系统迁就旧驱动否则以后升级数据库又是一轮折腾。3. 第二步授权权限最小化的实战姿势3.1 GRANT 语法逐项拆解用户建好之后核心动作就是授权。授权语句GRANT的完整骨架是GRANT 权限列表 ON 权限级别 TO 用户主机 [WITH GRANT OPTION];其中权限级别是关键它决定了这条授权的作用范围。MySQL 的权限级别大致分四层全局级别ON *.*对整个实例生效比如GRANT SELECT ON *.* TO ...。数据库级别ON dbname.*对指定库下所有对象生效是日常用得最多的。表级别ON dbname.tablename只针对某张表。列级别和存储过程级别可以精确到列或存储过程比如GRANT SELECT (id, name) ON dbname.tablename TO ...。常见的权限类型包括SELECT、INSERT、UPDATE、DELETE、CREATE、DROP、ALTER、INDEX、EXECUTE等。如果你图省事直接给了ALL PRIVILEGES等于把一个库的完全控制权交给了应用账号包括删库和建表。除非这个库是开发环境的玩具库否则我不建议这么干。举个例子一个典型的 Web 应用如果只需要读写业务表、不需要改表结构那就给这个权限GRANT SELECT, INSERT, UPDATE, DELETE ON shopdb.* TO app%;如果需要应用自动做表结构迁移比如用 Flyway 或 Liquibase那就得再加上CREATE、ALTER、INDEX、DROPGRANT SELECT, INSERT, UPDATE, DELETE, CREATE, ALTER, INDEX, DROP ON shopdb.* TO migrator%;注意迁移账号和应用运行账号最好是两个独立账号。应用运行账号权限小一点迁移账号只在发版时启用这才是生产级的做法。3.2 常见授权场景开发库、报表库、生产库权限这东西脱离场景谈都是耍流氓。我按最常见的三类场景给出一套可以直接抄的配置思路。场景一业务应用账号需求是读写业务数据尽量不碰表结构。对应授权GRANT SELECT, INSERT, UPDATE, DELETE ON shopdb.* TO app192.168.1.%;如果应用里有复杂的排序、事务、分页查询这些都不需要额外权限SELECT本身就够。ORDER BY、GROUP BY、事务的BEGIN/COMMIT都不属于需要授权的操作。很多人误以为事务处理需要权限其实START TRANSACTION、COMMIT这些是会话级行为和授权无关除非你用了XA RECOVER之类的管理命令。场景二只读报表/BI 账号报表系统只需要读数据最好连临时表都不能建否则一个取数脚本就能把数据库搞出大量临时表。只读授权GRANT SELECT ON shopdb.* TO report%;更进一步如果报表只需要看某个库里的特定几张表就把权限收敛到表级别GRANT SELECT ON shopdb.orders TO report%; GRANT SELECT ON shopdb.order_items TO report%;这样做的好处是即使报表账号被拖库或者被人拿去执行任意 SQL它也只能读到预先指定的几张表杀伤面被限制在最小。场景三存储过程执行账号业务里如果大量使用存储过程调用方其实只需要EXECUTE权限连表都不需要直接访问。授权方式GRANT EXECUTE ON PROCEDURE shopdb.sp_calculate_stat TO service%;存储过程内部的表操作只要过程定义者DEFINER有相应权限即可调用者并不需要直接拥有表权限。这个机制让权限设计可以非常优雅应用账号只执行过程数据库表完全对它不可见。3.3 查看、撤销、修改、删除用户授权不是一锤子买卖后续的查看和回收同样重要。查看一个用户当前拥有的权限用SHOW GRANTSSHOW GRANTS FOR app%;如果你想看自己当前会话的权限可以直接执行SHOW GRANTS;撤销权限用的是REVOKE语法和GRANT对称REVOKE DELETE ON shopdb.* FROM app%;修改密码用ALTER USERALTER USER app% IDENTIFIED BY NewPass2025;删除用户用DROP USERDROP USER app%;这里提醒一下DROP USER时主机名必须和创建时一致否则会报ERROR 1396。比如创建的是applocalhost你写成DROP USER appMySQL 找不到完全匹配的记录就会报错。删除前建议先SHOW GRANTS确认这个账号到底还绑了哪些权限免得误删。4. 第三步应用环境里的授权细节Navicat、Docker、连接池4.1 Docker 部署 MySQL 时的初始化账号现在 Docker 部署 MySQL 的场景太常见了Docker 官方镜像提供了一组环境变量来初始化账号省得你进入容器手敲 SQL。环境变量作用MYSQL_ROOT_PASSWORD设置 root 初始密码MYSQL_DATABASE自动创建一个数据库MYSQL_USER自动创建一个新用户MYSQL_PASSWORD上述新用户的密码比如这样启动容器docker run -d \ --name mysql8 \ -e MYSQL_ROOT_PASSWORDRoot2024 \ -e MYSQL_DATABASEshopdb \ -e MYSQL_USERapp \ -e MYSQL_PASSWORDApp2024 \ -p 3306:3306 \ mysql:8.0容器首次初始化时会自动帮你在shopdb库上把这个app用户建好并且默认授予这个库的全部权限。注意这里有个大坑用MYSQL_USER创建的用户默认是app%而且权限范围是整个指定的数据库权限非常大。如果你只希望这个账号具备读写能力初始化完成后建议进容器手动收缩权限改成只读或去掉 DDL。docker exec -it mysql8 mysql -uroot -p进去后执行REVOKE CREATE, DROP, ALTER, INDEX ON shopdb.* FROM app%;另外不要为了图省事把MYSQL_ROOT_PASSWORD设成root这种弱密码。容器暴露到外网的时候弱密码 root 账号被扫到就是分分钟的事。我的习惯是 root 密码只在内部管理使用业务连接一律走独立创建的普通账号。4.2 连接池与账号权限的配合线上 Java 项目几乎没有直连数据库的基本都有连接池比如 HikariCP、Druid、Tomcat JDBC Pool。连接池初始化时通常会用同一个账号创建多个物理连接这样一来权限设计就有一个注意点你给这个账号的权限会同时作用于连接池里的所有连接。所以连接池账号的权限设计应该按照这个应用最重的那类操作来授。比如应用既要做普通查询又需要在启动时做结构校验SELECT元数据那就得确保账号有SELECT权限并且能读取information_schema。有些时候应用会通过连接池执行SET SESSION之类操作来调整会话变量这类操作通常不需要额外授权但个别变量可能受权限管控遇到时报错再去查对应变量的权限要求即可。另一个容易被忽略的点是连接池的初始化 SQL。比如 Druid 连接池配置了connectionInitSqls为SET NAMES utf8mb4这个语句不需要权限但如果里面写了SET GLOBAL之类的语句普通账号会直接报权限不足。排查的时候不要只盯着账号权限先看连接池配置里有没有这种越权初始化动作。4.3 Navicat 等客户端的认证兼容很多同学用 Navicat 连 MySQL 8.0 时报认证插件错误排查思路在上面第 2.3 节已经讲了。这里补充一个实战细节Navicat 16 以及更新版本对caching_sha2_password支持已经很好如果你用的还是 Navicat 11、12 这种老版本直接升级到新版本是最省事的方案不要在一个老客户端上反复折腾驱动。另外还有 SSL 连接的问题。MySQL 8.0 默认开启了 SSL 支持很多客户端首次连接时会弹出 SSL 警告。如果你只想在内网快速调试连接时在客户端里把 SSL 选项设为禁用或如果可用则使用都行。但如果应用要求传输加密那就要给账号建好 SSL 相关的授权配置比如限定某个账号必须通过 SSL 连接CREATE USER secure_app% IDENTIFIED BY pass REQUIRE SSL;这种情况下客户端连接时就必须启用 SSL否则会直接认证失败。这个功能在政企内网和等保环境里比较常见开发环境没必要上。5. 高频报错与排查实录5.1 ERROR 1045 Access denied for user这是最常见的报错没有之一。看到ERROR 1045 (28000): Access denied for user xxxxxx的时候按这几步排查确认user host是否精确匹配。创建的是app192.168.1.%但连接来源是192.168.2.50自然会被拒绝。确认密码是否输入正确。密码有特殊字符时注意客户端里转义的问题。确认认证插件是否匹配。老客户端连 8.0 默认账号经常死在这一步。确认是否存在多个 host 记录相互干扰。比如既建了applocalhost又建了app%MySQL 会用匹配规则里更精确的那条如果更精确的那条密码不同就会导致密码正确却连不上。排查时可以用以下 SQL 看看当前库里到底有哪些账号SELECT user, host, plugin FROM mysql.user;5.2 ERROR 1130 Host is not allowed to connect报错大概长这样ERROR 1130 (HY000): Host 192.168.1.100 is not allowed to connect to this MySQL server。这个和 1045 还不一样它说明账号密码都没问题但 MySQL 层面拒绝了这个来源主机。常见原因有三个一是创建账号时主机范围设得太死比如只允许了localhost你偏要从远程连那必然被拒。解决办法是修改账号的主机范围ALTER USER applocalhost IDENTIFIED BY App2024; RENAME USER applocalhost TO app192.168.1.%;二是bind-address配置问题。MySQL 监听地址默认是127.0.0.1只监听本机。如果配置里写的是bind-address 127.0.0.1外部机器永远连不上改配置后要重启 MySQL 服务。三是操作系统防火墙或云安全组没放行 3306 端口。这个经常被忽略我见过太多人花半天调 MySQL 权限最后发现是云控制台安全组没加规则。排查顺序应该是先telnet ip 3306看端口通不通再回到 MySQL 里看账号和绑定配置。5.3 ERROR 1396 Operation DROP USER failed执行DROP USER app或者RENAME USER时报ERROR 1396绝大多数是主机名没写完整。必须在DROP USER里带上完整的userhostDROP USER applocalhost;如果还是报错用SELECT user, host FROM mysql.user;确认这条记录到底存不存在。另外如果用户正在被某个会话使用DROP操作也可能受到锁等待影响不过这种情况不常见。5.4 ERROR 1819 密码策略不满足创建用户时报ERROR 1819 (HY000): Your password does not satisfy the current policy requirements说明validate_password插件生效了。MySQL 的密码策略默认有等级LOW、MEDIUM、STRONG。8.0 默认是MEDIUM要求密码至少 8 位包含数字、大小写字母和特殊字符。如果这是开发环境你确实想降低要求可以临时调整策略SET GLOBAL validate_password.policy LOW; SET GLOBAL validate_password.length 6;但我要提醒一句生产环境千万别这么干。密码策略是保护数据库的第一道防线开发环境嫌麻烦可以低一点生产必须按规范来。合规审计的时候密码复杂度几乎必查。5.5 FLUSH PRIVILEGES 到底要不要执行关于FLUSH PRIVILEGES网上说法很混乱。结论是这样如果你用的是CREATE USER、GRANT、REVOKE、ALTER USER这类官方账号管理语句不需要执行FLUSH PRIVILEGESMySQL 会立即生效。只有在直接手动修改了mysql.user、mysql.db这些系统表或者排查某些缓存异常时才需要执行FLUSH PRIVILEGES重建权限缓存。我见过一些老教程授权完非得执行一句FLUSH PRIVILEGES才放心。这在功能上没有坏处但会制造一种误导让人觉得授权不是即时生效。更好的习惯是少直接改系统表用完官方语句不用 flush 也能生效。6. 一些我自己踩过之后才长记性的经验最后分享几条我这些年总结出来的实操心得不一定在文档里写得这么直白但都是真金白银换来的经验。第一条经验是每个账号都要能追溯到用途。我见过一个库里躺着二十几个root或者admin账号没人说得清它们是干嘛的。后来做权限治理的时候逐一删掉才发现某个报表系统在偷偷依赖其中的一个。建议在创建账号时就用命名表达用途比如app_prod_reader、migration_prod、bi_report同时每隔一段时间跑一遍SELECT user, host FROM mysql.user做盘点把不用的账号清掉。第二条经验是给权限做减法而不是做加法。对应用账号来说默认什么权限都不给按业务需求一个个往上加对离职同事和废弃系统的账号及时DROP USER。权限越大出事的半径就越大。哪怕只是开发库ALL PRIVILEGES也容易让手滑的人一个DROP DATABASE把几周的工作成果带走。第三条经验是把授权语句纳入版本管理。以前我都是手动在服务器上敲授权时间一长根本记不清哪条授权在哪个环境执行过。后来把所有CREATE USER、GRANT、REVOKE语句写进一个init.sql或单独的迁移脚本里跟着代码仓库一起走。新环境初始化时直接执行脚本既保证一致性又方便 review出问题还能回溯。第四条经验专门说给容器和云环境数据库账号的 host 限制不要和云网络架构脱节。有些云厂商的数据库产品会把来源 IP 显示成 NAT 后的 IP用%省事但也意味着任何拿到账号密码的机器都能连。我当时踩过一次一个数据分析平台用的 MySQL 账号是%结果某个临时任务把密码打到了日志里虽然很快回收了账号但那几个小时里所有能访问日志的人理论上都能连库。从那以后只要是生产库账号 host 一律按网段或者具体 IP 写没有例外。MySQL 的用户创建和授权说到底是数据库安全的第一道闸门。把userhost匹配逻辑理解透把GRANT/REVOKE的粒度掌握好再结合具体环境把坑一个个排掉你就能少掉很多头发。希望这篇整理能帮你把权限这套机制彻底捋顺下次不管是自己测试还是给别人排查都能一步到位。