塗抹MySQL--第5章 MySQL資料庫中的許可權體系 - 5.2許可權授予與回收(1)

5.2 許可權授予與回收

當前MySQL就剩system一個系統管理員帳戶了，完全不符合業務需求啊，怎麼辦呢，本節就來著重演示MySQL資料庫中如何建立使用者、分配許可權以及回收許可權。

在MySQL資料庫裡對於使用者許可權的授予和解除比較靈活，即可以透過專用命令，也可以透過直接操作字典表來實現，正所謂條條道路通目標。不過話說回來，修的馬路多不叫奇蹟，何況在中國這片神奇的土地，奇蹟這個詞本身就是奇蹟，因此三思真是不好意思用奇蹟這樣的詞來形容：這樣想像不到的不平凡的事(注，該段描述為現代漢語詞典中關於奇蹟一詞的解釋)，因此，我決定用一種加強的語氣來描述我的感受：

比奇蹟更神奇的是，這條條大路居然都修成了高速路；

比神奇的奇蹟更神奇的是，這些高速居然都是免費的；

比神奇的神奇奇蹟更神奇，那就是神蹟啊，額地神哪，免費的高速居然也不堵車，這肯定不是二環三環和四環，當然跟G6/G8線應該也沒啥關係，至少也是十八環外了，弟兄們，走吧，跟著三思去溜達溜達~~~

再次提示：

很多Linux/Unix下管理MySQL資料庫服務的DBA，初看到資料庫的管理帳戶root就發矇了，以為這是什麼重要的徵兆，其實是大可不必的，此root非彼root，MySQL資料庫裡的root帳戶跟作業系統中的root沒有絲毫的關聯，只是資料庫初始化時自動建立的這個名稱而已。在本書第三章初始化資料庫時，三思已經手動將該使用者更名為了system，我們的操作能夠成功，並且未對後續資料庫的正常管理帶來任何異常，也說明root這個帳戶名不具備什麼特殊的含義，完全可以隨意處理。

基於合適的使用者做符合其許可權的事的目地，執行與許可權相關操作的使用者當然也得有許可權，預設我們使用的是系統管理員帳戶，就是system使用者了，本例中所做的使用者管理操作，如非特別註明，均是使用mysql中的system使用者執行。

 

5.2.1 建立使用者

在建立使用者之前，首先說明兩點：

l 使用者名稱的長度不能超過16個字元；

l 使用者名稱和密碼對大小寫敏感，也就是說使用者Jss和jss是兩個不同的使用者，密碼也是如此；

5.2.1.1 傳統方式建立

MySQL中專用的建立使用者的命令是CREATE USER，該命令語法如下：

CREATE USER user_specification

    [, user_specification] ...

 

user_specification:

    user

    [

        IDENTIFIED BY [PASSWORD] 'password'

      | IDENTIFIED WITH auth_plugin [AS 'auth_string']

    ]

CREATE USER命令是最傳統的建立使用者方式，語法看起來還是挺簡單的，不過事實上使用者許可權相關的細節非常有講究，因為簡單，所以靈活，因為靈活，所以可配置性強，因為可配性強，所以細節很重要。

不過，剛開始接觸時，大家倒是不用關注太多，從易到難嘛，我們們先按照最簡單的方式建立一個名為jss的使用者吧，執行操作如下：

(system@localhost) [mysql]> create user jss;

Query OK, 0 rows affected (0.01 sec)

你猜怎麼著，成功了！不要擔心"0 rows affected"那個提示，對於操作使用者這類SQL語句，它的返回就是這樣樣子，只要不是返回什麼ERROR之類提示，就是成功了，如果想看到明確的結果，可以透過查詢mysql.user字典表中的記錄驗證一下：

(system@localhost) [mysql]> select user,host,password from mysql.user where user='jss';

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

| user | host | password |

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

| jss  | %    |          |

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

1 row in set (0.00 sec)

 

當然啦，最好的驗證方式仍然是登入測試，我們剛剛建立的使用者，即沒有設定登入的密碼，也沒有指定來源主機，因此該使用者可以從任意安裝了mysql客戶端，並能夠訪問目標伺服器的機器上建立連線。

換臺裝有MySQL客戶端的伺服器登入試試，例如：

[mysql@mysqldb02 ~]$ mysql -ujss -h 192.168.30.243

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

Your MySQL connection id is 8

Server version: 5.6.12-log JSS for mysqltest

 

Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.

 

Oracle is a registered trademark of Oracle Corporation and/or its

affiliates. Other names may be trademarks of their respective

owners.

 

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

 

