MYSQL資料庫管理之許可權管理

經常遇到有網友在QQ群或者論壇上問關於mysql許可權的問題,今天抽空總結一下關於這幾年使用MYSQL的時候關於MYSQL資料庫的許可權管理的經驗,也希望能對使用mysql的網友有所協助!

一、MYSQL許可權簡介

關於mysql的許可權簡單的理解就是mysql允許你做你權利以內的事情,不可以越界。比如只允許你執行select操作，那麼你就不能執行update操作。只允許你從某台機器上串連mysql，那麼你就不能從除那台機器以外的其他機器串連mysql。

那麼MYSQL的許可權是如何?的呢？這就要說到mysql的兩階段的驗證，下面詳細來介紹：第一階段：伺服器首先會檢查你是否允許串連。因為建立使用者的時候會加上主機限制，可以限制成本地、某個IP、某個IP段、以及任何地方等，只允許你從配置的指定地方登入。後面在實戰的時候會詳細說關於主機的限制。第二階段：如果你能串連，MYSQL會檢查你發出的每個請求，看你是否有足夠的許可權實施它。比如你要更新某個表、或者查詢某個表，MYSQL會檢查你對哪個表或者某個列是否有許可權。再比如，你要運行某個預存程序，MYSQL會檢查你對預存程序是否有執行許可權等。

MYSQL到底都有哪些許可權呢？從官網複製一個表來看看：

許可權	權限等級	許可權說明
CREATE	資料庫、表或索引	建立資料庫、表或索引許可權
DROP	資料庫或表	刪除資料庫或表許可權
GRANT OPTION	資料庫、表或儲存的程式	賦予許可權選項
REFERENCES	資料庫或表
ALTER	表	更改表，比如添加欄位、索引等
DELETE	表	刪除資料許可權
INDEX	表	索引許可權
INSERT	表	插入許可權
SELECT	表	查詢許可權
UPDATE	表	更新許可權
CREATE VIEW	視圖	建立視圖許可權
SHOW VIEW	視圖	查看視圖許可權
ALTER ROUTINE	預存程序	更改預存程序許可權
CREATE ROUTINE	預存程序	建立預存程序許可權
EXECUTE	預存程序	執行預存程序許可權
FILE	伺服器主機上的檔案訪問	檔案存取權限
CREATE TEMPORARY TABLES	伺服器管理	建立暫存資料表許可權
LOCK TABLES	伺服器管理	鎖表許可權
CREATE USER	伺服器管理	建立使用者權限
PROCESS	伺服器管理	查看進程許可權
RELOAD	伺服器管理	執行flush-hosts, flush-logs, flush-privileges, flush-status, flush-tables, flush-threads, refresh, reload等命令的許可權
REPLICATION CLIENT	伺服器管理	複製許可權
REPLICATION SLAVE	伺服器管理	複製許可權
SHOW DATABASES	伺服器管理	查看資料庫許可權
SHUTDOWN	伺服器管理	關閉資料庫許可權
SUPER	伺服器管理	執行kill線程許可權

 

MYSQL的許可權如何分布，就是針對錶可以設定什麼許可權，針對列可以設定什麼許可權等等，這個可以從官方文檔中的一個表來說明：

許可權分布	可能的設定的許可權
表許可權	'Select', 'Insert', 'Update', 'Delete', 'Create', 'Drop', 'Grant', 'References', 'Index', 'Alter'
列許可權	'Select', 'Insert', 'Update', 'References'
過程許可權	'Execute', 'Alter Routine', 'Grant'

針對許可權這部分，最主要的是要知道MYSQL是如何驗證的（兩階段驗證），以及mysql各個許可權是做什麼用的，以及那些許可權用在什麼地方(表or列？)。如果這些把握了那麼MYSQL許可權對你來說就是小菜一碟了，只要看一下後面的許可權管理就可以融會貫通了。

        

二、MYSQL許可權經驗原則

許可權控制主要是出於安全因素，因此需要遵循一下幾個經驗原則：

1.  只授予能滿足需要的最小許可權，防止使用者幹壞事。哈哈。比如使用者只是需要查詢，那就只給select許可權就可以了，不要給使用者賦予update、insert或者delete許可權。

2.  建立使用者的時候限制使用者的登入主機，一般是限制成指定IP或者內網IP段。

