1. 账户体系与权限模型先把地基打牢很多人刚开始接触 MySQL 的时候觉得“账户和权限”不就是CREATE USER加个GRANT嘛能有多复杂等真正接手一套生产环境面对几百个历史遗留账号、一堆权限不明不白的授权记录时才知道这东西要是当初没理清楚后面全是坑。我在实际运维中见过不少事故比如开发要了个只读账号结果给了ALL PRIVILEGES某天误执行DELETE把整张业务表清空了再比如 DBA 离职后账号没回收过了半年被人利用漏洞拖了库。说句不好听的MySQL 权限体系用不好数据库就是裸奔。1.1 MySQL 的权限到底存在哪里先明确一件事MySQL 的账户信息和权限信息全部存在系统数据库mysql里。核心就几张表mysql.user账户基本信息包括用户名、允许登录的主机Host、密码散列值、全局权限Global privileges以及账户状态是否锁定。mysql.db库级权限记录了某个用户对某个数据库的权限。mysql.tables_priv表级权限记录用户对某张具体表的权限。mysql.columns_priv列级权限精细到某一列。mysql.procs_priv存储过程、函数的执行权限。MySQL 的权限校验是分层的从user表开始往下走。你给用户授权时如果授权范围是*.*那就是全局权限直接写进mysql.user表如果是testdb.*写进mysql.db表如果是testdb.tb1写进mysql.tables_priv表。层级越往下权限粒度越细。理解这张权限分层表对你后面排查问题特别有帮助。比如某个用户明明有数据库权限却报SELECT command denied如果你知道权限校验顺序是“全局 → 库 → 表 → 列”就能猜到大概率是表级别上有更细的限制或者全局权限里SELECT被REVOKE掉了。权限校验有个特点只要在某一层被拒绝就不会继续往下尝试更细的授权。换句话说MySQL 判断你有没有权限是基于“最严格匹配”的。这里补充一个关键点MySQL 8.0 里mysql.user表结构已经发生很大变化密码字段从Password改成了authentication_string不再存储Password列。所以网上很多老教程让你直接UPDATE mysql.user SET PasswordPASSWORD(xxx)改密码在 8.0 里完全行不通这条路已经堵死了。1.2 客户端连接 MySQL 时会经历什么我经常用“进小区”来类比 MySQL 的认证过程。客户端连接 MySQL 时服务端要做两件事第一件验证身份。MySQL 根据你提供的用户名和来源 IPHost去mysql.user表里找有没有匹配的记录。注意这里有个容易踩坑的地方用户名相同、Host 不同的账户被视为不同账户。比如zhangsan%和zhangsanlocalhost是两个完全独立的账户。连接时MySQL 会按照 Host 的精确匹配度做排序localhost优先于%具体规则是空 Host 表示所有主机%也表示所有主机但如果同时存在更精确的匹配精确的会先用。我用一个实例说明假设 MySQL 里有两个账户applocalhost密码是123456app%密码是abcdef。你在数据库服务器本机上执行mysql -uapp -p123456能连上但是用mysql -h 192.168.1.10 -uapp -p123456去连本机 IP 时可能连不上。为什么因为匹配到了applocalhostMySQL 认为你请求的主机是走 socket 或回环地址但-h指定了 IP匹配逻辑会优先挑更精确的那条记录有些场景下连接来源被识别成“非 localhost”于是走上app%的密码校验自然就失败了。这个细节特别坑很多新手搞了一晚上都没弄明白。第二件确定权限范围。身份验证通过后MySQL 把该用户所有的权限记录读入内存后续每次执行 SQL 都先查权限缓存。这意味着一个问题你改了权限已经连接上的会话不会立即生效。需要新连接才能拿到最新权限或者执行FLUSH PRIVILEGES但其实对已连接的会话也不强制提示MySQL 在每次执行语句前会重新读取权限缓存因为权限变更之后 MySQL 会自动让缓存失效只是个别老版本行为不同。2. 零基础创建用户8.0 版本后语法有变化账户创建这块如果你以前用的 MySQL 5.7 或更早版本切换到 8.0 后会有不少“惊喜”。最大的一个变化是8.0 里GRANT语句不再隐式创建用户了。5.7 时代你可以直接GRANT ALL ON testdb.* TO app% IDENTIFIED BY password一条语句既建号又授权8.0 里这么写直接报错必须先用CREATE USER建号再用GRANT授权。这个变化是好事把“建号”和“授权”两个动作彻底解耦权限管理更清晰但确实让很多人一开始不适应。2.1 CREATE USER 标准语法逐个拆解先看一个我实际项目里经常用的标准建号语句CREATE USER app_user% IDENTIFIED BY App2024#Secure PASSWORD EXPIRE INTERVAL 90 DAY ACCOUNT UNLOCK;逐个解释每个部分app_user%用户名是app_user%代表允许从任意主机连接。生产环境我通常不建议权限大的账号用%除非确实需要。最好写得具体一点比如app_user10.0.0.%只允许内网网段访问。IDENTIFIED BY App2024#Secure设置密码。8.0 默认使用caching_sha2_password认证插件这个插件安全性高但有一点要注意如果客户端是老版本的libmysqlclient比如 PHP 7.1 之前、Python 的mysqlclient老版本可能连接时报Authentication plugin caching_sha2_password cannot be loaded的错误。这时你需要在创建用户时指定老认证插件IDENTIFIED WITH mysql_native_password BY 你的密码或者升级客户端驱动。PASSWORD EXPIRE INTERVAL 90 DAY设置密码 90 天过期。这个策略对线上环境很重要能强制开发定期换密码降低泄露风险。如果你不想设密码过期策略就写PASSWORD EXPIRE NEVER。ACCOUNT UNLOCK账户默认不锁定。如果账户因多次登录失败被锁可以用ACCOUNT LOCK主动锁住方便审计和冻结权限。我遇到过不少人在 8.0 上把密码写在命令行里结果 shell 历史记录全留下来了。这个习惯非常不好。正确做法是使用交互式输入或者用 MySQL 的mysql_config_editor保存登录凭据。但如果你必须写在 SQL 文件里记得文件权限设成 600。2.2 主机限制Host是权限管理的第一道闸门很多人创建用户时习惯直接user%表示“所有主机都能连”。这里我强烈建议能用具体 IP 就用具体 IP能用网段就用网段。为什么因为 MySQL 的 Host 限制是纯文本匹配%能匹配任何来源包括公网。一旦这个账号密码泄露外部攻击者可以直接从公网连你的数据库。我见过一次真实案例开发图省事给所有账号都配了%然后数据库暴露在公网 3306 端口结果被扫描器爆破成功整个库被删了要比特币。虽然现在云厂商有安全组可以限制来源 IP但数据库层面的主机限制永远不应该省。常用的 Host 写法Host写法含义使用建议%允许所有主机尽量避免除非是临时测试环境192.168.1.%允许 192.168.1.0/24 网段内网应用推荐10.0.0.5只允许指定 IP单点应用、管理账号推荐localhost只允许本机连接本地工具、运维脚本::1允许 IPv6 本机地址如用到 IPv6 时使用如果 Host 配置写错了用户连接时会报Access denied for user xxxxxx。这类报错很多真是 Host 不匹配导致而不是密码错误。排查时先看mysql.user表里有哪些 Host 变体然后用SELECT CURRENT_USER();查看当前会话匹配到的到底是哪个账户。2.3 密码策略别再用 123456 了MySQL 8.0 默认启用了密码验证组件validate_password它会对新密码做强度检查。默认策略要求密码至少 8 位且包含大写、小写、数字和特殊字符。很多开发第一次建号时直接IDENTIFIED BY 123456然后报错ERROR 1819 (HY000): Your password does not satisfy the current policy requirements。这时候别急着把策略关掉而是应该配合一个强密码。实在要临时建测试账号可以用下面的方式临时降低策略要求但用完记得改回来SET GLOBAL validate_password.policy LOW; SET GLOBAL validate_password.length 6;注意MySQL 8.0 里参数名带了前缀validate_password.5.7 则是validate_password_policy别搞混。生产环境不建议长期保持低策略密码长度至少 12 位起步配合密码过期策略和账户锁定策略基本能挡住大多数爆破风险。3. 授权 / 撤销权限实操账号建好了接下来是重头戏授权。MySQL 的权限类型非常多从全局管理权限到表级别的 DML 操作加起来几十种。下面我按实际使用频率分个类帮你建立一个整体认知然后再用真实案例演示 授权和撤销授权的完整操作。3.1 权限类型全家桶哪些权限必须给哪些碰都不要碰先看一个常用的权限速查表权限类型权限名作用范围说明数据操作SELECT表/列查询数据数据操作INSERT表/列插入数据数据操作UPDATE表/列更新数据数据操作DELETE表/列删除数据DDLCREATE库/表建库建表DDLALTER表修改表结构DDLDROP库/表删库删表DDLINDEX表创建/删除索引DDLCREATE VIEW视图建视图管理GRANT OPTION全局允许用户给别人授权危险管理SUPER全局超级权限管理操作管理PROCESS全局查看所有线程管理RELOAD全局执行 FLUSH 操作管理SHUTDOWN全局关闭 MySQL复制REPLICATION SLAVE全局复制从库用复制REPLICATION CLIENT全局查看复制状态重点提醒几个容易忽略的权限GRANT OPTION这个权限有点类似“授权代理”它允许用户把自己拥有的权限再授予其他用户。如果你给开发人员只授了SELECT但又不小心勾选了GRANT OPTION理论上他可以把SELECT转授给别人。虽然影响看起来不大但配合其他权限就可能造成提权生产环境必须谨慎。SUPER这个权限可执行很多高级管理操作比如SET GLOBAL修改系统参数、KILL其他会话、切换 binlog 等。给了SUPER基本等于半个管理员普通业务账号千万不要给。FILE允许用户通过LOAD DATA INFILE读写服务器上的文件。这个权限一旦被滥用攻击者可以直接读取 MySQL 运行用户可读的文件比如/etc/passwd、WEB 配置文件是拖库和数据泄露的高危通道。除非有明确需求比如数据导入导出否则一律不要授权。3.2 GRANT 授权语句的三种粒度授权语句的核心语法是GRANT 权限列表 ON 权限级别 TO 用户名主机 [WITH GRANT OPTION];权限级别决定了这一条授权的作用范围我们平时用到的就三种第一种全局级别*.*GRANT ALL PRIVILEGES ON *.* TO adminlocalhost WITH GRANT OPTION;这个授权范围是整个 MySQL 实例。WITH GRANT OPTION表示admin这个用户可以把权限授予别人。这种全局授权一般只给管理员数据看板、业务后台这类应用账号根本用不上。第二种库级别dbname.*GRANT SELECT, INSERT, UPDATE, DELETE ON shopdb.* TO shop_app192.168.10.%;这是生产环境中用得最多的一种授权把某个数据库的 DML 权限交给应用账号限定网段访问既满足业务需要又把爆炸半径控制在单个库内。第三种表级别dbname.tablenameGRANT SELECT ON shopdb.orders TO analyst%; GRANT SELECT (user_name, user_email), UPDATE (user_email) ON shopdb.users TO support%;表级别授权可以精细到列。第二条语句的意思是support这个账号只能查users表的user_name和user_email两列只能更新user_email一列。这种列级授权对敏感字段手机号、身份证、邮箱的保护非常实用但需要注意列级授权越多权限校验开销越大性能会有一点点损耗别把每个表都搞成列级。3.3 实战一个电商应用的完整授权方案现在我们来模拟一个真实的电商项目说说我是怎么配权限的。假设有三类角色app_rw业务应用主账号需要对shopdb库做增删改查。analyst_ro数据分析师只需只读能力还要限制住只能看shopdb里已归档订单。dba_adminDBA 管理员全局管理掌握授权大权。创建账号-- 应用主账号只允许从应用服务器网段连接 CREATE USER app_rw192.168.10.% IDENTIFIED BY Rw2024#Strong; -- 分析账号允许从办公网连接但限制网段 CREATE USER analyst_ro192.168.20.% IDENTIFIED BY Ro2024#Read; -- 管理员账号只允许从跳板机连接 CREATE USER dba_admin10.0.0.8 IDENTIFIED BY Dba2024#Admin;授权-- 应用账号获得 shopdb 全部业务表含未来的新表的增删改查权限 GRANT SELECT, INSERT, UPDATE, DELETE ON shopdb.* TO app_rw192.168.10.%; -- 分析账号仅查询权限并且只允许查一张归档表 GRANT SELECT ON shopdb.orders_archive TO analyst_ro192.168.20.%; -- 管理员授予全局权限带授权选项 GRANT ALL PRIVILEGES ON *.* TO dba_admin10.0.0.8 WITH GRANT OPTION; -- 让授权立即生效新连接自动生效旧线程一般也会在下次执行时感知到但执行一次是习惯 FLUSH PRIVILEGES;几个细节说明app_rw账号我没给CREATE和ALTER意味着它不能改表结构。这是有意为之——应用代码不该具备 DDL 权限防止上线脚本误执行DROP TABLE。如果要执行结构变更走 DBA 审核后由管理员操作。analyst_ro只给了orders_archive表权限其他表一概不能查。这样即使分析师账号泄露攻击者也拿不到核心业务数据。如果分析师需要多张表就用逗号分隔逐个授权GRANT SELECT ON shopdb.orders_2024 TO ...; GRANT SELECT ON shopdb.orders_2025 TO ...;。dba_admin的 Host 限定为跳板机 IP10.0.0.8。DBA 平时登录必须先连跳板机再连数据库所有操作都有审计记录。这个设置我强烈建议上线时配上别图省事给管理员也来个%。3.4 REVOKE 撤销权限别做“只授不收”的烂好人撤销权限用REVOKE语法和GRANT对称REVOKE 权限列表 ON 权限级别 FROM 用户名主机;继续用上面的电商案例假设项目迭代后分析账号analyst_ro不再需要查询orders_archive表了那就撤销REVOKE SELECT ON shopdb.orders_archive FROM analyst_ro192.168.20.%;如果只收回了部分权限比如之前给了SELECT, INSERT, UPDATE, DELETE现在想让它变成只读-- 先回收写权限 REVOKE INSERT, UPDATE, DELETE ON shopdb.* FROM app_rw192.168.10.%;注意一个细节REVOKE收回权限时如果用户当前有活动会话已经在执行的语句不会被打断但下一条语句执行时会重新校验权限然后报command denied。所以如果你要彻底停掉一个人的访问光REVOKE不够要直接改密码或锁账号然后杀掉已有会话-- 锁定账户 ALTER USER app_rw192.168.10.% ACCOUNT LOCK; -- 杀掉该用户当前所有会话需要 PROCESS 或 SUPER 权限 SELECT CONCAT(KILL , id, ;) FROM information_schema.processlist WHERE user app_rw; -- 执行查询出来的 KILL 语句实际工作中“权限回收不及时”是比“授权过多”更普遍的问题。项目都下线了数据库账号还留着人员转岗了旧权限没清理。这是很常见的安全漏洞。我建议每个季度做一次权限审计用一条 SQL 把所有账号权限导出来逐个确认是否还需要SELECT user, host, authentication_string, account_locked FROM mysql.user;再配合SHOW GRANTS FOR 用户名主机;查看每个账号的具体权限对照负责人确认该收的就收。3.5 删除用户清理不能留尾巴删除用户用DROP USERDROP USER old_user%;MySQL 8.0 里DROP USER会自动清理该用户在权限表里的相关记录包括db表、tables_priv表等不会像 5.7 老版本那样删了user表记录但残留db表数据。如果你用的是 5.7 或更早版本删除用户后最好查一下mysql.db、mysql.tables_priv有没有残留有就手动清理。删除之前有两个建议先用SHOW GRANTS FOR old_user%;看这个用户有哪些权限确认不会影响其他系统。如果有历史连接记录先锁定账户再观察几天确认没有业务依赖后再DROP USER。这个操作很稳我在生产上都是这么做的基本没出过岔子。4. 常用授权场景速查拿来就能用实际操作中很多授权需求都是重复的。我把这十年来遇到的高频场景整理成下面这张速查表方便朋友们直接抄作业。注意把username、host、password、dbname替换成自己的。4.1 场景速查表业务场景推荐授权语句注意事项应用只读账号CREATE USER ro% IDENTIFIED BY ...; GRANT SELECT ON dbname.* TO ro%;只给 SELECT不给 INSERT/UPDATE/DELETE应用读写账号CREATE USER rw% IDENTIFIED BY ...; GRANT SELECT, INSERT, UPDATE, DELETE ON dbname.* TO rw%;不给 DDL应用无法改表结构备份账号CREATE USER bklocalhost IDENTIFIED BY ...; GRANT SELECT, RELOAD, LOCK TABLES, REPLICATION CLIENT, SHOW VIEW, PROCESS ON *.* TO bklocalhost;备份需要多个权限缺一不可主从复制账号CREATE USER repl% IDENTIFIED BY ...; GRANT REPLICATION SLAVE ON *.* TO repl%;只需要 REPLICATION SLAVE 一个权限开发 / 测试库全权限CREATE USER dev% IDENTIFIED BY ...; GRANT ALL PRIVILEGES ON devdb.* TO dev%;仅限开发库绝不能用到生产DBA 管理账号CREATE USER dba10.0.0.8 IDENTIFIED BY ...; GRANT ALL PRIVILEGES ON *.* TO dba10.0.0.8 WITH GRANT OPTION;限定跳板机 IP不设%这张表里我特别想展开讲两个账号备份账号和复制账号。备份账号权限为什么这么碎因为mysqldump做逻辑备份时需要依次执行LOCK TABLES锁定表保证一致性、SHOW VIEW导出视图定义、PROCESS读取当前线程列表确认没有正在执行的 DDL等操作。如果只给SELECT备份到带视图的库就会报错。如果你用的是Percona XtraBackup这类物理备份工具需要的权限又是另一套RELOAD, LOCK TABLES, PROCESS, REPLICATION CLIENT。所以权限这块真不是“会一条 GRANT 就行”要根据工具需求来精确配置。复制账号为什么只需要REPLICATION SLAVE因为从库拉取 binlog 时只要这个权限就够了。有些老教程会写GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.*REPLICATION CLIENT是给监控工具查看主从状态用的如果不需要监控就不必多给。4.2 查看权限和排查问题的几个神仙 SQL查权限的 SQL 必须背熟几个-- 查看当前用户的权限 SHOW GRANTS; -- 查看指定用户的权限 SHOW GRANTS FOR app_rw192.168.10.%; -- 查看 MySQL 里所有用户8.0 推荐写法 SELECT user, host, account_locked, password_expired FROM mysql.user;有一次我接到一个“用户登录成功但查不了某张表”的工单。用SHOW GRANTS一看明明有库级SELECT权限为什么表查不了后来查mysql.tables_priv发现之前一次误操作把某些表的SELECT收回了但库级权限还保留着。这里就涉及 MySQL 权限匹配的细节MySQL 会先匹配更精确的授权如果有表级授权记录就以表级为准如果表级里没有对应权限再回退到库级判断。但因为权限校验是“只要某一层拒绝就不再往下”所以如果你做过表级REVOKE确实可能造成“库有权限但表查不了”的诡异现象。这类问题用SHOW GRANTS并不一定能完整看出来因为SHOW GRANTS输出的是合并后的权限集合所以排查时最好直接查mysql.tables_priv和mysql.columns_priv这两张原始表。5. 权限管理的黄金法则与常见问题排查这一章可以说是整篇文章的“避坑集锦”。我把工作中高频率踩到的坑、必须坚持的底线和一些调试技巧整理在一起新手照着做能少走很多弯路。5.1 权限管理的六个黄金法则法则一最小权限原则。每个账号只给它完成本职工作所需的最小权限。宁可多创建几个账号也不要一个账号通吃。分析账号能查线上库就够不需要INSERT应用账号能读写业务表就够不需要DROP。法则二主机限制绝不偷懒。所有账号都必须写清 Host能写 IP 就不写网段能写网段就不写%。管理员账号必须限定跳板机 IP这是底线。法则三定期改密与密码过期。生产环境密码至少 90 天轮换一次。8.0 可以用ALTER USER userhost IDENTIFIED BY newpass;在线改密配合PASSWORD EXPIRE INTERVAL自动提醒。法则四账号生命周期管理。员工入职开账号离职立刻锁号项目上线开账号项目下线立刻销号。我曾经在“清理僵尸账号”专项里一次性删掉了几十个半年前的账号吓得不少系统负责人赶紧来问“怎么删了我们的库”。所以删除前先和业务方确认这个流程不能省。法则五定期权限审计。每季度跑一次SHOW GRANTS逐个确认负责人、用途、是否还在使用。审计结果存档出了问题能追溯。法则六统一密码规则。密码复杂度统一走validate_password组件至少 12 位包含大小写、数字、特殊字符。别为了省事给内部系统用root或123456这类弱口令爆破工具扫描时第一轮就能撞库成功。5.2 常见问题排查实录问题一Access denied for user xxxxxx (using password: YES)这是最常见的报错也是 DBA 工单里的老熟人。排查顺序SELECT user, host, authentication_string FROM mysql.user;确认账号是否存在。检查来源 IP 是否匹配了正确的 Host。app%和applocalhost是完全不同的两条记录密码不一样别混。用mysql -u app -p -h 127.0.0.1和mysql -u app -p -h 10.0.0.5分开测试看是不是 Host 匹配问题。如果密码绝对正确但仍然报错检查ALTER USER是否设了PASSWORD EXPIRE。密码过期时登录也会报Access denied。问题二ERROR 1410 (42000): You are not allowed to create a user with GRANT这条报错的含义是你用GRANT ... IDENTIFIED BY试图在 8.0 里隐式建号。解决办法很简单先CREATE USER再GRANT两条语句分开执行。另外要确认你是否有CREATE USER权限没有的话先找 DBA 要权限。问题三ERROR 1819 (HY000): Your password does not satisfy the current policy requirements密码强度不够。要么换一个更复杂的密码要么临时调低validate_password.policy。生产环境建议直接换强密码不要关策略。问题四权限改了但不起作用先看是不是缓存问题。FLUSH PRIVILEGES之后新连接一般立刻生效如果还是不行确认当前会话是否已经被权限缓存“套住了”断开重连试试。另外注意SHOW GRANTS显示的结果是合并后的权限如果表级授权里SELECT没给库级授权里有也没用。问题五REVOKE 之后用户还能执行某操作可能的原因有两个一是该权限来自更高粒度的授权比如你只REVOKE了表级INSERT但全局级别INSERT还在二是用户当前会话可能还没失效需要重连或杀掉会话。排查时用SHOW GRANTS FOR userhost;看合并结果。问题六备份脚本报 Access denied备份账号权限不够按前面场景速查表补全RELOAD, LOCK TABLES, REPLICATION CLIENT, SHOW VIEW, PROCESS等权限。注意补权限时要重新发起备份因为现有会话不会自动获得新权限。5.3 实战中的权限审计脚本最后分享一个我常用的权限审计 SQL可以一次性把每个用户的主要权限列出来适合做季度审计SELECT user, host, account_locked, password_expired, IF(Super_priv Y, SUPER, ) AS super_priv, IF(Select_priv Y, SELECT, ) AS select_priv, IF(Insert_priv Y, INSERT, ) AS insert_priv, IF(Update_priv Y, UPDATE, ) AS update_priv, IF(Delete_priv Y, DELETE, ) AS delete_priv, IF(Create_priv Y, CREATE, ) AS create_priv, IF(Drop_priv Y, DROP, ) AS drop_priv FROM mysql.user;把这些结果导出 Excel按账号负责人逐个确认。能省去大量手工登录排查的时间。再分享一个小技巧线上环境我习惯把 DDL 权限CREATE、ALTER、DROP和普通业务权限分离。业务应用账号一律不给 DDL所有结构变更统一走 DBA 管理员账号执行。这样能避免开发误操作把线上表结构改了也能保证任何变更都有记录可查。这套做法我用了很多年稳定性非常高。账户与权限体系说到底是 MySQL 运维的“地基工程”。地基打得稳后面不管业务怎么扩张权限都能控制得住地基要是糊弄过去迟早要还债。建议大家参照上面的方案回去把自己手上的实例理一遍先导出所有账号再逐个审计权限最后把弱口令、僵尸账号、过宽授权都清一遍。做完这轮整理你再看 MySQL会发现心里踏实很多。