MySQL使用者與許可權

         在連線到 MySQL 伺服器並執行查詢時，會驗證您的身份併為您的活動授權。

l   驗證：驗證使用者的身份。這是訪問控制的第一個階段。每次連線時都必須成功驗證身份。如果驗證失敗，則無法連線，您的客戶機將斷開連線。

l   授權：驗證使用者的許可權。這是訪問控制的第二個階段，面向針對成功驗證了身份的活動連線的每個請求。對於每個請求， MySQL 將確定您要執行的操作，然後檢查您是否有執行此操作所需的恰當許可權。

        

         透過查詢 mysql.user 表可以檢視使用者許可權等資訊，例如：

mysql> SELECT * FROM mysql.user\G

mysql> SELECT user, host, password FROM mysql.user WHERE user='root';

         在使用本機口令驗證外掛（ mysql_native_password ，預設的驗證機制）連線到 MySQL 伺服器時，會將指定的使用者名稱、發出連線的主機以及口令與 mysql.user 表中的行進行匹配，以此確定您能否連線和執行操作。

         要使用 mysql 客戶機連線到本地伺服器，請指定您要使用的帳戶的使用者名稱和口令：

shell> mysql -u<username> -p<password>

         請注意，與 mysql.user 表中您的使用者關聯的主機名是指發起連線的主機（而不是伺服器主機）的名稱。要連線到未在您客戶機的本地主機上安裝的伺服器，請提供您要連線到的伺服器的主機名：

shell> mysql -u<username> -p<password> -h<server_host>

 

1.1.         建立使用者與設定口令

1)          建立使用者

         提供每個使用者帳戶的使用者和主機。例如，使用 CREATE USER...IDENTIFIED BY 語句

CREATE USER 'jim'@'localhost' IDENTIFIED BY 'Abc123';

         在建立帳戶時避免可能的安全風險：不建立沒有口令的帳戶、不建立匿名帳戶、在可能的情況下，避免在指定帳戶主機名時使用萬用字元。

         帳戶名稱包括使用者名稱和使用者必須從其連線到伺服器的客戶機主機的名稱。帳戶名稱的格式為 'user_name'@'host_name' 。使用者名稱長度最多可以有 16 個字元。如果使用者名稱和主機名包含特殊字元（如短劃線），則必須將它們放在單引號中。如果某個值在不帶引號時也有效，則引號是可選的。但是，在任何情況下都可使用引號。允許的主機名格式示例：

• 主機名： localhost

• 合格的主機名： 'hostname.example.com'

• IP 編號： 192.168.9.78

• IP 地址： 10.0.0.0/255.255.255.0

• 模式或萬用字元： % 或 _

 

使用者名稱和主機名示例：

• john@10.20.30.40

• john@'10.20.30.%'

• john@'%.ourdomain.com'

• '

         另外， GRANT 語句也可建立新帳戶或者修改現有帳戶。具體參考“授予許可權”。

 

2)          設定口令

設定 MySQL 使用者口令的方法有多種：

– CREATE USER...IDENTIFIED BY

– GRANT...IDENTIFIED BY

– SET PASSWORD

– mysqladmin password

– UPDATE 授權表（不推薦）

為所有使用者帳戶分配唯一的強口令。

• 避免可以輕易猜測到的口令。

• 使用以下 SELECT 語句可列出沒有口令的所有帳戶：

SELECT Host ， User FROM mysql.user WHERE Password = '';

• 確定重複口令：

SELECT User FROM mysql.user GROUP BY password HAVING count(user)>1;

• 讓口令失效：

ALTER USER jim@localhost PASSWORD EXPIRE;

                  MySQL 使用多種演算法對使用者表中儲存的口令加密：

l   mysql_native_password 外掛實施標準口令格式： 41 位元組寬的雜湊。

l   mysql_old_password 外掛實施較舊的格式，安全性較低， 16 位元組寬。

l   sha256_password 外掛實施在安全計算中廣泛採用的 SHA-256 雜湊演算法。

         old_passwords 系統變數的值指定 PASSWORD() 函式用於建立口令的演算法，如下所示：

：標準演算法，與 MySQL 4.1.1 及更高版本中所用的演算法相同

1 ：舊演算法，與 MySQL 4.1.1 之前版本中所用的演算法相同

