訪問許可權控制系統|全方位認識 mysql 系統庫

沃趣科技發表於2019-07-29
訪問許可權MySql

在上一期《許可權系統表|全方位認識 mysql 系統庫》中，我們針對mysql 系統庫中的許可權表做了一個簡單的認識，本期我們將在上一期的基礎上詳細介紹MySQL 的訪問許可權控制系統，下面請跟隨我們一起開始 mysql 系統庫的系統學習之旅吧。

1、訪問許可權控制系統概述

什麼是訪問許可權控制系統？

  • MySQL的mysql 系統庫提供了user、db、tables_priv、columns_priv、procs_priv、proxies_priv幾張表用於存放不同許可權範圍的使用者帳號相關的資料，這些表共同組成了MySQL 的訪問許可權系統。

  • MySQL許可權系統的主要功能是對從給定主機連線到MySQL Server的使用者進行身份驗證，並校驗該使用者在該Server中的資料庫物件訪問的許可權（如SELECT，INSERT，UPDATE和DELETE），另外，還包括管理匿名使用者訪問和授予特定的MySQL許可權的功能（如執行 LOAD DATA INFILE 語句和管理操作許可權等）。

MySQL訪問許可權系統的使用者介面由幾個SQL語句組成：如CREATE USER、GRANT和REVOKE。

在Server內部，MySQL 將許可權資訊儲存在mysql資料庫的許可權表中。MySQL Server在啟動時將這些表的內容讀入記憶體，後續針對使用者的訪問控制決策基於許可權表的記憶體副本實現。

MySQL訪問許可權系統可以確保只有被允許的(與使用者許可權匹配的)操作才能夠在Server中執行。當一個使用者連線到MySQL Server時，使用者的認證身份由"請求連線的主機名和使用者名稱"確定，MySQL使用主機名+使用者名稱的方式來識別和區分"相同主機不同使用者"和"不同主機相同使用者"發出的請求（例如：從office.example.com連線的使用者joe和從home.example.com連線的使用者joe在MySQL Server中實際上是被當作兩個不同的連線者來處理的，所以可以設定不同的密碼、不同的許可權），例如： 

root@localhost : mysql 01:03:04> show grants for test_a@'localhost';
+---------------------------------------------+
| Grants for test_a@localhost                 |
+---------------------------------------------+
| GRANT SELECT ON *.* TO 'test_a'@'localhost' |
+---------------------------------------------+
1 row in set (0.00 sec)
root@localhost : mysql 01:03:22> show grants for test_a@'%';
+---------------------------------------------+
| Grants for test_a@%                         |
+---------------------------------------------+
| GRANT SELECT, INSERT ON *.* TO 'test_a'@'%' |
+---------------------------------------------+
1 row in set (0.00 sec)

當使用者使用客戶端程式連線到MySQL Server時，MySQL的訪問控制分為如下兩個階段：

  • 階段1：Server根據身份標識(主機名+使用者名稱組成的帳號名)在MySQL 的訪問許可權控制表中查詢相關資訊，以確定需要接受或拒絕該使用者的連線(沒有查詢到就拒絕連線)，如果查詢到了使用者記錄，則校驗使用者提供的帳號密碼是否正確，如果密碼不正確則拒絕連線，這一階段報錯資訊類似為：ERROR 1045 (28000): Access denied for user 'test_a'@'localhost' (using password: YES)

  • 階段2：使用者連線成功之後，Server會檢查使用者訪問請求中的每個宣告來確定是否有足夠的許可權來執行。例如：如果嘗試從資料庫的表中查詢資料行或從資料庫中刪除表，Server將驗證該使用者否具有該表的SELECT許可權或資料庫的DROP許可權，如果無對應許可權，則這一階段的報錯資訊類似為：ERROR 1142 (42000) at line 1: UPDATE command denied to user 'test_a'@'localhost' for table 'sbtest1'

如果某使用者在已經建立連線期間，許可權發生了變更（自身修改或者其他使用者修改），那麼對於該使用者執行下一條語句時，這些許可權變更不一定會立即生效。如果未生效需要執行flush privileges;

2、MySQL 提供了哪些許可權

MySQL 提供的許可權列表如下（其中，all或者all privileges代表瞭如下列表中除了grant option許可權之外的所有許可權）： 

