?MySQL中用戶與權(quán)限的示例分析

小編給大家分享一下MySQL中用戶與權(quán)限的示例分析，相信大部分人都還不怎么了解，因此分享這篇文章給大家參考一下，希望大家閱讀完這篇文章后大有收獲，下面讓我們一起去了解一下吧！

MySQL用戶與權(quán)限

         在連接到 MySQL 服務器并執(zhí)行查詢時，會驗證您的身份并為您的活動授權(quán)。

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

l  授權(quán)：驗證用戶的權(quán)限。這是訪問控制的第二個階段，面向針對成功驗證了身份的活動連接的每個請求。對于每個請求， MySQL 將確定您要執(zhí)行的操作，然后檢查您是否有執(zhí)行此操作所需的恰當權(quán)限。

         通過查詢mysql.user表可以查看用戶權(quán)限等信息，例如：

mysql> SELECT * FROM mysql.user\G

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

         在使用本機口令驗證插件（mysql_native_password，默認的驗證機制）連接到 MySQL 服務器時，會將指定的用戶名、發(fā)出連接的主機以及口令與 mysql.user 表中的行進行匹配，以此確定您能否連接和執(zhí)行操作。

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

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

         請注意，與 mysql.user 表中您的用戶關(guān)聯(lián)的主機名是指發(fā)起連接的主機（而不是服務器主機）的名稱。要連接到未在您客戶機的本地主機上安裝的服務器，請?zhí)峁┠B接到的服務器的主機名：

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

1.1.        創(chuàng)建用戶與設(shè)置口令

1)         創(chuàng)建用戶

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

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

         在創(chuàng)建帳戶時避免可能的安全風險：不創(chuàng)建沒有口令的帳戶、不創(chuàng)建匿名帳戶、在可能的情況下，避免在指定帳戶主機名時使用通配符。

         帳戶名稱包括用戶名和用戶必須從其連接到服務器的客戶機主機的名稱。帳戶名稱的格式為 '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'

? john@'10.20.30.0/255.255.255.0'

         另外，GRANT 語句也可創(chuàng)建新帳戶或者修改現(xiàn)有帳戶。具體參考“授予權(quán)限”。

2)         設(shè)置口令

設(shè)置MySQL 用戶口令的方法有多種：

– CREATE USER...IDENTIFIED BY

– GRANT...IDENTIFIED BY

– SET PASSWORD

– mysqladmin password

– UPDATE 授權(quán)表（不推薦）

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

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

? 使用以下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 字節(jié)寬的散列。

l  mysql_old_password 插件實施較舊的格式，安全性較低， 16 字節(jié)寬。

l  sha256_password 插件實施在安全計算中廣泛采用的 SHA-256 散列算法。

         old_passwords 系統(tǒng)變量的值指定 PASSWORD() 函數(shù)用于創(chuàng)建口令的算法，如下所示：

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

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

2： SHA-256 算法

       啟動服務器時將 default-authentication-plugin 選項設(shè)置為 sha256_password，可將 SHA-256 口令用于所有新用戶，也可使用 CREATE USER 和 IDENTIFIED WITH

         sha256_password 子句為特定用戶指定 SHA-256 口令。有關(guān) sha256_password 插件的進一步信息，請訪問

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

3)         管理用戶

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

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

         使用 DROP USER 語句可刪除用戶帳戶；撤消現(xiàn)有帳戶的所有權(quán)限，然后刪除該帳戶，同時從存在帳戶的任何授權(quán)表中刪除帳戶的所有記錄；

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

4)         關(guān)于口令驗證插件

A.        客戶端明文驗證插件

         有些驗證方式（如 PAM（Pluggable Authentication Module，可插入驗證模塊）驗證）要求客戶機向服務器發(fā)送純文本口令，以便服務器處理普通形式的口令。mysql_clear_password 插件支持此行為。

       MySQL 客戶機庫中有一個內(nèi)置的明文驗證插件mysql_clear_password。該插件可用于將純文本口令發(fā)送給服務器，口令通常經(jīng)過散列處理。

         通過以下方式啟用：

方式一：LIBMYSQL_ENABLE_CLEARTEXT_PLUGIN 環(huán)境變量，在運行 MySQL 客戶機應用程序（如 mysql 和mysqladmin）時指定 --enable-cleartext-plugin；

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