2 ： SHA-256 演算法

       啟動伺服器時將 default-authentication-plugin 選項設定為 sha256_password ，可將 SHA-256 口令用於所有新使用者，也可使用 CREATE USER 和 IDENTIFIED WITH

         sha256_password 子句為特定使用者指定 SHA-256 口令。有關 sha256_password 外掛的進一步資訊，請訪問

http://dev.mysql.com/doc/refman/5.6/en/sha256-authentication-plugin.html

 

3)          管理使用者

         使用 RENAME USER 語句可重新命名使用者帳戶，即更改現有帳戶的帳戶名稱，更改帳戶名稱的使用者名稱或主機名部分，或同時更改兩者；

mysql> RENAME USER 'jim'@'localhost' TO 'james'@'localhost';

         使用 DROP USER 語句可刪除使用者帳戶；撤消現有帳戶的所有許可權，然後刪除該帳戶，同時從存在帳戶的任何授權表中刪除帳戶的所有記錄；

mysql> DROP USER 'jim'@'localhost';

 

4)          關於口令驗證外掛

A.         客戶端明文驗證外掛

         有些驗證方式（如 PAM （ Pluggable Authentication Module ，可插入驗證模組）驗證）要求客戶機向伺服器傳送純文字口令，以便伺服器處理普通形式的口令。 mysql_clear_password 外掛支援此行為。

       MySQL 客戶機庫中有一個內建的明文驗證外掛 mysql_clear_password 。該外掛可用於將純文字口令傳送給伺服器，口令通常經過雜湊處理。

         透過以下方式啟用：

方式一： LIBMYSQL_ENABLE_CLEARTEXT_PLUGIN 環境變數，在執行 MySQL 客戶機應用程式（如 mysql 和 mysqladmin ）時指定 --enable-cleartext-plugin ；

方式二： mysql_options() C API 函式的 MYSQL_ENABLE_CLEARTEXT_PLUGIN 選項；

 

B.         可載入驗證外掛

Ø   test_plugin_server ：實施本機和舊口令驗證，此外掛使用 auth_test_plugin.so 檔案。測試驗證外掛 (test_plugin_server) 使用本機或舊口令驗證進行身份驗證，適用於測試和開發。

Ø   auth_socket ：僅允許透過 UNIX 套接字從具有相同名稱的 UNIX 帳戶登入的 MySQL 使用者，此外掛使用 auth_socket.so 檔案。套接字對等憑證 (auth_socket) 外掛允許使用者僅在其 Linux sername 與其 MySQL 帳戶匹配時透過 UNIX 套接字檔案連線。

Ø   authentication_pam ：允許使用外部驗證機制登入，此外掛使用 authentication_pam.so 檔案。 PAM 驗證外掛 (authentication_pam) 是一個企業版外掛，允許使用外部驗證機制登入。 MySQL 不儲存您的口令，但使用 UNIX PAM （ Pluggable Authentication Module ，可插入驗證模組）機制傳輸客戶機提供的使用者名稱和口令供作業系統進行驗證。

         要載入可載入驗證外掛，可在伺服器啟動時在命令列中或在 my.cnf 檔案中使用 plugin-load 選項，如以下示例中所示：

[mysqld]

plugin-load=authentication_pam.so

         另外，您也可以開發自己的驗證外掛。測試驗證外掛適用於開發者建立自己的外掛；其原始碼隨 MySQL 原始碼一起分發。

 

C.         PAM 驗證外掛

         前面簡單介紹了多種外掛，下面重點介紹一下 PAM 驗證外掛， PAM 驗證外掛是一個企業版外掛，可向作業系統驗證 MySQL 帳戶。 PAM 可定義配置驗證的服務。這些服務儲存在 /etc/pam.d 中，該外掛也針對以下各項進行驗證：作業系統使用者和組、外部驗證（如 LDAP ）；

         要建立使用 PAM 驗證的 MySQL 使用者，請執行以下操作：

mysql>CREATE USER user@host IDENTIFIED WITH authentication_pam AS 'pam_service, os_group=mysql_user';

         PAM 在 /etc/pam.d 中查詢其驗證的服務。例如，要建立 PAM 服務 mysql-pam ，可在建立檔案 /etc/pam.d/mysql-pam 時使用以下內容：

