MySQL--10.創建 MySQL 用戶及賦予用戶許可權

0.1 使用語法:

通過在 mysql 中輸入 help grant 得到如下幫助信息

CREATE USER 'jeffrey'@'localhost' IDENTIFIED BY 'mypass';
GRANT ALL ON db1.* TO 'jeffrey'@'localhost';
GRANT SELECT ON db2.invoice TO 'jeffrey'@'localhost';
GRANT USAGE ON *.* TO 'jeffrey'@'localhost' WITH MAX_QUERIES_PER_HOUR 90;​

10.2 第一種創建用戶及授權方法：

創建用戶

mysql> create user oldboy@'localhost' identified by 'oldboy';
Query OK, 0 rows affected (0.00 sec)​

查看用戶其許可權

mysql> show grants for oldboy@'localhost';
+---------------------------------------------------------------------------------------------------------------+
| Grants for oldboy@localhost
|
+---------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'oldboy'@'localhost' IDENTIFIED BY PASSWORD
'*7495041D24E489A0096DCFA036B166446FDDD992' |
+---------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)​

授權用戶許可權

mysql> grant all on oldboy_gbk.* to oldboy@'localhost';
Query OK, 0 rows affected (0.04 sec)
mysql> show grants for oldboy@'localhost';
+---------------------------------------------------------------------------------------------------------------+
| Grants for oldboy@localhost
|
+---------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'oldboy'@'localhost' IDENTIFIED BY PASSWORD
'*7495041D24E489A0096DCFA036B166446FDDD992' |
| GRANT ALL PRIVILEGES ON `oldboy_gbk`.* TO 'oldboy'@'localhost'
|
+---------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)​

10.3 第二種創建用戶及授權方法：

mysql> grant all on oldboy_gbk.* to oldgirl@'localhost' identified by 'oldgirl';
Query OK, 0 rows affected (0.00 sec)
列表說明:
grant all on dbname.* to username@’lcoalhost’ identified by ‘password’
授 權命令對應許可權
目標：庫和表 用戶名和客戶端主機 用戶密碼
mysql> show grants for oldgirl@'localhost';
+----------------------------------------------------------------------------------------------------------------+
| Grants for oldgirl@localhost
|
+----------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'oldgirl'@'localhost' IDENTIFIED BY PASSWORD
'*4FD27385BB43242FE02158144D4C211F75A03F76' |
| GRANT ALL PRIVILEGES ON `oldboy_gbk`.* TO 'oldgirl'@'localhost'
|
+----------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)​

10.4 創建用戶及授權哪個網段的主機可以連接 oldboy_gbk 庫

提示：如果是 web 連接資料庫的用戶，儘量不要授權 all，而是 select，insert，update，delete
10.4.1 第一種方法：

mysql> grant all on oldboy_gbk.* to oldgirl@'172.16.1.%' identified by 'oldgirl';
Query OK, 0 rows affected (0.00 sec)
%表示 172.16.1.1-255 網段​

10.4.2 第二種方法：

mysql> grant all on oldboy_gbk.* to oldgirl@'172.16.1.0/255.255.255.0' identified by 'oldgirl';
Query OK, 0 rows affected (0.00 sec)
提示:不能這樣寫 oldgirl@’172.16.1.0/24’​

10.5 關於 mysql 回收某個用戶許可權

語法格式：

REVOKE
 priv_type [(column_list)]
 [, priv_type [(column_list)]] ...
 ON [object_type] priv_level
 FROM user [, user] ...
REVOKE ALL PRIVILEGES, GRANT OPTION
FROM user [, user] ...​

實例:查看 oldboy 用戶回收許可權前的許可權

mysql> show grants for oldboy@'localhost';
+---------------------------------------------------------------------------------------------------------------+
| Grants for oldboy@localhost
|
+---------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'oldboy'@'localhost' IDENTIFIED BY PASSWORD
'*7495041D24E489A0096DCFA036B166446FDDD992' |
| GRANT ALL PRIVILEGES ON `oldboy_gbk`.* TO 'oldboy'@'localhost'
|
+---------------------------------------------------------------------------------------------------------------+
3 rows in set (0.00 sec)​

查看回收 oldboy 用戶的 insert 許可權之後的許可權

mysql> REVOKE INSERT ON oldboy_gbk.* FROM 'oldboy'@'localhost';
Query OK, 0 rows affected (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
mysql> show grants for 'oldboy'@'localhost';
+---------------------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------------+
| Grants for oldboy@localhost
|
+---------------------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'oldboy'@'localhost' IDENTIFIED BY PASSWORD
'*7495041D24E489A0096DCFA036B166446FDDD992'
|
| GRANT SELECT, UPDATE, DELETE, CREATE, DROP, REFERENCES, INDEX, ALTER,
CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, CREATE VIEW, SHOW VIEW,
CREATE ROUTINE, ALTER ROUTINE, EVENT, TRIGGER ON `oldboy_gbk`.* TO
'oldboy'@'localhost' |
+---------------------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)​

10.6 企業生產環境如何授權用戶許可權（mysql 主庫）

博客，CMS 等產品的資料庫授權：
對於 web 連接用戶授權儘量採用最小化原則，很多開源軟體都是 web 界面安裝，因此，在安裝期間除了 select，insert，update，delete4 個許可權外，還需要 create，drop 等比較危險的許可權

mysql> grant insert,delete,update,select on blog.* to blog@'172.16.1.%' identified by 'blog';
Query OK, 0 rows affected (0.00 sec)
mysql> show grants for blog@'172.16.1.%';
+--------------------------------------------------------------------------------------------------------------+
| Grants for [email protected].%
|
+--------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'blog'@'172.16.1.%' IDENTIFIED BY PASSWORD
'*A5BA49C964C6DB89302E2EA293048E9224B33F34' |
| GRANT SELECT, INSERT, UPDATE, DELETE ON `blog`.* TO 'blog'@'172.16.1.%'
|
+--------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)​

常規情況下授權 select，insert，update，delete4 個許可權即可，有的開源軟體，例如 discuz bbs，
還需要 create，drop 等比較危險的許可權，生成資料庫表後，要收回 create、drop 許可權

mysql> revoke drop,create on blog.* from blog@'172.16.1.%';
Query OK, 0 rows affected (0.00 sec)
to your MySQL server version for the right syntax to use near 'from blog@'172.16.1.%'' at line 1
mysql> show grants for blog@'172.16.1.%';
+--------------------------------------------------------------------------------------------------------------+
| Grants for [email protected].%
|
+--------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'blog'@'172.16.1.%' IDENTIFIED BY PASSWORD
'*A5BA49C964C6DB89302E2EA293048E9224B33F34' |
| GRANT SELECT, INSERT, UPDATE, DELETE ON `blog`.* TO 'blog'@'172.16.1.%'
|
+--------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)