通過修改MySQL用戶授權IP了解其用戶管理

下文主要給大家帶來通過修改MySQL用戶授權IP了解其用戶管理，希望這些內容能夠帶給大家實際用處，這也是我編輯通過修改MySQL用戶授權IP了解其用戶管理這篇文章的主要目的。好了，廢話不多說，大家直接看下文吧。

近期把數據庫用戶的授權IP由IP段, 調整為具體IP了, 用意是排查問題時, 可以定位到具體應用云服務器, 或針對性的做某些設置.

本以為一個UPDATE就可搞定, 測試后卻發現被修改的用戶只剩下USAGE權限了, 演示如下:

mysql> SELECT user, host, password FROM mysql.userWHERE user = 'zzzz_acc'\G

*************************** 1. row***************************

   user: zzzz_acc

   host: 192.168.4.%

password:*2B979ED0716E5FCB08CA97C284FE270B65991F34

1 row in set (0.00 sec)

mysql> SHOW GRANTS FOR 'zzzz_acc'@'192.168.4.%'\G

*************************** 1. row***************************

Grants for zzzz_acc@192.168.4.%: GRANTUSAGE ON *.* TO 'zzzz_acc'@'192.168.4.%' IDENTIFIED BY PASSWORD'*2B979ED0716E5FCB08CA97C284FE270B65991F34'

*************************** 2. row***************************

Grants for zzzz_acc@192.168.4.%: GRANTSELECT, INSERT, DELETE, EXECUTE ON `zzzz`.* TO 'zzzz_acc'@'192.168.4.%'

2 rows in set (0.00 sec)

mysql> UPDATE mysql.user SET host ='192.168.4.14' WHERE user = 'zzzz_acc' AND host = '192.168.4.%';

Query OK, 1 row affected (0.00 sec)

Rows matched: 1  Changed: 1 Warnings: 0

手動修改權限表后, 要FLUSH下:

mysql> FLUSH PRIVILEGES;

Query OK, 0 rows affected (0.00 sec)

mysql> SHOW GRANTS FOR'zzzz_acc'@'192.168.4.14'\G

*************************** 1. row***************************

Grants for zzzz_acc@192.168.4.14: GRANTUSAGE ON *.* TO 'zzzz_acc'@'192.168.4.14' IDENTIFIED BY PASSWORD'*2B979ED0716E5FCB08CA97C284FE270B65991F34'

1 row in set (0.00 sec)

原因呢, 是該用戶權限是數據庫級別的, mysql.db表也要做相應的修改:

mysql> SELECT host, db, user FROM dbWHERE host = '192.168.4.%'\G

*************************** 1. row***************************

host: 192.168.4.%

  db:zzzz

user: zzzz_acc

1 row in set (0.00 sec)

mysql> UPDATE mysql.db SET host ='192.168.4.14' WHERE user = 'zzzz_acc' AND host = '192.168.4.%';

Query OK, 1 row affected (0.00 sec)

Rows matched: 1  Changed: 1 Warnings: 0

mysql> FLUSH PRIVILEGES;

Query OK, 0 rows affected (0.00 sec)

瞧, 這時權限回來了:

mysql> SHOW GRANTS FOR'zzzz_acc'@'192.168.4.14'\G

*************************** 1. row***************************

Grants for zzzz_acc@192.168.4.14: GRANTUSAGE ON *.* TO 'zzzz_acc'@'192.168.4.14' IDENTIFIED BY PASSWORD'*2B979ED0716E5FCB08CA97C284FE270B65991F34'

*************************** 2. row***************************

Grants for zzzz_acc@192.168.4.14: GRANTSELECT, INSERT, DELETE, EXECUTE ON `zzzz`.* TO 'zzzz_acc'@'192.168.4.14'

2 rows in set (0.00 sec)

總結下, 就是一般不要手動直接修改權限表, 官方文檔也不建議這樣. MySQL中對于用戶的管理, 如創建用戶, 調整權限, 修改密碼等, 都提供了相應的命令. 此處利用RENAME USER, 即可一步到位:

mysql> RENAME USER'zzzz_acc'@'192.168.4.%' TO 'zzzz_acc'@'192.168.4.14';

