MySQL创建用户与授权实战：权限模型、语法差异与最佳实践

1. 先搞懂 MySQL 的用户和权限体系再说建号很多人一上来就敲CREATE USER和GRANT结果不是报错就是权限没生效然后一脸懵。我刚开始接触 MySQL 时也这样踩坑无数之后才明白问题不在命令行本身而是没理解 MySQL 这套账户和权限的底层逻辑。MySQL 的账户不是一个简单的“用户名”而是由用户名 主机两部分组成的。什么意思同样是tom这个用户tomlocalhost和tom192.168.1.%是完全不同的两个账户可以设置完全不同的密码和权限。这个设计初看有点绕但实际非常合理——它让数据库可以精确控制“谁能从哪里连上来”而不是只要知道用户名和密码就到处都能登录。再来说权限。MySQL 的权限是分层级的就好比公司门禁系统有大门权限、楼层权限、办公室权限、抽屉权限还有财务室这种特殊房间的权限。MySQL 大体上也是这么分的全局权限对整个 MySQL 实例生效存在mysql.user表里比如SUPER、SELECT ON *.*。库级权限针对某一个数据库存在mysql.db表里。表级权限针对某一张表存在mysql.tables_priv表里。列级权限针对某一列的存在于mysql.columns_priv。存储过程 / 函数权限单独控制存在mysql.procs_priv。这些权限叠加之后MySQL 在判断一个操作是否被允许时是“或”的逻辑——任何一个层级有权限那么就可以执行。比如用户对db1没有任何 SELECT 权限但对db1.table1有 SELECT 权限那么他能查这张表查不了db1里其他表。理解了这套逻辑后面创建用户和授权才能真正做到心里有数。而不是像很多教程那样告诉你敲什么命令就敲什么命令换个场景就不会了。还有一点必须提前说明MySQL 8.0 和 5.7 在用户创建和授权的语法上有挺大区别。8.0 之前GRANT ALL ON *.* TO userhost IDENTIFIED BY password这种写法能一步到位既能建用户又能授权。但 8.0 开始GRANT语句里的IDENTIFIED BY被移除了必须先CREATE USER再GRANT。如果网上抄的代码还在用老写法在 8.0 上直接报语法错误。下面所有实操我都会以 8.0 为主同时标注版本差异。2. 手把手创建用户本地用户、远程用户一次说清2.1 本地用户与远程用户到底怎么建创建用户的语法很简单一句话CREATE USER 用户名主机 IDENTIFIED BY 密码;关键是“主机”这里怎么写。最常见的几种-- 只能从本机连接 CREATE USER tomlocalhost IDENTIFIED BY Tom12345; -- 只能从某个具体IP连接 CREATE USER tom192.168.1.100 IDENTIFIED BY Tom12345; -- 可以从某个网段连接用了通配符 % CREATE USER tom192.168.1.% IDENTIFIED BY Tom12345; -- 可以从任意主机连接生产环境慎用 CREATE USER tom% IDENTIFIED BY Tom12345;这里的%是通配符代表任意字符序列。192.168.1.%意思是只要是 192.168.1.x 的 IP 都能连。127.0.0.1有时候也会有坑因为localhost和127.0.0.1在 MySQL 的账户匹配里并不完全是一回事前者走 socket 连接后者走 TCP 回环连接。我遇到过几次这样的问题明明创建了tomlocalhost用mysql -h 127.0.0.1 -u tom -p连接却报访问被拒原因就是 IP 匹配时命中的是tom%或者没有匹配项。一个非常实用的技巧用SELECT user, host, plugin FROM mysql.user;查看当前所有账户你会看到同一个用户名的多行记录各不干扰。这就是上面说的“用户名 主机”组合账户的实际表现了。2.2 8.0 的密码规则和认证插件容易栽跟头的地方MySQL 8.0 默认的认证插件是caching_sha2_password而 5.7 及更早版本用的是mysql_native_password。如果客户端工具版本比较老可能会出现“密码明明正确但连不上”的情况。最常见的表现是 Navicat 老版本连 MySQL 8.0 报Authentication plugin caching_sha2_password cannot be loaded这种有两种解决方式要么升级客户端工具要么在创建用户时指定老插件CREATE USER tomlocalhost IDENTIFIED WITH mysql_native_password BY Tom12345;不过说句实话mysql_native_password是过渡方案能升级客户端就升级客户端长期用老插件不利于安全。再说密码复杂度。MySQL 8.0 默认装了validate_password组件密码太简单会直接报错ERROR 1819 (HY000): Your password does not satisfy the current policy requirements默认策略要求密码至少 8 位包含大小写字母、数字和特殊字符。我自己在本地测试时图省事想用123456直接被拒。不想太麻烦的话可以在创建用户前临时调低策略SET GLOBAL validate_password.policy LOW; SET GLOBAL validate_password.length 4;注意8.0 的参数名和 5.7 不一样。5.7 里是validate_password_policy8.0 里改成了validate_password.policy。这种细节要不是实际踩过坑光靠记是记不住的。生产环境不建议调低策略本地开发图方便倒是无所谓。2.3 创建用户时顺手把注释加上团队协作的救命细节很多人创建用户只写用户名和密码时间一长库里的用户越来越多谁建的、干嘛用的、该不该删全凭猜。我的习惯是从一开始就维护一条注释CREATE USER app_reader192.168.10.% IDENTIFIED BY Read2024 COMMENT 2024-06-01 创建供BI报表只读查询使用负责人张三;这样一年之后回头看每个用户都能追溯到业务来源清理僵尸账户时非常方便。这个习惯让我在接手别人数据库时省了不少力气真心建议你也养成。3. 授权实操从最小的 SELECT 到最高权限的 ALL3.1 理解 GRANT 的语法结构创建用户只是第一步真正决定这个账户能干嘛的是授权。授权语法如下GRANT 权限列表 ON 权限级别 TO 用户主机;权限列表多个权限用逗号分隔比如SELECT, INSERT, UPDATE。权限级别*.*全局、db1.*库级、db1.table1表级、db1.table1.col1列级。用户和主机必须和创建用户时保持一致。举几个具体的例子-- 只读权限只能查指定库的所有表 GRANT SELECT ON mydb.* TO readerlocalhost; -- 读写权限能查能改还能建新表 GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, ALTER, DROP, INDEX ON mydb.* TO writerlocalhost; -- 所有库所有表的所有权限相当于管理员之一慎用 GRANT ALL PRIVILEGES ON *.* TO adminlocalhost;我在实际工作中几乎不直接给ALL PRIVILEGES而是按需列权限。一方面是安全考虑——权限越大出问题时波及范围越广另一方面是审计需求——如果哪天要排查谁干了什么权限边界清晰的账户更容易追踪。这里要特别强调一个很多人不知道的细节授权时指定数据库和表但数据库或表还不存在MySQL 不会报错权限依然会被记录。比如你给future_db.*授权但future_db还没创建授权照样成功。等以后这个库真创建了权限自动生效。这个特性有好有坏好处是可以提前准备好账户坏处是容易让权限变得比想象中宽——你以为只是给某个已存在的库授权实际上可能因为拼写错误授到了一个“幽灵库”上而这个“幽灵库”以后一旦被创建权限就直接生效了。3.2 8.0 中“创建用户 授权”的标准姿势在 MySQL 8.0 里推荐的做法是分两步走-- 第一步创建用户 CREATE USER app_rw192.168.10.% IDENTIFIED BY App2024; -- 第二步授权 GRANT SELECT, INSERT, UPDATE, DELETE ON app_db.* TO app_rw192.168.10.%;执行完之后必须让权限生效FLUSH PRIVILEGES;但这里有个争议很多人说GRANT本身就会刷新权限不需要FLUSH PRIVILEGES。这个说法对吗对也不对。如果你是用CREATE USER和GRANT语句操作MySQL 已经把变更写入了授权表权限修改是即时生效的不需要额外刷新。FLUSH PRIVILEGES主要用来重新读取授权表比如你直接手动修改了mysql.user表这种操作不推荐但确实有人这么干或者修改了系统表后想让改动立即生效。我自己写博文和做项目时习惯上还是会执行一下FLUSH PRIVILEGES倒不是说必须在而是这个动作能让某些“权限改了半天没生效”的玄学问题直接消失成本极低顺手就做了。3.3 WITH GRANT OPTION给出去的权力能再给出去有一种特殊情况你想让某个用户不仅能查表还能把自己拥有的权限再转授给其他人。这时需要在授权的末尾加上WITH GRANT OPTIONGRANT SELECT ON mydb.* TO app_leadlocalhost WITH GRANT OPTION;这样app_lead用户登录后可以把自己拥有的 SELECT 权限再授给别的用户。听起来很方便但我强烈建议你在生产环境中不要随便用这个选项。因为它绕过了“统一入口授权”的原则可能导致权限扩散失控。你给了一个人“能授权”的权利他就能批量创建有权限的账户而你甚至不在授权记录里出了问题很难排查。这个坑我在管理一个多团队共享的数据库时踩过——某个团队负责人拿了GRANT OPTION后给自己团队开了七八个账号等有人离职时根本不知道哪些账号是他创建和维护的清理起来非常头疼。3.4 查看权限、撤销权限、删用户一套完整的闭环建了用户、授了权后续的查看和维护同样重要。常用的几个命令-- 查看某个用户的权限 SHOW GRANTS FOR app_rw192.168.10.%; -- 查看当前登录用户的权限 SHOW GRANTS; -- 撤销某条权限注意撤销的是权限不是删除用户 REVOKE DELETE ON app_db.* FROM app_rw192.168.10.%; -- 收回用户所有的权限 REVOKE ALL PRIVILEGES, GRANT OPTION FROM app_rw192.168.10.%; -- 删除用户 DROP USER app_rw192.168.10.%;SHOW GRANTS是我在工作中用得最多的排查命令之一。当用户报告“我明明有权限为什么还是报权限不足”时第一步就是看当前实际生效的权限是什么——很多时候是用户从错误的入口连接匹配到了另一个账户或者权限授到了tomlocalhost但他实际是从远端连接的。这里再补充一个之前提到的细节如果你用了WITH GRANT OPTION授权撤销时必须单独处理。REVOKE ALL PRIVILEGES不会同时收回GRANT OPTION需要显式地REVOKE GRANT OPTION ON mydb.* FROM app_leadlocalhost;否则你回收了所有权限但他还能给别人授权这个 bug 很容易被忽略。4. 实战场景给应用配一个不多不少刚刚好的数据库账户4.1 最小授权原则一份可直接照抄的操作记录理论说了一堆不如直接来一个完整的实战流程。假设现在有一台应用服务器IP 是192.168.10.88需要连接 MySQL 里的shop数据库发评论需要写入查商品需要读取。按照最小授权原则它的账户应该这样创建-- 1. 创建应用专用账户 CREATE USER shop_app192.168.10.88 IDENTIFIED BY Shop#2024_88; -- 2. 只授予 shop 库的增删改查权限 GRANT SELECT, INSERT, UPDATE, DELETE ON shop.* TO shop_app192.168.10.88; -- 3. 确认权限正确 SHOW GRANTS FOR shop_app192.168.10.88; -- 4. 让权限立即生效虽然通常不是必须但养成习惯无害 FLUSH PRIVILEGES;你看整个过程就四条语句。没有ALL PRIVILEGES没有*.*没有GRANT OPTION。这样做的好处是什么如果应用被入侵了攻击者拿到的也只是shop库中这四张权限能操作的内容无法登录到其他库更不可能改全局配置。这就是纵深防御里“权限最小化”的具体落地。如果你的应用需要定时任务比如每天从别的系统同步数据进来那还需要额外加INSERT和UPDATE但不需要DELETE除非业务上真的需要。曾经有个朋友问我为什么他们的应用偶尔报错“无删除权限”我去看了一下发现他们当初图省事直接授了ALL PRIVILEGES根本不会触发这个问题。但反过来想什么时候需要删数据只有用户在前台执行“删除评论”“清空购物车”这类操作时应用才需要DELETE权限。如果应用压根没有删除功能那这个权限就是多余的多余的权限都是潜在风险。4.2 权限精确到表让接口更安全继续说这个shop库的场景。如果业务方要求“用户中心”只能查用户表不能看订单表这时候命令就变成了GRANT SELECT ON shop.users TO user_center192.168.10.%;这样user_center这个账户就只能读shop.users表其他表一概看不见、查不了。这种细粒度授权在数据敏感度较高的系统里非常实用比如用户表、支付表、订单表建议按业务模块拆分权限不要一个“万能账户”打天下。更深一层的列级授权也用得上只是场景少一些。比如GRANT SELECT (id, name, email) ON shop.users TO marketinglocalhost;这样marketing账户只能查shop.users表的id、name、email三列密码、手机号这种敏感列直接被挡住。列级授权能解决很多合规需求比如“运营不能看到用户密码哈希”“客服只能看到昵称和手机尾号”等等。不过也要吐槽一下列级授权在 MySQL 里的颗粒度虽好但日常维护成本略高一般小团队不太用得上而且列名一变就要同步权限很容易遗漏。4.3 CentOS 服务器上 MySQL 的账号维护实用命令因为热词里大量出现 CentOS 和 MySQL 安装相关的内容这里顺便把 Linux 服务器上常用的几个维护命令整理一下方便实际操作时对照# 登录 MySQLroot 用户注意无密码进不去的话带上 -p mysql -u root -p # 查看所有用户在 MySQL 提示符内执行 SELECT user, host, plugin FROM mysql.user; # 直接查看某个库有哪些授权账户的权限摘要 SELECT * FROM information_schema.schema_privileges WHERE TABLE_SCHEMA shop; # 查看当前实例所有授权记录适合做权限审计 SELECT * FROM mysql.db;有些朋友安装完 MySQL 后root 密码忘了或者初始密码不知道这在 CentOS 上尤其常见。网上大部分教程是让你改配置文件skip-grant-tables重启跳过授权但这是一招险棋。因为跳过了认证之后如果你开了远程访问相当于裸奔。我建议的流程是先看一下 MySQL 错误日志里的临时密码grep temporary password /var/log/mysqld.log用临时密码登录然后立刻改密码ALTER USER rootlocalhost IDENTIFIED BY NewRoot2024;如果临时密码也找不到再考虑skip-grant-tables方案同时务必确认防火墙只允许本机访问 MySQL 端口改完密码后立刻去掉skip-grant-tables并重启。这条经验是我帮一个朋友排查时总结出来的。他当时为了临时修改密码添加了skip-grant-tables结果改完密码忘了删留下了巨大的安全隐患。所以请记住skip-grant-tables只是修东西时的临时拐杖不是正常运行状态。4.4 权限修改后的排查顺序报错了先按这个检查实际运维中用户连不上、权限无效这类问题80% 都能通过下面这个顺序定位出来先确认账户和主机匹配执行SELECT user, host FROM mysql.user;看看有没有这个账户、主机范围是否覆盖了来源 IP。再看认证插件8.0 下老客户端连不上十有八九是caching_sha2_password问题。看端口和防火墙MySQL 默认 3306CentOS 上如果你开了 firewalld记得firewall-cmd --list-ports看一下有没有放行。就算 MySQL 用户和授权全对防火墙把端口挡了照样连不上。确认权限是否刷新如果你是用 INSERT/UPDATE 直接改的授权表不建议那必须FLUSH PRIVILEGES。如果用 GRANT通常不用。最后看连接串检查应用里的 JDBC 配置或 Python 连接字符串用户名、密码、主机、端口有没有写错。这个排查顺序我写过很多次因为它能救人于水火。有一次我一个项目怎么连都报Access denied for user shop_app... (using password: YES)排查半天结果发现密码里有符号在配置文件的 URL 里被当成参数分隔符解析掉了。这种“坑”完全不在数据库这边但最后问题表现就是“连不上”。5. 常见坑与经验锦囊都是真金白银踩出来的5.1 那些年我们踩过的用户授权报错报错信息常见原因解决方案ERROR 1410 (42000): You are not allowed to create a user with GRANT当前账户没有CREATE USER权限或没有GRANT OPTION用 root 或超级管理员执行或者给当前用户授CREATE USER权限ERROR 1819 (HY000): Your password does not satisfy the current policy requirements密码不符合密码策略换用符合策略的强密码或临时调低validate_password策略ERROR 1044 (42000): Access denied for user ... to database ...对该库没有操作权限检查授权层级是否覆盖到该库ERROR 1045 (28000): Access denied for user ... (using password: YES)用户名或密码错误、主机不匹配、认证插件不兼容按上一节排查顺序逐项确认Authentication plugin caching_sha2_password cannot be loaded客户端工具太老不支持 8.0 默认插件升级客户端或指定mysql_native_password创建用户The user specified as a definer (root%) does not exist视图或存储过程的 DEFINER 指向了一个不存在的账户修改视图/存储过程的 DEFINER或创建对应账户这张表里的每一个坑我都真实遇到过尤其是最后一个definer does not exist非常隐蔽。场景是这样的开发把视图和存储过程用root%作为 DEFINER 创建了但线上环境根本没有root%这个账户因为安全策略只允许rootlocalhost于是别人访问这个视图时MySQL 要用 DEFINER 的权限来检查是否允许访问结果发现 DEFINER 本身不存在直接报错。解决办法就是创建对应的 DEFINER 账户或者用ALTER VIEW/CREATE OR REPLACE重新指定 DEFINER。5.2 生产环境用户权限设计的几条铁律我在实际运维中总结了几条不算“标准答案”但非常实用的原则分享给正在规划数据库权限的人第一条永远不要用 root 去跑应用。应用连接数据库的账户哪怕权限再大也别直接用 root。root 是管理员的代名词一旦应用被注入了恶意 SQLroot 权限等于把整个数据库拱手送人。应用账户应该有自己的专属账号和最小权限。第二条不同业务模块用不同账号。即使用户量不大我也建议至少拆成“只读账号”“读写账号”“管理账号”三个层级。只读账号给报表、BI、数据分析用读写账号给业务应用用管理账号只掌握在 DBA 和运维手里。这样即使某个环节失守影响面也是可控的。第三条权限变更要留痕。公司项目如果允许多人操作数据库最好约定权限变更统一走工单工单里写清楚“要授权给谁、什么权限、因为什么、有效期到什么时候”。听起来繁琐但等到做安全审计或者排查越权操作时这些记录就是救命稻草。我一个人管数据库的时候也觉得麻烦后来出了几次“疑似数据异常”的纠纷全靠这些记录定位了问题来源才真正服气。第四条定期清理僵尸账户。每季度执行一次SELECT user, host FROM mysql.user;和当初的授权记录对比把已离职、已下线的账户顺手DROP USER。别嫌麻烦数据库里的僵尸账户就像房间里的过期药品平时看着没事真出事时全是坑。5.3 顺手主义的两个小技巧最后分享两个不影响大局但能提升幸福感的小技巧技巧一用模板保存常用授权语句。我本地有个名为mysql_user_template.sql的文件里面写好了常用的创建用户、授权、查看权限、回收权限的语句模板参数用占位符表示。每次需要新建账户时复制一份替换掉用户名、主机、密码、库名几秒钟搞定还不会漏掉FLUSH PRIVILEGES这种容易忘的步骤。技巧二给 root 配一个专属的复杂密码并限制仅本机登录。8.0 安装时默认 root 只能从本机访问这个默认策略千万别改。如果确实需要远程管理建议用admin这类专门创建的账户加*.*权限而不是直接放开 root 的远程访问权限。这样做的好处是一旦需要审计“谁改了什么全局配置”至少有据可查而 root 始终是那个唯一的、本机专属的“最终保险丝”。我在实际使用中发现用户管理和权限分配这件事技术上并不难真正的挑战在于一开始有没有把规则定清楚。很多人学 MySQL 的时候只关注了GRANT ALL和SELECT * FROM这类“能跑通”的命令忽略了权限体系设计的本质——它不是为了限制你开发而是为了让你在出问题的时候能快速定位、缩小损失、保住底线。