(jss@192.168.30.243) [(none)]> 

可以看到當前就是以jss身份連線到30.243伺服器。由於前面建立使用者時並沒有指定任何密碼，因此連線時無須指定密碼即可順利登入資料庫。

5.2.1.2 修改使用者密碼

想必讀者朋友也都看出來了，這樣登入很不安全，密碼這個可以有。那麼怎麼給使用者設定密碼呢？ALTER USER？NONONO，我們一般都不會這樣幹，甚至在MySQL 5.6.6版本之前，根本就沒有提供ALTER USER這樣的語法。“怎麼會這樣”，您是否在心裡暗自問自己這個問題，其實若對MySQL的使用者與許可權體系有全面的認識，就會明白這種設計，對於MySQL資料庫來說是合乎邏輯的。

MySQL資料庫中的使用者沒有太多屬性，從前面的CREATE USER語法就能看的出來，與使用者相關的選項，除了必須指定的使用者名稱外，就是一個密碼選項(唯一一個選項居然還不是必選項)。至於使用者許可權的授予，則是由單獨的SQL命令操作(後面會介紹這些命令)。因此對於使用者來說，可能變更的就是使用者的密碼，針對這一點需求，MySQL沒必要整出一個ALTER USER語法，它只需要單獨針對修改密碼的操作，提供一條命令即可，於是就有了SET PASSWORD命令，該命令語法如下：

SET PASSWORD [FOR user] =

    {

        PASSWORD('some password')

      | OLD_PASSWORD('some password')

      | 'encrypted password'

    }

比如，修改jss使用者的密碼為5ienet.com，執行命令如下：

(jss@192.168.30.243) [(none)]> set password for jss=password('5ienet.com');

Query OK, 0 rows affected (0.00 sec)

SET PASSWORD命令會自動更新系統授權表，之後再使用jss使用者連線MySQL資料庫，就必須輸入密碼才行，否則就會丟擲：

ERROR 1045 (28000): Access denied for user 'jss'@'192.168.30.203' (using password: NO)

 

說一下SET PASSWORD命令中各選項的功能：

l SET PASSWORD：固定的語法格式，照著抄即可；

l [FOR user]：FOR選項用於指定要修改密碼的使用者，如果是修改當前使用者的密碼，可以不用指定這個選項，如果要修改其它使用者(前提是操作者確實有許可權)，那麼必須透過FOR選項指定要修改的目標使用者，格式為user@host；

l PASSWORD/OLD_PASSWORD：這是兩個密碼專用函式。MySQL資料庫中使用者密碼當然不會是以明文的形式儲存，它可不像國內某些專業IT社群那樣，打著專業旗號卻幹出很不專業的事情。MySQL中能夠查詢到的使用者密碼是按照它自己的加密邏輯處理後的字串形式。在修改密碼時，也必須指定加密後的字元形式儲存，否則登入驗證就會碰到異常。可是，都說了是加密後的形式，那我們又怎麼能知道字元被加密後是什麼形式呢，這裡就要分兩點來看：

n 第一種是使用者確實知道，甭管它是透過什麼方式獲得的(確實有多種方式)，那麼在指定密碼時就可以直接指定其加密後的形式；

n 第二種是使用者不知道加密後的字元是什麼，那麼就可以由MySQL來幫助我們生成，MySQL資料庫提供了相應的函式PASSWORD()，直接呼叫該函式即可，這種方式是最常見的呼叫方式，我們前面的示例中也是採用這種方式。

 

提示：關於OLD_PASSWORD()函式。

這個函式的命名容易產生誤解，看起來彷彿是跟使用者的舊密碼有什麼關係，其實不是這樣，它只是為了應對MySQL的版本相容性才出現的。在4.1之前的版本中，PASSWORD()函式生成16位長度的加密字串，而在之後的版本中，為了提高安全性，MySQL改進了密碼的生成演算法，現在生成的加密串為41位長度的字串，那麼這就會出現一個相容性方面的問題，就是將使用者使用4.1之前的客戶端連線MySQL服務時，就會出現由於加密格式不統一造成的登入失敗，為了提高相容性，MySQL新增加了OLD_PASSWORD()函式，仍然採用原始的加密策略生成16位長度的字串，管理員在設定使用者口令時，就可以使用這個函式生成密碼，使其能夠相容4.1之前版本的MySQL客戶端。

兩個函式處理相同字串的輸出如下：

(jss@192.168.30.243) [(none)]> select password('123456'),old_password('123456');

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

| password('123456')                        | old_password('123456') |

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

| *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 | 565491d704013245       |

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

