塗抹MySQL--第5章 MySQL資料庫中的許可權體系 - 5.1談談許可權處理邏輯

    提到許可權，通常都是使用者A擁有物件B的許可權，很多朋友想必已經對此形成了思維定勢，畢竟像Oracle或SQL Server這類大型資料庫軟體中的許可權驗證，也都是如此設定，指定甲使用者擁有操作乙物件的許可權。

    而MySQL資料庫的許可權驗證在設計階段就體現的有所不同，它在這中間又加了一級維度，變成從丙處來的那個甲擁有訪問乙的許可權。可能有些同學一下子轉不過彎來，那我換一個角度來描述：甲只有從丙處連線過來，才能夠訪問物件乙。這樣對比的話，是否又跟Oracle/SQL Server這類資料庫的身份驗證機制比較相似了呢，只是如Oracle這類資料庫軟體，預設是不加丙這一層的（如果想加當然也可以支援），而在MySQL中，丙（來源）成了一個必選項，也就是說，對於MySQL資料庫，甲的身份當然重要，但甲從哪兒來的也同樣重要，即使同樣叫“甲”，從A處來和從B處來的甲的許可權可以不同，甚至應該視作是兩個不同的使用者。

    在本章正式開始前先描述這樣一段，並不是想說MySQL有多麼高階或先進，只是想表達這樣一種看法，MySQL確實有所不同。OK，接下來，跟隨三思一起，進入MySQL的許可權世界吧！

5.1  談談許可權處理邏輯

    所有許可權認證的根本目的，都是為了讓使用者只能做允許它做的事情，MySQL也不例外，大家（泛指資料庫產品）實現的原理也都差不多，只不過機制上稍有差異，在許可權粒度控制上有所不同。

    MySQL資料庫服務採用的是白名單的許可權策略，也就是說，明確指定了哪些使用者能夠做什麼，但沒法明確地指定某些使用者不能做什麼，對許可權的驗證主要是透過mysql庫下的幾個資料字典表，來實現不同粒度的許可權需求，關於這幾個字典表後面會有章節詳細介紹。這裡簡要介紹其處理邏輯，MySQL在檢查使用者連線時可以分為兩個階段。

5.1.1  能不能連線

    當使用者發出請求嘗試連線MySQL服務時，MySQL首先是檢查登入使用者的相關資訊，比如發起登入請求的主機名是否匹配、登入使用的使用者名稱或密碼是否正確，如果這一關過不去，那連線就直接被拒絕了，常見的登入失敗資訊“ERROR 1045 (28000): Access denied for user '...'”，就是在這個階段的驗證未透過丟擲的錯誤提示。

    MySQL資料庫驗證許可權有3個維度：我是誰、從哪兒來、到哪兒去（真像哲學家探討人生的終極命題呀）。這3個維度中，前兩個決定能不能連線，就是說驗證使用者的身份是否合法，本步透過之後，才會涉及能不能訪問目標物件的環節。

    在MySQL資料庫中驗證使用者，需要檢查3項值：使用者名稱、使用者密碼和來源主機，這3項資訊的正確值（建立使用者時指定），儲存在mysql庫中的user表物件內，分別對應user表物件中的user、password和host三列。如果事先看過user表中這幾列的定義，會發現MySQL的設計非常有意思，這3列居然都可以為空（注意不是NULL值），這也是某些場景裡登入MySQL資料庫不需要輸入使用者名稱或不需要輸入密碼的原因。

5.1.2  能不能執行操作

    連線到資料庫之後，能不能執行操作，比如說建庫、建表、改表，查詢或修改資料等，這個階段涉及的因素（物件）要複雜一點點，除了上面提到的mysql.user字典表起作用外，另外還有mysql.db、mysql.tables_priv、mysql.columns_priv、mysql.proc_priv（事實上在5.6.10版本以前，還有mysql.host表，不過之後版本中，host表已經明確被廢棄，其實在之前版本里它也沒什麼用，原本就是被判了死緩，現在緩期過完了，不過沒有轉為無期，而是直接執行死刑）幾個字典表來對資料庫，或針對物件甚至是物件列做更細粒度的控制。

    這些字典表雖說各有分工，但相互之間在許可權分配上還是會有一定的重合，比如說tables_priv字典表一看就知道是專門針對表物件的許可權明細，不過user表和db表中也可以授予使用者操作表物件的許可權。那麼MySQL服務是怎麼來區分這些許可權的呢？我的個人理解，總的原則仍然是按照粒度。

    比如要執行對整個資料庫服務的管理操作，那麼一定是根據user表中的記錄驗證許可權是否匹配，因為只有這個表是針對MySQL服務全域性的；如果請求某個明確的資料庫物件，比如更新某個表中記錄，那麼MySQL服務也仍然會按照粒度從粗到細的方式，先檢查user字典表中全域性的設定，找不到匹配的話，則繼續檢查db字典表這樣的方式；一旦在某個粒度匹配到合適的許可權，就允許使用者執行，否則繼續查詢更細的粒度表；如果所有的粒度濾過一遍，還是沒能匹配到合適的許可權，那麼使用者的操作就會被拒絕了。

    透過上述邏輯還可以明確一點，就是粒度控制越細，許可權驗證上的步驟就會越多，相應對效能必然會有影響，這一點在進行許可權分配時務必考慮在內。

5.1.3  許可權變更何時生效

    向使用者分配的許可權，哪些情況下會生效呢？一般來說，MySQL資料庫在啟動時就會將前面提到的幾個許可權字典表中的內容讀到記憶體裡，當有使用者連線或執行操作時，根據記憶體中的資料來檢查使用者是否有許可權執行相應的操作。

    注意，如果你讀的足夠認真並且大腦持續在進行思考，這會兒應該會產生這樣的一個疑問：如果使用者連線上資料庫後，管理員對該使用者的許可權進行了修改操作，是否即時生效呢？針對這個問題，答案是：看情況！

• 如果是透過GRANT、REVOKE、SET PASSWORD、RENAME USER等MySQL提供的命令執行修改，那麼許可權將馬上生效，因為這些命令將觸發系統重新載入授權表（GRANT TABLES）到記憶體。
• 如果是手動修改字典表方式（INSERT、UPDATE、DELETE），沒錯，MySQL中可以手動修改字典表中的記錄達到變更使用者許可權的目的，但這種情況下許可權並不會馬上生效，除非重啟MySQL服務，或者DBA主動觸發授權表的重新裝載。

    問題又來了，授權表被重新載入後，對當前已連線的客戶端又會產生哪些影響呢？具體如下：

• 表或列粒度的許可權將在客戶端下次執行操作時生效。
• 資料庫級的許可權將在客戶端執行USE db_name語句，切換資料庫時生效。
• 全域性許可權和密碼修改，對當前已連線的客戶端無效，下次連線時才會生效。