Query OK, 0 rows affected (0.01 sec)

但也存在著變通, 這里想起之前遇到的一個問題, 數據庫的管理用戶'root'@'localhost'有ALL PRIVILEGES的權限, 但沒有了GRANT OPTION權限(不要問我為什么會這樣). 即該管理用戶, 不能賦予和撤銷其它用戶的權限, 也意味著不能創建實際有效的用戶了, 演示如下:

mysql> SHOW GRANTS\G

*************************** 1. row***************************

Grants for root@localhost: GRANT ALLPRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED BYPASSWORD '*2B979ED0716E5FCB08CA97C284FE270B65991F34'

1 row in set (0.00 sec)

mysql> CREATE USER'zzzz_acc2'@'192.168.4.15' IDENTIFIED BY 'zzzz_acc2';

Query OK, 0 rows affected (0.15 sec)

mysql> GRANT SELECT, INSERT, DELETE,EXECUTE ON `zzzz`.* TO 'zzzz_acc2'@'192.168.4.15';

ERROR 1044 (42000): Access denied for user'root'@'localhost' to database 'zzzz'

怎么辦呢, 也只能直接修改mysql.user表的grant_priv字段了:

mysql> SELECT user, host, password,grant_priv FROM mysql.user WHERE user = 'root' AND host = 'localhost'\G

*************************** 1. row***************************

      user: root

     host: localhost

 password: *2B979ED0716E5FCB08CA97C284FE270B65991F34

grant_priv: N

1 row in set (0.00 sec)

mysql> UPDATE mysql.user SET grant_priv= 'Y' WHERE user = 'root' AND host = 'localhost';

Query OK, 1 row affected (0.00 sec)

Rows matched: 1  Changed: 1 Warnings: 0

mysql> FLUSH PRIVILEGES;

Query OK, 0 rows affected (0.00 sec)

mysql> SHOW GRANTS\G

*************************** 1. row***************************

Grants for root@localhost: GRANT ALLPRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED BY PASSWORD'*2B979ED0716E5FCB08CA97C284FE270B65991F34' WITH GRANTOPTION

1 row in set (0.00 sec)

下面注意了, 雖然看到了GRANT OPTION權限, 還要重新登陸下, 該權限才能生效, 類似于修改了全局參數, 要重新連接下一樣, 否則還會遇到上面的報錯.

mysql> GRANT SELECT, INSERT,DELETE, EXECUTE ON `zzzz`.* TO 'zzzz_acc2'@'192.168.4.15';

Query OK, 0 rows affected (0.00 sec)

mysql> SHOW GRANTS FOR'zzzz_acc2'@'192.168.4.15'\G

*************************** 1. row***************************

Grants for zzzz_acc2@192.168.4.15: GRANTUSAGE ON *.* TO 'zzzz_acc2'@'192.168.4.15' IDENTIFIED BY PASSWORD'*0CF81DDE0A7213E4AB18F2925316C049BCF3F2E8'

*************************** 2. row***************************

Grants for zzzz_acc2@192.168.4.15: GRANTSELECT, INSERT, DELETE, EXECUTE ON `zzzz`.* TO 'zzzz_acc2'@'192.168.4.15'

2 rows in set (0.00 sec)

上面兩個示例涉及到了MySQL權限表中的user, 和db. 前者記錄了用戶的全局權限和一些非權限信息, 后者記錄了數據庫級別權限; 與權限相關的還有table_priv, columns_priv和 proc_priv表, 分別對應了表, 列, 和存儲過程(函數)級別的權限(proxies_priv代理用戶權限暫不提).

最后再看下MySQL 5.6版本在管理用戶密碼部分中不太完善的地方, 由于項目臨時調試, 需要開放線上數據庫一個特定時長的臨時只讀權限(在從庫上), 可是5.6版本只提供了密碼過期設置, 并沒有對過期時長做限制. 這些細節在5.7版本中有很多完善, 如密碼何時過期, 用戶鎖定, SSL加密連接設置等.

對于以上關于通過修改MySQL用戶授權IP了解其用戶管理，大家是不是覺得非常有幫助。如果需要了解更多內容，請繼續關注我們的行業資訊，相信你會喜歡上這些內容的。