?MySQL中用戶與權限的示例分析

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

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'

? john@'10.20.30.0/255.255.255.0'

         另外，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，后者向操作系統驗證?？蛻魴C必須以明文形式發送口令。啟用客戶端明文驗證插件以實現此目的：

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.        權限列表

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'

? john@'10.20.30.0/255.255.255.0 '

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;

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