root@localhost : (none) 11:55:05> show privileges;
+-------------------------+--------------------------------------- 
+-------------------------------------------------------+
| Privilege               | Context                               |     Comment                                               |
+-------------------------+---------------------------------------    +-------------------------------------------------------+
| Alter                   | Tables                                |     To alter the table                                    |
| Alter routine           | Functions,Procedures                  |     To alter or drop stored functions/procedures          |
| Create                  | Databases,Tables,Indexes              |     To create new databases and tables                    |
| Create routine          | Databases                             |     To use CREATE FUNCTION/PROCEDURE                      |
| Create temporary tables | Databases                             |     To use CREATE TEMPORARY TABLE                         |
| Create view             | Tables                                |     To create new views                                   |
| Create user             | Server Admin                          |     To create new users                                   |
| Delete                  | Tables                                |     To delete existing rows                               |
| Drop                    | Databases,Tables                      |     To drop databases, tables, and views                  |
| Event                   | Server Admin                          |     To create, alter, drop and execute events             |
| Execute                 | Functions,Procedures                  |     To execute stored routines                            |
| File                    | File access on server                 |     To read and write files on the server                 |
| Grant option            | Databases,Tables,Functions,Procedures |     To give to other users those privileges you possess   |
| Index                   | Tables                                |     To create or drop indexes                             |
| Insert                  | Tables                                |     To insert data into tables                            |
| Lock tables             | Databases                             |     To use LOCK TABLES (together with SELECT privilege)   |
| Process                 | Server Admin                          |     To view the plain text of currently executing queries |
| Proxy                   | Server Admin                          |     To make proxy user possible                           |
| References              | Databases,Tables                      |     To have references on tables                          |
| Reload                  | Server Admin                          |     To reload or refresh tables, logs and privileges      |
| Replication client      | Server Admin                          |     To ask where the slave or master servers are          |
| Replication slave       | Server Admin                          |     To read binary log events from the master             |
| Select                  | Tables                                |     To retrieve rows from table                           |
| Show databases          | Server Admin                          |     To see all databases with SHOW DATABASES              |
| Show view               | Tables                                |     To see views with SHOW CREATE VIEW                    |
| Shutdown                | Server Admin                          |     To shut down the server                               |
| Super                   | Server Admin                          |     To use KILL thread, SET GLOBAL, CHANGE MASTER, etc.   |
| Trigger                 | Tables                                |     To use triggers                                       |
| Create tablespace       | Server Admin                          |     To create/alter/drop tablespaces                      |
| Update                  | Tables                                |     To update existing rows                               |
| Usage                   | Server Admin                          |     No privileges - allow connect only                    |
+-------------------------+---------------------------------------    +-------------------------------------------------------+
31 rows in set (0.00 sec)

以上許可權列表中，Context欄位顯示了該許可權的一個使用環境(或者說是許可權的作用域)，根據Context的不同，分為如下三類：

  • 管理許可權：用於管理MySQL Server的操作。這些許可權是全域性性的，不能授予給特定的資料庫或資料庫物件（只能使用 . 方式授予） 。

    * Create user 
    * Event 
    * Process 
    * Proxy 
    * Reload 
    * Replication client 
    * Replication slave 
    * Show databases 
    * Shutdown 
    * Super 
    * Create tablespace 
    * Usage 
    * Grant option

  • 資料庫級別許可權：用於授予某資料庫及其資料庫中的所有物件的許可權。這些許可權可以被授予特定的資料庫，也可以授予所有資料庫(可以使用 . ，代表全域性物件，也可以使用db.*，代表某庫下的所有物件) 。

    * Create 
    * Create routine 
    * Create temporary tables 
    * Drop 
    * Lock tables 
    * References

  • 資料庫物件級別許可權： 可為資料庫中的特定物件、資料庫內給定型別的物件授予許可權，也可以授予所有資料庫( . ，代表全域性物件，db.*代表某庫下的所有物件，db.tb代表某庫下某物件) 。

    * Alter 
    * Alter routine 
    * Create view 
    * Delete 
    * Execute 
    * File 
    * Index 
    * Insert 
    * Select 
    * Show view 
    * Trigger 
    * Update

通常，按照我們使用經驗還可以按照如下方式劃分。

  • 開發許可權

    * DELETE 
    * INSERT 
    * SELECT 
    * UPDATE 
    * ALTER 
    * CREATE TEMPORARY TABLES 
    * TRIGGER 
    * CREATE VIEW 
    * SHOW VIEW 
    * ALTER ROUTINE 
    * CREATE ROUTINE 
    * EXECUTE 
    * INDEX 
    * EVENT

  • 管理許可權-表級別(這裡把帶表級別的管理命令都歸類為表級別) 
    * CREATE 
    * FILE 
    * DROP 
    * LOCK TABLES

  • 管理許可權-server級別 
    * GRANT OPTION 
    * CREATE TABLESPACE 
    * CREATE USER 
    * PROCESS 
    * PROXY 
    * RELOAD 
    * REPLICATION CLIENT 
    * REPLICATION SLAVE 
    * SHOW DATABASES 
    * SHUTDOWN 
    * SUPER 
    * USAGE 
    * ALL [PRIVILEGES]