#%PAM-1.0

auth include password-auth

account include password-auth

         除了 MySQL 驗證以外， PAM 還會與其他驗證方法（包括 LDAP 和 Active Directory ）整合，因此可以使用 PAM 向網路中的單個儲存驗證許多服務（包括 MySQL ）。要建立直接對映到某個作業系統使用者的 MySQL 使用者，可使用如下語句：

mysql>CREATE USER bob@localhost IDENTIFIED WITH authentication_pam AS 'mysql-pam';

         當 bob 登入時， MySQL 會將從客戶機接收的使用者名稱和口令傳遞到 PAM ，後者向作業系統驗證。客戶機必須以明文形式傳送口令。啟用客戶端明文驗證外掛以實現此目的：

shell> mysql --enable-cleartext-plugin -ubob -p

Enter password: bob’s_OS_password

         要使用 PAM 驗證外掛啟用基於組的登入，可建立一個啟用 PAM 的匿名代理帳戶，該帳戶不與任何使用者匹配，但指定一組從作業系統組到 MySQL 使用者的對映：

mysql>CREATE USER ''@'' IDENTIFIED WITH authentication_pam AS 'mysql-pam, sales=m_sales, finance=m_finance';

         上例假定您擁有 sales 和 finance 作業系統組以及 m_sales 和 m_finance MySQL 使用者。然後，必須向該匿名代理帳戶授予 PROXY 許可權，使其能以 m_sales 和 m_finance MySQL 使用者身份登入：

GRANT PROXY ON m_sales@localhost TO ''@'';

GRANT PROXY ON m_finance@localhost TO ''@'';

         現在，作為 sales 和 finance 組成員的使用者可以在 mysql 命令列提示符處提供其作業系統憑證，以 m_sales 或 m_finance MySQL 使用者身份登入，從而擁有授予這些帳戶的所有許可權。例如，如果 peter 是 sales 組的成員，則可透過以下方式登入：

shell> mysql --enable-cleartext-plugin -upeter -p

Enter password: peter’s_OS_password

Welcome to the MySQL monitor. Commands end with ; or \g.

...

mysql> SELECT CURRENT_USER();

+-------------------+

| CURRENT_USER() |

+-------------------+

| m_sales@localhost |

+-------------------+

1 row in set (0.01 sec)

 

1.2.         許可權列表

Privilege	Meaning and Grantable Levels
ALL   [PRIVILEGES]	Grant all privileges at specified access level except   GRANT OPTION
ALTER	Enable use of   . Levels: Global ， database ， table.
ALTER ROUTINE	Enable stored routines to be altered or dropped. Levels: Global ， database ， procedure.
CREATE	Enable database and table creation. Levels: Global ， database ， table.
CREATE ROUTINE	Enable stored routine creation. Levels: Global ， database.
CREATE   TABLESPACE	Enable tablespaces and log file groups to be created ， altered ， or dropped. Level: Global.
CREATE   TEMPORARY TABLES	Enable use of   . Levels: Global ， database.
CREATE USER	Enable use of   ， ， ， and   . Level: Global.
CREATE VIEW	Enable views to be created or altered. Levels: Global ， database ， table.
DELETE	Enable use of   . Level: Global ， database ， table.
DROP	Enable databases ， tables ， and views to be dropped. Levels: Global ， database ， table.
EVENT	Enable use of events for the Event Scheduler. Levels: Global ， database.
EXECUTE	Enable the user to execute stored routines. Levels: Global ， database ， table.
FILE	Enable the user to cause the server to read or write files. Level:   Global.
GRANT OPTION	Enable privileges to be granted to or removed from other accounts.   Levels: Global ， database ， table ， procedure ， proxy.
INDEX	Enable indexes to be created or dropped. Levels: Global ， database ， table.
INSERT	Enable use of   . Levels: Global ， database ， table ， column.
LOCK TABLES	Enable use of     on tables for which you have the     privilege. Levels: Global ， database.
PROCESS	Enable the user to see all processes with   . Level: Global.
PROXY	Enable user proxying. Level: From user to user.
REFERENCES	Enable foreign key creation. Levels: Global ， database ， table ， column.
RELOAD	Enable use of     operations. Level: Global.
REPLICATION   CLIENT	Enable the user to ask where master or slave servers are. Level: Global.
REPLICATION   SLAVE	Enable replication slaves to read binary log events from the master.   Level: Global.
SELECT	Enable use of   . Levels: Global ， database ， table ， column.
SHOW DATABASES	Enable     to show all databases. Level: Global.
SHOW VIEW	Enable use of   . Levels: Global ， database ， table.
SHUTDOWN	Enable use of   . Level: Global.
SUPER	Enable use of other administrative operations such as   ， , ， ， and     command. Level: Global.
TRIGGER	Enable trigger operations. Levels: Global ， database ， table.
UPDATE	Enable use of   . Levels: Global ， database ， table ， column.
USAGE	Synonym for   “no privileges”

 

