我去年接手一个内部系统时发现整个项目组六个人共用一个 root 账号连库谁都能 drop table出了事故只能靠 binlog 恢复。那时候我意识到MySQL 的创建用户 授权不是 DBA 的专属活而是每个后端工程师都应该熟练掌握的基本功。这篇文章就围绕这个话题展开把我在实际运维中积累的细节、踩过的坑、优化过的方法都整理出来从基础的 CREATE USER 讲到权限体系的设计希望能让你看完就能用、用了少踩坑。1. 为什么要分账号而不是一直用 root很多初学者觉得 MySQL 装完之后root 什么都能干为什么还要折腾创建用户和授权直接上 root 不是更省事吗这个想法在本地开发环境没问题但一旦涉及团队协作、生产环境、外包项目交接问题就全暴露了。root 账号的权限太大误操作的影响范围是全局的多个开发人员共用 root 账号出了事故没法定位是谁执行的程序代码里写上 root 密码一旦泄漏攻击者等于直接拿到数据库的最高控制权。我在实际项目里遇到过一件事一个外包团队走后我们接手了一个系统发现他们的程序里用的就是 root 账号连接数据库密码还写在配置文件里没加密。后来我们做了数据迁移需要临时把旧库的某些表同步到新库给外包的人开了个只读权限的账号他们立刻就不乐意了因为之前他们可以随意改数据。这个例子很直白地说明了权限管理在实际协作中的价值——账号权限的设计本质上就是谁可以做什么的边界定义这个边界越清晰系统的故障半径就越小。从 MySQL 的设计角度讲用户账号由两部分组成用户名和允许登录的主机地址。rootlocalhost和root192.168.1.%是两个完全独立的账号可以设置完全不同的密码和权限。这一点是很多新手容易忽略的地方——你给rootlocalhost改了密码但通过远程连接的root%密码完全不受影响。所以创建独立用户并授权核心目的有三个最小权限原则每个账号只给完成本职工作所需的最少权限、责任可追溯按人员或按应用分配账号出了问题能定位来源、风险隔离即使某个账号泄漏影响范围也被限制在授予的权限之内。2. CREATE USER创建用户的基础语法与易错点2.1 最基础的创建语句在讲授权之前先要把用户建好。MySQL 创建用户的标准语法是CREATE USER 用户名主机限制 IDENTIFIED BY 密码;举个例子创建一个用户readonly_admin允许从本机登录密码是SecurePass123CREATE USER readonly_adminlocalhost IDENTIFIED BY SecurePass123;如果你希望这个用户可以从任意主机远程登录比如程序服务器访问数据库服务器主机限制位置写%即可CREATE USER readonly_admin% IDENTIFIED BY SecurePass123;这里的主机限制host 部分非常关键它决定了用户可以从哪里连接数据库。常见的有这么几种写法写法含义适用场景localhost仅允许本机连接本地调试、同机部署的应用%允许任意主机连接远程应用服务器访问192.168.1.%允许 192.168.1.x 网段连接内网限定的应用负载10.0.0.5仅允许指定 IP 连接绑定固定出口 IP 的业务主机限制写得越具体账号被窃取后可被使用的范围就越小。我见过很多团队图省事全用%结果数据库在公网上暴露后密码一旦被爆破攻击者直接用这个账号连进来。如果业务服务器的 IP 固定建议优先把主机限制写成具体 IP 或网段。2.2 密码策略与 MySQL 8 的变化如果你是用的 MySQL 8.0会发现默认密码认证插件是caching_sha2_password而 MySQL 5.7 及更早版本用的是mysql_native_password。这个差异会导致一个经典问题——老的客户端比如 PHP 7.x 的 mysqli、某些旧版 Navicat连接 MySQL 8.0 时会报 Authentication plugin caching_sha2_password cannot be loaded 错误。遇到这种问题有两种解法一种是给用户指定兼容的认证插件CREATE USER myuser% IDENTIFIED WITH mysql_native_password BY MyPass123;另一种是对已存在的用户修改认证插件ALTER USER myuser% IDENTIFIED WITH mysql_native_password BY MyPass123;从安全角度讲caching_sha2_password加密强度更高如果是新项目且客户端都支持建议保持默认的认证方式不要一味迁就老客户端。另外MySQL 默认开启了密码复杂度校验validate_password 组件如果你的密码太简单创建用户时会报ERROR 1819 (HY000): Your password does not satisfy the current policy requirements。此时需要先确认当前密码策略级别SHOW VARIABLES LIKE validate_password%;再决定是设置一个符合复杂度要求的强密码还是临时调整策略生产环境不建议关闭密码强度校验。2.3 查看与删除用户创建完用户后可以用下面这个语句确认用户是否创建成功SELECT user, host, plugin FROM mysql.user;这个语句会把 MySQL 中所有账号列出来注意其中可能会有root的多个记录、系统自带的mysql.sys等这些都别乱动删错了会影响系统运行。删除用户用DROP USER myuser%;2.4 修改密码的三种场景修改自己的登录密码ALTER USER USER() IDENTIFIED BY NewPass456;管理员修改指定用户的密码ALTER USER myuser% IDENTIFIED BY NewPass456;如果用户的密码过期了比如你设置了PASSWORD EXPIRE策略需要设为永不过期或重设过期时间ALTER USER myuser% PASSWORD EXPIRE NEVER;3. GRANT 授权把权限精确到表级别甚至行级别用户创建出来只是一个空壳什么权限都没有。要通过 GRANT 语句给他分配具体的操作权限。MySQL 权限体系从大类上分为数据操作权限SELECT、INSERT、UPDATE、DELETE、结构操作权限CREATE、ALTER、DROP、INDEX、管理权限SUPER、PROCESS、RELOAD 等。3.1 权限类型速查表我先整理一份常用权限对照表方便你在授权时查阅权限名作用范围说明SELECT表/视图查询数据INSERT表插入数据UPDATE表更新数据DELETE表删除数据CREATE库/表/索引创建结构ALTER表修改表结构DROP库/表/视图删除结构INDEX表创建/删除索引REFERENCES表创建外键CREATE VIEW视图创建视图TRIGGER表操作触发器GRANT OPTION全局允许将自己的权限授予他人SUPER全局高级管理操作如终止连接PROCESS全局查看所有线程信息RELOAD全局重新加载权限表ALL PRIVILEGES全局/库/表除 GRANT OPTION 外所有权限3.2 最常见的授权场景场景一给程序分配一个只读账号用于报表查询CREATE USER report_reader% IDENTIFIED BY ReadOnly_2024; GRANT SELECT ON mydb.* TO report_reader%;这里mydb.*表示 mydb 数据库下所有表。如果只想让该用户查某一张表把*换成表名即可GRANT SELECT ON mydb.order_info TO report_reader%;场景二给后端服务分配一个读写账号可以增删改查但不可修改表结构CREATE USER app_writer% IDENTIFIED BY AppWrite_2024; GRANT SELECT, INSERT, UPDATE, DELETE ON mydb.* TO app_writer%;场景三给开发人员分配一个除了 DROP 什么都能干的账号CREATE USER dev_user% IDENTIFIED BY Dev_2024_Strong; GRANT CREATE, ALTER, INDEX, SELECT, INSERT, UPDATE, DELETE ON mydb.* TO dev_user%;场景四给数据迁移人员分配一个临时管理员权限GRANT ALL PRIVILEGES ON mydb.* TO migration_user% WITH GRANT OPTION;注意WITH GRANT OPTION这个子句加上之后该用户可以把自己拥有的权限再授权给其他用户这实际上已经接近半个管理员了除非明确需要否则不要乱加。我在公司内部审计时发现过一种隐患某个同事为了图方便给一个基础账号加了WITH GRANT OPTION结果这个账号的权限又被他转发给了好几个人的私人账号权限链路完全失控最后只能统一清理重建。3.3 授权之后要不要 FLUSH PRIVILEGES网上经常能看到这段操作GRANT ALL PRIVILEGES ON *.* TO myuser%; FLUSH PRIVILEGES;其实在 MySQL 5.7 之后使用GRANT、CREATE USER、ALTER USER等语句操作的权限变更会立即生效不需要手动执行FLUSH PRIVILEGES。只有在直接修改mysql.user表比如用UPDATE、INSERT、DELETE直接操作系统表的情况下才需要执行FLUSH PRIVILEGES来让权限变更重新加载到缓存中。我个人建议凡是能用官方语句解决的问题就不要绕道去改系统表。直接改mysql.user表属于非常规操作容易踩坑一旦写错可能导致整个权限体系损坏。3.4 查看一个用户当前有哪些权限权限授予后用下面这个语句查看SHOW GRANTS FOR myuser%;输出大概是GRANT SELECT, INSERT, UPDATE, DELETE ON mydb.* TO myuser%SHOW GRANTS是排查权限问题时最常用的命令之一。很多明明授权了但操作时报错的场景第一步就应该用它确认当前账号实际拥有的权限而不是靠记忆或猜。4. 实战排查授权后不生效、远程连不上、密码过期这一章专门写我真实踩过、也帮别人排查过的几类高频问题。权限管理的报错千奇百怪但都绕不开几个关键环节网络连通性、账号主机限制、认证插件、权限范围。4.1 授权之后应用还是报 Access denied这个问题是出现频率最高的。排查链路如下第一步用文本协议直接登录测试mysql -h数据库IP -P3306 -umyuser -p如果这一步就报Access denied for user myuserxxx (using password: YES)说明账号或密码存在问题如果换成 root 能登录而新用户不行基本可以断定是账号或授权语句没配对。第二步确认该用户是在哪个 host 下被授权的前面强调过myuser%和myuserlocalhost是两个账号。如果应用从远程 IP 192.168.1.50 连接但你把权限授予了myuserlocalhost那肯定连不上因为 MySQL 会根据你的来源 IP 匹配对应的 host 定义。第三步查看用户的认证插件SELECT user, host, plugin FROM mysql.user WHERE user myuser;如果是caching_sha2_password而客户端驱动较旧需要在连接参数里明确指定支持该插件的加密方式或按前文所述把插件的兼容性问题处理掉。4.2 用户能登录但执行 SQL 时报权限不足比如用户能连接 MySQL但一执行SELECT * FROM mydb.orders就报SELECT command denied to user myuser% for table orders。这种情况 90% 是授权时数据库名写错了。比如库名是mydb授权语句写成GRANT SELECT ON mydb_kpi.* TO ...或者授权时用了*.*误以为等于所有权限但*.*只是全局的当前存在的库如果后续新建了别的库没再次授权原来授权的库还是访问不了。我的建议是把授权语句中涉及的库名、表名逐一复制到 SHOW GRANTS 的输出里核对不要凭记忆判断。4.3 远程连接失败报 Cant connect to MySQL server这种情况常见原因是端口不通和用户权限没关系。排查顺序确认 MySQL 是否在监听 3306 端口netstat -tlnp | grep 3306Linux。确认 MySQL 配置中bind-address是否设置为0.0.0.0或内网 IP如果只设置为127.0.0.1外部任何地址都无法连接。确认服务器防火墙是否放行 3306 端口iptables 或安全组规则。最后才检查 MySQL 用户表的 host 配置。很多时候大家看到Cant connect就以为是权限问题折腾半天才发现是防火墙没开浪费大量时间。记住一条经验报错信息包含 Access denied 才去查权限报错信息包含 Cant connect 优先查网络和监听配置。4.4 密码过期导致程序突然连不上MySQL 8.0 默认的default_password_lifetime可能使密码在 360 天后过期届时该用户再连接就会被拒绝。如果遇到程序某一天突然连不上数据库日志里显示Your password has expired就是这个原因。解决办法是修改该账号的密码过期策略ALTER USER myuser% PASSWORD EXPIRE NEVER;或者直接重设密码ALTER USER myuser% IDENTIFIED BY NewPass456;从实际经验看密码过期策略最好在创建用户时就明确设置比如内部工具账号统一设为永不过期高安全等级的生产账号设置明确过期周期并在到期前做主动轮换。4.5 MySQL 8.0 的 caching_sha2_password 与客户端不兼容这个问题值得单独拿出来讲因为太多人遇到过了。MySQL 8.0 默认创建的用户使用caching_sha2_password但很多第三方图形工具、老版本驱动并不支持。遇到Authentication plugin caching_sha2_password cannot be loaded时处理方式有两种改用户的认证插件为mysql_native_password对老客户端友好但加密强度略低升级客户端驱动或工具到支持caching_sha2_password的版本我在实际项目中倾向于第二种——升级客户端而不是把数据库的认证水平降级。如果实在是因为历史原因无法升级驱动再考虑第一种方案并且把这种情况记录在案列为技术债务。5. 权限回收与账号治理从能用到用得心里有数5.1 用 REVOKE 回收权限回收权限和授予权限是对称的操作。语法是REVOKE SELECT, INSERT ON mydb.* FROM myuser%;把刚才授予的SELECT、INSERT权限收回来。如果想把该用户的所有权限一次性收回可以用REVOKE ALL PRIVILEGES, GRANT OPTION FROM myuser%;注意REVOKE ALL PRIVILEGES之后用户依然可以正常连接数据库只是什么操作都做不了。如果要彻底禁止此账号访问直接DROP USER即可。5.2 权限治理的季度体检习惯我个人的习惯是每季度对数据库账号做一次体检重点关注这几类账号长时间未使用的僵尸账号连接日志里半年没出现的直接禁用权限过大的账号比如普通开发账号带了DROP、SUPER共用账号多个人共用一个账号出问题没法定位主机限制为%的管理员级账号一旦密码泄露影响面非常大巡检时用到的核心查询就是前面列过的SELECT user, host FROM mysql.user和SHOW GRANTS FOR ...。再加上 MySQL 自带的连接日志或审计日志基本能把账号使用情况摸清楚。5.3 与可视化工具的结合很多人习惯用 Navicat、DBeaver、MySQL Workbench 来管理用户。图形界面确实直观但我建议你在掌握命令行操作之后再使用图形工具做辅助。原因很简单图形工具操作的是同一套权限核心其对底层细节的展示有限一旦遇到奇怪的报错或者需要批量修改多个账号的权限命令行依然是最高效、最可控的方式。以 MySQL Workbench 为例在 Server 菜单下的 Users and Privileges 里可以可视化创建用户、勾选权限但如果你想精确控制某个库的某张表的某几种权限仍然需要手动输入 DDL。所以我的经验是图形工具适合查看和粗略修改命令行适合精确控制和脚本化批处理。5.4 创建用户授权的完整最佳实践清单最后整理一份可以直接抄走的操作清单按顺序执行即可覆盖大多数场景步骤操作示例1创建用户并指定主机限制CREATE USER app_user192.168.10.% IDENTIFIED BY StrongPass_2024;2授予最小必要权限GRANT SELECT, INSERT, UPDATE, DELETE ON mydb.* TO app_user192.168.10.%;3查看最终权限确认无误SHOW GRANTS FOR app_user192.168.10.%;4如有需要设置密码过期策略ALTER USER app_user192.168.10.% PASSWORD EXPIRE INTERVAL 90 DAY;5定期巡检并回收多余权限REVOKE DELETE ON mydb.* FROM app_user192.168.10.%;6. 一个完整的实战演示从零搭建一个用户管理 授权的完整流程上面的内容偏理论这一章我们用一个模拟场景把它串起来。场景假设新项目shop_mall需要三类角色——管理员、后端应用、报表查询。其中管理员需要对数据库有完全控制权但不能管理 MySQL 本身比如不能修改全局参数后端应用需要读写shop_mall库所有表但不能创建表、不能删除表报表查询只需要读shop_mall库下的orders和products表。第一步创建管理员账号CREATE USER shop_admin% IDENTIFIED BY Admin_ShopHome_2024; GRANT ALL PRIVILEGES ON shop_mall.* TO shop_admin%;这里不给他GRANT OPTION就是防止他把权限再转授给别人。第二步创建后端应用账号CREATE USER shop_app% IDENTIFIED BY App_ShopHome_2024; GRANT SELECT, INSERT, UPDATE, DELETE ON shop_mall.* TO shop_app%;第三步创建报表只读账号CREATE USER shop_report% IDENTIFIED BY Report_ShopHome_2024; GRANT SELECT ON shop_mall.orders TO shop_report%; GRANT SELECT ON shop_mall.products TO shop_report%;如果后续报表需要读更多表只需追加授权GRANT SELECT ON shop_mall.users TO shop_report%;第四步验证权限用shop_app登录测试插入一条数据成功再执行DROP TABLE应该报权限不足。实际在做验证这一环节时我有两个小技巧一是使用--force参数跳过 SQL 执行错误编写一个小脚本批量验证所有账号的权限是否符合预期二是把每个账号的SHOW GRANTS输出保存到 SVN/Git 里作为权限基线任何变更都有历史版本可查。7. 最后再分享几个我这些年沉淀的权限管理经验关于 MySQL 用户权限这块网上教程一搜一大把但真正值钱的往往是那些藏在细节里的经验。我在这里补充几条第一条所有程序和人员账号都必须设置主机限制。生产环境尽量精确到 IP 或网段开发环境如果实在没法固定 IP也必须限制到公司出口 IP。没有一个业务场景需要数据库对所有公网 IP 开放。第二条给程序用的账号别加 GRANT OPTION。程序根本不需要转授权限这是权限蔓延最常见的源头。第三条授权宁细勿粗。报表查询给SELECT就够了绝不为了省事给ALL PRIVILEGES。权限越细出问题时定位越快。第四条账号命名最好带上用途或团队标识。比如app_shop_backend、dev_john、report_bi_daily看到名字就知道这个账号是给谁用的、用来干什么。生产环境尤其重要因为半年后没人记得住当初这个账号是干嘛的。第五条权限变更留痕。所有CREATE USER、GRANT、REVOKE、DROP USER操作都做好操作记录时间、执行人、变更内容、变更原因。虽然 MySQL 的审计插件可以做到自动记录但在未部署审计插件的情况下人工记录至少能保证有据可查。我自己经历过的最痛的一次教训就是没做权限留痕结果一个核心库的用户权限被改出问题后排查了整整一天才从 binlog 里翻出变更记录。从那以后我每做一个权限操作都会在工单里记一笔成本很低但关键时刻能救命。