1. MySQL用户体系先搞清楚user表到底存了什么1.1 用户不是数据库名也不是登录名很多人刚开始接触MySQL用户管理时容易把“用户”和“数据库”混为一谈。比如说公司新来一个数据分析师需要给他开一个只读账号看线上报表不少人会直接执行一句GRANT SELECT ON report_db.* TO analyst%;然后发现居然能登录——但怎么也记不清这句语句到底创建了什么。这里面的核心区分是MySQL里的“用户”是一个连接身份不是数据库本身也不是操作系统的账号。它由三部分组成用户名、来源主机、密码以及认证插件等。而数据库是存储数据的逻辑容器两者是通过授权关系绑定在一起的。换句话说用户是对“谁能以什么身份从哪台机器连上来”的描述权限是对“连上来之后能碰哪些对象”的描述。我见过不少运维事故本质都是把用户当成了MySQL实例层面的全局对象却忽略了“用户”和“权限”是两个可以独立管理的维度。MySQL 8.0之前GRANT语句可以顺手把用户建出来MySQL 8.0之后官方明确建议先用CREATE USER创建账号再用GRANT授权两件事分开做。这样看起来啰嗦但好处是责任边界清楚建账号是建账号授权限是授权限后续排查问题时能少绕很多弯。1.2 连接身份的三要素用户名、主机、密码每次成功连接MySQL服务器都会在mysql.user表里匹配一条记录匹配条件就是“用户名 来源主机 密码校验”。其中最容易忽略的是“来源主机”。一位工程师曾经为了图省事把所有账号都建成了app%只允许从办公网连数据库的账号也配成了%结果某天数据库出现大量慢查询排查了半天才发现是有业务测试环境的机器也在用同一个账号连接——主机限制形同虚设。%不是不能拿来做主机匹配但要非常清楚地知道它意味着什么任何IP都可以尝试用这个账号登录。安全一点的做法是精确到具体IP或者网段比如app192.168.10.%这样即使密码泄露攻击者也要先进入指定网段才能利用。密码也分版本。MySQL 5.7及之前用的是mysql_native_password这种比较老的认证插件8.0默认换成了caching_sha2_password。很多老客户端驱动不支持新插件就会出现“账号密码都对但连不上”的诡异现象。实际运维中如果遇到旧驱动兼容问题可以临时为某个账号指定老插件但一定要做技术债登记后续推动客户端升级而不是让所有账号都迁回旧插件。1.3 一张user表看懂全局常用字段拆解mysql.user表是MySQL用户信息的核心存储但直接用SELECT * FROM mysql.user\G;会输出几十个字段新手容易看花眼。我一般只看这么几个关键字段字段名含义常见值User用户名root / app / readonly 等Host允许登录的来源主机localhost / % / 192.168.1.%plugin认证插件caching_sha2_password / mysql_native_passwordauthentication_string密码的哈希值不是明文一长串随机字符account_locked账号是否锁定N / Ypassword_expired密码是否已过期N / Ypassword_last_changed密码最近修改时间时间戳这些字段中Host和User合起来做主键所以同一个用户名可以对应多条记录applocalhost和app192.168.10.%是不同的记录可以配置不同的密码和权限。我排查“为什么新加的账号怎么都登不上去”时第一件事永远是先查mysql.user表确认用户记录是否真的存在。有时候CREATE USER执行成功但实际当前连接的节点是只读节点数据并没有同步到写节点客户端自然连不上。这类问题用方向看很常见但一步步查下来也就十分钟。2. 创建用户与改密的正确姿势2.1 先CREATE USER再GRANT顺序决定了排查成本拿MySQL 8.0举例最标准的创建用户流程是这个样子CREATE USER readonly_user192.168.10.% IDENTIFIED BY Str0ng!Passwd; GRANT SELECT ON report_db.* TO readonly_user192.168.10.%; FLUSH PRIVILEGES;有些老教程会告诉你GRANT可以顺带建用户比如GRANT SELECT ON report_db.* TO old_style% IDENTIFIED BY Passw0rd;这条语句在5.7里确实可以运行但到了8.0它要么直接报错要么给出警告因为官方已经把这个逻辑去掉了。为什么去掉因为“顺带建用户”会让权限回收和账号清理的边界变得模糊——你想回收权限结果发现账号还在你想删账号又担心还有权限没清干净。分开执行后每一个动作都指向唯一目标审计和排错都清晰得多。干活时我还习惯在CREATE USER后面加上ACCOUNT LOCK和PASSWORD EXPIRE先把账号建出来锁住等密码通过安全渠道交给使用方再解锁CREATE USER temp_import127.0.0.1 IDENTIFIED BY Init123456 ACCOUNT LOCK PASSWORD EXPIRE;这样能避免“账号建好了还没交接就被别人抢先连上来”的尴尬。默认情况下PASSWORD EXPIRE会要求使用方在首次登录后立即修改密码适合临时交接场景。2.2 主机限定为什么我从不随便给“%”%看起来很省事但它带来两个问题安全边界消失以及DNS反查可能拖慢连接速度。MySQL在收到连接请求时如果主机配置不是IP而是主机名会做反向解析。虽然%不需要反查但app%.example.com这类配置会引入解析开销而且一旦DNS出问题连接就会超时。我的习惯是按网络区域拆分app_server192.168.10.0/255.255.255.0后端服务专用账号只允许内网网段连接dba127.0.0.1和dba192.168.1.%管理账号只允许跳板机网段连readonly_report10.0.0.%报表系统只读账号限定报表服务器所在网段。注意MySQL的主机匹配支持IP掩码写法但实际运维中我更推荐直接写192.168.10.%这种形式简单明了网段比较大的时候才考虑掩码。这里还有个细节rootlocalhost和root127.0.0.1虽然是同一个用户名但在MySQL看来是两条记录。有些人只改了rootlocalhost的密码然后拿127.0.0.1去连发现鬼打墙——旧密码还能用。查一下user表才发现是两个条目这种情况在新手期非常普遍。2.3 改密码不是只有SET PASSWORD一条路MySQL 8.0下改密码的常用方式有这几种-- 方式一建议使用 ALTER USER app192.168.10.% IDENTIFIED BY New#Pass123; -- 方式二自己先算好哈希再设置 ALTER USER app192.168.10.% IDENTIFIED WITH caching_sha2_password BY New#Pass123;SET PASSWORD FOR xxxxxx 明文密码这种写法在8.0之前的版本比较常见但新版更推荐ALTER USER。原因不只是语法统一更重要的是ALTER USER还能顺带处理密码过期、账号锁定、认证插件切换等状态一个语句搞定多个属性。改完密码之后有一个很多人不知道的细节已经建立的连接不会断。MySQL不会因为密码修改就把在线会话踢掉只有下一次新连接才会用新密码校验。所以线上“改密即断连”的预期本身就不成立实际需要阻断现有会话时要配合KILL CONNECTION或者KILL QUERY来做。2.4 实测改密之后旧连接为什么还活着我有一次给生产库的某个业务账号轮换密码凌晨两点改完群里马上有人反馈“旧的定时任务还在跑”我当时也愣了一下。后来查了官方文档和相关资料才确认MySQL的密码认证发生在连接初始化阶段已认证通过的会话在生命周期内不会再次校验密码。处理办法很简单先把这些会话找出来SELECT id, user, host, db, command, time, state FROM information_schema.processlist WHERE user app192.168.10.%;确认哪些会话是历史遗留然后逐条KILLKILL 12345;这才是“改密立即生效”的完整闭环。否则你辛辛苦苦改了密码老的连接还挂着定时任务也还在用旧权限跑安全目标根本没达到。这也是很多公司做“密码轮换”时只改密码、不杀会话最后审计发现还有一堆残留连接的原因。3. MySQL权限体系全拆解五层模型的那点事3.1 权限层级全局、库、表、列、例程MySQL的权限不是一张扁平的清单而是分层的。理解分层是避免“为什么我给用户授了库权限但存储过程还执行不了”这种问题的前提。简单画一个层次关系全局权限作用在整个MySQL实例上存在mysql.user表里比如SELECT、PROCESS、SUPER库权限作用在某个数据库上存在mysql.db表里比如SELECT ON db1.*表权限作用在具体表上存在mysql.tables_priv表里比如SELECT ON db1.t1列权限作用在表的某些列上存在mysql.columns_priv表里比如只授SELECT (col1)例程权限作用在存储过程和函数上存在mysql.procs_priv表里。MySQL在判断用户能否执行某操作时是按照“全局→库→表→列”的优先级逐级放宽还是收紧呢其实取决于权限类型全局权限是超集一旦全局有SELECT库级、表级怎么限制都没用反过来全局没有就看库权限库没有看表权限表没有看列权限。这就是为什么你给一个用户设了“不能查某张表”的限制但如果这个用户在全局或者库级别有SELECT限制根本不生效——权限判断逻辑默认是“有就放行”而不是“细粒度优先”。3.2 权限粒度怎么选不是越细越好权限粒度越细控制的灵活性越高但维护成本也呈指数上升。一个库可能有几十张表如果每张表都单独授权那么权限列表会非常长新的开发同事离职、项目下线时清理工作会变成噩梦。我的经验法则普通业务账号给到库级权限比如SELECT, INSERT, UPDATE, DELETE ON app_db.*跨库访问很少的账号也尽量库级不要动不动就给全局只有那种“只需要查两张核心表”的报表账号才给它表级权限列级权限能不用就不用一方面性能上有额外开销另一方面权限矩阵复杂到后面没人能看懂。例如下面这个场景只让报表账号查order_db里的order_main和order_item两张表不给整个库权限。CREATE USER report_job10.0.0.% IDENTIFIED BY R3port123; GRANT SELECT ON order_db.order_main TO report_job10.0.0.%; GRANT SELECT ON order_db.order_item TO report_job10.0.0.%;这样做的好处是将来如果order_db里加了一张敏感表报表账号天然没有权限不会因为库级授权“顺手”就看到了。3.3 GRANT授权的一个隐蔽坑WITH GRANT OPTIONWITH GRANT OPTION是个非常容易被忽视的权限它的意思是“允许该用户把他拥有的权限再授予其他用户”。举例GRANT SELECT ON report_db.* TO owner192.168.10.% WITH GRANT OPTION;等于是把report_db.*的SELECT权力放给了owner而且owner还能把SELECT权限授给别人。这在一个多人协作的数据库环境中很容易失控A授权给BB授权给C最后出现了一堆你不知道从哪冒出来的账号审计一查全是“谁的权限来自谁都理不清”。我在实际管理中很少给普通账号WITH GRANT OPTION只有DBA账号才需要。查询权限的授予来源可以用SELECT * FROM mysql.tables_priv WHERE Grantor IS NOT NULL;Grantor字段记录了这个权限是哪个账号授出去的能帮你理清授权链。3.4 FLUSH PRIVILEGES到底什么时候用网上有大量的教程说“改完权限一定要执行FLUSH PRIVILEGES”这个说法其实把因果关系搞反了。FLUSH PRIVILEGES的作用是让MySQL重新读取权限表仅此而已。用GRANT、REVOKE、CREATE USER、ALTER USER这些语句修改权限时MySQL会立刻更新内存中的权限缓存不需要执行FLUSH用INSERT、UPDATE、DELETE直接修改系统表比如手动往mysql.user里插记录MySQL不会感知变化才需要FLUSH。我见过某运维同学因为懒得写规范的授权SQL直接UPDATE mysql.user SET ...然后不FLUSH结果用户怎么都连不上折腾了半小时才发现系统表改了但没生效。正确姿势是允许用INSERT/UPDATE修表的人只有你一个并且每次修改后必须FLUSH。但更好的习惯是打消手动改表的念头——所有变更都用SQL标准语句来做系统表只读。4. 角色管理8.0多账号维护的利器4.1 为什么角色比你一个个授权强MySQL从8.0开始正式引入了角色Role概念。角色不是账号它是一种可以被授予权限的集合体然后再把这个集合体赋给多个用户。举个真实场景某公司有三个后端服务每个服务都需要访问三个库分别是order_db读写、user_db只读、log_db只读。如果不用角色你得给每个服务账号分别授三套权限GRANT SELECT, INSERT, UPDATE, DELETE ON order_db.* TO svc_order%; GRANT SELECT, INSERT, UPDATE, DELETE ON order_db.* TO svc_pay%; GRANT SELECT, INSERT, UPDATE, DELETE ON order_db.* TO svc_refund%;三个账号还勉强能接受如果账号数量到二十个权限变更就是一场灾难某天需要给order_db增加一个EXECUTE权限你得改二十遍漏一个就出问题。有了角色做法完全不一样CREATE ROLE r_order_rw, r_user_ro, r_log_ro; GRANT SELECT, INSERT, UPDATE, DELETE ON order_db.* TO r_order_rw; GRANT SELECT ON user_db.* TO r_user_ro; GRANT SELECT ON log_db.* TO r_log_ro; GRANT r_order_rw TO svc_order%; GRANT r_order_rw, r_user_ro, r_log_ro TO svc_pay%;之后不管账号怎么缩放权限变更只需要改角色的授权所有继承该角色的账号自动同步。效率和安全边界都清晰很多。4.2 角色默认不生效activate它角色授权给用户之后有一个非常容易踩的坑角色默认是“非激活”状态。也就是说你把角色赋给账号然后去用这个账号连接发现权限并没有加上需要执行SET DEFAULT ROLE r_order_rw TO svc_pay%;或者让用户自己连接后执行SET ROLE r_order_rw;为什么这么设计官方给出的考量是一个用户可以拥有多个角色但每个角色代表不同的权限边界默认全开可能造成权限扩散。比如同一个DBA账号平时使用只读角色只在需要变更时临时切到管理角色这样能降低误操作风险。实际工作中我的习惯是给账号一次性把所有需要的角色都设为默认角色SET DEFAULT ROLE ALL TO svc_pay%;这样简化了“为什么角色给了却看不到权限”的困惑。如果你追求更严格的控制也可以只默认激活部分角色需要时再动态切换但要做好团队培训和文档说明否则大家会以为是系统Bug。4.3 角色与直接授权的叠加规则一个用户可能既被直接授予了库权限又拥有一个带有库权限的角色。MySQL的权限合并原则是“取并集”。举个例子用户svc_pay直接有SELECT ON order_db.*的权限用户又被赋予角色r_order_rw这个角色含SELECT, INSERT, UPDATE, DELETE ON order_db.*最终svc_pay在order_db上的权限是SELECT、INSERT、UPDATE、DELETE的并集。如果你试图“用角色来限制已授权用户的权限”对不起做不到。MySQL不是Linux的文件权限不存在“角色覆盖直接授权”的机制。权限只有叠加没有互相屏蔽。这一点直接影响权限规划你不能因为某个角色里不含DELETE就期待给用户直接授予了DELETE之后还能被角色拦住。要限制一个用户正确做法是把直接授权回收只留角色授权。REVOKE DELETE ON order_db.* FROM svc_pay%;然后角色里的DELETE如果也想去掉只能从角色本身回收REVOKE DELETE ON order_db.* FROM r_order_rw;权限管理里最怕的就是“我以为角色的权限会限制用户的直接权限”这个误解会导致越权访问而且极难追踪。5. 用户操作风险控制权限回收、账号下线与只读防护5.1 收权限REVOKE的坑和替代方案回收权限的标准语法是REVOKE INSERT, UPDATE, DELETE ON order_db.* FROM svc_refund%;这看起来很简单但实际中容易忽略一个问题REVOKE只能回收GRANT授出去的权限。如果权限是通过角色授出的直接对用户执行REVOKE并不会生效你需要先回收角色或者从角色里回收权限-- 从用户回收角色 REVOKE r_order_rw FROM svc_refund%; -- 或者从角色回收权限 REVOKE DELETE ON order_db.* FROM r_order_rw;在执行回收之前建议先看看这个用户实际的有效权限是什么SHOW GRANTS FOR svc_refund%;它会列出直接授权和角色授权是判断回收范围的第一手依据。还有一种常见的替代方案把user表里面的权限字段直接置为N。但我不推荐除非你很清楚自己在做什么——这种操作绕过了权限缓存不FLUSH不生效而且还容易误改其他字段百害而无一利。5.2 删除用户前先查会话再动手删除用户比创建用户危险得多。一个人说删就删可能下一秒线上服务就报Access denied。我自己的标准流程是三步走先查这个用户的会话是否还在运行SELECT id, user, host, db, command, time FROM information_schema.processlist WHERE user LIKE svc_refund%;确认会话后通知相关方停掉任务等会话自然结束或者手动KILL。执行删除DROP USER svc_refund%;别看这三步简单真出故障的场景往往是跳过了第二步。账号已经DROP了但老连接因为不断开任务还在跑业务方根本感知不到直到连接被网络层切断才炸出问题。所以删除用户前务必把“在线会话”当成第一排查项。如果是下线一个即将废弃的应用更稳妥的做法是先把账号锁定ALTER USER svc_refund% ACCOUNT LOCK;观察一段时间确认没有业务依赖再DROP。锁定比删除温和得多随时可以解锁恢复生产环境建议优先用这套。5.3 只读账号是线上环境的必备品很多人觉得“只读账号”只给报表和数据分析用。其实不然我给自己的建议是任何能连上生产库的人工操作账号都应该有对应的只读版本。比如DBA在排查慢查询时需要看看线上数据但完全不需要写权限。如果DBA日常连接用的是有写权限的账号一次误操作就可能造成数据污染。所以可以这样做CREATE USER dba_ro192.168.1.% IDENTIFIED BY Ro2024Pass; GRANT SELECT, SHOW VIEW, PROCESS ON *.* TO dba_ro192.168.1.%;这里给PROCESS权限是允许查看所有会话SHOW VIEW是允许查看视图定义这两个都是排查问题必需的但不是写权限。线上只读账号的权限范围通常还要再加上临时表权限CREATE TEMPORARY TABLES因为有些报表查询需要建临时表但很多同学没给结果查询一跑就报错。5.4 一个生产环境的权限白名单示例分享一套我自己整理的账号规划适合中小规模业务库账号类型命名规范权限范围适用场景业务读写svc_应用名_rw指定库的 SELECT/INSERT/UPDATE/DELETE后端服务连接业务只读svc_应用名_ro指定库的 SELECT报表、数据核对DBA管理dba_admin全局 SELECT/PROCESS/SUPER/RELOAD 等DBA日常运维DBA只读dba_ro全局 SELECT/SHOW VIEW/PROCESS排查慢查询、巡检监控采集monitor_ro全局 SELECT 及 PROCESS监控系统抓取状态变量这套规范的核心思想是每个账号都有清晰的边界命名能看出用途权限能看出场景。等账号数量多了以后这种规范带来的维护便利远远超过分类时花的那点时间。6. 一次真实权限事故的排查全记录6.1 事故表现服务突然连不上MySQL某天下午值班同事收到告警某内部系统的后端服务连续报错日志里出现Access denied for user svc_shop192.168.20.55 (using password: YES)。奇怪的是这个账号前一天还能正常使用中间没有做任何密码变更。按一般思路先怀疑密码问题重置密码后重启服务发现依然报错。又怀疑账号被锁执行了ALTER USER ... ACCOUNT UNLOCK还是无效。直到有人想起来了前一天刚执行过一个角色清理任务把某个不再使用的旧角色批量回收了。6.2 排查链路从报错信息到权限矩阵我先查了mysql.user表SELECT User, Host, account_locked, password_expired FROM mysql.user WHERE User svc_shop;记录确实存在没有锁定密码也没有过期。再执行SHOW GRANTS FOR svc_shop192.168.20.%;输出显示这个账号的权限来自两个角色r_shop_rw和r_shop_ro。而r_shop_ro正是昨天被回收的角色。问题来了r_shop_ro被回收后svc_shop的SELECT权限是否还在按理说r_shop_rw可能包含SELECT但我在梳理角色定义时发现r_shop_rw只授权了INSERT, UPDATE, DELETE把SELECT放在了r_shop_ro里。这是典型的拆分错误同一个业务账号依赖两个角色一个负责写、一个负责读回收其中一个时没有同步检查依赖。于是故障链路清晰了昨天清理角色的同事只看了角色名以为r_shop_ro没人用实际上多个账号都在依赖它——定期从库里读取配置读到一半权限悬空了。6.3 根因复盘角色拆分与回收的教训问题修复本身很简单重新把r_shop_ro角色授权回来即可。但复盘时总结了几条值得写下来的经验回收或删除任何角色之前先查哪些用户拥有该角色SELECT * FROM mysql.role_edges WHERE TO_USER svc_shop;或者全量查一遍SELECT FROM_USER, TO_USER FROM mysql.role_edges;权限拆分时不要把“业务必须的基础权限”放在一个单一角色里。每个业务账号的基础读权限应该单独确认不能因为“应该有另一个角色兜底”就觉得没问题。权限回收和删除操作要像“下线服务”一样走变更流程先通知、再执行、后验证。验证的方式就是用一个该账号的典型查询跑一遍mysql -u svc_shop -p -h 127.0.0.1 -e SELECT COUNT(*) FROM shop_db.t_config;一句话总结**权限管理里的每一个REVOKE和DROP都值得你多花一分钟确认它到底会影响谁。**很多线上问题不是语法写错而是权限依赖关系没看清楚。能把这一步做踏实日常用户管理的故障率能降一半以上。