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