有次帮一家创业公司排查数据库故障业务反馈后台能登录但所有报表都拉不出来登录MySQL一看项目跑批任务居然是用root账号直连生产库密码还明文写在一个shell脚本里。问运维为什么不单独建账号答曰从开发到上线一直这么干的方便。这种场景过去一年我遇到不止一次——MySQL用户管理和权限设置看起来是每个学数据库的人最早接触的内容可真正到了生产环境账号怎么建、权限给多大、主机怎么限制、密码多久过期、授权为什么有时不生效能一次说清楚的人真不多。这篇文章适合三类人刚接手公司数据库开发任务、需要自建账号的新手被Access denied或远程连接问题折磨过的运维以及想规范权限体系、不想再裸奔跑root的团队负责人。我会按实际管理数据库的顺序来讲从用户创建到权限下放再到高频故障的排查链路最后附上一些我在生产环境里沉淀下来的管理习惯尽量让看完的人回到工位就能直接照着做。1. 用户管理基本功CREATE USER、密码策略与账号生命周期1.1 创建用户别忽略主机限定MySQL里创建一个用户核心语法就一句话CREATE USER app_dev192.168.1.% IDENTIFIED BY StrongPass!2024;很多人刚接触时会困惑用户名后面跟一个主机是什么意思。简单说MySQL的账号不是只有用户名一个维度而是用户名登录来源主机两个维度共同决定的。app_dev192.168.1.%表示名为app_dev的用户只能从192.168.1.x这个网段登录如果写成app_dev%则表示允许从任意主机登录。这个主机限定是权限体系的第一道门也是很多人最后才意识到的重要一环。我在实际项目里见过一个反面案例开发为了图省事统一用sa%创建账号结果任何一台能访问数据库端口的机器只要能猜出密码就能连上库。后来做安全加固时单是梳理这些%账号的来源就花了整整两天。所以从第一天就应该养成习惯能写具体IP就不要写网段能写网段就不要写%。localhost、127.0.0.1、::1这些本机访问地址在有些场景下是分开匹配的如果你希望账号只能本机登录可以同时创建多个同名但不同host的账号我会在第2章详细讲匹配规则。1.2 密码管理修改、过期与初始化密码修改在8.0版本前后语法略有差异统一推荐使用ALTER USERALTER USER app_dev192.168.1.% IDENTIFIED BY NewPass!2024;如果是刚安装完MySQL、第一次用root登进去通常环境变量或者安装日志里会给一个临时密码登录后系统会要求你先改密码再执行其他操作。这时候用得最多的就是上面这条语句。密码这块我想重点说说过期策略。生产环境里密码长期不换是很多公司的常态但一旦有人离职或者共用账号泄露影响面会非常广。MySQL从5.7开始支持密码过期控制可以在创建用户时指定也可以对已有用户设置ALTER USER app_dev192.168.1.% PASSWORD EXPIRE INTERVAL 90 DAY;这样设置后这个账号每90天密码到期到期后应用连接会直接报Your password has expired必须重置密码才能继续使用。初次接触这个机制的人可能会觉得这不给自己找麻烦吗但如果你管过几个账号泄露的项目就会明白周期性强制换密码比事后补救成本低太多了。另外提醒一点如果用的是MySQL 8.0默认认证插件caching_sha2_password老版本客户端比如5.x的JDBC驱动、旧版Navicat连接时容易报认证失败第4章会专门展开。1.3 账号下线DROP USER 与遗留授权清理删除用户同样简单DROP USER app_dev192.168.1.%;但这里埋着一个大家容易忽略的坑如果你当年是通过GRANT语句给一个用户授过权后来直接删了用户有些授权记录并不会自动跟着消失干净。在MySQL 8.0里DROP USER会把该用户在所有权限表里的相关记录一并清掉但在某些老版本或通过直接修改授权表的清理操作中可能留下残留数据。更稳妥的做法是删除前先查看一下这个账号的权限全貌SHOW GRANTS FOR app_dev192.168.1.%;确认无误再删。另外针对账号改名MySQL提供了RENAME USERRENAME USER app_dev192.168.1.% TO app_new192.168.1.%;这个操作会把原账号的权限整体迁移到新账号名下比新建用户重新授权删除旧账号三步走要省事得多也不会漏掉某个库的授权。2. MySQL权限判断机制授权表、权限层级与连接匹配规则2.1 权限层级从全局到单元格MySQL的权限模型是分层级的从大到小依次是全局权限、数据库权限、表权限、列权限、存储过程/函数权限。理解这个层级是后面所有授权操作的基础。全局权限作用于服务器上的所有数据库通常用*.*表示比如GRANT ALL ON *.*。数据库权限作用于某个具体库下的所有对象用db_name.*表示比如app_db.*。表权限作用于某张具体的表用db_name.table_name表示。列权限作用于表的某个或某几个列粒度最细。存储过程/函数权限控制能否执行、查看某个例程。日常业务中90%以上的账号只需要数据库级或表级权限。全局权限一般只给DBA这类管理角色。权限粒度太粗会把风险放大而粒度太细比如精确到列又会让授权语句复杂难维护所以这个度需要根据团队规模和使用场景权衡。我在给业务项目设计权限时默认会遵循一个原则应用账号给库级DML权限分析人员给只读权限DBA才给全局权限。2.2 五张核心授权表的分工MySQL把权限元数据存在mysql库下的授权表里权限判断的本质就是查这些表。很多权限问题排查到最后其实就是在看这几张表的内容授权表对应权限范围典型记录内容mysql.user全局权限能否连接、全局级SELECT/INSERT/UPDATE等、资源限制mysql.db数据库权限某个库下的SELECT/INSERT/UPDATE等mysql.tables_priv表权限某张表的SELECT/INSERT/UPDATE等mysql.columns_priv列权限某列上的SELECT/UPDATE等mysql.procs_priv存储过程/函数权限EXECUTE、ALTER ROUTINE等这里有个容易混淆的点GRANT SELECT ON app_db.* TO uh之后记录写入的是mysql.db而GRANT SELECT ON app_db.orders TO uh之后记录写入的是mysql.tables_priv。所以查看用户权限时不能只看mysql.user一张表五张表里都可能存着与你账号相关的授权。后面第5章给的审计SQL会演示如何整合这几张表的信息。2.3 连接阶段如何匹配 userhostMySQL在收到一个连接请求时会先根据请求里的用户名和来源IP去匹配账号定义。匹配优先级是这样的精确匹配优先u192.168.1.10优先于u192.168.1.%匹配到多个定义时取最具体的那一条匿名用户用户名为空优先级最低如果一条都没匹配上直接拒绝登录。这个规则解释了为什么很多人会遇到明明创建了u%用u这个账号却连不上本机的怪事。因为系统里很可能同时存在一个ulocalhost的定义本机登录时MySQL优先匹配到了这个更具体的host而它可能没有正确授权或者密码和u%不一样。遇到这种迷惑行为第一步就该查mysql.user表看看同一个用户名下到底定义了几个host条目。2.4 权限缓存与FLUSH PRIVILEGES的真面目MySQL在启动时会读取授权表并缓存到内存中。如果使用CREATE USER、GRANT、REVOKE、DROP USER这些标准语句服务器会自动刷新缓存不需要额外操作。那为什么网上到处都在说修改权限后要执行FLUSH PRIVILEGES真相是FLUSH PRIVILEGES只在两种情况下是必要的。你直接通过INSERT/UPDATE/DELETE语句修改了mysql.user等授权表你手动向授权表里插入过数据导致内存缓存与表内容不一致。换句话说正常走标准语句时不需要flush。但如果有人绕过标准语句直接操作了授权表比如用工具改表或者误操作那就必须执行FLUSH PRIVILEGES让它重新读取授权表。我在排查一次授权后仍然Access denied问题时就发现对方是用UPDATE mysql.user改的权限忘了执行flush导致缓存里还是旧状态白查了半天。3. GRANT与REVOKE权限授予、回收和最小权限落地3.1 GRANT的三种授权范围与核心语法授权语句的基本模板是GRANT 权限列表 ON 权限范围 TO 用户主机 [WITH GRANT OPTION];权限范围决定了这条授权语句的粒度-- 全局权限所有库的所有对象 GRANT SELECT ON *.* TO readonly%; -- 数据库权限某个库的所有对象 GRANT SELECT, INSERT, UPDATE, DELETE ON app_db.* TO app_writer192.168.1.%; -- 表权限某张表 GRANT SELECT ON app_db.orders TO analyst%;WITH GRANT OPTION是一个需要特别谨慎的参数它表示允许这个用户把自己拥有的权限再授予别人。比如你给A授了SELECT ON app_db.* WITH GRANT OPTIONA就能把app_db的SELECT权再授给B。很多项目里root密码没泄露但业务库权限被扩散得一塌糊涂往往就是某个带GRANT OPTION的账号被滥用导致的。所以在非DBA账号上我不会轻易加这个选项。3.2 实战权限模板不同角色的标准授权根据多年的项目经验我把最常见的几类账号权限整理成一个可以直接套用的模板角色典型账号权限范围授权语句示例应用账号app_writer所在库的增删改查GRANT SELECT, INSERT, UPDATE, DELETE ON app_db.* TO ...只读分析账号analyst_ro所在库的查询GRANT SELECT ON app_db.* TO ...开发账号dev_opsDML临时表部分管理GRANT SELECT, INSERT, UPDATE, DELETE, CREATE TEMPORARY TABLES ON dev_db.* TO ...主从复制账号repl_user复制相关全局权限GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO ...DBA账号dba_admin全局管理权限GRANT ALL PRIVILEGES ON *.* TO ... WITH GRANT OPTION应用账号只给DML权限而不给DDL如CREATE、ALTER、DROP是为了防止应用被注入或者误操作时把表结构改了。我在一次项目复盘时发现某个应用的账号拥有DROP权限一次SQL注入攻击差点把整个订单表清空从那之后我对DDL权限的收紧标准就变得特别严格。创建主从复制账号是很多做读写分离的人会忽略的一步。直接拿业务账号充当复制账号虽然也能跑通但权限范围过宽一旦主库被入侵从库也会跟着遭殃。正确做法是单独建一个只拥有复制权限的账号例如CREATE USER repl_user192.168.% IDENTIFIED BY ReplPass!2024; GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO repl_user192.168.%;3.3 REVOKE回收与SHOW GRANTS审计撤销权限用REVOKE语法与GRANT对称REVOKE INSERT, UPDATE ON app_db.* FROM app_writer192.168.1.%;需要注意REVOKE只能回收已授予的权限不能回收该账号本身没有被授予的权限。比如你只授过SELECT想通过REVOKE DELETE来撤销删除权限是没意义的语句会报错或者不生效。查看某个账号当前完整的权限清单用SHOW GRANTS FOR app_writer192.168.1.%;这个命令会把该账号在所有层级上的权限一股脑列出来。我在做权限季度审计时就是对每个账号跑一遍SHOW GRANTS然后对照台账核对是否有权限蔓延。这个动作看起来简单但坚持下去能避免大量潜在风险。3.4 MySQL 8.0角色机制带来的简化如果你用的是MySQL 8.0可以考虑用角色Role来管理权限。角色的本质是一组命名的权限集合它让给10个人分别授同样的权限从10条语句变成2条语句。-- 创建角色并授权 CREATE ROLE app_readonly; GRANT SELECT ON app_db.* TO app_readonly; -- 把角色授予用户 GRANT app_readonly TO analyst1%; GRANT app_readonly TO analyst2%;这里有一个非常容易踩的坑角色授予用户后默认是未激活状态。也就是说你刚执行完GRANT app_readonly TO analyst1%立刻让analyst1去查数据仍然会报没权限。必须先激活SET DEFAULT ROLE app_readonly TO analyst1%;或者让用户登录后手动执行SET ROLE app_readonly。生产环境里我一般用SET DEFAULT ROLE否则每次登录都要手动激活角色应用侧很容易漏配。4. 高频权限故障排查Access denied、远程连不上、密码过期的完整链路4.1 授权了还是Access denied先查host匹配再看缓存权限类故障里出现频率最高的就是明明授了权客户端还是报Access denied for user xxxyyy。这类问题的排查链路我总结了固定的三步。第一步确认连接来源到底匹配到了哪个账号。登录服务器执行SELECT user, host, plugin, account_locked, password_expired FROM mysql.user WHERE user 你的用户名;这步要重点看Host字段。比如报错信息里显示ulocalhost而你的账号定义是u%那基本可以断定是host匹配问题——系统有一条更精确的localhost记录拦截了本机连接。解决办法要么把%改成localhost要么直接用localhost登录。第二步确认授权确实存在且没有被REVOKE误伤。执行SHOW GRANTS FOR u具体的host注意host要写与第一步查到的一致不能想当然写%。第三步确认是否动过授权表没刷新缓存。如果你查mysql.user表发现自己用UPDATE改过字段那就执行FLUSH PRIVILEGES;做完这三步大部分Access denied问题都能定位到根因。我经手过的案例里大约一半出在host匹配上三成出在授权没给到位剩余两成才涉及缓存、锁账户、密码过期等杂项。4.2 远程连接失败bind-address、防火墙与用户host的三重校验远程连不上MySQL是热词里搜索量极高的一类问题同时踩三个坑的人不少。第一个坑是MySQL服务本身只监听了本机。检查my.cnf或my.ini里的bind-address如果值是127.0.0.1那MySQL只会监听本机回环地址其他机器当然连不上。需要改成0.0.0.0或内网IP并重启服务。第二个坑是云服务器安全组或本地防火墙没放行3306端口用telnet 服务器IP 3306或nc -vz 服务器IP 3306测试即可确认。第三个坑才是账号层面也就是第2章讲的host匹配ulocalhost无法从远程登录需要创建或改用允许对应来源IP的账号。这三个坑经常叠加出现。我的排查顺序是先在服务器本机用socket或127.0.0.1登录验证MySQL本身是否正常再用telnet验证端口连通性最后才排查账号host和权限。如果你用Navicat这类图形化工具连接报错提示往往比命令行更模糊按照这个链路排查效率最高。4.3 caching_sha2_password 导致的认证失败MySQL 8.0默认的认证插件是caching_sha2_password而很多老客户端默认只支持mysql_native_password。典型报错是Authentication plugin caching_sha2_password cannot be loaded这种问题不一定非得改全局配置可以针对旧客户端单独调整账号的认证插件ALTER USER old_client% IDENTIFIED WITH mysql_native_password BY NewPass!2024;不过说实话到了8.0时代我更推荐的做法是升级客户端驱动到支持caching_sha2_password的版本而不是为了兼容旧客户端去降级认证插件。安全性和向前兼容性都更理想。4.4 密码过期应用半夜报警最常见的原因之一密码过期问题在开发环境很少见但在生产环境的共享账号上极易发生。应用连接数据库时如果密码已过期报错提示通常是ERROR 1862 (HY000): Your password has expired. To log in you must be changed it using ALTER USER这里有个容易误导人的地方有些监控平台显示的报错会包装成Login failed或Connection refused导致排查时方向跑偏。遇到半夜应用大面积连不上库的紧急情况如果最近刚好设置过密码过期策略第一优先检查的应该是密码是否到期。处理方式ALTER USER app_user% IDENTIFIED BY 新密码;同时在账号策略上如果有运维窗口可以用PASSWORD EXPIRE NEVER临时取消过期限制等业务低峰期再重新启用90天策略。4.5 Docker与RPM安装后的权限初始化和常见坑用Docker部署MySQL现在非常普遍但容器环境的权限管理有几个容易踩的坑。如果你是docker run方式启动的可以这样创建初始管理员docker run --name mysql8 \ -e MYSQL_ROOT_PASSWORDRootPass!2024 \ -e MYSQL_DATABASEapp_db \ -e MYSQL_USERapp_user \ -e MYSQL_PASSWORDAppPass!2024 \ -p 3306:3306 -d mysql:8.0容器启动时如果指定了MYSQL_USERMySQL会自动创建该用户并授权它访问MYSQL_DATABASE指定的库。但注意这个自动创建的账号只对该库有全部权限不能创建其他库也不能执行DDL之外的管理操作。RPM包安装的MySQL则常见另一个问题/var/lib/mysql目录权限不对导致无法启动或者socket文件路径不一致导致ERROR 2002 (HY000): Cant connect to local MySQL server through socket /tmp/mysql.sock。这种错误本质上不是权限管理问题但很多人会误以为是账号权限问题去查GRANT结果绕了一大圈。排查时要先确认socket路径是否与配置一致登录命令可以显式指定mysql -u root -p -S /var/run/mysqld/mysqld.sock5. 权限管理常态化账号台账、周期审计与几条实践习惯5.1 给账号建台账从源头杜绝僵尸账号很多团队的数据库账号越建越多却从来没人统计过到底哪些账号还在用。我建议用一张简单的表格维护账号台账字段包括账号名、授权主机、所属项目、负责人、权限范围、创建时间、最近登录时间、过期时间。这张表可以放在团队Wiki里也可以直接用MySQL查出来导出。配合mysql.user表里的last_used信息或者通过SHOW PROCESSLIST定期采集中间态连接能够识别那些长期不登录的僵尸账号。我见过一个极端案例一个离职快两年的同事账号还在生产库上有全部库的DML权限因为没人清理。账号台账配合季度审计是防止这类问题的最后一道防线。5.2 权限审计SQL快速梳理用户与权限全貌下面几条SQL是我在实际审计中反复用的分享出来-- 查看所有用户的host、锁定状态、密码过期情况 SELECT User, Host, account_locked, password_expired FROM mysql.user WHERE User NOT IN (mysql.sys, mysql.session, debian-sys-maint); -- 查看某个库下被授权了哪些账号 SELECT User, Host, Db, Select_priv, Insert_priv, Update_priv, Delete_priv FROM mysql.db WHERE Db app_db;-- 查看所有用户的全局权限粗筛权限过大的账号 SELECT User, Host, Super_priv, Process_priv, Grant_priv, Create_user_priv FROM mysql.user WHERE Super_priv Y OR Grant_priv Y OR Create_user_priv Y;审计的核心不是把所有权限清零而是把那些不应该拥有高权限却拥有高权限的账号找出来。Super_priv、Grant_priv、Create_user_priv这三个字段是权限爆炸的源头每次审计算上它们基本不会漏。5.3 我沉淀下来的几条实践习惯最后分享几个我自己的习惯谈不上标准答案但在多套生产环境里都验证过靠谱。第一所有应用账号一律用应用服务器网段的host限定不用%。网段内部机器出问题的概率远低于公网暴露面。第二每个项目至少拆三个账号读写账号给应用只读账号给分析和BIDBA账号留给专职运维。宁可多建几个账号也不要让所有人共用一套凭据否则出了问题连溯源都做不了。第三密码策略统一设置90天过期敏感库比如支付相关压缩到30天。每次密码过期会带来短暂的运维阵痛但换来的是长期可控的安全状态。第四不要迷信密码复杂度。validate_password组件能强制密码强度但更关键的是密码不要硬编码在代码仓库和脚本里。我见过不少团队密码复杂度设置得极高却把密码明文提交到了Git仓库这种本末倒置的做法等于给攻击者递钥匙。权限管理这件事技术本身并不难难的是形成习惯。用户能创建、权限能授予、故障能排查这三步走顺之后MySQL的权限体系就不会再是大概懂用起来慌的状态了。哪怕你现在只管理一台本地开发库从现在开始把账号按规范建起来把权限按最小化给出去后面再迁移到云上或容器环境都会省很多力气。