下面，我們挨個解釋每個許可權的作用。

  • ALL或ALL PRIVILEGES：除了grant option之外，其他所有許可權的簡寫方式

  • ALTER：該許可權用於使用ALTER TABLE語句來更改表的結構（ALTER TABLE語句除了該許可權之外還需要CREATE和INSERT許可權。ALTER TABLE RENAME語句需要舊錶上的ALTER和DROP許可權，新表上的CREATE和INSERT許可權）。

  • ALTER ROUTINE：該許可權用於修改或刪除儲存過程或儲存函式。

  • CREATE：該許可權用於建立庫和表。

  • CREATE ROUTINE：該許可權用於建立儲存過程或函式。

  • CREATE TABLESPACE：該許可權用於建立、修改、刪除表空間檔案和日誌組檔案。

  • CREATE TEMPORARY TABLES：該許可權用於建立臨時表，使用CREATE TEMPORARY TABLE語句建立臨時表，一旦某會話建立臨時表成功後，Server不會在該表上執行進一步的許可權檢查。即，建立該臨時表的會話可以該臨時表執行任何操作，例如：DROP TABLE、INSERT、UPDATE、SELECT等操作。

  • CREATE USER：該許可權用於使用ALTER USER、CREATE USER、DROP USER、RENAME USER、REVOKE ALL PRIVILEGES語句。

  • CREATE VIEW：該許可權用於使用CREATE VIEW語句。

  • DELETE：該許可權用於從資料庫表中刪除資料記錄。

  • DROP：該許可權使用者刪除現有庫、或表、或檢視等物件。另外，如果在分割槽表上使用ALTER TABLE ... DROP PARTITION語句，必須要有表的DROP許可權，要執行TRUNCATE TABLE也需要DROP許可權(但要注意，如果將MySQL資料庫的DROP許可權授予給使用者，則該使用者可以刪除儲存MySQL訪問權記錄的資料庫mysql）。

  • EVENT：該選項用於建立、更改、刪除或檢視Event Scheduler事件。

  • EXECUTE：該許可權用於執行儲存過過程或函式。

  • FILE：該許可權用於執行LOAD DATA INFILE和SELECT ... INTO OUTFILE語句以及LOAD_FILE（）函式來讀取和寫入Server主機上的檔案。具有FILE許可權的使用者可以讀取Server主機上任何可讀或MySQL Server可讀的檔案。（即，使用者可讀取datadir目錄中的任何檔案），FILE許可權還使使用者能夠在MySQL Server有寫入許可權的任何目錄下建立新檔案。所以，作為安全保護措施，Server不會覆蓋現有檔案(即執行匯出資料到文字時，如果檔名重疊則匯出語句無法成執行)。在MySQL 5.7版本中，可以使用secure_file_priv系統變數限制FILE許可權的讀寫目錄。

  • GRANT OPTION：該許可權用於授予或回收其他使用者或自己擁有的許可權。

  • INDEX：該許可權用於建立或刪除索引。INDEX許可權適用於在已存在的表上使用CREATE INDEX語句，如果使用者具有CREATE許可權，則可以在CREATE TABLE語句中包含索引定義語句。

  • INSERT：該許可權用於向表中插入資料記錄行。對於ANALYZE TABLE、OPTIMIZE TABLE和REPAIR TABLE表維護語句也需要INSERT許可權。

  • LOCK TABLES：該許可權用於LOCK TABLES語句對錶顯式加鎖，持有表鎖的使用者對該表有讀寫許可權，未持有表鎖的使用者對錶的讀寫訪問會被阻塞。

  • PROCESS：該許可權用於顯示有關在Server內執行的執行緒資訊（即關於會話正在執行的語句相關狀態資訊）。擁有該許可權的使用者在使用SHOW PROCESSLIST語句或mysqladmin processlist命令檢視有關執行緒資訊時除了自己的執行緒資訊之外還可以檢視到屬於其他帳號的執行緒資訊。另外，使用SHOW ENGINE語句以及檢視information_schema 資料字典庫中的相當一部分表也需要該許可權。

  • PROXY：該許可權使使用者能夠模仿另一個使用者。

  • REFERENCES：該許可權在建立外來鍵約束時，需要使用者具有父表的REFERENCES許可權。

  • RELOAD：該許可權允許使用者使用FLUSH語句。擁有該許可權的使用者還可以使用與FLUSH操作等效的mysqladmin子命令：flush-hosts，flush-logs，flush-privileges，flush-status，flush-tables，flush-threads，refresh和reload
    * mysqladmin的reload子命令會通知Server將許可權表重新載入到記憶體中。flush-privileges作用與reload子命令作用相同。refresh子命令會通知Server關閉並重新開啟日誌檔案並重新整理所有表。類似地，其他flush-xxx子命令也會執行類似於重新整理的功能，這些子命令重新整理的物件更具體，例如：只想重新整理日誌檔案則使用flush-logs子命令。

  • REPLICATION CLIENT：該許可權用於使用SHOW MASTER STATUS、SHOW SLAVE STATUS和SHOW BINARY LOGS語句。

  • REPLICATION SLAVE：該許可權用於從庫伺服器連線到主庫伺服器並請求主庫binlog 日誌。如果沒有此許可權，從庫將無法請求主庫資料庫變更的binlog日誌。

  • SELECT：該許可權用於從資料庫表中查詢資料行記錄。SELECT語句只有在它們實際從表中檢索行記錄時才需要SELECT許可權。但某些SELECT語句不需要訪問表，並且可以在沒有任何資料庫許可權的情況下執行。例如，使用SELECT語句拼接的常量表示式：SELECT 1 + 1; SELECT PI()* 2; 
    * 另外，當使用UPDATE或DELETE語句時使用where子句指定了某列的條件值時，也需要該列的SELECT許可權。否則，你會發現可以update不帶where子句更新全表，卻不能使用where語句指定更新某些行記錄 。
    * 對基表或檢視使用EXPLAIN語句也需要使用者對錶或檢視具有該許可權。

  • SHOW DATABASES：該許可權用於執行SHOW DATABASE語句，若沒有此許可權的帳戶，則只能看到他們具有對應訪問許可權的資料庫列表，如果Server使用了--skip-show-database選項啟動，則沒有該許可權的使用者即使對某庫有其他訪問許可權也不能使用SHOW DATABASES語句檢視任何資料庫列表(會報：ERROR 1227 (42000): Access denied; you need (at least one of) the SHOW DATABASES privilege(s) for this operation)

  • SHOW VIEW：該許可權用於執行SHOW CREATE VIEW語句。對檢視使用EXPLAIN語句也需要此許可權。

  • SHUTDOWN：該許可權用於執行SHUTDOWN語句、mysqladmin shutdown命令和mysql_shutdown() C API函式。

  • SUPER：該許可權用於如下這些操作和Server行為： 
    * 修改全域性系統配置變數需要此許可權。對於某些系統變數，修改會話級別的系統配置變數也需要SUPER許可權(如果修改會話級別的系統配置變數值需要SUPER許可權的，在變數的解釋文件中會進行說明，例如：binlog_format、sql_log_bin和sql_log_off） 
    * 對全域性事務特徵的更改(start transaction語句) 。
    * 從庫伺服器用於執行啟動和停止複製的語句，包括組複製 。
    * 從庫伺服器用於執行使用CHANGE MASTER TO和CHANGE REPLICATION FILTER語句 。
    * 執行PURGE BINARY LOGS和BINLOG語句 。
    * 如果檢視或儲存程式定義了DEFINER屬性，則擁有SUPER許可權的使用者就算不是該檢視或儲存程式的建立者，仍然可以執行該檢視或儲存程式 。
    * 執行CREATE SERVER、ALTER SERVER和DROP SERVER語句 。
    * 執行mysqladmin debug命令 。
    * 用於InnoDB key自旋 。
    * 用於執行透過DES_ENCRYPT()函式啟用讀取DES金鑰檔案 。
    * 用於執行使用者自定義函式時啟用版本令牌 。
    * 對於超過了最大連線數之後具有SUPER的帳戶還可以的操作 。
    * 1)、使用KILL語句或mysqladmin kill命令來終止屬於其他帳戶的執行緒。（注意：無論是否擁有SUPER許可權，使用者總是可以kill自己的執行緒） 
    * 2)、即使Server總連線數達到max_connections系統變數定義的值，Server 也會接受來自具有SUPER許可權的使用者一個額外的連線 。
    * 3)、即使Server啟用了read_only系統變數，具有SUPER許可權的使用者仍然可以執行資料更新。另外，還有帳戶管理語句GRANT和REVOKE等 。
    * 4)、SUPER客戶端連線Server時，Server不執行init_connect系統變數指定的內容 。
    * 5)、處於離線模式（已啟用offline_mode系統變數）的Server不會中斷具有SUPER許可權使用者的連線，且仍然接收具有SUPER許可權使用者的新連線請求 。
    * 如果啟用了二進位制日誌記錄功能，則使用者可能還需要SUPER許可權才能建立或更改儲存的功能。

  • TRIGGER：該許可權用於觸發器的操作。您必須擁有某表的該許可權才能針對該表建立、刪除、執行或檢視該表的觸發器。

  • UPDATE：該許可權用於執行對資料庫表中的資料行更新操作。

  • USAGE：該許可權代表使用者“無任何許可權”。全域性級別許可權，擁有該許可權的使用者可以登入到資料庫Server中，但預設配置下除了能夠執行部分show命令之外，其他任何資料變更和資料庫查詢的操作都無法執行。

  • 向使用者只授予使用者需要的許可權，不要授予額外的多餘的，特別是管理許可權，如下： 
    * FILE：該許可權用於將任何檔案讀入資料庫表中，MySQL Server可以在Server主機上讀取任何檔案。包括Server資料目錄中所有可讀檔案。然後可以使用SELECT訪問該匯入資料的表，將其讀取表中的內容返回給客戶端 。
    * GRANT OPTION：該許可權用於執行將許可權授予其他使用者 。
    * ALTER：該許可權用於修改表定義，重新命名錶等操作 。
    * SHUTDOWN：該許可權用於終止Server伺服器，如果被濫用可被用於關閉Server來達到拒絕服務的目的 。
    * PROCESS：該許可權可用於檢視當前正在執行的語句的純文字，包括設定或更改密碼的語句文字 。
    * SUPER：該許可權可用於終止其他使用者會話或更改伺服器的執行方式。詳見上述SUPER解釋項。