前面提到，在5.6.6版本之前，MySQL資料庫都沒有ALTER USER語法，那麼為什麼後來又增加了ALTER USER，這個語法又能用來做什麼呢？為什麼增加這個語句我也沒想明白，不過這個語句的功能可能要讓很多人打死都猜不到。新增的ALTER USER語句的功能，與其它資料庫軟體中的ALTER USER功能差異巨大，一言以蔽之，就是讓使用者的密碼過期。注意一定要正確理解，是密碼過期，而不是使用者過期喲。使用者仍然可以用(登入)，只是密碼過期後，無法做任何操作。

比如說，我們先將jss使用者密碼設定為過期，執行操作如下：

(system@localhost) [mysql]> alter user jss password expire;

Query OK, 0 rows affected (0.00 sec)

而後再以jss使用者登入，用原始密碼仍然能夠登入成功，但是做操作就不行嘍：

(jss@192.168.30.243) [(none)]> show databases;

ERROR 1820 (HY000): You must SET PASSWORD before executing this statement

實踐過之後，您是否回憶起了什麼，或者說您現在應該知道，第二章RPM包方式安裝後連線資料庫，必須先修改使用者密碼才能執行操作，是如何實現的了吧。

5.1.2.3 透過登入主機驗證使用者

話說MySQL資料庫中，使用者登入除了驗證使用者名稱和密碼外，不是號稱還要檢查來源主機呢嘛，怎麼前面的登入操作，似乎並未感到有對主機層的驗證呢。這個嘛，因為建立使用者時就沒有指定登入主機啊，沒指定，預設就是不限制。不過這個“不限制”指的是不做限制，實際上字典表中還是會有對應的標識，查詢一下mysql.user字典表中的資訊：

(system@localhost) [(mysql)]> select user,host from mysql.user where user='jss';

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

| user | host |

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

| jss  | %    |

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

1 rows in set (0.00 sec)

注意到這條記錄中host列的值了沒，顯示一個% 百分號。熟悉SQL語法的朋友都知道，%在SQL語法中是做為萬用字元，代表任意字串，在這裡出現則代表任意主機，這個才是前面所說的不限制登入主機的真正原則。

沒錯，主機名可以指定萬用字元，規則與標準的SQL語法中定義完全相同：

l %：對應任意長度的任意字元；

l _：對應一位長度的任意字元；

如果user字典表中的Host列值為空或%，均代表任意主機。因此，如果希望建立的使用者只能從某個主機，或某個IP段訪問，那麼在建立使用者時，就必須明確指定host，指定的host即可以是IP，也可以是主機名，或者是可正確解析至IP地址的其它自定義名稱。

接下來我們嘗試建立一個名為jss_ip的使用者，並且該使用者僅允許從192.168.30.203的主機連線至MySQL服務端，執行命令如下：

(system@localhost) [(mysql)]> create user jss_ip@'192.168.30.203' identified by 'jss';

Query OK, 0 rows affected (0.00 sec)

這樣使用jss_ip使用者登入時，只有從192.168.30.203主機發出登入請求才能成功，從非192.168.30.203的主機上，使用jss_ip的使用者連線時，不管密碼是否正確，都會丟擲ERROR 1045 (28000): Access denied錯誤資訊：

$ mysql -ujss_ip -pjss -h 192.168.30.243

ERROR 1045 (28000): Access denied for user 'jss_ip'@'192.168.10.113' (using password: YES)

如果希望192.168.30.%網段的主機均能夠使用jss_ip使用者連線，又該如何設定呢，這種情況下就該萬用字元出馬了：

(system@localhost) [(none)]> create user jss_ip@'192.168.30.%' identified by 'jss';

Query OK, 0 rows affected (0.00 sec)

而後從192.168.30.%網段的任意主機上嘗試連線MySQL伺服器，都能夠順利登入：

$ mysql -ujss_ip -pjss -h 192.168.30.243

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

 

其它大型資料庫軟體，直接指定使用者即可登入資料庫，但在MySQL資料庫中，則額外還需要有主機這一維度，使用者和主機('user'@'host')組成一個唯一帳戶，登入MySQL資料庫時，實際上是透過帳戶進行驗證。

由於Host能夠支援萬用字元，使得登入驗證時來源主機的部分更加靈活，下表列舉了一些User和Host的常見組合，希望能夠有助於大家理解。

