每次看到搜索框里跳出“mysql 如何创建用户并且授权”这类关键词我就知道提问的人多半刚被Access denied for user rootlocalhost这种报错折磨过或者是刚入手一台服务器正对着漆黑的终端窗口琢磨怎么把数据库安全地交给同事、交给应用去用。创建用户和授权这事在MySQL日常运维里属于基础中的基础但基础不等于简单——它牵扯到账户体系、权限粒度、认证方式还有一堆版本差异带来的新坑。这篇我就把完整的操作链路、底层逻辑和实际运维中才见得到的细节一次性拆开讲清楚。不管你是刚摸MySQL的新手还是已经写过增删改查、准备接手数据库管理的老手这都能给你省下不少试错时间。文章会从最基础的CREATE USER开始一路讲到授权粒度怎么选、MySQL 8.0 的认证机制变化、权限的查询与回收最后给一套可以直接抄作业的生产环境建号方案。所有操作都基于MySQL 8.0同时也会指出来哪些写法在5.7里还能用、在8.0里已经废了。1. 一条SQL搞定用户创建语法拆解与常用选项先明确一个基本事实在MySQL里创建一个用户不等于给了它任何访问数据的权利这两件事是彻底分开的。数据库用户只是一个登录凭证能不能看某张表、能不能删某行数据全靠后续的授权语句说了算。这个设计跟操作系统的用户体系很像——你可以创建一堆账号但每个账号默认被关在门外直到管理员给它们分配了钥匙。1.1 最小可用的建号语句长什么样最简单的创建用户语句是这一条CREATE USER zhangsanlocalhost IDENTIFIED BY your_password;这条语句干了三件事定义用户名zhangsan定义允许登录的主机localhost定义登录密码your_password。执行完以后你可以在MySQL里看到一个叫zhangsan的账户但它连SHOW DATABASES;都跑不全因为默认没有任何权限。符号后面的主机部分经常被新手忽略但它是整个权限体系里最容易理解错的一环。zhangsanlocalhost和zhangsan%是两个完全独立的账号哪怕用户名一样密码也可以不一样权限更是各管各的。localhost只允许从本机连接%表示允许从任意地址连接指定IP则只允许从那个IP连接。有一个很实际的建议生产环境里能用具体IP就别用%能用localhost就别开外网。%看着方便实际上是把攻击面直接铺开到了全网后面想排查谁在连我的数据库时也会更难。1.2 命名字符与密码选项的硬性规则MySQL 8.0 对用户名、密码有几个硬性约束建号前最好先过一遍用户名最长32个字符不能以-这类特殊符号开头但中间可以用下划线。密码默认有复杂度要求validate_password组件默认开启时简单密码会直接报错。比如设个123456MySQL会拒绝执行并提示不符合策略。IDENTIFIED WITH可以手动指定认证插件例如IDENTIFIED WITH mysql_native_password BY password。8.0默认用的caching_sha2_password后面会专门讲这个坑。如果只是本地开发环境想省事可以在MySQL里临时关掉密码复杂度校验再建号但我的建议是别这么干。生产库一旦养成随便一个弱密码就能进的习惯后面出事只是时间问题。开发库要图省事至少也设一个长度超过8位、带大小写和数字的密码手感和安全能兼顾。1.3 建号时可以直接带上的几个实用选项CREATE USER支持在一条语句里带多个选项常用的有三个CREATE USER zhangsanlocalhost IDENTIFIED BY your_password WITH MAX_QUERIES_PER_HOUR 1000 PASSWORD EXPIRE INTERVAL 90 DAY;MAX_QUERIES_PER_HOUR限制这个账号每小时最多执行多少条查询适合用来控制应用账号的暴力查询行为。PASSWORD EXPIRE INTERVAL 90 DAY密码每90天到期到期后必须改密才能继续操作。对共享账号来说这个选项能逼着大家定期轮换密码。ACCOUNT LOCK/ACCOUNT UNLOCK默认是ACCOUNT UNLOCK如果建号时就知道暂时不该让人用可以先ACCOUNT LOCK等需要时再解锁比建了再删稳妥。这些选项在CREATE USER和ALTER USER里都能用。有时候业务方急着要账号但我们又没想好到底给什么权限我的处理方式是先按需建号然后用一条ALTER USER ... ACCOUNT LOCK;把它锁住等权限方案定下来再解锁交付安全又留有余地。2. 授权不是一句话的事权限粒度、写法与版本差异创建用户只是上半场授权才是重头戏。很多人以为授权就是GRANT ALL ON *.* TO userhost;这一锤子买卖真到了线上这么干等于把整个数据库的生死交给了这个账号风险极大。正确的做法是先搞清楚MySQL到底有哪些权限、每种权限管到什么范围再根据业务需求做最小化授权。2.1 权限的四个层级全局、库、表、列MySQL的授权模型是分层的从大到小依次是权限层级授权方式影响范围典型场景全局级ON *.*服务器上所有库所有表DBA管理账号、备份账号库级ON db_name.*指定数据库下所有对象某个业务库的专属账号表级ON db_name.table_name指定表只允许操作订单表列级ON db_name.table_name (col1, col2)指定表的指定列只能读用户表的姓名不能读余额例程级ON PROCEDURE/FUNCTION db_name.routine_name指定存储过程/函数允许调用某个存储过程层级越小控制越精细但管理成本也越高。实际项目里用到最多的是库级授权这跟业务划分是对应的——通常一个应用对应一个数据库给它整个库的权限就够用了没必要逐表开放。列级授权有个容易被忽视的地方它只能针对SELECT、INSERT、UPDATE这种明细操作DELETE不支持列级限制。想做到不能删数据但又能改数据只能靠表级或库级权限里不给DELETE来实现。2.2 GRANT 语句的标准写法和常用组合授权的基本语法是GRANT privilege_type ON privilege_level TO userhost [WITH GRANT OPTION];实际使用中几种典型的权限组合大概是这样的只读账号GRANT SELECT ON mydb.* TO readonly_user192.168.1.%;这个账号能查mydb库里所有表但不能插入、修改、删除。适合给做报表的同事、数据分析平台用。读写账号不含DDLGRANT SELECT, INSERT, UPDATE, DELETE ON mydb.* TO app_user192.168.1.%;这是应用账号最常见的一种能对业务表做增删改查但建不了表、改不了表结构。把DDL权限跟应用账号隔离能有效防止应用跑着跑着把表结构改了这种事。包含DDL的开发账号GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, ALTER, DROP, INDEX ON mydb.* TO dev_user192.168.1.%;适合开发环境开发可以自由改表结构放到生产环境就要慎重了。管理全部库表的账号GRANT ALL PRIVILEGES ON *.* TO admin_userlocalhost WITH GRANT OPTION;WITH GRANT OPTION表示这个账号还能把权限转授给其他账号。只有DBA级别的账号才需要这个选项普通账号千万别加。搜索词里有一条oracle数据库创建一个远程的用户可以修改、查询、删除、创建所有的库和表的权限这种人通常就是想要一把万能钥匙。我会直接泼冷水这种所有库所有表随便删的账号最好只在最低权限确实能满足需求时保留一个其余时候按业务域拆分哪怕管理起来多几个账号也比被一个失控的超级账号拖下水强。2.3 MySQL 8.0 与 5.7 授权写法的关键差异网上搜MySQL授权能搜到大量GRANT ALL PRIVILEGES ON *.* TO userhost IDENTIFIED BY password;这种老写法。这是MySQL 5.7及更早版本支持的建号授权一条龙语法在8.0里已经不可用了。8.0强制要求把创建用户和授权分开GRANT语句里只能引用于已存在的用户否则直接报错。遇到这种报错不要怀疑是权限不够先检查用户有没有先建出来。另外8.0里GRANT的权限列表也变了新加了一些权限比如BACKUP_ADMIN、FLUSH_TABLES、SHOW_ROUTINE稍微新一点的权限名可以通过SHOW PRIVILEGES;查看。写授权语句前先看一眼当前版本支持哪些权限名能少踩不少文档过时导致的坑。还有授权过程中最常见的报错是ERROR 1410 (42000): You are not allowed to create a user with GRANT这个报错的意思是当前这个账号没有CREATE USER权限也没有GRANT OPTION所以不能给别人授权。用root或者有GRANT OPTION的账号去执行就没问题。3. MySQL 8.0 用户体系的变化认证插件与密码策略的绕坑指南如果只是把MySQL当黑盒数据库用你可能一直不会碰到认证插件的问题。但只要换个客户端、换台服务器远程连一下8.0默认的认证方式就能给你上一课。这一节把8.0在用户认证和密码管理上最影响日常使用的变化说清楚也是网上教程最容易讲乱的地方。3.1 caching_sha2_password 为什么能把老客户端卡死MySQL 8.0 默认的认证插件是caching_sha2_password取代了5.7时代咱们用得顺手的mysql_native_password。在加密强度和安全性上前者确实更好但它带来了一个兼容性问题很老版本的客户端、部分图形工具、某些语言的老版驱动默认不支持caching_sha2_password连接时会报Authentication plugin caching_sha2_password cannot be loaded实际上只要客户端和驱动是2018年以后发布的正式版本基本都适配了caching_sha2_password问题大多出在工具太老、驱动太旧上。碰上了优先升级客户端和驱动而不是降级认证插件。但如果真想用回旧认证方式可以这样操作ALTER USER zhangsanlocalhost IDENTIFIED WITH mysql_native_password BY your_password;或者在建号时直接指定CREATE USER zhangsanlocalhost IDENTIFIED WITH mysql_native_password BY your_password;这里有一个取舍用mysql_native_password确实能解决老旧客户端的连接问题但代价是放弃了8.0默认认证方案的强度。我处理这类问题时一般先问清楚对方用的什么客户端、什么版本驱动能升级就升级实在没法升级的才给老认证插件。毕竟数据库的认证方式属于安全底线为了迁就一个落伍客户端而拉低整个库的防线不太划算。3.2 密码策略相关报错与解决办法MySQL 8.0 默认开启了validate_password组件也就是说你建号时设的密码必须满足一定复杂度。我以前在内网搭测试环境想设个root123直接被弹回来ERROR 1819 (HY000): Your password does not satisfy the current policy requirements遇到这种报错先别急着改密码可以先查看当前策略SHOW VARIABLES LIKE validate_password%;常见的输出大概是这样变量名值含义validate_password.LENGTH8密码最短长度validate_password.MIXED_CASE_COUNT1至少要含大小写字母各几位validate_password.NUMBER_COUNT1至少含几个数字validate_password.POLICYMEDIUM密码策略等级MEDIUM 以上还需要包含特殊字符按默认MEDIUM策略一个合规密码应该长这样MyPssw0rd2024。测试环境想放宽可以通过SET GLOBAL validate_password.POLICYLOW;临时降级但正式环境没必要这么做。密码复杂一点带来的麻烦远小于数据库被扫库爆破的代价。3.3 远程连接到底卡在哪一步跟mysql创建用户并且授权一起频繁出现的热搜是mysql安装配置教程和docker安装mysql说明很多人是装完MySQL以后卡在远程连不上这一步。除了bind-address、防火墙这类常规排查项账户层面最容易出问题的有两点第一个是主机限制。如果建号写的是userlocalhost那么从远程连必然被拒。这种场景需要专门建一个远程账号比如CREATE USER remote_user192.168.1.% IDENTIFIED BY strong_password; GRANT SELECT, INSERT, UPDATE, DELETE ON mydb.* TO remote_user192.168.1.%;第二个是账号存在但没授权。很多人建完号以后直接跑mysql -u zhangsan -p能登录是能登录但一执行查询就报SELECT command denied to user zhangsanlocalhost for table xxx这就是典型的有账号没权限。解决方式只有一个按需补授权。4. 看权限、回收权限、删掉用户完整的权限生命周期管理创建用户和授权不是一锤子买卖。项目迭代、人员变动、应用下线都要求你能随时查看某个账号有什么权限、回收不该有的权限、优雅地停用账号。这一节补全权限管理的完整闭环也是很多教程容易跳过的部分。4.1 用 SHOW GRANTS 和 information_schema 检查权限想知道某用户现在有什么权限最直接的办法是SHOW GRANTS FOR zhangsanlocalhost;输出结果是一串GRANT语句比如GRANT SELECT, INSERT, UPDATE ON mydb.* TO zhangsanlocalhost这样看单账号很直观但如果你想一次性了解所有用户的权限分布就得查系统表了SELECT user, host, Select_priv, Insert_priv, Update_priv, Delete_priv FROM mysql.user;这张表记录了全局权限配合每个库的权限表mysql.db基本可以还原出整个权限体系。用root登录去检查时注意权限矩阵是叠加的一个账号在某张表上的最终权限是全局权限、库权限、表权限的并集。所以排查权限问题时不能只看一条记录要三层叠加一起看。4.2 REVOKE 回收权限的正确姿势回收权限用REVOKE语法跟GRANT对称这里要注意两点一是回收权限不需要对方重新登录立即生效。比如REVOKE DELETE ON mydb.* FROM app_user192.168.1.%;执行完以后app_user再执行DELETE就会被拒绝。但这里有个细节如果app_user同时拥有全局DELETE权限那么REVOKE库级权限并不能拦住它——因为权限是并集全局权限仍然有效。所以要真正限制一个账号必须把对应权限在所有层级都收干净。二是REVOKE ALL PRIVILEGES, GRANT OPTION FROM userhost;会回收账号的所有权限但不会删除账号本身也不影响它继续登录。配合DROP USER才能彻底停用。4.3 从锁定到删除账号销毁的完整路径如果你确定一个账号再也不需要了推荐这样收尾-- 第一步锁住账号阻止新登录 ALTER USER zhangsanlocalhost ACCOUNT LOCK; -- 第二步确认没有业务依赖这个账号后彻底删除 DROP USER zhangsanlocalhost;两步走比直接DROP USER稳妥。特别是线上环境账号可能被某个定时任务、某个埋点脚本悄悄引用着直接一刀切删除会导致业务半夜报错。先锁定观察一两个业务周期确认没有告警再删除是更成熟的运维习惯。DROP USER在8.0里也可以一条语句删多个账号DROP USER user1localhost, user2localhost;这个写法在批量清理账号时很好用不用一条条执行。5. 一套可以直接抄作业的生产环境建号方案前面把点都讲透了这一节把它们串成一个完整流程。以下是我在新环境里给MySQL做初始化的实际套路适用场景是刚装完MySQL需要给应用、给同事、给备份脚本分别建号授权跟着走基本不会出大错。5.1 明确账号规划不同角色不同账号生产环境里我最不建议的用法就是一个root走天下。至少应该把账号拆成下面几类账号角色建议权限典型来源IPDBA管理账号ALL PRIVILEGES GRANT OPTION堡垒机/跳板机IP应用读写账号SELECT, INSERT, UPDATE, DELETE应用服务器IP报表只读账号SELECT报表服务器IP备份账号SELECT, RELOAD, SHOW DATABASES, LOCK TABLES, REPLICATION CLIENT备份服务器IP开发调试账号SELECT 必要时 INSERT/UPDATE/DELETE开发网段IP权限分配的核心思想是最小权限原则凡是业务不需要的能力一律不给。“可能以后用得上”的权限不是提前给的等真需要时再GRANT也来得及权限审批的链路比出事故后补救要轻得多。5.2 初始化脚本实例下面这段SQL可以直接放在初始化脚本里按实际环境替换IP、库名、密码-- 1. DBA账号仅限本机或堡垒机 CREATE USER dba10.0.0.8 IDENTIFIED BY Strong#Dba2024; GRANT ALL PRIVILEGES ON *.* TO dba10.0.0.8 WITH GRANT OPTION; -- 2. 应用账号只允许从应用服务器网段连接 CREATE USER app_account10.0.1.0/255.255.255.0 IDENTIFIED BY Strong#App2024; GRANT SELECT, INSERT, UPDATE, DELETE ON appdb.* TO app_account10.0.1.0/255.255.255.0; -- 3. 只读报表账号 CREATE USER report_reader10.0.2.15 IDENTIFIED BY Strong#Report2024; GRANT SELECT ON appdb.* TO report_reader10.0.2.15; -- 4. 刷新权限使授权立即生效8.0里GRANT后会动态生效但执行一下无碍 FLUSH PRIVILEGES;注意MySQL的host支持用子网掩码表示网段比如10.0.1.0/255.255.255.0表示只允许10.0.1.1到10.0.1.254这个范围的IP连接。这种方式在管理规范化时非常有用避免了%的过度开放。5.3 建号之后立刻要做的事账号建完不是直接交付就完事了我通常会顺手做三件事第一验证登录。用新账号从目标主机试连一下确认认证和网络层都通mysql -h 10.0.1.10 -u app_account -p appdb第二验证权限边界。刻意执行一条权限之外的SQL比如只读账号试着INSERT INTO appdb.users ...应该看到command denied报错。这一步是确认不该有的权限确实没有。第三把账号信息记入密码管理库或文档。生产环境账号越久越记不清当初建这个号是干嘛的养成建号即记录的习惯后面做权限审计会轻松很多。6. 实操心得那些文档里不会写的坑与建议最后这部分挑几个我实际运维中反复踩到、但通常文档不会专门提醒的点当经验帖分享出来。每条背后都有真实的教训希望你能绕开。6.1 刷新权限到底什么时候需要网上大量文章在GRANT后都跟着一句FLUSH PRIVILEGES;导致很多人以为授权必须刷新才生效。实际上用CREATE USER、GRANT、REVOKE、DROP USER这些语句修改权限时MySQL会立即更新权限缓存不需要手动刷新。FLUSH PRIVILEGES真正的作用是当通过直接修改mysql.user表等方式绕过权限系统改数据后强制重新加载权限表。日常授权管理用不上它。我见过有同事直接在mysql.user表里改Select_priv字段来授权改完还纳闷为什么不生效——这时候才需要FLUSH PRIVILEGES。我的建议是老老实实用标准SQL管理权限别碰系统表FLUSH PRIVILEGES也就没用了。6.2 主机匹配规则的坑localhost 和 127.0.0.1 不是一回事在MySQL的账户体系里userlocalhost和user127.0.0.1是不同的账户。因为你用mysql -h 127.0.0.1连接时TCP/IP协议栈看到的来源地址是127.0.0.1而用mysql -u user直接本地socket连接时才匹配localhost。实际运维中同一个用户名localhost和127.0.0.1各自建了一个账号结果应用连接时总报权限不足查了半天才发现匹配的是另一个账号。解决方案不复杂建号时统一指定一个主机模式比如全部用%或全部用具体IP并在文档里写明规则两个模式混着用很容易踩坑。6.3 密码过期策略会遇到所有操作都被拒绝的呆滞瞬间如果你给某个账号设了PASSWORD EXPIRE到期后用户登录MySQL时会发现除了改密码其他所有操作都会报ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.这不是用户被删了也不是权限坏了只是密码过期保护机制在起作用。处理方式也简单让用户重新设置密码即可ALTER USER zhangsanlocalhost IDENTIFIED BY NewStrongPass2024;如果想避免密码到期弄崩自动化任务给服务账号设置密码永不过期给人工账号设置定期过期两种策略分开管理会舒心很多。比如应用账号毕竟密码是写在配置文件里的定期过期就意味着你得定期去改配置、重启服务而这个过程中一旦有人忘了改配置应用就起不来晚上三四点被叫起来改密码的感觉可不好受。所以应用服务账号我基本都会设置成永不过期保密码强度足够长员工个人账号则可以设90天过期逼着定期改密。6.4 授权与存储过程、视图相关的隐藏权限最后提醒一个容易漏掉的地方存储过程、视图、触发器这些对象的执行权限不走普通的SELECT、INSERT授权体系。比如只给了一个账号某张表的SELECT但业务要调用的存储过程会往表里写数据这个账号可能因为缺EXECUTE权限而失败。GRANT EXECUTE ON PROCEDURE mydb.generate_report TO report_user10.0.2.15; GRANT SELECT ON mydb.v_user_info TO report_user10.0.2.15;搜索词里有人专门问sql创建用户并给视图权限指的就是这种场景。视图在MySQL里授权时需要显式地授权给它底层涉及的表或直接授权视图本身如果视图底层表权限不开放换一个账号去查这个视图可能会被拒绝。所以给报表类账号授权时最好把视图和它依赖的表一起梳理清楚避免权限缺口。另外存储过程中的SQL SECURITY属性也会影响权限判定。SQL SECURITY INVOKER意味着调用者需要拥有被操作对象的权限SQL SECURITY DEFINER则以定义者身份执行对调用者的权限要求低很多。如果你的调用方账号总是报权限不足而你又不想给太多底层权限试试把这个存储过程的SQL SECURITY改成DEFINER有时候一条语句就能解决问题。从我这些年的经验来看MySQL用户与授权真正难的不是那几条SQL命令而是能不能像设计系统一样去设计账号体系每个账号该有什么权限、从哪个网段来、密码多久换一次、生命周期怎么管。把这几个问题想清楚了剩下的不过是照着语法写语句的事。说实话你不需要成为一个权限管理大师但至少要建立最小权限和分层管理这两个基本意识这样无论MySQL版本怎么升级你的账号体系都不会出大乱子。