3、 帳號命名規則

MySQL的帳戶由使用者名稱和主機名兩部分組成(例如：user_name@host_name)。採用這種方式Server就可以區分相同使用者來自不同主機的連線，本小節將介紹如何編寫有效的帳戶名稱(包括特殊值和萬用字元規則)，對於使用SQL語句CREATE USER、GRANT和SET PASSWORD來操作使用者的，都遵循以下規則：

  • 帳戶名稱構成語法：'user_name'@'host_name'

  • 僅由使用者名稱組成的帳戶名相當於'user_name'@'％'。例如：'me'相當於'me'@'％'

  • 如果使用者名稱稱和主機名稱的字串是合法的非引用識別符號(即，不包含sql中的關鍵字或命令字)，則不需要使用反撇進行引用。如果使用者名稱或主機名的字串中包含特殊字元(如空格或者- 符號)或者萬用字元(如：點號或者%號)，則需要使用單引號或者雙引號引起來，例如：'test-user'@'%.com'(注意:一旦使用引號,注意'me@localhost'和'me'@'localhost'的含義是不同的，'me@localhost' 實際上在使用的時候，MySQL是解析為'me@localhost'@'%' ，而不是'me'@'localhost'），如果使用者名稱或主機名不包含引用字元或特殊字元等，可不需要使用分撇和引號，但為了規範起見，建議至少對主機名和使用者名稱使用引號，例如：'me'@'localhost'

  • 對CURRENT_USER關鍵字和CURRENT_USER()函式在查詢語句中的效果相同，例如：select current_user; 和 select current_user(); 兩個語句的查詢結果相同，都是返回當前連線的帳號名。

MySQL 中的帳號名在mysql系統字典庫中的許可權表user 表中儲存時，會將user_name和host_name分開儲存在user和host兩列中：

  • user表中儲存的帳號資訊中，每個帳號包含一行記錄。user和host列儲存帳號對應的使用者名稱和主機名，其他列儲存了帳號對應的許可權和帳號的其他屬性資訊。

  • 其他許可權表儲存著帳戶對例項中的庫級別，表級別，列級別等許可權資訊。這些表與user表一樣，也使用user和host列來分別儲存帳號對應使用者名稱和主機名。這些表儲存著不同許可權作用域的許可權資訊等(例如：db、columns_priv、procs_priv、proxies_priv、tables_priv，但這些表中並不儲存密碼資訊)。

  • 為了進行帳號的訪問檢查，使用者名稱嚴格區分大小寫，但主機名不區分大小寫。

關於帳號使用者名稱和主機名中某些特殊值或萬用字元約定，如下：

  • 預設情況下，user表中儲存著一些匿名帳號，所以，預設情況下MySQL允許匿名帳號連線(即，user_name為空的帳號，但使用匿名帳號需要使用引號，如：''@ localhost')。

  • 帳戶名稱中的host_name部分可以使用多種形式，並且允許使用萬用字元，如下： 

    * 主機名字串可以是域名或作業系統主機名（需要DNS解析服務），也可以是IP地址（IPv4或IPv6）。對於域名，'localhost'表示本地主機，“127.0.0.1”表示IPv4的環回介面，':: 1'表示IPv6的環回介面 。

    • * 主機名或IP地址值中都允許使用萬用字元％和_。這些與LIKE運算子中的萬用字元含義相同。例如，“％”表示匹配任意主機名，而“％.mysql.com”表示匹配mysql.com域中的任何主機，'192.51.100.％'表示匹配C類私有網路192.51.100中的任意主機，由於主機名允許使用IP+萬用字元值（例如：“192.51.100.％”匹配192.51.100子網上的任意主機），為了阻止有人透過192.51.100.somewhere.com 格式的主機名字串構造來嘗試掃描存活主機，MySQL不會在以數字和點開頭的主機名上執行匹配動作。例如：如果主機名部分為1.2.example.com，則直接被MySQL忽略， IP地址只能使用萬用字元組合，而不能與主機名進行組合，否則也會被忽略 。
    * 對於指定為IPv4地址的主機名，可以結合子網掩碼來控制子網IP數量(注意：子網掩碼不使用IPV6)，格式：host_ip/netmask。例如：CREATE USER 'david'@'192.51.100.0/255.255.255.0';，表示使用者名稱為david，主機名為192.51.100.0子網下的任意主機，滿足此條件的客戶端主機IP地址範圍從192.51.100.0到192.51.100.255。

  • 對於 MySQL帳號名的主機名 部分為IP時，子網掩碼ABC網路都支援，例如：

  • 192.0.0.0/255.0.0.0：掩碼8位，表示192 A類網路上的任何主機。

  • 192.51.100.0/255.255.0.0：掩碼16位，表示192.51 B類網路上的任何主機。

  • 192.51.100.0/255.255.255.0：掩碼24位，表示192.51.100 C類網路上的任何主機。

  • 192.51.100.1：不帶掩碼，表示僅匹配具有此特定IP地址的主機。

MySQL Server使用DNS解析時，需要注意以下問題：

  • 假設本地網路上的主機具有host1.example.com的完全限定名稱（DNS地址）。如果DNS將此主機解析為host1.example.com返回，則在MySQL帳號名的主機名部分也需要使用host1.example.com，如果DNS解析僅返回為host1，則在MySQL帳號名的主機名部分也需要使用host1，否則會被拒絕連線。

  • 如果DNS返回的是IP地址192.51.100.2，那麼它將優先進行IP地址的精確匹配，然後匹配對應網路的萬用字元，但不匹配非法的IP地址(例如：192.51.100.2)或子網(例如：192.51.100.％）。

4、MySQL 帳號訪問控制兩階段

4.1. 第一階段(帳號和密碼認證)

當您嘗試連線MySQL Server時，Server根據如下條件來決定是否需要接受或拒絕連線：

  • 您的身份資訊（帳號名，由user_name@host_name格式組成）以及密碼資訊是否可以驗證透過。

  • 你的帳戶是否處於鎖定狀態。

當MySQL Server接收到一個新的連線請求時，Server首先檢查使用者憑證(帳號+密碼)，然後檢查帳戶的鎖定狀態。任意一個步驟檢查失敗則拒絕連線發訪問。如果兩個步驟都透過檢查，則進入第2階段並等待執行請求。

  • MySQL Server 使用user表中的Host、User、authentication_string三個列儲存的使用者憑證資訊來執行憑證檢查。 使用者的鎖定狀態記錄在user表的account_locked列中。 如下： 

root@localhost : (none) 12:43:38> select     host,user,authentication_string,account_locked from mysql.user;
+-----------+---------------+----------------------------------------    ---+----------------+
| host      | user          | authentication_string                         | account_locked |
+-----------+---------------+----------------------------------------    ---+----------------+
| localhost | root          |     *3B3D7D2FD587C29C730F36CD52B4BA8CCF4C744F | N              |
| localhost | mysql.session |     *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | Y              |
| localhost | mysql.sys     |     *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | Y              |
| %         | admin         |     *3B3D7D2FD587C29C730F36CD52B4BA8CCF4C744F | N              |
| %         | repl          |     *3B3D7D2FD587C29C730F36CD52B4BA8CCF4C744F | N              |
| %         | qbench        |     *1966B10B87AA6A1F8E1215A1C81DDD5FBBA6B0D0 | N              |
| %         | program       |     *3B3D7D2FD587C29C730F36CD52B4BA8CCF4C744F | N              |
+-----------+---------------+----------------------------------------    ---+----------------+
7 rows in set (0.00 sec)
# 帳戶鎖定狀態可以透過ALTER USER語句進行更改
ALTER USER [IF EXISTS]
    user [auth_option] [, user [auth_option]] ...
    [REQUIRE {NONE | tls_option [[AND] tls_option] ...}]
    [WITH resource_option [resource_option] ...]
    [password_option | lock_option] ...
......
lock_option: {
    ACCOUNT LOCK
  | ACCOUNT UNLOCK
}

上文中提到過，使用者的身份資訊基於兩部分組成（user_name和host_name），對於身份資訊的兩個組成部分，有如下認證規則：

  • 如果使用者名稱列值不為空，則使用者嘗試連線時就必須傳入使用者名稱字串，且必須完全匹配，如果使用者名稱列值為空，則為空的列值在進行認證時，可以匹配任何使用者名稱（包括使用者名稱稱為空和不為空的，為空的使用者名稱被稱為匿名使用者）。在帳號訪問控制的第一階段匹配到匿名使用者的，在第二階段認證仍然會使用匿名使用者。

  • 如果密碼資訊列authentication_string列為空，則意味著使用者嘗試連線Server時不需要輸入密碼(注意：密碼資訊列與帳號名稱列不同，密碼資訊列為空時，只能匹配空串的密碼，不能匹配任意密碼)。如果Server使用了認證外掛對客戶端進行身份驗證，則外掛實現的身份驗證方法中可能會，也可能不會使用authentication_string密碼資訊列中的密碼字串。甚至可能還會使用外部密碼認證伺服器對MySQL Server進行身份驗證。

  • user表中非空的authentication_string列值表示加密過的密碼字串(hash加密)。MySQL在authentication_string列中不儲存明文格式的密碼（使用帳戶認證外掛實現的密碼雜湊方法加密）。在連線認證過程中使用加密的密碼來檢查密碼是否正確。從MySQL的角度來看，加密的密碼才是真正的密碼，所以，非授權情況下，不要隨意讓別人知道你的密碼資訊，特別是對mysql庫的訪問許可權。

下表列舉了一些user_name和host_name常用的組合：

  • 'fred'@'h1.example.net'：表示使用fred使用者從h1.example.net主機連線。

  • ''@'h1.example.net'：表示任何使用者從h1.example.net主機連線。

  • 'fred'@'％'：表示fredon過戶從任何主機連線。

  • ''@'％'：表示任何使用者從任何主機連線。

  • 'fred'%'％.example.net'：表示fred使用者從example.net域中的任何主機連線。

  • 'fred'@'x.example.％'：表示fred使用者從x.example.net，x.example.com，x.example.edu任意域名字尾的主機連線(但字尾%限制可能不生效)。

  • 'fred'@'192.51.100.177'：表示fred使用者從IP地址為192.51.100.177的主機連線。

  • 'fred'@'192.51.100.％'：表示fred使用者從192.51.100 C類子網中的任何主機連線。

  • 'fred'@'192.51.100.0/255.255.255.0'：表示fred使用者從192.51.100 C類子網中的任何主機連線。

客戶端傳入Server中的身份標識(主機名和使用者名稱)可能與使用者表中的多個行記錄匹配成功。當一個使用者嘗試連線Server時，如果在Server的user表中匹配到多個行記錄的身份認證資訊，則Server必須確定要能夠確定使用哪一行記錄進行許可（不同的身份資訊行記錄可能對應著不同的許可權）：

  • Server 只要將user表讀入記憶體，即就會在記憶體中對使用者資訊進行排序。

  • 當客戶端嘗試連線時，Server 會按照記憶體中排好序的內容依次進行匹配。

  • Server 使用與客戶端主機名和使用者名稱相匹配的第一行進行授權。

Server 使用的排序規則中，先排序主機列值（越精確的值越靠前，字串主機名和IP地址是最具體的，另外，IP地址的精確性不會受到掩碼的影響，例如：192.51.100.13和192.51.100.0/255.255.255.0被視為具有相同的精確度。萬用字元'％'表示“任何主機”，被視為精確度較差的主機名。空字串“'也意味著”任何主機“，但精確度比'％'更差，所以排序在'%'之後）。然後再按照使用者列值進行排序（排序規則跟主機列值類似），host和user兩列的排序規則有點類似與多列索引中的排序規則。

示例一：假設使用者表中記錄的內容如下所示： 

+-----------+----------+-
| Host      | User     | ...
+-----------+----------+-
| %         | root     | ...
| %         | jeffrey  | ...
| localhost | root     | ...
| localhost |          | ...
+-----------+----------+-
# 當Server將表中的內容讀入記憶體時，會使用剛剛描述的規則在記憶體中對使用者資訊行進行排序。排序後的結果如下所示：
+-----------+----------+-
| Host      | User     | ...
+-----------+----------+-
| localhost | root     | ...
| localhost |          | ...
| %         | jeffrey  | ...
| %         | root     | ...
+-----------+----------+-
# 當客戶端嘗試連線時，Server 會檢視在記憶體中已排好序的使用者身份認證資訊，並使用第一個匹配項進行許可。如：對於使用者jeffrey的localhost的主機連線，首先，精確匹配localhost主機列，有兩列匹配，然後再匹配使用者名稱列，也有兩列(空值和jeffrey)，兩列交集最終確定匹配行為：host=localhost,user=''，即''@'localhost'身份

示例二： 假設使用者表中記錄的資訊如下所示： 

+----------------+----------+-
| Host           | User     | ...
+----------------+----------+-
| %              | jeffrey  | ...
| h1.example.net |          | ...
+----------------+----------+-
# 在記憶體中排序之後的內容如下所示：
+----------------+----------+-
| Host           | User     | ...
+----------------+----------+-
| h1.example.net |          | ...
| %              | jeffrey  | ...
+----------------+----------+-
# 來自h1.example.net主機的jeffrey使用者的連線與第一行記錄匹配成功，而來自任何主機的jeffrey使用者的連線與第二行匹配成功

注意：

  • 透過上述示例可知，當存在匿名使用者的時候，如果您能夠成功連線到伺服器，但您的許可權可能不符合您的期望，那麼表示您此時可能正在透過其他帳戶進行身份驗證。 可以使用select current_user();或者select current_user;語句來檢查你當前登入成功的帳號身份資訊是什麼？ 以便確定是否正確對應了許可權資訊，如下： 

mysql> SELECT CURRENT_USER();
+----------------+
| CURRENT_USER() |
+----------------+
| @localhost     |
+----------------+

4.2. 第二階段(許可權檢查)

當客戶端與MySQL Server 建立連線之後，Server 進入許可權訪問控制的第2階段。在第2階段中，客戶端傳送給服務端的每個請求，服務端都會檢查請求操作的型別，然後檢查是否有足夠的訪問許可權來執行請求操作。該檢查工作依賴於mysql schema下的user、db、tables_priv、columns_priv、procs_priv、proxies_priv許可權表中存放的許可權資訊。

user：該表中的許可權作用範圍是全域性的，所以該表中相應許可權型別列值為'Y'時，就表示表示對資料庫例項中的所有資料庫表都有該許可權，所以，在大多數時候，我們需要根據具體的業務環境需求來給定需要訪問的資料庫對應的許可權，而不是投方便直接給所有庫所有表的許可權（關於如何給定許可權，請參考上文提到的許可權分類）。

  • user列為空時表示匿名使用者，非空值必須匹配字串字面本身表示的使用者名稱，使用者名稱不能使用萬用字元。

  • host列值不允許為空(雖然授權語句和建立使用者的語句可以只寫使用者名稱而不寫主機名，但實際上儲存在表中時會被轉換為%)，但可以使用萬用字元（%和_： %表示任意主機，_表示主機名中的任意一個字元），可以使用like關鍵字來配合萬用字元進行匹配。

db：該表中的許可權作用範圍是資料庫級別，對應資料庫內的所有物件：

  • user列和host列的表現形式要求與user表相同。

  • PS： 與user表類似，Server會在啟動時就將db表中的內容讀入記憶體，並在記憶體中進行排序，根據Host，Db和User 三列對db表中的資料進行排序。 排序會將最具體的值放在最前面，將最不具體的值放到最後，當Server進行使用者匹配查詢時，會使用第一個匹配行進行許可。

tables_priv，columns_priv和procs_priv：這三張表中記錄著表級別許可權、列級別許可權、執行緒級別許可權：

  • user列和host列的表現形式要求與user表相同。

  • Db，Table_name，Column_name和Routine_name列不能包含萬用字元或為空值。

  • PS： 與user表類似，Server會在啟動時就將db表中的內容讀入記憶體，並在記憶體中進行排序，根據Host，Db和User三列對tables_priv，columns_priv和procs_priv表資料進行排序。

當一個客戶端連線在進行第二階段許可權驗證時，首先檢查user表，如果所檢查許可權是user表特有的(其他許可權表沒有的許可權類別)，則user表中允許執行則Server 授予客戶端訪問許可權，否則直接拒絕而不會繼續檢查其他許可權表(因為其他許可權表不具備該許可權列表，無需檢查)，如果所檢查許可權類別除了在user表之外，在其他許可權表中也具有該許可權類別(例如：DML許可權)，則即時在user表中不允許(畢竟user表中的許可權是表示是否具有全域性許可權的意思)，也會繼續往下檢查db表，然後再檢查tables_priv表，以此類推。

  • PS：  

    * 如果某客戶端在user表中類似DML許可權不足，而在其他db、tables_priv、columns_priv表中都沒有找到對應的user,host列記錄（則表示使用者在所有許可權表中都沒有對應操作型別的許可權），則客戶端訪問被拒絕，返回無訪問許可權提示資訊 。

    • * grant語句在授予使用者許可權時，授予庫級別許可權時，資料庫不需要事先存在即可授權成功，但如果是對錶級別物件授權，則表需要事先存在，否則授權失敗，提示表不存在的報錯資訊 。
    * 對於儲存程式的請求操作，Server 使用procs_priv表檢查許可權，而不是tables_priv和columns_priv表。

    上文中提及的許可權檢查邏輯，可以使用如下布林型的虛擬碼來表示： 

global privileges
OR (database privileges AND host privileges)
OR table privileges
OR column privileges
OR routine privileges

PS：某些型別的一個語句可以需要請求多個型別的許可權，例如：INSERT...SELECT，該語句需要請求INSERT和SELECT兩個許可權，而這兩個許可權可能在授予使用者的時候授予範圍不同，假如INSERT授予的是全域性範圍許可權，而SELECT是授予的db級別的許可權，此時，INSERT許可權是儲存在user表中的，SELECT許可權是儲存在db表中的，那麼也就是說，這個時候Server需要分兩次查詢之後將兩個表中記錄的許可權資訊進行組合，然後再用於判斷使用者是否具INSERT...SELECT語句的訪問請求許可權，並返回相應的請求結果。如果任意一個許可權不滿足，則拒絕訪問。

5、許可權變更的影響

當mysqld啟動時，將讀取所有許可權表的內容到記憶體中。後續所有使用者對MySQL Server的訪問的權鑑都是基於記憶體中儲存的這些值進行。

  • 如果在MySQL Server執行期間使用帳戶管理語句（如GRANT，REVOKE，SET PASSWORD或RENAME USER）間接修改了許可權表，則Server會立即將許可權表中的內容重新載入到記憶體中。

  • 如果在MySQL Server執行期間使用INSERT，UPDATE或DELETE等語句直接修改許可權表，那麼對許可權表的更改不會立即生效，除非重新啟動Server或使用flush privileges;語句或者mysqladmin flush-privileges|reload等命令來重新載入許可權表。

對於許可權表的過載，需要注意如下事項：

  • 對於表和列級許可權，修改並過載許可權表之後，對於已經建立的客戶端連線，會在對錶、列的下一個請求中生效。 對於新建連線，第一個請求即生效。

  • 對於庫級別許可權，修改並過載許可權表之後，對於已經建立的客戶端連線，會在下一次使用use db_name;語句時生效。 對於新建連線，第一個請求即生效 。

    * PS： 如果回收了某使用者的某庫的許可權，但客戶端是已經建立連線的且客戶端當前預設庫正好是已回收許可權的庫，則如果客戶端不使用use db_name;語句切換預設庫，對於該客戶端來講可能無法感知到庫級別許可權發生了修改。

  • 對於全域性許可權和密碼的修改，不影響已建立連線的客戶端，只針對重連或新建立的客戶端連線生效。

如果Server 啟動時使用了--skip-grant-tables選項，則Server不會讀許可權表，也不會進行任何訪問許可權控制，這個時候任何人都可以免密碼登入資料庫並可以做任何事情，這種情況除非維護時間視窗，否則禁止使用，在這種情況下，如果要重新載入許可權表，無需重新啟動，只需要執行flush privileges;語句即可。

6、MySQL 常見連線問題

客戶端無法連線伺服器的問題

  • 服務端未啟動，可以透過檢查服務端程式是否存在來排除(ps aux |grep mysqld，如果未啟動則嘗試拉起，如果啟動失敗則檢查錯誤日誌排查原因)，通常報錯資訊類似如下：

      * TCP/IP方式連線： ERROR 2003: Can't connect to MySQL server on 'host_name' (111)

      * socket方式連線： ERROR 2002: Can't connect to local MySQL server through socket '/tmp/mysql.sock' (111)

  • 客戶端連錯埠，可以透過檢查伺服器程式執行的埠(netstat -ln | grep mysqld)，找到正確的埠並在客戶端指定連線該埠可以解決。

  • 伺服器啟用了--skip-networking選項或者--bind-address = 127.0.0.1選項時，它將僅在本地環回介面上偵聽TCP/IP連線，並且不會接受遠端連線。 去除這些選項並重啟程式可以解決。

  • 服務端防火牆未開啟MySQL Server的埠訪問許可權，關閉防火牆或者允許MySQL Server的服務埠對外提供服務可以解決。

  • 沒有使用正確的帳號或者密碼連線伺服器，通常報錯資訊類似： ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)

  • 如果你的資料庫是第一次初始化，且使用了命令mysqld --initialize-secure來初始化，則會為root使用者生成一個隨機密碼字串，在MySQL Server啟動之後需要在error log中搜尋password關鍵字，以找到隨機密碼字串進行登入(使用該命令初始化會產生一個隨機密碼，如果你不需要該隨機密碼，則可以使用mysqld --initialize-insecure命令來初始化資料庫)，否則也會報錯： ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)

  • 如果你升級了服務端到最新版本，而客戶端沒有做相應的升級，則可能報認證協議不支援的錯誤(最好的辦法是升級客戶端版本，不建議去修改密碼認證外掛)： Client does not support authentication protocol requested by server; consider upgrading MySQL client

  • 服務端達到了最大使用者連線數引數限制，此時使用具有super許可權的管理員帳號登入資料庫，修改最大連線數。

  • 服務端達到了最大錯誤連線數引數限制，可能反覆嘗試連線的某些客戶端被拒絕連線(例如使用錯誤的帳號或密碼反覆嘗試多次，達到了最大錯誤連線數)，此時，使用管理員帳號從其他主機登入資料庫執行flush hosts;語句重新整理主機快取資訊，或者修改最大錯誤連線數引數。

PS：MySQL 訪問許可權系統有如下限制：

  • 不能明確拒絕給定使用者訪問，只能明確地允許給定使用者的訪問，例如： 使用了正確的帳號和密碼，且從被授予訪問的主機上訪問資料庫。

  • 不能單獨授予使用者只能建立或刪除資料庫中的表，而不能建立或刪除資料庫本身（指定了某使用者對某表的 create和drop許可權之後，使用者就能夠建立和刪除該表所在的庫）。

  • 帳號的密碼在Server中的作用域是全域性的。 不能使用密碼來與特定物件的訪問許可權掛鉤（如資料庫、表或儲存過程與函式等）。

本期內容就介紹到這裡，本期內容參考連結如下：

  • https://dev.mysql.com/doc/refman/5.7/en/privilege-system.html

  • https://dev.mysql.com/doc/refman/5.7/en/user-account-management.html

  • https://dev.mysql.com/doc/refman/5.7/en/grant-tables.html


作者簡介

羅小波·沃趣科技高階資料庫技術專家

IT從業多年，主要負責MySQL 產品的資料庫支撐與售後二線支撐。曾參與版本釋出系統、輕量級監控系統、運維管理平臺、資料庫管理平臺的設計與編寫，熟悉MySQL體系結構，Innodb儲存引擎，喜好專研開源技術，多次在公開場合做過線下線上資料庫專題分享，發表過多篇資料庫相關的研究文章。


來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/28218939/viewspace-2652122/，如需轉載，請註明出處，否則將追究法律責任。

相關文章