User列	Host列	對應連線情況
'jss'	'192.168.1.2'	使用jss使用者登入時，只有從192.168.1.2主機發出登入請求才能成功建立連線
'jss'	'%'	使用jss使用者登入時，可以從主機名為(net/com/cn....)的任意主機建立連線
'jss'	'com'	使用jss使用者登入時，只能從主機名為com的主機發出請求才能成功建立連線
'jss'	'%'	可以從任意主機使用jss使用者連線
''	'10.0.0.%'	可以從10.0.0.%網段內的任意主機建立連線，並且無須輸入任何使用者資訊
''	'%'	任意主機均可以建立連線，並且連線過程中無須使用者資訊

表5-1 使用者與主機組合示例

大家是否注意到上表中前幾行記錄中的使用者名稱都叫jss，不過實際上它們不僅不是同一條記錄，甚至不是一個使用者，因為MySQL資料庫是根據'user'@'host'來唯一一條記錄，user表中每一條記錄都是一個獨立的帳戶，每一個獨立的帳戶都可以擁有各自的許可權設定。

這種設計對於初接觸MySQL資料庫的朋友的確可能帶來困擾，因為大家一般都只聽過有user，誰能想到這中間還夾著一層host，不過我舉個例子大家應該就明白了。比如說您有兩位同事，都叫楊偉(user)，一個從山東(host)來，另一個從山西(host)來，您就知道他們肯定不是一個人，這種情況擱現實生活中叫重名，兩個確實是各自獨立的個體。

“重名”說盡管能夠幫助大家理解user+host的組合，不過朋友們可能還是會有疑問，就是重名所帶來的現實尷尬，比方說有可能碰到你喊一聲“美女”，結果一堆人答應的場景，那MySQL資料庫中會不會出現這種情況呢，它又怎麼保證一定是那個你想搭訕的姑娘回應呢。按照我的理解，拿這個問題拷問MySQL的智商實在太難為它了，別說MySQL搞不清楚，就是換個活生生的人也搞不定啊，因此，肯定的答覆就是，MySQL保證不了。

不過放心啦，MySQL不會返回一堆記錄讓人無所適從的，因為規矩是限定死的嘛，只能有一條回應，當然啦，它也不會隨隨便便挑一個給你，做為一款資料庫軟體，“嚴謹”是烙印在它的基因中的，MySQL遇到這種情況，會按照既定的規則來處理，處理的規則歸根結底就兩個字：排序，而後從排好序的結果中取第一條記錄。

MySQL在排序時會將最明確的Host值放在前面，比如說某個具體的主機名或IP地址就非常明確，而像萬用字元"%"就是最不明確的代表(它代表任意主機)，排序時會放在後面，空字串''儘管也表示任意主機，但排序的優先順序比'%'更低，它會放在最後。對於Host相同的記錄，MySQL會再按照User列中的值排序，規則與Host完全相同，都是最明確的值放在最前面。

舉例來說，user字典表中有下列的記錄：

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

| Host      | User     | ...

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

| %         | system   | ...

| %         | jss      | ...

| localhost | system   | ...

| localhost |          | ...

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

按照MySQL資料庫的規則，排序好之後的結果類似這樣：

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

| Host      | User     | ...

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

| localhost | system   | ...

| localhost |          | ...

| %         | jss      | ...

| %         | system   | ...

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

 

提示：

排序是什麼時候做的呢？要知道，MySQL在服務啟動時就會將user表讀取到記憶體中，在讀取的過程中就會排序。MySQL服務執行過程中，修改使用者許可權觸發許可權更新時，會重新整理記憶體中的字典表，這期間又會進行排序，也就是記憶體中的字典表永遠都是排好序的。

客戶端建立連線時使用的使用者名稱和主機，有可能同時匹配user表中的多條記錄，在上面給出的例子中，使用system使用者登入就有可能即匹配system@'localhost'，又匹配system@'%'兩條記錄，按照前面介紹的規則，如果是在localhost本地執行登入，那麼一定會匹配為system@'localhost'這個使用者，否則的話，則會是system@'%'這個使用者了。

再給一個例子，user表中有如下兩條記錄：

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

| Host           | User     | ...

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

| com |          | ...

| %              | jss      | ...

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

當使用者使用jss使用者並且從"com"主機登入MySQL資料庫時，會匹配第一條記錄，如果是從其它主機登入的話則是匹配第二條記錄。實際上，從com主機登入MySQL的話，是否指定使用者根本就沒有區別，因為，"com"這個已經非常明確，並且user列值為空字串，也就代表著只要是從com主機發出的登入請求，不管指定的使用者是什麼(甚至可以是user表中不存在的使用者)，均會匹配為這條記錄。 

5.1.2.4 GRANT方式建立使用者

