MySQL 创建用户与授权实战：从最小权限到远程连接排查

接手过不少烂摊子之后我越来越确定一件事很多MySQL出问题的现场根源不在SQL写得差而在账号权限乱得没法看。前阵子帮人排查一台测试机发现上面四五个应用全在用 root 连库问就是“图省事”。省事是真省事但这账号一旦被拖库整台数据库服务器等于裸奔。所以这篇文章就把 MySQL 创建用户和授权这件事从头到尾捋清楚怎么建号、怎么给权限、远程连不上到底卡在哪、权限给出去之后怎么收回来以及我实际运维中踩过的那些坑。不管是刚入门的开发还是要管生产库的运维照着做基本不会出大问题。1. 为什么我劝你别一直用 root权限设计的核心价值1.1 最小权限原则省下来的都是事故很多人觉得 MySQL 的权限管理麻烦不如一个 root 走天下。但在实际生产环境里这套“省事”逻辑是要付出代价的。想象一下你有一台服务器上面跑了订单系统、日志系统、管理后台三个应用全部用 root 连接数据库。某天日志系统被注入了恶意语句攻击者拿到这个连接后不仅能读日志表还能删订单库、改管理员的密码甚至 DROP 掉整个实例。如果从一开始就按“一应用一账号”来分配权限日志系统只有日志库的增删改查权限那么即使这个连接被脱裤攻击者能做的也仅限于日志库范围内的破坏影响面被限制在一个很小的圈子里。这就是最小权限原则每个账号只拥有完成自身任务所必需的最小权限集合。多花五分钟建号授权能避免的可能是几小时的恢复工作和不可估量的数据损失。1.2 MySQL 账号的真实结构用户和主机是成对出现的新手最容易忽略的一点是MySQL 里定义一个用户不是光看用户名而是“用户名 登录来源主机”的组合。完整写法是用户名主机比如applocalhost和app10.0.0.5在 MySQL 眼里这是两个完全不同的账号可以分别设置不同的密码和权限。这个设计初衷很清晰同一个用户名从本机登录和从远程应用服务器登录可以被分配完全不同的权限。比如本机登录可以管理整个库远程登录只允许查询某个表。理解了这一点很多关于“明明授权了却连不上”的困惑就能解开一半——你八成是给applocalhost授权了但从远程用app去连命中的是另一个账号甚至压根不存在的账号。后面我会专门讲这块的排查。2. 建号这一步就藏着不少坑CREATE USER 详解2.1 基本语法与最简单的建号姿势MySQL 从 5.7 开始推荐用CREATE USER来创建账号而不是像老教程那样直接往mysql.user表里 INSERT。直接改系统表的做法在 8.0 里已经被禁止了所以老老实实用官方语句。最基本的创建用户语句长这样CREATE USER applocalhost IDENTIFIED BY YourStr0ngPassword;这条语句干了三件事创建了一个名为 app 的用户限制其只能从本机localhost登录并设置了初始密码。执行成功后这个账号可以在 MySQL 里登录但还没有任何操作权限——连SHOW DATABASES都只能看到一个 information_schema更别说读写业务库了。所以建完号之后必须立刻跟进授权。2.2 密码策略为什么你设的密码总是被拒很多人在执行上面那条语句时会被报错ERROR 1819 (HY000): Your password does not satisfy the current policy requirements。这不是因为你手气不好而是 MySQL 默认安装了密码校验插件validate_password。在 MySQL 8.0 中默认密码策略要求密码至少 8 位并且包含大小写字母、数字和特殊字符中的至少三类。这个策略可以通过以下语句查看当前要求SHOW VARIABLES LIKE validate_password%;如果只是本地测试环境想降低密码强度也可以但我不建议在生产环境这么干。更合理的做法是让开发自己提一个符合复杂度的密码你负责用下面的语句做校验强度评估SELECT VALIDATE_PASSWORD_STRENGTH(YourStr0ngPassword);这条语句会返回 0 到 100 的分数75 以上表示强度不错。我习惯在给业务方生成密码时随手跑一下这个函数低于 75 就直接打回重设省得后面被安全扫描工具揪出来。2.3 8.0 默认认证插件的兼容性坑MySQL 8.0 默认的认证插件是caching_sha2_password而 5.7 及更早版本默认是mysql_native_password。如果你用的是比较老版本的客户端工具、JDBC 驱动或者编程语言的 MySQL 扩展连接 8.0 时会报认证失败的错误比如Authentication plugin caching_sha2_password cannot be loaded。解决方式有两种。第一种是升级客户端驱动这是最推荐的方案毕竟 caching_sha2_password 在安全性上更强。第二种是迁就老客户端在创建用户时显式指定老插件CREATE USER applocalhost IDENTIFIED WITH mysql_native_password BY YourStr0ngPassword;或者对已存在的用户做修改ALTER USER applocalhost IDENTIFIED WITH mysql_native_password BY YourStr0ngPassword;这种写法会降低账号的认证安全性而且 MySQL 官方已经在 8.0 系列中把 mysql_native_password 标记为废弃未来版本可能移除。所以除非有迫不得已的兼容需求否则别用第二种。我实际处理过的一个案例是内部老系统用 PHP 5.6 连 MySQL 8.0实在没法升级只能临时用mysql_native_password过渡等系统升级后第一时间切了回来。2.4 用户已存在怎么办IF NOT EXISTS 与重复执行问题脚本化运维时经常会遇到重复执行建号语句的报错ERROR 1396 (HY000): Operation CREATE USER failed for applocalhost。原因是同名同主机的用户已经存在。稳妥的写法是加一个判断CREATE USER IF NOT EXISTS applocalhost IDENTIFIED BY YourStr0ngPassword;加了IF NOT EXISTS之后重复执行不会报错但也不会修改已有用户的密码。如果你想要的效果是“用户存在则更新密码不存在则创建”那得先查后改或者直接用ALTER USER。但这属于更进阶的运维写法常规场景下建号脚本保持幂等就够了。3. GRANT 授权给权限不是撒芝麻要有边界3.1 授权语句的完整面貌创建完用户之后下一步就是授权。授权用GRANT语句标准格式如下GRANT SELECT, INSERT, UPDATE, DELETE ON mydb.* TO applocalhost;这条语句表示允许 app 这个用户从本机登录后对 mydb 数据库下的所有表执行查询、插入、更新和删除操作。ON后面跟的是权限作用范围常用的有这么几种授权范围写法含义所有库所有表ON *.*全局权限一般只给管理员单个库所有表ON mydb.*库级权限最常用单个库单张表ON mydb.orders表级权限控制更细单张表的某些列ON mydb.orders (order_id, amount)列级权限极少用3.2 权限粒度对照从 SELECT 到 ALL PRIVILEGESMySQL 的权限类型多到让人眼花但实际开发中常用的就那么几个整理成一张表方便对照权限作用使用场景SELECT查询数据所有只读账号必备INSERT插入数据业务写账号UPDATE更新数据业务写账号DELETE删除数据业务写账号CREATE创建库/表需要执行建表脚本的账号ALTER修改表结构执行迁移脚本的账号DROP删除库/表高危权限非必要不给INDEX创建/删除索引执行索引维护的账号REFERENCES创建外键约束一般不需要ALL PRIVILEGES以上所有权限库管理员账号谨慎使用我见过不少团队图省事直接给应用账号一个ALL PRIVILEGES ON mydb.*结果某个开发误执行了DROP TABLE整个表瞬间没了。给ALTER和DROP权限之前先问问自己这个账号真的需要在运行时改表结构吗大部分业务应用只需要SELECT, INSERT, UPDATE, DELETE四个权限就够了。DDL 操作应该交给专门的迁移账号或者 DBA 来执行。3.3 授权而已每次都要 FLUSH PRIVILEGES 吗这是一个传了很多年的误区。很多老教程会说“授权后要执行 FLUSH PRIVILEGES 刷新权限”实际上通过 GRANT、REVOKE、CREATE USER、ALTER USER 这些标准账号管理语句做的修改会立即生效不需要刷新。FLUSH PRIVILEGES真正的作用场景只有一个你通过直接修改系统表比如往mysql.user表里 INSERT来调整权限。而这种操作方式在 MySQL 8.0 里已经被限制得很死官方也不推荐。所以结论很简单正常用 GRANT 授权之后不用执行任何刷新命令。我在生产环境操作了无数次从没因为没刷新导致权限不生效的情况。如果确实遇到授权后不生效问题大概率出在客户端已经建立了旧连接需要重连才能拿到新权限而不是权限没刷新。3.4 只读账号怎么建给开发/报表同学的安全姿势日常开发中一个高频需求是要给同事或者报表系统开一个只读账号能查数据但不能改数据。这个场景下最安全的授权方式是只给SELECT并且最好限制只能查特定库CREATE USER readonly% IDENTIFIED BY Read0nlyPass!; GRANT SELECT ON mydb.* TO readonly%;如果觉得一张表一张表地给太繁琐也可以一次性授权整个库的查询权限。唯一要留意的是只读账号别给SELECT ... FOR UPDATE以外的写操作SELECT INTO OUTFILE这类能把数据导出到服务器的语句也要留意。真遇上需要导数据的场景宁可用客户端工具导出到本地也别给只读账号额外的文件权限。4. 远程连不上八成卡在这几处配置上4.1 localhost、% 与指定 IP 的区别一次看清授权时最常遇到的困惑是applocalhost和app%到底差在哪下面这张表说得很清楚主机部分写法允许登录的来源典型场景localhost仅本机通过 socket 或 127.0.0.1数据库和应用部署在同一台机器127.0.0.1仅本机通过 TCP 回环地址同上但强制走 TCP 协议%任意主机应用服务器和数据库分离或开发远程连接192.168.1.%特定网段限定内网网段访问192.168.1.100指定单个 IP限制只有某一台机器能连这里有一个常见的坑如果你执行了CREATE USER app%然后又执行GRANT ALL ON mydb.* TO applocalhost那从远程连接时用的是app%这个账号的权限而你授予的权限却是挂在applocalhost上的。两个账号看似同名实际上各管各的。所以建号之前先想清楚登录来源别一会儿建%一会儿建localhost最后自己都分不清哪个账号生效。4.2 bind-address 和防火墙授权了也连不上的隐形杀手权限给了用户也没建错但从远程还是连不上这种情况十有八九出在 MySQL 配置或系统防火墙。先看 MySQL 的监听配置。编辑 MySQL 配置文件Linux 下通常在/etc/mysql/mysql.conf.d/mysqld.cnf或/etc/my.cnf找到这一行bind-address 127.0.0.1如果 bind-address 是127.0.0.1MySQL 只监听本机回环地址远程的 TCP 连接根本到不了 MySQL 这一层。需要改成0.0.0.0监听所有网卡或者指定内网 IPbind-address 0.0.0.0改完配置后重启 MySQLsudo systemctl restart mysql再看防火墙。如果系统装了 firewalld 或 ufw默认策略通常是拒绝外部访问 3306 端口。放行命令分别如下# firewalld sudo firewall-cmd --permanent --add-port3306/tcp sudo firewall-cmd --reload # ufw sudo ufw allow 3306/tcp这两处都处理完之后再用远程客户端试连。我排查过的一个真实案例用户授权的 host 是%bind-address 也改了防火墙端口也放行了但还是连不上。最后发现是云服务商的安全组规则里没放行 3306云平台层面的防火墙把流量挡在了外面。所以远程连接的问题要从“应用 - 系统防火墙 - MySQL 监听 - 账号授权”这条链路逐层排查任何一层出问题都连不上。4.3 密码正确却报 Access denied关注认证插件和 DNS 反解如果已经走到了 MySQL 的认证环节却报ERROR 1045 (28000): Access denied for user app...除了密码确实输错了之外还有一种情况是认证插件不匹配就是前面提到的 caching_sha2_password 和 mysql_native_password 的问题。解决方案前面已经给过这里不再重复。另外MySQL 在解析客户端 IP 对应的主机名时默认会做 DNS 反解。如果 DNS 服务不稳定每次连接都会有一段延迟甚至报 host 无法解析的错误。线上经验是把这个选项关掉在配置文件的[mysqld]段加上skip-name-resolve开启后MySQL 不再将 IP 反解为主机名账号匹配直接按 IP 来。注意开启这个选项后如果你有授权给applocalhost.localdomain这类主机名的账号就匹配不上了需要改成对应的 IP 或%。一般生产环境都会开启减少一层依赖。5. 权限的查看、回收和删除把退路留好5.1 SHOW GRANTS永远先搞清楚当前账号有什么权限接手一个项目时我做的第一件事永远是先看账号权限清单而不是猜。查看某个账号权限的语句SHOW GRANTS FOR applocalhost;查看当前登录账号自己的权限SHOW GRANTS FOR CURRENT_USER();输出结果会列出所有已经授予该账号的权限语句格式大概是GRANT SELECT, INSERT, UPDATE, DELETE ON mydb.* TO applocalhost。这个命令在排查“为什么这个账号能删表”“为什么那个账号看不到库”之类的问题时是第一手依据。另外一个高频需求是查看当前 MySQL 里到底有哪些账号。用这条SELECT user, host, account_locked, password_expired FROM mysql.user;这条查询走的是系统库mysql.user注意是只读查询别手滑去 UPDATE 或 DELETE。5.2 REVOKE权限给错了怎么优雅地收回来收回权限用REVOKE语法和GRANT正好是对称的。比如你之前给了app账号 DELETE 权限现在觉得风险太大可以这样收回REVOKE DELETE ON mydb.* FROM applocalhost;如果想把某个账号在某库上的所有权限全部一次性收回可以这样REVOKE ALL PRIVILEGES ON mydb.* FROM applocalhost;注意REVOKE ALL PRIVILEGES收回的是你在ON指定范围内的权限不会自动删除账号本身也不会回收其他库上的权限。想彻底清掉这个账号的一切痕迹直接删用户更干净见下一节。5.3 DROP USER删账号的正确姿势员工离职、应用下线、测试环境清理都需要删账号。标准语句DROP USER applocalhost;MySQL 8.0 还支持一次删多个账号DROP USER applocalhost, readonly%;如果账号不存在会报错所以脚本化删除时同样建议加IF EXISTSDROP USER IF EXISTS applocalhost;有一个细节删除用户前先确认没有会话还在用这个账号连接。直接 DROP 掉正在使用的账号会导致该用户的现有连接在下次操作时报错或中断。稳妥的顺序是先在应用侧切换连接串确认没有业务流量了再执行 DROP。5.4 给权限之前先问自己三个问题多年运维下来我给自己定了一条规矩每次执行 GRANT 之前按下面三个问题过一遍这个账号必须从哪些主机登录——决定 host 部分写什么尽量不用%这个账号需要操作哪些库哪些表——决定ON范围能精确到表就别放松到库这个账号需要哪些操作类型——只读还是读写需不需要 DDL这套提问方式帮我挡住了不少潜在风险。最典型的例子一个报表系统说要连生产库“查数据”结果要求给ALL PRIVILEGES ON *.*一问才知道是前端开发图方便想用同一个账号连所有环境。最后协调下来给了只读账号只授权了报表库的SELECT矛盾当场就解决了。6. 实战串联从需求到落地的完整操作记录6.1 场景还原一套刚上线的业务系统需要两个账号假设我新部署了一套电商系统数据库叫shop。现在需要两个账号应用账号shop_app从应用服务器192.168.1.10连接对shop库有增删改查权限只读账号shop_report从报表服务器192.168.1.20连接对shop库只有查询权限6.2 完整的操作序列先创建应用账号限制主机为应用服务器的 IPCREATE USER shop_app192.168.1.10 IDENTIFIED BY ShopApp2024!; GRANT SELECT, INSERT, UPDATE, DELETE ON shop.* TO shop_app192.168.1.10;再创建只读账号CREATE USER shop_report192.168.1.20 IDENTIFIED BY Report2024!; GRANT SELECT ON shop.* TO shop_report192.168.1.20;检查两个账号的权限SHOW GRANTS FOR shop_app192.168.1.10; SHOW GRANTS FOR shop_report192.168.1.20;此时从应用服务器执行mysql -u shop_app -p -h 数据库服务器IP shop应该能正常连接。验证只读效果用shop_report账号执行一条INSERT预期会报权限不足的错误这就证明授权符合预期。6.3 实操中的几个补充细节第一给应用账号的密码建议用密码生成器生成随机字符串而不是让开发自己拍脑袋定一个“好记”的密码。第二所有建号授权语句建议记录到变更文档里注明申请人、申请日期、权限范围、到期时间方便后续审计和回收。第三如果某些账号是给临时人员用的记得设置密码过期策略ALTER USER shop_app192.168.1.10 PASSWORD EXPIRE INTERVAL 90 DAY;这样每 90 天强制改一次密码避免一个密码用到天荒地老。7. 授权管理这件事值得养成几个好习惯运营数据库这些年沉淀下来的几个习惯值得分享。第一个习惯是每个环境单独一套账号绝不跨环境复用。测试环境的账号密码即使泄露了也不会影响到生产库这层隔离在关键时刻能救命。第二个习惯是定期巡检账号。我通常每个月跑一次上面提到的mysql.user查询把长期不用的账号、没有密码过期策略的账号都拎出来清理一遍。第三个习惯是授权操作尽量走脚本复用而不是每次手工敲 SQL。比如把“创建只读账号”写成固定格式的脚本需要时改一下用户名和库名就能用减少操作失误的概率。MySQL 的账号权限管理并不是什么高深学问但它是数据库安全的第一道闸门。把创建用户和授权这件事做到位比装一堆安全软件都管用。最后分享一个我常跟团队说的一句话给权限时抠门一点出事故时才能从容一点。