1.3.         授予許可權

1)          GRANT 語句

GRANT 語句可建立新帳戶或者修改現有帳戶。 GRANT 語法：

GRANT SELECT ON world_innodb.* TO 'kari'@'localhost' IDENTIFIED BY 'Abc123';

該語句的子句：

– 要授予的許可權

– 許可權級別：

—   全域性： *.*

—   資料庫： <db_name>.*

—   表： <db_name>.<table_name>

—   儲存過程： <db_name>.<routine_name>

– 要授予其許可權的帳戶

– 可選口令

 

2)          允許的主機名格式示例

• 主機名： localhost

• 合格的主機名： 'hostname.example.com'

• IP 編號： 192.168.9.78

• IP 地址： 10.0.0.0/255.255.255.0

• 模式或萬用字元： % 或 _

 

使用者名稱和主機名示例：

• john@10.20.30.40

• john@'10.20.30.%'

• john@'%.ourdomain.com'

• '

 

3)          授予管理許可權

         以下全域性許可權適用於管理使用者：

l   FILE ：允許使用者指示 MySQL 伺服器在伺服器主機檔案系統中讀取和寫入檔案；

l   PROCESS ：允許使用者使用 SHOW PROCESSLIST 語句，檢視客戶機正在執行的所有語句；

l   SUPER ：允許使用者中止其他客戶機連線，或者更改伺服器的執行時配置； SUPER 管理許可權允許使用者執行額外任務，其中包括設定全域性變數和終止客戶機連線。

l   ALL ：授予所有許可權（但不能向其他使用者授予許可權），要儘可能少地授予管理許可權，因為管理許可權可能會被惡意使用者或粗心使用者濫用。

l   使用 ALL 和 ALL PRIVILEGES 授予所有許可權（但不能向其他帳戶授予許可權）。使用 GRANT ALL … WITH GRANT OPTION 授予所有許可權（可以向其他帳戶授予許可權）。

l   使用 USAGE 允許連線到伺服器。此許可權將在 user 表中為帳戶建立一個記錄，但沒有任何許可權。然後，可以使用帳戶訪問伺服器用於有限的目的，例如發出 SHOW VARIABLES 或 SHOW STATUS 語句。不能使用帳戶訪問表之類的資料庫內容，但可在以後授予此類許可權。

         其他管理許可權包括 CREATE USER 、 CREATE TEMPORARY TABLES 、 SHOW DATABASES 、 LOCK TABLES 、 RELOAD 和 SHUTDOWN 。可以利用管理許可權來破壞安全、訪問許可權資料或者對伺服器執行 DDoS 攻擊。確保僅將這些許可權授予適當的帳戶。有關授予 MySQL 許可權的更多資訊，請參閱《 MySQL 參考手冊》：

http://dev.mysql.com/doc/refman/5.6/en/privileges-provided.html

 

4)          查詢使用者許可權

         使用 SHOW GRANTS 語句顯示常規帳戶許可權，口令以加密形式儲存和顯示。

SHOW GRANTS;

SHOW GRANTS FOR CURRENT_USER();

mysql> SHOW GRANTS FOR 'kari'@'myhost.example.com';

+----------------------------------------------------------------+

| Grants for kari@myhost.example.com |

+----------------------------------------------------------------+

| GRANT FILE ON *.* TO 'kari'@'myhost.example.com' |

| GRANT SELECT ON `world_innodb`.* TO 'kari'@'myhost.example.com‘|

| IDENTIFIED BY PASSWORD |