CREATE USER只是建立使用者的高速路之一，如果你覺著這條道路實在太過平坦，路邊風景太過平淡，行程太過平常，不妨在抵達目的地之前，拐彎開上GRANT大道，飽覽不一樣的風景。

GRANT命令並非本小節重點，這裡僅簡要描述一下其語句中與使用者相關的部分：

GRANT priv_clause TO user [IDENTIFIED BY [PASSWORD] 'password'] ...

與建立使用者相關的語法，看起來跟CREATE USER是差不多的嘛，事實上當然不是差不多，根本就是一模一樣嘛，下面舉個例子，操作如下：

(system@localhost) [(mysql)]> grant select on jssdb.* to jss_grant@192.168.30.203 identified by 'jss';

Query OK, 0 rows affected (0.00 sec)

 

(system@localhost) [(mysql)]> select user,host,password from mysql.user where user ='jss_grant';

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

| user      | host           | password                                  |

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

| jss_grant | 192.168.30.203 | *284578888014774CC4EF4C5C292F694CEDBB5457 |

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

1 row in set (0.00 sec)

上述語句在實現了前面第3個例子(建立使用者jss_ip)的功能外，還額外授予了jss_grant使用者查詢mysql.user表的許可權。MySQL的開發團隊靠著永不屈服、永不放棄、永不退縮、永不言敗的奮爭精神，用智慧和巧妙的構思完美複製了ORACLE GRANT語句的功能，這是全世界默默無聞的MySQL開發人員長期以來內生品格的自然流露，是全世界默默無聞的MySQL開發人員開拓前進的不竭動力，這就是傳說中的，瑞典夢。

 

5.1.2.5 另類方式建立使用者

如果說上述方式覺著都不順手，或者，大腦短路導致短暫忘記了命令的語法，那也沒關係，mysql.user表還記得吧，直接向該字典表中插入記錄(一般insert語法想忘不容易)，也是靠譜的，例如：

(system@localhost) [(none)]> insert into mysql.user (host,user,password,ssl_cipher,x509_issuer,x509_subject) values ('192.168.30.203','jss_insert',password('jss'),'','','');

Query OK, 1 row affected (0.00 sec)

 

 

(system@localhost) [(none)]> select user,host,password from mysql.user where user ='jss_insert';

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

| user       | host           | password                                  |

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

| jss_insert | 192.168.30.203 | *284578888014774CC4EF4C5C292F694CEDBB5457 |

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

1 row in set (0.00 sec)

手動修改許可權字典表後，需要執行FLUSH PRIVILEGES語句，重新載入授權資訊到記憶體中，否則手動修改的許可權不會生效，執行操作如下：

(system@localhost) [none]> flush privileges;

Query OK, 0 rows affected (0.00 sec)

接下來可以嘗試從192.168.30.203主機，分別使用jss_insert使用者和jss_ip登入，對比看看效果，不僅看起來相同，實際表現也是一模一樣。

這點跟ORACLE資料庫就截然不同了，ORACLE這類資料庫是絕對不建議使用者修改資料字典表的，而且一般情況下也不知道都應該改哪些地方(沒錯，完全可能不止一處需要修改)，因此對於ORACLE資料庫，最安全最穩妥也最快捷的方式，還是老老實實按照ORACLE提供的命令進行操作，而MySQL則完全不同，官方不僅完全不介意使用者透過操作字典表的方式進行功能修改(想想也是，連軟體都是開源的，在這種地方設什麼障礙也沒有意義)，甚至鼓勵透過這種方式，話說回來，截止到MySQL5.6.12版本，都沒還沒有提供修改"使用者屬性"的ALTER USER的語法，因此如果想對使用者屬性做修改，直接update mysql.user表就算是比較便捷的方式了。

當然啦，MySQL中的使用者其實也沒什麼屬性可供修改，大多都是許可權，唯一稱的上屬性又有修改可能的，就是使用者的密碼資訊了，前面介紹過SET PASSWORD語句，專用於修改使用者密碼非常專業，但是它並不是唯一的方法，在MySQL資料庫中，我們可以使用更加直接的方式。實際上之前我們就這麼幹過，還記的第三章中修改過root使用者密碼時所做的操作嗎，沒做，使用者的資訊儲存在mysql.user字典表中，我們直接修改該表也是一樣的。

例如，直接修改字典表，將jss使用者的密碼變更為123456，執行操作如下：

(system@localhost) [(none)]> update mysql.user set password=password('123456') where user='jss' and host='%';

Query OK, 1 row affected (0.00 sec)

Rows matched: 1  Changed: 1  Warnings: 0