B.        可載入驗證插件

?  test_plugin_server：實施本機和舊口令驗證，此插件使用 auth_test_plugin.so 文件。測試驗證插件 (test_plugin_server) 使用本機或舊口令驗證進行身份驗證，適用于測試和開發(fā)。

?  auth_socket：僅允許通過 UNIX 套接字從具有相同名稱的 UNIX 帳戶登錄的 MySQL 用戶，此插件使用 auth_socket.so 文件。套接字對等憑證 (auth_socket) 插件允許用戶僅在其 Linux sername 與其 MySQL帳戶匹配時通過 UNIX 套接字文件連接。

?  authentication_pam：允許使用外部驗證機制登錄，此插件使用 authentication_pam.so 文件。PAM 驗證插件 (authentication_pam) 是一個企業(yè)版插件，允許使用外部驗證機制登錄。 MySQL 不存儲您的口令，但使用 UNIX PAM（Pluggable Authentication Module，可插入驗證模塊）機制傳輸客戶機提供的用戶名和口令供操作系統(tǒng)進行驗證。

         要載入可載入驗證插件，可在服務器啟動時在命令行中或在 my.cnf 文件中使用plugin-load 選項，如以下示例中所示：

[mysqld]

plugin-load=authentication_pam.so

         另外，您也可以開發(fā)自己的驗證插件。測試驗證插件適用于開發(fā)者創(chuàng)建自己的插件；其源代碼隨MySQL 源代碼一起分發(fā)。

C.        PAM 驗證插件

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

         要創(chuàng)建使用 PAM 驗證的 MySQL 用戶，請執(zhí)行以下操作：

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

         PAM 在 /etc/pam.d 中查找其驗證的服務。例如，要創(chuàng)建 PAM 服務 mysql-pam，可在創(chuàng)建文件 /etc/pam.d/mysql-pam 時使用以下內(nèi)容：

#%PAM-1.0

auth include password-auth

account include password-auth

         除了 MySQL 驗證以外， PAM 還會與其他驗證方法（包括 LDAP 和 Active Directory）集成，因此可以使用 PAM 向網(wǎng)絡中的單個存儲驗證許多服務（包括 MySQL）。要創(chuàng)建直接映射到某個操作系統(tǒng)用戶的 MySQL 用戶，可使用如下語句：

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

         當 bob 登錄時， MySQL 會將從客戶機接收的用戶名和口令傳遞到 PAM，后者向操作系統(tǒng)驗證?？蛻魴C必須以明文形式發(fā)送口令。啟用客戶端明文驗證插件以實現(xiàn)此目的：

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

Enter password: bob’s_OS_password

         要使用 PAM 驗證插件啟用基于組的登錄，可創(chuàng)建一個啟用 PAM 的匿名代理帳戶，該帳戶不與任何用戶匹配，但指定一組從操作系統(tǒng)組到 MySQL 用戶的映射：

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

         上例假定您擁有 sales 和 finance 操作系統(tǒng)組以及 m_sales 和 m_finance MySQL用戶。然后，必須向該匿名代理帳戶授予 PROXY 權(quán)限，使其能以 m_sales 和 m_finance MySQL 用戶身份登錄：

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

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

         現(xiàn)在，作為 sales 和 finance 組成員的用戶可以在 mysql 命令行提示符處提供其操作系統(tǒng)憑證，以 m_sales 或 m_finance MySQL 用戶身份登錄，從而擁有授予這些帳戶的所有權(quán)限。例如，如果 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.        權(quán)限列表

1.3.        授予權(quán)限

1)         GRANT 語句

GRANT 語句可創(chuàng)建新帳戶或者修改現(xiàn)有帳戶。GRANT 語法：

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

該語句的子句：

– 要授予的權(quán)限

– 權(quán)限級別：

—  全局：*.*

—  數(shù)據(jù)庫：<db_name>.*

—  表：<db_name>.<table_name>

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

– 要授予其權(quán)限的帳戶

– 可選口令

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'

? john@'10.20.30.0/255.255.255.0 '

3)         授予管理權(quán)限

         以下全局權(quán)限適用于管理用戶：