|'*E74858DB86EBA20BC33D0AECAE8A8108C56B17FA' |

+----------------------------------------------------------------+

SHOW GRANTS 顯示了為指定使用者重新建立許可權的語句。該語句僅顯示在該語句中指定的帳戶的許可權。

如果帳戶有口令，則 SHOW GRANTS 將在 GRANT 語句末尾顯示一條 IDENTIFIED BY PASSWORD 子句，該子句可列出帳戶的全域性許可權。 IDENTIFIED BY 之後的單詞 PASSWORD 指示顯示的口令值是儲存在使用者表中的加密值，不是實際口令。由於口令是使用單向加密儲存的，因此 MySQL 無法顯示未加密的口令。

如果帳戶可以將其部分或全部許可權授予其他帳戶，則輸出將在其適用的每條 GRANT 語句末尾顯示 WITH GRANT OPTION 。

 

1.4.         許可權表

1)          許可權表

MySQL 安裝過程會建立許可權表，許可權表使用 MyISAM 儲存引擎。

user ：       針對伺服器已知的每個帳戶包含一個記錄

db ：          特定於資料庫的許可權

tables_priv ：   特定於表的許可權

columns_priv ：特定於列的許可權

procs_priv ：儲存過程和函式許可權

每個許可權表有 host 列和 user 列，用於標識其記錄適用的帳戶。在連線嘗試過程中，伺服器會確定客戶機是否能連線。伺服器根據 user 表的 Host 、 User 和 Password 列確定客戶機是否可連線。要成功連線， MySQL 必須將使用者表中的某個記錄與客戶機發起連線的主機、客戶機提供的使用者名稱以及匹配記錄中列出的口令匹配。

在連線後，伺服器會確定每條語句的訪問許可權。在客戶機連線之後， MySQL 將檢查每條語句的訪問許可權：將帳戶的身份與許可權表的 Host 和 User 列匹配。

l   user 表的每行中的許可權全域性適用於其 Host 和 User 列標識的帳戶。

l   db 、 tables_priv 、 columns_priv 和 procs_priv 表的匹配記錄中的許可權在由特定許可權表的名稱標識的級別上適用。

例如，一個 db 表記錄中的許可權適用於在該記錄中指定的資料庫，但不適用於其他資料庫。

2)          影響許可權的情況

伺服器會在其啟動過程中將授權表讀取到記憶體中，並使用記憶體中副本來檢查客戶機訪問許可權。在下列情況下，伺服器將重新整理其授權表的記憶體中副本：

l   透過發出帳戶管理語句（如 CREATE USER 、 GRANT 、 REVOKE 或 SET PASSWORD ）修改了使用者帳戶。

l   透過發出 FLUSH PRIVILEGES 語句或者執行 mysqladmin flush-privileges 或 mysqladmin reload 命令顯式重新載入了表。

由於以下原因，應避免直接更改授權表：

l   帳戶管理語句的語法設計清晰、簡單明瞭。

l   如果在某個帳戶管理語句中犯錯，該語句就會失敗，不會更改任何設定。

l   如果在直接更改授權表時犯錯，則可能會將所有使用者鎖在系統外面。

 

1.5.         撤消使用者許可權

• 使用 REVOKE 語句可以撤消特定的 SQL 語句許可權：

REVOKE DELETE ， INSERT ， UPDATE ON world_innodb.*

FROM 'Amon'@'localhost';

• 撤消許可權以便將許可權授予其他使用者：

REVOKE GRANT OPTION ON world_innodb.*

FROM 'Jan'@'localhost';

• 撤消所有許可權（包括向他人授權）：

REVOKE ALL PRIVILEGES ， GRANT OPTION

FROM 'Sasha'@'localhost';

• 在發出 REVOKE 之前使用 SHOW GRANTS 語句確定要

撤消的許可權，隨後再次確認結果。

 

1.6.         訪問控制

l   要指示伺服器不讀取授權表並禁用訪問控制，可使用 --skip-grant-tables 選項。每個連線都成功：

a)        可以提供任何使用者名稱及任何口令，並且可以從任何主機連線。

b)        該選項將禁用整個許可權系統。

c)        連線的使用者實際上擁有所有許可權。

d)        此選項會禁用帳戶管理語句，如 CREATE USER 、 GRANT 、 REVOKE 和 SET PASSWORD 。

 

