限管理實戰(zhàn)：賬號授權(quán)、角色與最小權(quán)限排查指南)
直接切入正題。MySQL 權(quán)限管理說大不大說小不小但只要是跑過幾年數(shù)據(jù)庫的人多多少少都在權(quán)限上栽過跟頭。我剛接手某個系統(tǒng)的數(shù)據(jù)庫時光排查一個“開發(fā)賬號為什么連不上測試庫”的問題就折騰了大半天最后發(fā)現(xiàn)不是密碼錯、也不是網(wǎng)絡(luò)不通而是賬號的 Host 限定寫死了 localhost客戶端從跳板機過來自然被拒。類似這種問題用一條 SQL 就能查清楚但前提是你得先懂 MySQL 的權(quán)限體系是怎么運作的。這篇文章我打算把 MySQL 權(quán)限管理從頭到尾捋一遍。內(nèi)容包括權(quán)限表到底存了什么、賬號怎么建、授權(quán)怎么給得又準又穩(wěn)、角色怎么幫你省事以及日常最容易踩的坑和排查思路。無論你是剛?cè)腴T的新手還是已經(jīng)帶過項目的老人只要認真看完并用命令行手敲一遍后面處理權(quán)限問題基本不會慌。1. 先搞懂 MySQL 權(quán)限體系是怎么設(shè)計的很多人第一次用 MySQL習(xí)慣性地拿 root 一把梭所有操作都在 root 下完成權(quán)限對他而言只是“密碼別丟”。但只要業(yè)務(wù)稍微正規(guī)一點、賬號多幾個這套做法就撐不住了。理解權(quán)限體系首先要理解 MySQL 到底把權(quán)限存在哪、按什么順序校驗。1.1 權(quán)限數(shù)據(jù)到底存哪張表MySQL 的權(quán)限信息不是藏在某個配置文件里的而是實打?qū)嵉卮嬖谙到y(tǒng)數(shù)據(jù)庫mysql下的幾張表里。理解這幾張表后面一切排查都能順藤摸瓜。表名控制范圍典型權(quán)限示例mysql.user全局權(quán)限整個實例SELECT ON.、CREATE USER、SUPERmysql.db數(shù)據(jù)庫級別SELECT ON dbname.*mysql.tables_priv表級別SELECT ON dbname.tablenamemysql.columns_priv列級別SELECT (col1, col2) ON ...mysql.procs_priv存儲過程/函數(shù)EXECUTE ON ...權(quán)限判斷是層層疊加的一個賬號最終能不能干某件事取決于上述幾層權(quán)限的并集。注意是“并集”不是“交集”——只要任何一層有權(quán)限就算有權(quán)限。這跟很多人的直覺不一樣比如你在全局層給了某賬號對db1.*的 SELECT又在表層收回了對db1.t1的 SELECT實際效果并不是“t1 表不能被查”而是仍然可以查因為全局權(quán)限的 SELECT 依然生效。這個邏輯我用一句話總結(jié)MySQL 的授權(quán)永遠是“加權(quán)限”導(dǎo)向而不是“排除權(quán)限”導(dǎo)向。想限制某個特定范圍必須避免在更高層級授予同樣權(quán)限。生產(chǎn)上很多權(quán)限失控的案例根源就在于此。1.2 權(quán)限校驗這兩步搞懂就能定位大多數(shù)問題MySQL 對一個連接請求的權(quán)限校驗其實分兩個階段。第一階段是“連接校驗”?？蛻舳诉B上來時MySQL 根據(jù)user、host、密碼去mysql.user表里找匹配的記錄。如果找不到匹配項直接報ERROR 1045 (28000): Access denied for user ...。注意這里的host匹配規(guī)則MySQL 會按host的精確度排序比如test192.168.1.100精確匹配比test192.168.1.%優(yōu)先比test%更優(yōu)先。不是說你寫了%就一定能匹配上如果存在更精確的記錄賬號用的其實是那條精確記錄的密碼。第二階段是“請求校驗”。連接建立后每次執(zhí)行 SQLMySQL 都要檢查當前賬號是否具備對應(yīng)操作權(quán)限。比如你執(zhí)行SELECT * FROM db1.t1MySQL 會依次檢查全局層、庫層、表層。如果用的是 MySQL 的 CDB/RDS 這類云數(shù)據(jù)庫還會多一層額外的管控但底層邏輯不變。這兩個階段的區(qū)別特別重要。很多“能連上但執(zhí)行 SQL 報錯”的情況問題都出在第二階段而“完全連不上”的情況則基本集中于第一階段。1.3 權(quán)限分類管理權(quán)限、數(shù)據(jù)權(quán)限、結(jié)構(gòu)權(quán)限MySQL 里的權(quán)限類型很多初學(xué)者一看幫助文檔密密麻麻就頭大。我習(xí)慣把它們歸成三大類管理類權(quán)限CREATE USER、SUPER、PROCESS、RELOAD、SHUTDOWN、REPLICATION CLIENT、REPLICATION SLAVE、GRANT OPTION 等。這類權(quán)限影響整個實例一般只給 DBA 角色。數(shù)據(jù)類權(quán)限SELECT、INSERT、UPDATE、DELETE。這是業(yè)務(wù)賬號最常用的四件套控制對數(shù)據(jù)的讀寫。結(jié)構(gòu)類權(quán)限CREATE、ALTER、DROP、INDEX、CREATE VIEW、CREATE ROUTINE、TRIGGER、EVENT 等。這類權(quán)限能改表結(jié)構(gòu)給業(yè)務(wù)開發(fā)時要格外謹慎。MySQL 8.0 之后還引入了動態(tài)權(quán)限比如備份工具用的 BACKUP_ADMIN、查系統(tǒng)變量的 SESSION_VARIABLES_ADMIN 等。如果是從 5.7 升上來的老司機有可能會遇到“腳本執(zhí)行失敗提示缺少 XX_ADMIN 權(quán)限”的情況別慌按提示補授權(quán)即可。2. 賬號管理從創(chuàng)建到回收的全流程權(quán)限是賬號的屬性所以賬號管理是權(quán)限管理的地基。這一節(jié)我直接用命令行演示每一步都解釋一下為什么要這樣寫。2.1 創(chuàng)建賬號時的 Host 限定別天天用 %創(chuàng)建賬號的標準語法是CREATE USER report10.20.30.% IDENTIFIED BY Report2024!;這里最容易被忽略的就是后面的主機限定。很多人圖省事一律寫report%覺得“能連就行”。但在生產(chǎn)環(huán)境這個習(xí)慣非常危險。%表示任何來源 IP 都能用這個賬號嘗試登錄一旦密碼泄露攻擊者不需要內(nèi)網(wǎng)跳板機就能直接在公網(wǎng)爆破。我把實際場景給你拆開應(yīng)用服務(wù)器 IP 固定那就寫成app192.168.1.100精確到單 IP。應(yīng)用服務(wù)器是一個網(wǎng)段寫成app192.168.1.%。只有本機訪問寫成localhost或127.0.0.1。只有內(nèi)網(wǎng)運維跳板機能連寫跳板機 IP。什么情況才用%說實話如果業(yè)務(wù)實在沒法固定出口 IP比如云函數(shù)、容器調(diào)度平臺用%也能接受但同時必須要求賬號使用強密碼并開啟 SSL 連接或限制可操作范圍。2.2 查看賬號信息別只會用 SHOW DATABASES創(chuàng)建完賬號可以直接查mysql.user表確認SELECT user, host, plugin, password_last_changed, account_locked FROM mysql.user WHERE user report;plugin字段很關(guān)鍵。MySQL 8.0 默認使用caching_sha2_password如果你的客戶端或者連接驅(qū)動的版本比較老比如 Python 的某些舊版mysqlclient、Java 的舊版 Connector/J會連接失敗。解決辦法有兩個升級客戶端驅(qū)動或者把賬號的插件改回mysql_native_passwordALTER USER report10.20.30.% IDENTIFIED WITH mysql_native_password BY Report2024!;不過我這里更推薦升級驅(qū)動因為mysql_native_password是舊算法安全性不如新的。別為了兼容老客戶端而降級加密方式除非你明確知道風(fēng)險可以接受。2.3 改密碼、鎖賬號、刪賬號都有正規(guī)語法改密碼用ALTER USERALTER USER report10.20.30.% IDENTIFIED BY NewPass2024!;臨時凍結(jié)某個賬號、但不想刪用ACCOUNT LOCKALTER USER report10.20.30.% ACCOUNT LOCK;對應(yīng)解鎖ALTER USER report10.20.30.% ACCOUNT UNLOCK;刪除賬號DROP USER report10.20.30.%;這里我要提醒一個細節(jié)在處理云數(shù)據(jù)庫或管控平臺托管的 MySQL 時有些 DROP/ALTER 操作可能在控制臺被攔截需要走平臺的工單流程或者先解除平臺側(cè)的賬號保護。不要裸奔式地直接對mysql.user表執(zhí)行 UPDATE所有賬號操作請通過官方語法執(zhí)行否則容易留下臟數(shù)據(jù)也會讓后續(xù)審計困難。3. 授權(quán)與撤銷GRANT 和 REVOKE 的正確打開方式賬號建好了沒授權(quán)等于空殼。授權(quán)這一節(jié)是權(quán)限管理的核心區(qū)也是最容易誤操作的區(qū)域。3.1 GRANT 語法與授權(quán)粒度選擇先看標準語法GRANT SELECT ON db1.* TO report10.20.30.%;這條命令的意思允許report賬號在10.20.30.%網(wǎng)段下對db1庫下的所有表執(zhí)行 SELECT。按粒度從粗到細有幾種常用授權(quán)方式-- 庫級某庫全部表 GRANT SELECT, INSERT, UPDATE, DELETE ON db1.* TO report10.20.30.%; -- 表級某庫某張表 GRANT SELECT ON db1.orders TO report10.20.30.%; -- 列級只允許查某幾列 GRANT SELECT (order_id, user_name) ON db1.orders TO report10.20.30.%;列級權(quán)限我單獨說一句。它適合那種“同一張表不同角色可見列不同”的場景比如財務(wù)表普通運營只能看部分字段核心金額字段只有財務(wù)能看到。但列級權(quán)限會讓權(quán)限表膨脹排查也會復(fù)雜能不用盡量不用。還有一個GRANT OPTION權(quán)限它是授權(quán)權(quán)限也就是允許 A 用戶把自己的權(quán)限再授予別人。這個權(quán)限是雙刃劍GRANT SELECT ON db1.* TO dba% WITH GRANT OPTION;加了WITH GRANT OPTIONdba這個普通賬號就有權(quán)把 db1 的 SELECT 權(quán)限授給其他人。如果是給業(yè)務(wù)同學(xué)開賬號千萬慎用。業(yè)務(wù)賬號拿到了 GRANT OPTION理論上可以給自己加權(quán)限那就失控了。3.2 授權(quán)的邏輯是整個覆蓋面很多新手會以為后授權(quán)的語句會覆蓋先前的實際上 GRANT 是追加式的。舉例說明GRANT SELECT ON db1.* TO app192.168.1.%; GRANT UPDATE ON db1.* TO app192.168.1.%;執(zhí)行完這兩條之后app賬號同時擁有 db1 的 SELECT 和 UPDATE。第二條并不會移除第一條的權(quán)限。這一點在生產(chǎn)上非常實用。你不需要一次性把所有權(quán)限都想全可以按需求逐步追加。但要注意追加多了之后權(quán)限會像滾雪球一樣膨脹。我之前接手的一個庫某個賬號兩年下來密密麻麻攢了十幾次 GRANT 記錄最后連自己有哪些權(quán)限都說不清。所以我的習(xí)慣是每次授權(quán)前都先SHOW GRANTS看一眼當前權(quán)限現(xiàn)狀再決定是追加還是先清理。3.3 REVOKE 撤銷權(quán)限用對了才干凈撤銷和授權(quán)對應(yīng)REVOKE SELECT ON db1.* FROM app192.168.1.%;如果想撤銷某賬號在某個庫的所有權(quán)限用REVOKE ALL PRIVILEGES ON db1.* FROM app192.168.1.%;這兩個語句的執(zhí)行邏輯同樣遵循“并集”原則。如果app在全局層擁有SELECT ON *.*那你即使撤銷了 db1 庫的 SELECT它依然能查 db1 的表。排查“明明撤銷了還是能訪問”這種問題第一反應(yīng)就應(yīng)該是看看更高層級還有沒有權(quán)限殘留。查看權(quán)限的語法SHOW GRANTS FOR app192.168.1.%;輸出結(jié)果會列出該賬號在當前實例的所有生效權(quán)限。沒有匹配賬號時可以用SHOW GRANTS FOR CURRENT_USER查看自己當前連接的權(quán)限。3.4 授權(quán)后要刷新嗎這個問題我?guī)缀趺看味紩粏柕?。答案是用GRANT、REVOKE、CREATE USER、ALTER USER這些官方語句操作權(quán)限時MySQL 會實時更新內(nèi)存中的權(quán)限緩存不需要額外FLUSH PRIVILEGES。但如果你繞過官方語法直接操作mysql.user、mysql.db等表比如UPDATE mysql.user SET ...那就必須執(zhí)行FLUSH PRIVILEGES;這個命令的本質(zhì)是讓 MySQL 重新加載權(quán)限表到內(nèi)存。盡量避免用手改表的方式改權(quán)限真的出了錯排查起來很痛苦。4. 角色管理批量授權(quán)和撤銷的利器從 MySQL 8.0 開始角色Role成為權(quán)限管理的一個重要手段。角色就相當于權(quán)限的“套餐”你可以把一組權(quán)限打包成一個角色然后把這個角色賦予多個賬號。4.1 為什么需要角色直接授權(quán)也能干活為什么還要角色我用一個真實的業(yè)務(wù)場景來說明。某公司有三類賬號開發(fā)、測試、運維。開發(fā)需要只讀訪問開發(fā)庫測試需要在測試庫有讀寫權(quán)限運維需要多個數(shù)據(jù)庫的管理權(quán)限。如果不用角色每新增一個開發(fā)就要手動 GRANT 一次而且很容易給錯權(quán)限。如果用角色設(shè)定三個固定角色CREATE ROLE dev_read, test_rw, ops_admin;然后給每個角色配好權(quán)限GRANT SELECT ON dev_db.* TO dev_read; GRANT SELECT, INSERT, UPDATE, DELETE ON test_db.* TO test_rw; GRANT ALL PRIVILEGES ON *.* TO ops_admin;新來一個開發(fā)直接CREATE USER dev_zhang192.168.10.% IDENTIFIED BY Zhang2024!; GRANT dev_read TO dev_zhang192.168.10.%;以后這個同學(xué)轉(zhuǎn)崗只需要一條REVOKE dev_read FROM ...所有庫級權(quán)限一次清干凈。這就是角色的核心價值把權(quán)限的粒度從“賬號維度”提升到“角色維度”把管理復(fù)雜度從 O(N×M) 降到 O(NM)。中文環(huán)境里的角色名我建議用英文加下劃線別直接用中文避免字符集和工具兼容問題。4.2 激活角色不激活等于沒授權(quán)角色授權(quán)后有一個容易忽略的步驟激活。默認情況下新創(chuàng)建的用戶是被賦予了角色但會話里實際上沒有生效必須顯式激活。激活方式有兩種。一種是在會話里啟動角色SET ROLE dev_read;另一種是設(shè)置默認角色讓每次登錄自動生效SET DEFAULT ROLE dev_read TO dev_zhang192.168.10.%;日常使用推薦第二種讓賬號一登錄就能用角色。如果你發(fā)現(xiàn)某個賬號被賦予了角色但執(zhí)行 SQL 依然報權(quán)限不足十有八九就是沒設(shè)默認角色。查看當前賬號激活了哪些角色SELECT CURRENT_ROLE();4.3 角色與直接授權(quán)疊加時怎么算角色權(quán)限和直接授予的權(quán)限是疊加的。比如某賬號既被直接授予了SELECT ON db1.*又被授予了包含INSERT ON db1.*的角色那它最終對 db1 就是 SELECT INSERT。這帶來的一個好處是你不用把一個角色的所有權(quán)限都設(shè)計得面面俱到可以給某個賬號補少量額外權(quán)限。但同樣也有風(fēng)險角色越加越大權(quán)限逐漸膨脹。我的習(xí)慣是定期用下面的 SQL 檢查角色到底“長”成了什么樣SELECT r.user, r.host, r.default_role_all, g.grantee, g.privilege_type, g.table_schema FROM mysql.role_edges r JOIN information_schema.role_table_grants g ON g.grantee CONCAT(, r.role_name, , r.role_host, ) WHERE r.user dev_zhang AND r.host 192.168.10.%;這只是一個思路實際字段可能因版本略有不同但查information_schema的思路是正確的。權(quán)限體系越亂越要靠這種查詢來還原真相。5. 最小權(quán)限原則落地從需求到實現(xiàn)的完整路徑前面講的都是語法和機制這一節(jié)講的是方法論。權(quán)限管理做得好不好不取決于你會不會敲 GRANT而取決于你能不能設(shè)計出一套“夠用但不越界”的授權(quán)方案。5.1 最小權(quán)限的三步分析法我每次接到新的權(quán)限申請都會按三步走第一步明確“誰來用”申請人是誰、從哪些 IP 訪問、訪問頻率如何。如果是定時任務(wù)走固定跳板機如果是報表服務(wù)可能走應(yīng)用服務(wù)器的出口 IP如果是人工查詢可能走辦公網(wǎng) IP。第二步明確“用什么操作”只需要查詢就只給 SELECT需要導(dǎo)入數(shù)據(jù)加 INSERT/UPDATE/DELETE需要建臨時表再加 CREATE TEMPORARY TABLES需要執(zhí)行存儲過程加 EXECUTE。不要因為“順手”就把 DDL 也給了。第三步明確“作用到哪一層”是實例級、庫級、表級還是列級。原則上能細就細能窄就窄。但也要平衡管理成本一個庫幾十張表每張單獨授權(quán)維護就是噩夢。通常庫級授權(quán)已經(jīng)能滿足八成場景。拿一個具體例子說吧。某數(shù)據(jù)分析平臺要讀取訂單庫做統(tǒng)計賬號設(shè)計應(yīng)該是CREATE USER bi_read192.168.20.% IDENTIFIED BY Read2024!; GRANT SELECT ON orders_db.* TO bi_read192.168.20.%;這個方案比直接給個 root 或者SELECT ON *.*安全得多。既能滿足 BI 的需求又不會讓這個賬號碰其他庫。5.2 生產(chǎn)環(huán)境的賬號分類根據(jù)我的經(jīng)驗生產(chǎn)環(huán)境建議把賬號至少分成這樣幾類DBA 管理賬號少數(shù) DBA 使用具備管理權(quán)限。應(yīng)用只讀賬號只讀線上核心庫用于報表和查詢。應(yīng)用讀寫賬號業(yè)務(wù)主庫賬號具備業(yè)務(wù)表的增刪改查不給 DDL。運維腳本賬號跳板機執(zhí)行運維腳本給特定庫的臨時表權(quán)限。備份拉取賬號專供備份系統(tǒng)使用通常需要 SELECT 和 LOCK TABLES 權(quán)限。巡檢賬號用監(jiān)控工具采集性能數(shù)據(jù)需要 PROCESS、REPLICATION CLIENT、SHOW DATABASES 等只讀管理權(quán)限。不同類別賬號命名也要有區(qū)分度我常用的命名習(xí)慣是應(yīng)用名_環(huán)境_職責比如order_prod_rw、order_dev_read。一個團隊如果賬號命名混亂權(quán)限審計基本無從談起。5.3 權(quán)限過度開放的典型姿態(tài)我見過好幾例權(quán)限事故事故現(xiàn)場各有不同但根因總是出奇一致給得太寬、管得太松。第一種典型姿態(tài)是“開發(fā)環(huán)境用 ALL PRIVILEGES 甚至 GRANT OPTION”。開發(fā)環(huán)境雖然不像生產(chǎn)那樣敏感但開發(fā)環(huán)境往往是生產(chǎn)環(huán)境的鏡像一旦被拖庫或誤刪同樣影響開發(fā)進度。開發(fā)庫我建議給SELECT, INSERT, UPDATE, DELETE頂多加一個CREATE TEMPORARY TABLES。第二種典型姿態(tài)是“同一個賬號跨環(huán)境使用”。有人圖省事把dev、test、prod三個環(huán)境都用同一個賬號和密碼美其名曰統(tǒng)一管理。一旦密碼泄露所有環(huán)境一起淪陷。正確的做法是每個環(huán)境獨立賬號、獨立密碼至少保證不同環(huán)境的密碼互不相同。第三種典型姿態(tài)是“應(yīng)用賬號給了 SUPER”。SUPER 權(quán)限能執(zhí)行SET GLOBAL、終止連接、甚至改變主從復(fù)制狀態(tài)。應(yīng)用賬號完全用不上這些給了就是給自己埋雷。5.4 定期權(quán)限審計怎么做權(quán)限管理不是一次性的需要持續(xù)審計。我這里給一個很實用的巡檢 SQL找出擁有全局寫權(quán)限的賬號SELECT user, host, Select_priv, Insert_priv, Update_priv, Delete_priv, Create_priv, Drop_priv FROM mysql.user WHERE Select_privY AND (Insert_privY OR Update_privY OR Delete_privY) OR Create_privY OR Drop_privY;但這種方式略粗糙更規(guī)范的查詢是直接查information_schema.user_privilegesSELECT GRANTEE, PRIVILEGE_TYPE FROM information_schema.user_privileges WHERE PRIVILEGE_TYPE IN (SELECT,INSERT,UPDATE,DELETE,CREATE,DROP,SUPER);這個查詢能列出所有在全局層擁有數(shù)據(jù)的賬號。注意如果你用云數(shù)據(jù)庫托管有些云側(cè)賬號會帶一些特殊前綴和內(nèi)置權(quán)限不要誤刪。審計的目標不是“把所有賬號都揪出來”而是“確認每個賬號的權(quán)限來源清晰、范圍可控”。6. 常見問題與排查技巧實錄權(quán)限相關(guān)的故障是數(shù)據(jù)庫運維里出現(xiàn)頻率最高的一類。我把這些年實際遇到的、以及在社區(qū)里高頻被問的幾個問題整理一下每一條都給了排查思路。6.1 連接報 1045Access denied這是最常見的報錯。遇到之后別急著改密碼按以下順序排查確認user host是否存在查mysql.user表看是否創(chuàng)建過該賬號Host 是否匹配你當前的來源 IP。確認密碼是否正確最好用一個已知正確的客戶端測試避免被密碼里的特殊字符帶偏。確認是否鎖定查account_locked字段。確認密碼是否過期MySQL 8.0 支持密碼過期策略過期賬號連接會強制修改密碼或者在日志里看到Access denied。排查時可以用一段 SQL 模擬 MySQL 對連接的匹配SELECT user, host, plugin, account_locked, password_last_changed FROM mysql.user WHERE user 目標賬號;一般到這個步驟就能定位。6.2 有權(quán)限但還是報 1142command denied報SELECT command denied to user xxxx的時候說明賬號對目標對象沒有對應(yīng)權(quán)限。這時候用SHOW GRANTS一看就知道SHOW GRANTS FOR xxxx;如果顯示的權(quán)限里沒有你要執(zhí)行的操作那就是授權(quán)沒給到位。這里有個特別隱蔽的場景某些云數(shù)據(jù)庫或管控平臺連接時走的是特殊代理你看到的授權(quán)結(jié)果是本地實例的但代理層還可能有一層獨立權(quán)限。遇到這種請以平臺側(cè)的權(quán)限配置為準。6.3 授權(quán)之后客戶端連不上這個問題的坑在于授權(quán)語句本身沒有報錯但客戶端依然拒絕連接。原因可能有兩個一是服務(wù)端配置問題檢查bind_address和skip_networking。如果skip_networkingON即使授權(quán)正確遠程一律無法連接。二是防火墻或安全組規(guī)則問題。MySQL 默認端口 3306 只放行了一部分 IP授權(quán)再正確到不了端口也白搭。不要把這兩個問題跟權(quán)限混為一談否則排查半天只會原地打轉(zhuǎn)。6.4 忘記 root 密碼怎么辦這屬于救急操作不建議經(jīng)常用但有必要知道。以 Linux 環(huán)境為例大致流程是停止 MySQL 服務(wù)。用mysqld_safe --skip-grant-tables或修改配置文件臨時跳過認證啟動。連接后先FLUSH PRIVILEGES把權(quán)限緩存重新加載。用ALTER USER rootlocalhost IDENTIFIED BY 新密碼;重新設(shè)置密碼。正常重啟 MySQL。這里要特別強調(diào)一點--skip-grant-tables啟動的實例相當于沒有任何權(quán)限保護務(wù)必確保該操作只在可控的維護窗口、且監(jiān)聽地址限定在 127.0.0.1 時執(zhí)行。操作完立刻重啟恢復(fù)原狀。6.5 FLUSH PRIVILEGES 到底什么時候用日常用 GRANT/REVOKE 不需要手改權(quán)限表才需要。還有一種情況是你在控制臺或管理工具中直接編輯了賬號權(quán)限但工具沒有觸發(fā)權(quán)限重載此時手動FLUSH PRIVILEGES能解決問題。不過我要說句實在話如果你依賴FLUSH PRIVILEGES的頻率高到每天都要用說明你的權(quán)限管理流程可能有缺陷。正規(guī)流程下99% 的權(quán)限變更都能通過 GRANT/REVOKE/CREATE USER 完成不需要碰 FLUSH。6.6 密碼策略太弱導(dǎo)致創(chuàng)建賬號失敗MySQL 8.0 默認開啟了密碼校驗插件要求密碼必須足夠復(fù)雜。創(chuàng)建賬號時如果提示ERROR 1819 (HY000): Your password does not satisfy the current policy requirements說明密碼太弱。解決辦法是調(diào)整密碼而不是關(guān)掉校驗插件。默認中等強度要求包含數(shù)字、大小寫字母和特殊字符長度至少 8 位。如果你確實需要調(diào)整策略可以查看當前策略SHOW VARIABLES LIKE validate_password%;我建議生產(chǎn)環(huán)境保持默認策略甚至調(diào)高validate_password.length。密碼短小精悍這種想法在數(shù)據(jù)庫場景就是定時炸彈。7. 權(quán)限管理的習(xí)慣與方法論補充語法和排錯都講完了最后聊幾個我在實際工作中比較在意的點不一定在文檔里但很實用。7.1 權(quán)限變更要有“前因后果”每次授權(quán)前先記錄三件事為什么給、給誰、給多久。很多團隊最后權(quán)限亂成一鍋粥就是因為沒有一個完整的變更記錄。最簡單的做法是每次 GRANT 之前先把SHOW GRANTS的結(jié)果貼到工單里授權(quán)完成后把新結(jié)果也貼進去。出問題時一眼就能看出是哪次變更引入了問題。7.2 注意賬號密碼的時效管理MySQL 8.0 支持密碼自動過期可以在創(chuàng)建時指定CREATE USER tmp_account% IDENTIFIED BY Tmp2024! PASSWORD EXPIRE INTERVAL 90 DAY;臨時賬號設(shè)置過期時間非常有用。我曾經(jīng)遇過某對接方離職三個月后他的賬號還在服務(wù)器里結(jié)果被人用舊憑證登錄的情況。如果當時設(shè)了過期策略這個風(fēng)險早就被掐滅了。7.3 權(quán)限最小化不等于權(quán)限不可用最后說一下邊界。最小權(quán)限原則不是讓你把所有賬號都鎖死而是讓“該用的權(quán)限剛好夠用”。如果一個業(yè)務(wù)賬號確實需要跨庫查詢那就給相應(yīng)庫的只讀權(quán)限如果確實需要 DDL那就限定在某個庫內(nèi)。我自己經(jīng)歷過的最極端案例是某應(yīng)用因為缺少 DROP 權(quán)限導(dǎo)致上線腳本失敗回滾時發(fā)現(xiàn)另一個庫也缺權(quán)限。事后復(fù)盤問題不在于給少了而在于權(quán)限設(shè)計沒有提前和業(yè)務(wù)充分對齊。所以授權(quán)前多問一句“你上線會做什么操作”比事后補授權(quán)高效得多。7.4 給新人同學(xué)的實踐建議如果這些內(nèi)容你第一次接觸我建議搭一個本地虛擬機或者用云數(shù)據(jù)庫開發(fā)實例把以下流程完整走一遍創(chuàng)建三個不同 Host 限定的賬號。分別授予庫級、表級、列級權(quán)限。用SHOW GRANTS逐一核對。創(chuàng)建兩個角色分別授予不同權(quán)限然后賦給同一賬號驗證疊加效果。模擬一個權(quán)限申請場景從需求分析到授權(quán)落庫走一遍。這個過程我大概每帶一個新人都要求走一次。走完之后你再回頭看日常的權(quán)限問題會發(fā)現(xiàn)思路清晰得多。權(quán)限管理這東西說到底是“規(guī)則設(shè)計 流程執(zhí)行 持續(xù)審計”三件事。把這三件事做好MySQL 權(quán)限就能從一團亂麻變成你手里最靠譜的管控手段。 MySQL權(quán)限管理實戰(zhàn)：賬號授權(quán)、角色與最小權(quán)限排查指南
直接切入正題。MySQL 權(quán)限管理說大不大說小不小但只要是跑過幾年數(shù)據(jù)庫的人多多少少都在權(quán)限上栽過跟頭。我剛接手某個系統(tǒng)的數(shù)據(jù)庫時光排查一個“開發(fā)賬號為什么連不上測試庫”的問題就折騰了大半天最后發(fā)現(xiàn)不是密碼錯、也不是網(wǎng)絡(luò)不通而是賬號的 Host 限定寫死了 localhost客戶端從跳板機過來自然被拒。類似這種問題用一條 SQL 就能查清楚但前提是你得先懂 MySQL 的權(quán)限體系是怎么運作的。這篇文章我打算把 MySQL 權(quán)限管理從頭到尾捋一遍。內(nèi)容包括權(quán)限表到底存了什么、賬號怎么建、授權(quán)怎么給得又準又穩(wěn)、角色怎么幫你省事以及日常最容易踩的坑和排查思路。無論你是剛?cè)腴T的新手還是已經(jīng)帶過項目的老人只要認真看完并用命令行手敲一遍后面處理權(quán)限問題基本不會慌。1. 先搞懂 MySQL 權(quán)限體系是怎么設(shè)計的很多人第一次用 MySQL習(xí)慣性地拿 root 一把梭所有操作都在 root 下完成權(quán)限對他而言只是“密碼別丟”。但只要業(yè)務(wù)稍微正規(guī)一點、賬號多幾個這套做法就撐不住了。理解權(quán)限體系首先要理解 MySQL 到底把權(quán)限存在哪、按什么順序校驗。1.1 權(quán)限數(shù)據(jù)到底存哪張表MySQL 的權(quán)限信息不是藏在某個配置文件里的而是實打?qū)嵉卮嬖谙到y(tǒng)數(shù)據(jù)庫mysql下的幾張表里。理解這幾張表后面一切排查都能順藤摸瓜。表名控制范圍典型權(quán)限示例mysql.user全局權(quán)限整個實例SELECT ON.、CREATE USER、SUPERmysql.db數(shù)據(jù)庫級別SELECT ON dbname.*mysql.tables_priv表級別SELECT ON dbname.tablenamemysql.columns_priv列級別SELECT (col1, col2) ON ...mysql.procs_priv存儲過程/函數(shù)EXECUTE ON ...權(quán)限判斷是層層疊加的一個賬號最終能不能干某件事取決于上述幾層權(quán)限的并集。注意是“并集”不是“交集”——只要任何一層有權(quán)限就算有權(quán)限。這跟很多人的直覺不一樣比如你在全局層給了某賬號對db1.*的 SELECT又在表層收回了對db1.t1的 SELECT實際效果并不是“t1 表不能被查”而是仍然可以查因為全局權(quán)限的 SELECT 依然生效。這個邏輯我用一句話總結(jié)MySQL 的授權(quán)永遠是“加權(quán)限”導(dǎo)向而不是“排除權(quán)限”導(dǎo)向。想限制某個特定范圍必須避免在更高層級授予同樣權(quán)限。生產(chǎn)上很多權(quán)限失控的案例根源就在于此。1.2 權(quán)限校驗這兩步搞懂就能定位大多數(shù)問題MySQL 對一個連接請求的權(quán)限校驗其實分兩個階段。第一階段是“連接校驗”?？蛻舳诉B上來時MySQL 根據(jù)user、host、密碼去mysql.user表里找匹配的記錄。如果找不到匹配項直接報ERROR 1045 (28000): Access denied for user ...。注意這里的host匹配規(guī)則MySQL 會按host的精確度排序比如test192.168.1.100精確匹配比test192.168.1.%優(yōu)先比test%更優(yōu)先。不是說你寫了%就一定能匹配上如果存在更精確的記錄賬號用的其實是那條精確記錄的密碼。第二階段是“請求校驗”。連接建立后每次執(zhí)行 SQLMySQL 都要檢查當前賬號是否具備對應(yīng)操作權(quán)限。比如你執(zhí)行SELECT * FROM db1.t1MySQL 會依次檢查全局層、庫層、表層。如果用的是 MySQL 的 CDB/RDS 這類云數(shù)據(jù)庫還會多一層額外的管控但底層邏輯不變。這兩個階段的區(qū)別特別重要。很多“能連上但執(zhí)行 SQL 報錯”的情況問題都出在第二階段而“完全連不上”的情況則基本集中于第一階段。1.3 權(quán)限分類管理權(quán)限、數(shù)據(jù)權(quán)限、結(jié)構(gòu)權(quán)限MySQL 里的權(quán)限類型很多初學(xué)者一看幫助文檔密密麻麻就頭大。我習(xí)慣把它們歸成三大類管理類權(quán)限CREATE USER、SUPER、PROCESS、RELOAD、SHUTDOWN、REPLICATION CLIENT、REPLICATION SLAVE、GRANT OPTION 等。這類權(quán)限影響整個實例一般只給 DBA 角色。數(shù)據(jù)類權(quán)限SELECT、INSERT、UPDATE、DELETE。這是業(yè)務(wù)賬號最常用的四件套控制對數(shù)據(jù)的讀寫。結(jié)構(gòu)類權(quán)限CREATE、ALTER、DROP、INDEX、CREATE VIEW、CREATE ROUTINE、TRIGGER、EVENT 等。這類權(quán)限能改表結(jié)構(gòu)給業(yè)務(wù)開發(fā)時要格外謹慎。MySQL 8.0 之后還引入了動態(tài)權(quán)限比如備份工具用的 BACKUP_ADMIN、查系統(tǒng)變量的 SESSION_VARIABLES_ADMIN 等。如果是從 5.7 升上來的老司機有可能會遇到“腳本執(zhí)行失敗提示缺少 XX_ADMIN 權(quán)限”的情況別慌按提示補授權(quán)即可。2. 賬號管理從創(chuàng)建到回收的全流程權(quán)限是賬號的屬性所以賬號管理是權(quán)限管理的地基。這一節(jié)我直接用命令行演示每一步都解釋一下為什么要這樣寫。2.1 創(chuàng)建賬號時的 Host 限定別天天用 %創(chuàng)建賬號的標準語法是CREATE USER report10.20.30.% IDENTIFIED BY Report2024!;這里最容易被忽略的就是后面的主機限定。很多人圖省事一律寫report%覺得“能連就行”。但在生產(chǎn)環(huán)境這個習(xí)慣非常危險。%表示任何來源 IP 都能用這個賬號嘗試登錄一旦密碼泄露攻擊者不需要內(nèi)網(wǎng)跳板機就能直接在公網(wǎng)爆破。我把實際場景給你拆開應(yīng)用服務(wù)器 IP 固定那就寫成app192.168.1.100精確到單 IP。應(yīng)用服務(wù)器是一個網(wǎng)段寫成app192.168.1.%。只有本機訪問寫成localhost或127.0.0.1。只有內(nèi)網(wǎng)運維跳板機能連寫跳板機 IP。什么情況才用%說實話如果業(yè)務(wù)實在沒法固定出口 IP比如云函數(shù)、容器調(diào)度平臺用%也能接受但同時必須要求賬號使用強密碼并開啟 SSL 連接或限制可操作范圍。2.2 查看賬號信息別只會用 SHOW DATABASES創(chuàng)建完賬號可以直接查mysql.user表確認SELECT user, host, plugin, password_last_changed, account_locked FROM mysql.user WHERE user report;plugin字段很關(guān)鍵。MySQL 8.0 默認使用caching_sha2_password如果你的客戶端或者連接驅(qū)動的版本比較老比如 Python 的某些舊版mysqlclient、Java 的舊版 Connector/J會連接失敗。解決辦法有兩個升級客戶端驅(qū)動或者把賬號的插件改回mysql_native_passwordALTER USER report10.20.30.% IDENTIFIED WITH mysql_native_password BY Report2024!;不過我這里更推薦升級驅(qū)動因為mysql_native_password是舊算法安全性不如新的。別為了兼容老客戶端而降級加密方式除非你明確知道風(fēng)險可以接受。2.3 改密碼、鎖賬號、刪賬號都有正規(guī)語法改密碼用ALTER USERALTER USER report10.20.30.% IDENTIFIED BY NewPass2024!;臨時凍結(jié)某個賬號、但不想刪用ACCOUNT LOCKALTER USER report10.20.30.% ACCOUNT LOCK;對應(yīng)解鎖ALTER USER report10.20.30.% ACCOUNT UNLOCK;刪除賬號DROP USER report10.20.30.%;這里我要提醒一個細節(jié)在處理云數(shù)據(jù)庫或管控平臺托管的 MySQL 時有些 DROP/ALTER 操作可能在控制臺被攔截需要走平臺的工單流程或者先解除平臺側(cè)的賬號保護。不要裸奔式地直接對mysql.user表執(zhí)行 UPDATE所有賬號操作請通過官方語法執(zhí)行否則容易留下臟數(shù)據(jù)也會讓后續(xù)審計困難。3. 授權(quán)與撤銷GRANT 和 REVOKE 的正確打開方式賬號建好了沒授權(quán)等于空殼。授權(quán)這一節(jié)是權(quán)限管理的核心區(qū)也是最容易誤操作的區(qū)域。3.1 GRANT 語法與授權(quán)粒度選擇先看標準語法GRANT SELECT ON db1.* TO report10.20.30.%;這條命令的意思允許report賬號在10.20.30.%網(wǎng)段下對db1庫下的所有表執(zhí)行 SELECT。按粒度從粗到細有幾種常用授權(quán)方式-- 庫級某庫全部表 GRANT SELECT, INSERT, UPDATE, DELETE ON db1.* TO report10.20.30.%; -- 表級某庫某張表 GRANT SELECT ON db1.orders TO report10.20.30.%; -- 列級只允許查某幾列 GRANT SELECT (order_id, user_name) ON db1.orders TO report10.20.30.%;列級權(quán)限我單獨說一句。它適合那種“同一張表不同角色可見列不同”的場景比如財務(wù)表普通運營只能看部分字段核心金額字段只有財務(wù)能看到。但列級權(quán)限會讓權(quán)限表膨脹排查也會復(fù)雜能不用盡量不用。還有一個GRANT OPTION權(quán)限它是授權(quán)權(quán)限也就是允許 A 用戶把自己的權(quán)限再授予別人。這個權(quán)限是雙刃劍GRANT SELECT ON db1.* TO dba% WITH GRANT OPTION;加了WITH GRANT OPTIONdba這個普通賬號就有權(quán)把 db1 的 SELECT 權(quán)限授給其他人。如果是給業(yè)務(wù)同學(xué)開賬號千萬慎用。業(yè)務(wù)賬號拿到了 GRANT OPTION理論上可以給自己加權(quán)限那就失控了。3.2 授權(quán)的邏輯是整個覆蓋面很多新手會以為后授權(quán)的語句會覆蓋先前的實際上 GRANT 是追加式的。舉例說明GRANT SELECT ON db1.* TO app192.168.1.%; GRANT UPDATE ON db1.* TO app192.168.1.%;執(zhí)行完這兩條之后app賬號同時擁有 db1 的 SELECT 和 UPDATE。第二條并不會移除第一條的權(quán)限。這一點在生產(chǎn)上非常實用。你不需要一次性把所有權(quán)限都想全可以按需求逐步追加。但要注意追加多了之后權(quán)限會像滾雪球一樣膨脹。我之前接手的一個庫某個賬號兩年下來密密麻麻攢了十幾次 GRANT 記錄最后連自己有哪些權(quán)限都說不清。所以我的習(xí)慣是每次授權(quán)前都先SHOW GRANTS看一眼當前權(quán)限現(xiàn)狀再決定是追加還是先清理。3.3 REVOKE 撤銷權(quán)限用對了才干凈撤銷和授權(quán)對應(yīng)REVOKE SELECT ON db1.* FROM app192.168.1.%;如果想撤銷某賬號在某個庫的所有權(quán)限用REVOKE ALL PRIVILEGES ON db1.* FROM app192.168.1.%;這兩個語句的執(zhí)行邏輯同樣遵循“并集”原則。如果app在全局層擁有SELECT ON *.*那你即使撤銷了 db1 庫的 SELECT它依然能查 db1 的表。排查“明明撤銷了還是能訪問”這種問題第一反應(yīng)就應(yīng)該是看看更高層級還有沒有權(quán)限殘留。查看權(quán)限的語法SHOW GRANTS FOR app192.168.1.%;輸出結(jié)果會列出該賬號在當前實例的所有生效權(quán)限。沒有匹配賬號時可以用SHOW GRANTS FOR CURRENT_USER查看自己當前連接的權(quán)限。3.4 授權(quán)后要刷新嗎這個問題我?guī)缀趺看味紩粏柕?。答案是用GRANT、REVOKE、CREATE USER、ALTER USER這些官方語句操作權(quán)限時MySQL 會實時更新內(nèi)存中的權(quán)限緩存不需要額外FLUSH PRIVILEGES。但如果你繞過官方語法直接操作mysql.user、mysql.db等表比如UPDATE mysql.user SET ...那就必須執(zhí)行FLUSH PRIVILEGES;這個命令的本質(zhì)是讓 MySQL 重新加載權(quán)限表到內(nèi)存。盡量避免用手改表的方式改權(quán)限真的出了錯排查起來很痛苦。4. 角色管理批量授權(quán)和撤銷的利器從 MySQL 8.0 開始角色Role成為權(quán)限管理的一個重要手段。角色就相當于權(quán)限的“套餐”你可以把一組權(quán)限打包成一個角色然后把這個角色賦予多個賬號。4.1 為什么需要角色直接授權(quán)也能干活為什么還要角色我用一個真實的業(yè)務(wù)場景來說明。某公司有三類賬號開發(fā)、測試、運維。開發(fā)需要只讀訪問開發(fā)庫測試需要在測試庫有讀寫權(quán)限運維需要多個數(shù)據(jù)庫的管理權(quán)限。如果不用角色每新增一個開發(fā)就要手動 GRANT 一次而且很容易給錯權(quán)限。如果用角色設(shè)定三個固定角色CREATE ROLE dev_read, test_rw, ops_admin;然后給每個角色配好權(quán)限GRANT SELECT ON dev_db.* TO dev_read; GRANT SELECT, INSERT, UPDATE, DELETE ON test_db.* TO test_rw; GRANT ALL PRIVILEGES ON *.* TO ops_admin;新來一個開發(fā)直接CREATE USER dev_zhang192.168.10.% IDENTIFIED BY Zhang2024!; GRANT dev_read TO dev_zhang192.168.10.%;以后這個同學(xué)轉(zhuǎn)崗只需要一條REVOKE dev_read FROM ...所有庫級權(quán)限一次清干凈。這就是角色的核心價值把權(quán)限的粒度從“賬號維度”提升到“角色維度”把管理復(fù)雜度從 O(N×M) 降到 O(NM)。中文環(huán)境里的角色名我建議用英文加下劃線別直接用中文避免字符集和工具兼容問題。4.2 激活角色不激活等于沒授權(quán)角色授權(quán)后有一個容易忽略的步驟激活。默認情況下新創(chuàng)建的用戶是被賦予了角色但會話里實際上沒有生效必須顯式激活。激活方式有兩種。一種是在會話里啟動角色SET ROLE dev_read;另一種是設(shè)置默認角色讓每次登錄自動生效SET DEFAULT ROLE dev_read TO dev_zhang192.168.10.%;日常使用推薦第二種讓賬號一登錄就能用角色。如果你發(fā)現(xiàn)某個賬號被賦予了角色但執(zhí)行 SQL 依然報權(quán)限不足十有八九就是沒設(shè)默認角色。查看當前賬號激活了哪些角色SELECT CURRENT_ROLE();4.3 角色與直接授權(quán)疊加時怎么算角色權(quán)限和直接授予的權(quán)限是疊加的。比如某賬號既被直接授予了SELECT ON db1.*又被授予了包含INSERT ON db1.*的角色那它最終對 db1 就是 SELECT INSERT。這帶來的一個好處是你不用把一個角色的所有權(quán)限都設(shè)計得面面俱到可以給某個賬號補少量額外權(quán)限。但同樣也有風(fēng)險角色越加越大權(quán)限逐漸膨脹。我的習(xí)慣是定期用下面的 SQL 檢查角色到底“長”成了什么樣SELECT r.user, r.host, r.default_role_all, g.grantee, g.privilege_type, g.table_schema FROM mysql.role_edges r JOIN information_schema.role_table_grants g ON g.grantee CONCAT(, r.role_name, , r.role_host, ) WHERE r.user dev_zhang AND r.host 192.168.10.%;這只是一個思路實際字段可能因版本略有不同但查information_schema的思路是正確的。權(quán)限體系越亂越要靠這種查詢來還原真相。5. 最小權(quán)限原則落地從需求到實現(xiàn)的完整路徑前面講的都是語法和機制這一節(jié)講的是方法論。權(quán)限管理做得好不好不取決于你會不會敲 GRANT而取決于你能不能設(shè)計出一套“夠用但不越界”的授權(quán)方案。5.1 最小權(quán)限的三步分析法我每次接到新的權(quán)限申請都會按三步走第一步明確“誰來用”申請人是誰、從哪些 IP 訪問、訪問頻率如何。如果是定時任務(wù)走固定跳板機如果是報表服務(wù)可能走應(yīng)用服務(wù)器的出口 IP如果是人工查詢可能走辦公網(wǎng) IP。第二步明確“用什么操作”只需要查詢就只給 SELECT需要導(dǎo)入數(shù)據(jù)加 INSERT/UPDATE/DELETE需要建臨時表再加 CREATE TEMPORARY TABLES需要執(zhí)行存儲過程加 EXECUTE。不要因為“順手”就把 DDL 也給了。第三步明確“作用到哪一層”是實例級、庫級、表級還是列級。原則上能細就細能窄就窄。但也要平衡管理成本一個庫幾十張表每張單獨授權(quán)維護就是噩夢。通常庫級授權(quán)已經(jīng)能滿足八成場景。拿一個具體例子說吧。某數(shù)據(jù)分析平臺要讀取訂單庫做統(tǒng)計賬號設(shè)計應(yīng)該是CREATE USER bi_read192.168.20.% IDENTIFIED BY Read2024!; GRANT SELECT ON orders_db.* TO bi_read192.168.20.%;這個方案比直接給個 root 或者SELECT ON *.*安全得多。既能滿足 BI 的需求又不會讓這個賬號碰其他庫。5.2 生產(chǎn)環(huán)境的賬號分類根據(jù)我的經(jīng)驗生產(chǎn)環(huán)境建議把賬號至少分成這樣幾類DBA 管理賬號少數(shù) DBA 使用具備管理權(quán)限。應(yīng)用只讀賬號只讀線上核心庫用于報表和查詢。應(yīng)用讀寫賬號業(yè)務(wù)主庫賬號具備業(yè)務(wù)表的增刪改查不給 DDL。運維腳本賬號跳板機執(zhí)行運維腳本給特定庫的臨時表權(quán)限。備份拉取賬號專供備份系統(tǒng)使用通常需要 SELECT 和 LOCK TABLES 權(quán)限。巡檢賬號用監(jiān)控工具采集性能數(shù)據(jù)需要 PROCESS、REPLICATION CLIENT、SHOW DATABASES 等只讀管理權(quán)限。不同類別賬號命名也要有區(qū)分度我常用的命名習(xí)慣是應(yīng)用名_環(huán)境_職責比如order_prod_rw、order_dev_read。一個團隊如果賬號命名混亂權(quán)限審計基本無從談起。5.3 權(quán)限過度開放的典型姿態(tài)我見過好幾例權(quán)限事故事故現(xiàn)場各有不同但根因總是出奇一致給得太寬、管得太松。第一種典型姿態(tài)是“開發(fā)環(huán)境用 ALL PRIVILEGES 甚至 GRANT OPTION”。開發(fā)環(huán)境雖然不像生產(chǎn)那樣敏感但開發(fā)環(huán)境往往是生產(chǎn)環(huán)境的鏡像一旦被拖庫或誤刪同樣影響開發(fā)進度。開發(fā)庫我建議給SELECT, INSERT, UPDATE, DELETE頂多加一個CREATE TEMPORARY TABLES。第二種典型姿態(tài)是“同一個賬號跨環(huán)境使用”。有人圖省事把dev、test、prod三個環(huán)境都用同一個賬號和密碼美其名曰統(tǒng)一管理。一旦密碼泄露所有環(huán)境一起淪陷。正確的做法是每個環(huán)境獨立賬號、獨立密碼至少保證不同環(huán)境的密碼互不相同。第三種典型姿態(tài)是“應(yīng)用賬號給了 SUPER”。SUPER 權(quán)限能執(zhí)行SET GLOBAL、終止連接、甚至改變主從復(fù)制狀態(tài)。應(yīng)用賬號完全用不上這些給了就是給自己埋雷。5.4 定期權(quán)限審計怎么做權(quán)限管理不是一次性的需要持續(xù)審計。我這里給一個很實用的巡檢 SQL找出擁有全局寫權(quán)限的賬號SELECT user, host, Select_priv, Insert_priv, Update_priv, Delete_priv, Create_priv, Drop_priv FROM mysql.user WHERE Select_privY AND (Insert_privY OR Update_privY OR Delete_privY) OR Create_privY OR Drop_privY;但這種方式略粗糙更規(guī)范的查詢是直接查information_schema.user_privilegesSELECT GRANTEE, PRIVILEGE_TYPE FROM information_schema.user_privileges WHERE PRIVILEGE_TYPE IN (SELECT,INSERT,UPDATE,DELETE,CREATE,DROP,SUPER);這個查詢能列出所有在全局層擁有數(shù)據(jù)的賬號。注意如果你用云數(shù)據(jù)庫托管有些云側(cè)賬號會帶一些特殊前綴和內(nèi)置權(quán)限不要誤刪。審計的目標不是“把所有賬號都揪出來”而是“確認每個賬號的權(quán)限來源清晰、范圍可控”。6. 常見問題與排查技巧實錄權(quán)限相關(guān)的故障是數(shù)據(jù)庫運維里出現(xiàn)頻率最高的一類。我把這些年實際遇到的、以及在社區(qū)里高頻被問的幾個問題整理一下每一條都給了排查思路。6.1 連接報 1045Access denied這是最常見的報錯。遇到之后別急著改密碼按以下順序排查確認user host是否存在查mysql.user表看是否創(chuàng)建過該賬號Host 是否匹配你當前的來源 IP。確認密碼是否正確最好用一個已知正確的客戶端測試避免被密碼里的特殊字符帶偏。確認是否鎖定查account_locked字段。確認密碼是否過期MySQL 8.0 支持密碼過期策略過期賬號連接會強制修改密碼或者在日志里看到Access denied。排查時可以用一段 SQL 模擬 MySQL 對連接的匹配SELECT user, host, plugin, account_locked, password_last_changed FROM mysql.user WHERE user 目標賬號;一般到這個步驟就能定位。6.2 有權(quán)限但還是報 1142command denied報SELECT command denied to user xxxx的時候說明賬號對目標對象沒有對應(yīng)權(quán)限。這時候用SHOW GRANTS一看就知道SHOW GRANTS FOR xxxx;如果顯示的權(quán)限里沒有你要執(zhí)行的操作那就是授權(quán)沒給到位。這里有個特別隱蔽的場景某些云數(shù)據(jù)庫或管控平臺連接時走的是特殊代理你看到的授權(quán)結(jié)果是本地實例的但代理層還可能有一層獨立權(quán)限。遇到這種請以平臺側(cè)的權(quán)限配置為準。6.3 授權(quán)之后客戶端連不上這個問題的坑在于授權(quán)語句本身沒有報錯但客戶端依然拒絕連接。原因可能有兩個一是服務(wù)端配置問題檢查bind_address和skip_networking。如果skip_networkingON即使授權(quán)正確遠程一律無法連接。二是防火墻或安全組規(guī)則問題。MySQL 默認端口 3306 只放行了一部分 IP授權(quán)再正確到不了端口也白搭。不要把這兩個問題跟權(quán)限混為一談否則排查半天只會原地打轉(zhuǎn)。6.4 忘記 root 密碼怎么辦這屬于救急操作不建議經(jīng)常用但有必要知道。以 Linux 環(huán)境為例大致流程是停止 MySQL 服務(wù)。用mysqld_safe --skip-grant-tables或修改配置文件臨時跳過認證啟動。連接后先FLUSH PRIVILEGES把權(quán)限緩存重新加載。用ALTER USER rootlocalhost IDENTIFIED BY 新密碼;重新設(shè)置密碼。正常重啟 MySQL。這里要特別強調(diào)一點--skip-grant-tables啟動的實例相當于沒有任何權(quán)限保護務(wù)必確保該操作只在可控的維護窗口、且監(jiān)聽地址限定在 127.0.0.1 時執(zhí)行。操作完立刻重啟恢復(fù)原狀。6.5 FLUSH PRIVILEGES 到底什么時候用日常用 GRANT/REVOKE 不需要手改權(quán)限表才需要。還有一種情況是你在控制臺或管理工具中直接編輯了賬號權(quán)限但工具沒有觸發(fā)權(quán)限重載此時手動FLUSH PRIVILEGES能解決問題。不過我要說句實在話如果你依賴FLUSH PRIVILEGES的頻率高到每天都要用說明你的權(quán)限管理流程可能有缺陷。正規(guī)流程下99% 的權(quán)限變更都能通過 GRANT/REVOKE/CREATE USER 完成不需要碰 FLUSH。6.6 密碼策略太弱導(dǎo)致創(chuàng)建賬號失敗MySQL 8.0 默認開啟了密碼校驗插件要求密碼必須足夠復(fù)雜。創(chuàng)建賬號時如果提示ERROR 1819 (HY000): Your password does not satisfy the current policy requirements說明密碼太弱。解決辦法是調(diào)整密碼而不是關(guān)掉校驗插件。默認中等強度要求包含數(shù)字、大小寫字母和特殊字符長度至少 8 位。如果你確實需要調(diào)整策略可以查看當前策略SHOW VARIABLES LIKE validate_password%;我建議生產(chǎn)環(huán)境保持默認策略甚至調(diào)高validate_password.length。密碼短小精悍這種想法在數(shù)據(jù)庫場景就是定時炸彈。7. 權(quán)限管理的習(xí)慣與方法論補充語法和排錯都講完了最后聊幾個我在實際工作中比較在意的點不一定在文檔里但很實用。7.1 權(quán)限變更要有“前因后果”每次授權(quán)前先記錄三件事為什么給、給誰、給多久。很多團隊最后權(quán)限亂成一鍋粥就是因為沒有一個完整的變更記錄。最簡單的做法是每次 GRANT 之前先把SHOW GRANTS的結(jié)果貼到工單里授權(quán)完成后把新結(jié)果也貼進去。出問題時一眼就能看出是哪次變更引入了問題。7.2 注意賬號密碼的時效管理MySQL 8.0 支持密碼自動過期可以在創(chuàng)建時指定CREATE USER tmp_account% IDENTIFIED BY Tmp2024! PASSWORD EXPIRE INTERVAL 90 DAY;臨時賬號設(shè)置過期時間非常有用。我曾經(jīng)遇過某對接方離職三個月后他的賬號還在服務(wù)器里結(jié)果被人用舊憑證登錄的情況。如果當時設(shè)了過期策略這個風(fēng)險早就被掐滅了。7.3 權(quán)限最小化不等于權(quán)限不可用最后說一下邊界。最小權(quán)限原則不是讓你把所有賬號都鎖死而是讓“該用的權(quán)限剛好夠用”。如果一個業(yè)務(wù)賬號確實需要跨庫查詢那就給相應(yīng)庫的只讀權(quán)限如果確實需要 DDL那就限定在某個庫內(nèi)。我自己經(jīng)歷過的最極端案例是某應(yīng)用因為缺少 DROP 權(quán)限導(dǎo)致上線腳本失敗回滾時發(fā)現(xiàn)另一個庫也缺權(quán)限。事后復(fù)盤問題不在于給少了而在于權(quán)限設(shè)計沒有提前和業(yè)務(wù)充分對齊。所以授權(quán)前多問一句“你上線會做什么操作”比事后補授權(quán)高效得多。7.4 給新人同學(xué)的實踐建議如果這些內(nèi)容你第一次接觸我建議搭一個本地虛擬機或者用云數(shù)據(jù)庫開發(fā)實例把以下流程完整走一遍創(chuàng)建三個不同 Host 限定的賬號。分別授予庫級、表級、列級權(quán)限。用SHOW GRANTS逐一核對。創(chuàng)建兩個角色分別授予不同權(quán)限然后賦給同一賬號驗證疊加效果。模擬一個權(quán)限申請場景從需求分析到授權(quán)落庫走一遍。這個過程我大概每帶一個新人都要求走一次。走完之后你再回頭看日常的權(quán)限問題會發(fā)現(xiàn)思路清晰得多。權(quán)限管理這東西說到底是“規(guī)則設(shè)計 流程執(zhí)行 持續(xù)審計”三件事。把這三件事做好MySQL 權(quán)限就能從一團亂麻變成你手里最靠譜的管控手段。