l  FILE：允許用戶指示 MySQL 服務器在服務器主機文件系統(tǒng)中讀取和寫入文件；

l  PROCESS：允許用戶使用 SHOW PROCESSLIST 語句，查看客戶機正在執(zhí)行的所有語句；

l  SUPER：允許用戶中止其他客戶機連接，或者更改服務器的運行時配置；SUPER 管理權(quán)限允許用戶執(zhí)行額外任務，其中包括設(shè)置全局變量和終止客戶機連接。

l  ALL：授予所有權(quán)限（但不能向其他用戶授予權(quán)限），要盡可能少地授予管理權(quán)限，因為管理權(quán)限可能會被惡意用戶或粗心用戶濫用。

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

l  使用 USAGE 允許連接到服務器。此權(quán)限將在 user 表中為帳戶創(chuàng)建一個記錄，但沒有任何權(quán)限。然后，可以使用帳戶訪問服務器用于有限的目的，例如發(fā)出 SHOW VARIABLES 或 SHOW STATUS 語句。不能使用帳戶訪問表之類的數(shù)據(jù)庫內(nèi)容，但可在以后授予此類權(quán)限。

         其他管理權(quán)限包括 CREATE USER、 CREATE TEMPORARY TABLES、 SHOW DATABASES、LOCK TABLES、 RELOAD 和 SHUTDOWN?？梢岳霉芾頇?quán)限來破壞安全、訪問權(quán)限數(shù)據(jù)或者對服務器執(zhí)行DDoS攻擊。確保僅將這些權(quán)限授予適當?shù)膸?。有關(guān)授予 MySQL 權(quán)限的更多信息，請參閱《MySQL 參考手冊》：

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

4)         查詢用戶權(quán)限

         使用 SHOW GRANTS 語句顯示常規(guī)帳戶權(quán)限，口令以加密形式存儲和顯示。

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 顯示了為指定用戶重新創(chuàng)建權(quán)限的語句。該語句僅顯示在該語句中指定的帳戶的權(quán)限。

如果帳戶有口令，則 SHOW GRANTS 將在 GRANT 語句末尾顯示一條 IDENTIFIED BY PASSWORD 子句，該子句可列出帳戶的全局權(quán)限。 IDENTIFIED BY 之后的單詞PASSWORD 指示顯示的口令值是存儲在用戶表中的加密值，不是實際口令。由于口令是使用單向加密存儲的，因此 MySQL 無法顯示未加密的口令。

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

1.4.        權(quán)限表

1)         權(quán)限表

MySQL 安裝過程會創(chuàng)建權(quán)限表，權(quán)限表使用 MyISAM 存儲引擎。

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

db：         特定于數(shù)據(jù)庫的權(quán)限

tables_priv：  特定于表的權(quán)限

columns_priv：特定于列的權(quán)限

procs_priv：存儲過程和函數(shù)權(quán)限

每個權(quán)限表有 host 列和 user 列，用于標識其記錄適用的帳戶。在連接嘗試過程中，服務器會確定客戶機是否能連接。服務器根據(jù) user 表的 Host、 User 和 Password 列確定客戶機是否可連接。要成功連接， MySQL 必須將用戶表中的某個記錄與客戶機發(fā)起連接的主機、客戶機提供的用戶名以及匹配記錄中列出的口令匹配。

在連接后，服務器會確定每條語句的訪問權(quán)限。在客戶機連接之后，MySQL 將檢查每條語句的訪問權(quán)限：將帳戶的身份與權(quán)限表的 Host和 User 列匹配。

l  user 表的每行中的權(quán)限全局適用于其 Host 和 User 列標識的帳戶。

l  db、 tables_priv、 columns_priv 和 procs_priv 表的匹配記錄中的權(quán)限在由特定權(quán)限表的名稱標識的級別上適用。

例如，一個 db 表記錄中的權(quán)限適用于在該記錄中指定的數(shù)據(jù)庫，但不適用于其他數(shù)據(jù)庫。

2)         影響權(quán)限的情況

服務器會在其啟動過程中將授權(quán)表讀取到內(nèi)存中，并使用內(nèi)存中副本來檢查客戶機訪問權(quán)限。在下列情況下，服務器將刷新其授權(quán)表的內(nèi)存中副本：