3.  初始化資料庫的時候刪除沒有密碼的使用者。安裝完資料庫的時候會自動建立一些使用者，這些使用者預設沒有密碼。

4.  為每個使用者佈建滿足密碼複雜度的密碼。

5.  定期清理不需要的使用者。回收許可權或者刪除使用者。

 

三、MYSQL許可權實戰

1.  GRANT命令使用說明

先來看一個例子，建立一個只允許從本地登入的超級使用者feihong，並允許將許可權賦予別的使用者，密碼為test@feihong.111

GRANT ALL PRIVILEGES ON *.* TO feihong@'localhost' IDENTIFIED BY 'test@feihong.111' WITH GRANT OPTION;

GRANT命令說明：

ALL PRIVILEGES 是表示所有許可權，你也可以使用select、update等許可權提到的許可權。

ON 用來指定許可權針對哪些庫和表。

*.* 中前面的*號用來指定資料庫名，後面的*號用來指定表名。

TO 表示將許可權賦予某個使用者。

feihong@'localhost' 表示feihong使用者，@後面接限制的主機，可以是IP、IP段、網域名稱以及%，%表示任何地方。注意：這裡%有的版本不包括本地，以前碰到過給某個使用者佈建了%允許任何地方登入，但是在本地登入不了，這個和版本有關係，遇到這個問題再加一個localhost的使用者就可以了。

IDENTIFIED BY 指定使用者的登入密碼。

WITH GRANT OPTION 這個選項表示該使用者可以將自己擁有的許可權授權給別人。注意：經常有人在建立操作使用者的時候不指定WITH GRANT OPTION選項導致後來該使用者不能使用GRANT命令建立使用者或者給其他使用者授權。

備忘：可以使用GRANT重複給使用者添加許可權，許可權疊加，比如你先給使用者添加了一個select許可權，然後又給使用者添加了一個insert許可權，那麼該使用者就同時擁有了select和insert許可權。

2.  建立一個超級使用者

建立一個只允許從本地登入的超級使用者feihong，並允許將許可權賦予別的使用者，密碼為test@feihong.111

GRANT ALL PRIVILEGES ON *.* TO feihong@'localhost' IDENTIFIED BY 'test@feihong.111' WITH GRANT OPTION;

3.   建立一個網站使用者(程式使用者)

建立一個一般的程式使用者，這個使用者可能只需要SELECT, INSERT, UPDATE, DELETE, CREATE TEMPORARY TABLES等許可權如果有預存程序還需要加上EXECUTE許可權，一般是指定內網網段192.168.100網段。

GRANT  USAGE,SELECT, INSERT, UPDATE, DELETE, SHOW VIEW ,CREATE TEMPORARY TABLES,EXECUTE ON `test`.* TO webuser@'192.168.100.%' IDENTIFIED BY  'test@feihong.111';

4.  建立一個普通使用者(僅有查詢許可權)

GRANT USAGE,SELECT ON `test`.* TO public@'192.168.100.%' IDENTIFIED BY  'public@feihong.111';

5.  重新整理許可權

使用這個命令使許可權生效，尤其是你對那些許可權表user、db、host等做了update或者delete更新的時候。以前遇到過使用grant後許可權沒有更新的情況，大家可以養成習慣，只要對許可權做了更改就使用FLUSH PRIVILEGES命令來重新整理許可權。

FLUSH PRIVILEGES;

6.  查看許可權

使用如下命令可以方便的查看到某個使用者的許可權：

SHOW GRANTS FOR 'webuser'@'192.168.100.%';

7.  回收許可權

將前面建立的webuser使用者的DELETE許可權回收，使用如下命令

REVOKE DELETE ON test.* FROM 'webuser'@'192.168.100.%';

8.  刪除使用者

注意刪除使用者不要使用DELETE直接刪除，因為使用DELETE刪除後使用者的許可權並未刪除，建立同名使用者後又會繼承以前的許可權。正確的做法是使用DROP USER命令刪除使用者，比如要刪除'webuser'@'192.168.100.%'使用者採用如下命令：

DROP USER 'webuser'@'192.168.100.%';

        

大家可以採用percona-toolkit工具中的pt-show-grants工具來輔助管理mysql許可權。具體使用見博文http://blog.chinaunix.net/uid-20639775-id-3207926.html