MySQL許可權表探索

MySQL許可權表是指在mysql資料庫下的5張表：user, db, tables_priv, columns_priv, procs_priv，這5張表記錄了 所有的使用者及其許可權資訊，MySQL就是通過這5張表控制使用者訪問的。本文將探索這5張許可權表。

MySQL許可權表的結構和 內容

1、user：記錄帳號、密碼、全域性許可權資訊等。

mysql> desc mysql.user;  +------------------------+-----------------------------------+------+-----+---------+-------+  | Field                  | Type                              | Null | Key | Default | Extra |  +------------------------+-----------------------------------+------+-----+---------+-------+  | Host                   | char(60)                          | NO   | PRI |         |       |   | User                   | char(16)                          | NO   | PRI |         |       |   | Password               | char(41)                          | NO   |     |         |       |   | Select_priv            | enum('N','Y')                     | NO   |     | N       |       |   | Insert_priv            | enum('N','Y')                     | NO   |     | N       |       |   | Update_priv            | enum('N','Y')                     | NO   |     | N       |       |   | Delete_priv            | enum('N','Y')                     | NO   |     | N       |       |   | Create_priv            | enum('N','Y')                     | NO   |     | N       |       |   | Drop_priv              | enum('N','Y')                     | NO   |     | N       |       |   | Reload_priv            | enum('N','Y')                     | NO   |     | N       |       |   | Shutdown_priv          | enum('N','Y')                     | NO   |     | N       |       |   | Process_priv           | enum('N','Y')                     | NO   |     | N       |       |   | File_priv              | enum('N','Y')                     | NO   |     | N       |       |   | Grant_priv             | enum('N','Y')                     | NO   |     | N       |       |   | References_priv        | enum('N','Y')                     | NO   |     | N       |       |   | Index_priv             | enum('N','Y')                     | NO   |     | N       |       |   | Alter_priv             | enum('N','Y')                     | NO   |     | N       |       |   | Show_db_priv           | enum('N','Y')                     | NO   |     | N       |       |   | Super_priv             | enum('N','Y')                     | NO   |     | N       |       |   | Create_tmp_table_priv  | enum('N','Y')                     | NO   |     | N       |       |   | Lock_tables_priv       | enum('N','Y')                     | NO   |     | N       |       |   | Execute_priv           | enum('N','Y')                     | NO   |     | N       |       |   | Repl_slave_priv        | enum('N','Y')                     | NO   |     | N       |       |   | Repl_client_priv       | enum('N','Y')                     | NO   |     | N       |       |   | Create_view_priv       | enum('N','Y')                     | NO   |     | N       |       |   | Show_view_priv         | enum('N','Y')                     | NO   |     | N       |       |   | Create_routine_priv    | enum('N','Y')                     | NO   |     | N       |       |   | Alter_routine_priv     | enum('N','Y')                     | NO   |     | N       |       |   | Create_user_priv       | enum('N','Y')                     | NO   |     | N       |       |   | Event_priv             | enum('N','Y')                     | NO   |     | N       |       |   | Trigger_priv           | enum('N','Y')                     | NO   |     | N       |       |   | Create_tablespace_priv | enum('N','Y')                     | NO   |     | N       |       |   | ssl_type               | enum('','ANY','X509','SPECIFIED') | NO   |     |         |       |   | ssl_cipher             | blob                              | NO   |     | NULL    |       |   | x509_issuer            | blob                              | NO   |     | NULL    |       |   | x509_subject           | blob                              | NO   |     | NULL    |       |   | max_questions          | int(11) unsigned                  | NO   |     | 0       |       |   | max_updates            | int(11) unsigned                  | NO   |     | 0       |       |   | max_connections        | int(11) unsigned                  | NO   |     | 0       |       |   | max_user_connections   | int(11) unsigned                  | NO   |     | 0       |       |   | plugin                 | char(64)                          | YES  |     |         |       |   | authentication_string  | text                              | YES  |     | NULL    |       |   +------------------------+-----------------------------------+------+-----+---------+-------+

1）*_priv：適用MySQL伺服器全域性的許可權，假設某個帳號擁有Delete_priv的全域性許可權，則表示它可以對任何錶 進行刪除資料的操作，這非常危險，所有一般只有超級使用者root有這樣的許可權，其它普通使用者沒有。

2）max_*：資源管理列，用於規定帳號的資源使用上限，其中：

max_questions：每小時發出的語句數上限

max_updates：每小時發出的修改類語句數上限

max_connections：每小時串連數上限

max_user_connections：允許保有的串連數上限

3）SSL相關列：

ssl_type，ssl_cipher，x509_isuser, x509_subject

2、db：記錄資料庫相關許可權

mysql> desc mysql.db;  +-----------------------+---------------+------+-----+---------+-------+  | Field                 | Type          | Null | Key | Default | Extra |  +-----------------------+---------------+------+-----+---------+-------+  | Host                  | char(60)      | NO   | PRI |         |       |   | Db                    | char(64)      | NO   | PRI |         |       |   | User                  | char(16)      | NO   | PRI |         |       |   | Select_priv           | enum('N','Y') | NO   |     | N       |       |   | Insert_priv           | enum('N','Y') | NO   |     | N       |       |   | Update_priv           | enum('N','Y') | NO   |     | N       |       |   | Delete_priv           | enum('N','Y') | NO   |     | N       |       |   | Create_priv           | enum('N','Y') | NO   |     | N       |       |   | Drop_priv             | enum('N','Y') | NO   |     | N       |       |   | Grant_priv            | enum('N','Y') | NO   |     | N       |       |   | References_priv       | enum('N','Y') | NO   |     | N       |       |   | Index_priv            | enum('N','Y') | NO   |     | N       |       |   | Alter_priv            | enum('N','Y') | NO   |     | N       |       |   | Create_tmp_table_priv | enum('N','Y') | NO   |     | N       |       |   | Lock_tables_priv      | enum('N','Y') | NO   |     | N       |       |   | Create_view_priv      | enum('N','Y') | NO   |     | N       |       |   | Show_view_priv        | enum('N','Y') | NO   |     | N       |       |   | Create_routine_priv   | enum('N','Y') | NO   |     | N       |       |   | Alter_routine_priv    | enum('N','Y') | NO   |     | N       |       |   | Execute_priv          | enum('N','Y') | NO   |     | N       |       |   | Event_priv            | enum('N','Y') | NO   |     | N       |       |   | Trigger_priv          | enum('N','Y') | NO   |     | N       |       |   +-----------------------+---------------+------+-----+---------+-------+