l  通過發(fā)出帳戶管理語句（如 CREATE USER、 GRANT、 REVOKE 或 SET PASSWORD）修改了用戶帳戶。

l  通過發(fā)出 FLUSH PRIVILEGES 語句或者執(zhí)行 mysqladmin flush-privileges或mysqladmin reload 命令顯式重新載入了表。

由于以下原因，應避免直接更改授權(quán)表：

l  帳戶管理語句的語法設(shè)計清晰、簡單明了。

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

l  如果在直接更改授權(quán)表時犯錯，則可能會將所有用戶鎖在系統(tǒng)外面。

1.5.        撤消用戶權(quán)限

? 使用REVOKE 語句可以撤消特定的SQL 語句權(quán)限：

REVOKE DELETE，INSERT，UPDATE ON world_innodb.*

FROM 'Amon'@'localhost';

? 撤消權(quán)限以便將權(quán)限授予其他用戶：

REVOKE GRANT OPTION ON world_innodb.*

FROM 'Jan'@'localhost';

? 撤消所有權(quán)限（包括向他人授權(quán)）：

REVOKE ALL PRIVILEGES，GRANT OPTION

FROM 'Sasha'@'localhost';

? 在發(fā)出REVOKE 之前使用SHOW GRANTS 語句確定要

撤消的權(quán)限，隨后再次確認結(jié)果。

1.6.        訪問控制

l  要指示服務器不讀取授權(quán)表并禁用訪問控制，可使用--skip-grant-tables 選項。每個連接都成功：

a)       可以提供任何用戶名及任何口令，并且可以從任何主機連接。

b)       該選項將禁用整個權(quán)限系統(tǒng)。

c)       連接的用戶實際上擁有所有權(quán)限。

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

l  阻止客戶機連接：

– 使用--skip-networking 選項可阻止網(wǎng)絡訪問，并且僅允許在本地套接字、命名管道或共享內(nèi)存上訪問。

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

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

帳戶管理語句需要授權(quán)表的內(nèi)存中副本；在禁用訪問控制時，這些副本不可用。要更改權(quán)限或設(shè)置口令，可直接修改授權(quán)表?；蛘撸谶B接到服務器之后發(fā)出一條 FLUSH PRIVILEGES 語句，這將使服務器讀取表，并且還會啟用帳戶管理語句。

1.7.        資源限制

通過將全局變量MAX_USER_CONNECTIONS 設(shè)置為非零值，限制使用服務器資源。這將限制任何一個帳戶的同時連接數(shù)量，但不會限制客戶機在連接后能執(zhí)行的操作。

? 限制單個帳戶的以下服務器資源：

– MAX_QUERIES_PER_HOUR：一個帳戶每小時可發(fā)出的查詢數(shù)量

– MAX_UPDATES_PER_HOUR：一個帳戶每小時可發(fā)出的更新數(shù)量

– MAX_CONNECTIONS_PER_HOUR：一個帳戶每小時可連接到服務器的次數(shù)

– MAX_USER_CONNECTIONS：允許的同時連接數(shù)量

       要設(shè)置帳戶的資源限制，可使用 GRANT 語句以及指定要限制的每個資源的 WITH 子句。每個限制的默認值是零，表示沒有限制。例如，要限制用戶 francis 訪問客戶數(shù)據(jù)庫，可發(fā)出以下語句：

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 限制設(shè)置為 0可將其設(shè)置為全局默認值，表示此帳戶允許的最大同時連接數(shù)是max_user_connections 系統(tǒng)變量的全局值。

要將任何每小時資源的現(xiàn)有限制設(shè)置為默認的“無限制” ，可指定值 0，如以下示例中所示：

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

-> WITH MAX_CONNECTIONS_PER_HOUR 0;

1.8.        MySQL權(quán)限實戰(zhàn)

a.查看當前用戶的權(quán)限：

mysql> show grants;

b.查看某個用戶的權(quán)限：

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

mysql>show grants for current_user();

c.回收權(quán)限

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;

以上是“MySQL中用戶與權(quán)限的示例分析”這篇文章的所有內(nèi)容，感謝各位的閱讀！相信大家都有了一定的了解，希望分享的內(nèi)容對大家有所幫助，如果還想學習更多知識，歡迎關(guān)注億速云行業(yè)資訊頻道！