l   阻止客戶機連線：

– 使用 --skip-networking 選項可阻止網路訪問，並且僅允許在本地套接字、命名管道或共享記憶體上訪問。

– 使用 --socket 選項可在非標準套接字上啟動伺服器以防止本地應用程式或使用者隨便訪問。

 

如果您忘了 root 口令，需要將其重置，則禁用訪問控制會很方便，因為任何使用者都可使用完全許可權連線，無需提供口令。顯然，這是很危險的。要阻止遠端客戶機透過 TCP/IP 進行連線，可使用 --skip-networking 選項。這樣，客戶機只能使用 UNIX 上的套接字檔案或者 Windows 上的命名管道或共享記憶體從 localhost 連線。要避免來自本地主機的隨意連線，可在命令提示符下使用非標準套接字名稱。

帳戶管理語句需要授權表的記憶體中副本；在禁用訪問控制時，這些副本不可用。要更改許可權或設定口令，可直接修改授權表。或者，在連線到伺服器之後發出一條 FLUSH PRIVILEGES 語句，這將使伺服器讀取表，並且還會啟用帳戶管理語句。

 

1.7.         資源限制

透過將全域性變數 MAX_USER_CONNECTIONS 設定為非零值，限制使用伺服器資源。這將限制任何一個帳戶的同時連線數量，但不會限制客戶機在連線後能執行的操作。

• 限制單個帳戶的以下伺服器資源：

– MAX_QUERIES_PER_HOUR ：一個帳戶每小時可發出的查詢數量

– MAX_UPDATES_PER_HOUR ：一個帳戶每小時可發出的更新數量

– MAX_CONNECTIONS_PER_HOUR ：一個帳戶每小時可連線到伺服器的次數

– MAX_USER_CONNECTIONS ：允許的同時連線數量

       要設定帳戶的資源限制，可使用 GRANT 語句以及指定要限制的每個資源的 WITH 子句。每個限制的預設值是零，表示沒有限制。例如，要限制使用者 francis 訪問客戶資料庫，可發出以下語句：

mysql> GRANT ALL ON customer.* TO 'francis'@'localhost'

-> WITH MAX_QUERIES_PER_HOUR 20

-> MAX_UPDATES_PER_HOUR 10

-> MAX_CONNECTIONS_PER_HOUR 5

-> MAX_USER_CONNECTIONS 2;

按任意順序在 WITH 子句中提供資源限制。將 MAX_USER_CONNECTIONS 限制設定為 0 可將其設定為全域性預設值，表示此帳戶允許的最大同時連線數是 max_user_connections 系統變數的全域性值。

要將任何每小時資源的現有限制設定為預設的“無限制” ，可指定值 0 ，如以下示例中所示：

mysql> GRANT USAGE ON *.* TO 'quinn'@'localhost'

-> WITH MAX_CONNECTIONS_PER_HOUR 0;

 

1.8.         MySQL 許可權實戰

a. 檢視當前使用者的許可權：

mysql> show grants;

b. 檢視某個使用者的許可權：

mysql> show grants for 'jack'@'%';

mysql>show grants for current_user();

c. 回收許可權

mysql> revoke delete on *.* from 'jack'@'localhost';

d. 刪除使用者

mysql> drop user 'jack'@'localhost';

e. 對賬戶重新命名

mysql> rename user 'jack'@'%' to 'jim'@'%';

f. 修改密碼

i) 用 set password 命令

mysql> SET PASSWORD FOR 'root'@'localhost' = PASSWORD('123456');

     ii) 用 mysqladmin

  [root@rhel5 ~]# mysqladmin -uroot -p123456 password 1234abcd

  備註：

  格式： mysqladmin -u 使用者名稱 -p 舊密碼 password 新密碼

iii) 用 update 直接編輯 user 表

  mysql> use mysql

  mysql> update user set PASSWORD = PASSWORD('1234abcd') where user = 'root';

  mysql> flush privileges;

       iv) 在丟失 root 密碼的時候

  [root@rhel5 ~]# mysqld_safe --skip-grant-tables &

  [root@rhel5 ~]# mysql -u root

mysql> update user set password = PASSWORD('123456') where user = 'root';

mysql> flush privileges;