引言

某天，女朋友突然問你：“還有多少私房錢？”這個時候驚恐的你該怎么辦呢？直接把余額給她看？顯然很不符合我們的性格；如果這個時候能有一個臨時的支付寶賬號，讓她看不到真實的余額該有多好啊！

這樣的賬號就涉及到了數(shù)據(jù)庫的權(quán)限問題，下面我們一起來討論一下 MySQL 中的權(quán)限管理。

權(quán)限的驗證流程

通常，我們提及數(shù)據(jù)庫中的權(quán)限的時候，我們想到的可能是 MySQL 數(shù)據(jù)庫中的 user 表。但是，我想告訴你的是 MySQL 數(shù)據(jù)庫中的權(quán)限驗證不僅僅只有一個 user 表這么簡單，我們可以通過下圖深入了解一下 MySQL 數(shù)據(jù)庫的認證體系。

通過上圖，我們可以清晰地看到，MySQL 數(shù)據(jù)的權(quán)限認證過程并不是只有一個 user 表在起作用。

第一層：用戶登錄

在用戶登錄 MySQL 數(shù)據(jù)庫的時候，首先會將用戶輸入的用戶名密碼以及 Host 跟 mysql 數(shù)據(jù)庫中的 user 表中的 Host、User 以及 Password 三個字段相匹配，這一步是判斷用戶是否擁有登錄權(quán)限。

如果匹配不成功，將會報一個 ERROR 1045 (28000): Access denied for user 'xiaoyang'@'localhost' (using password: YES) 錯誤。一旦 MySQL 認為用戶沒有登錄權(quán)限，將會直接拒絕登錄。

[root@dxd ~]# mysql -uxiaoyang -pxiaoyang
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1045 (28000): Access denied for user 'xiaoyang'@'localhost' (using password: YES)

第二層：全局權(quán)限

當用戶通過了第一層用戶登錄驗證之后，將會直接在 user 表中匹配全局權(quán)限，一旦匹配成功之后就會對全局所有的數(shù)據(jù)庫都擁有相應(yīng)的權(quán)限。例如，只給 xiaoyang 這個用戶設(shè)置一個全局可讀權(quán)限，那么 xiaoyang 將擁有全局可讀權(quán)限。

使用 root 用戶創(chuàng)建 xiaoyang 用戶：

創(chuàng)建一個用戶，Create User創(chuàng)建的用戶默認沒有任何權(quán)限

mysql> CREATE USER ‘xiaoyang’@’localhost’ IDENTIFIED BY ‘Xiangyang123!’;
Query OK, 0 rows affected (0.00 sec)

使用 xiaoyang 用戶測試查看權(quán)限：

mysql> select * from test01.city;
ERROR 1142 (42000): SELECT command denied to user ‘xiaoyang’@’localhost’ for table ‘city’

使用 root 用戶給 xiaoyang 用戶授權(quán)：

• 將 xiaoyang 這個用戶設(shè)置一個全局可讀權(quán)限

mysql> update mysql.user set Select_priv=’Y’ where User=’xiaoyang’;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0

• 刷新權(quán)限，使其生效

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

測試 xiaoyang 查看權(quán)限：

• 測試查看權(quán)限

mysql> select * from test01.city;
+——+———————+———+
| id | name | fid |
+——+———————+———+
| 1 | 徐匯區(qū) | 1 |
| 2 | 浦東新區(qū) | 1 |
| 3 | 青浦區(qū) | 1 |
+——+———————+———+
3 rows in set (0.00 sec)

通過上面的例子可以看出，當 xiaoyang 這個用戶擁有一個全局可讀權(quán)限之后，就可以查看所有數(shù)據(jù)庫中的所有數(shù)據(jù)了。設(shè)想一下，如果 xiaoyang 這個用戶沒有全局權(quán)限，怎么辦？

第三層：數(shù)據(jù)庫級別權(quán)限

如果全局權(quán)限驗證失敗，將會進入數(shù)據(jù)庫級權(quán)限驗證，這個層級的權(quán)限是設(shè)置某個用戶針對于某個數(shù)據(jù)庫的權(quán)限。例如：xiaoyang 這個用戶只允許操作 test01 這個數(shù)據(jù)庫，我們來看一下它的實現(xiàn)過程。

將 xiaoyang 用戶設(shè)置 test01 數(shù)據(jù)庫的查詢權(quán)限：

• 添加查看權(quán)限(清空了其他的所有權(quán)限之后添加)

mysql> insert into mysql.db (Host,User,Select_priv,Db) values(‘localhost’, ‘xiaoyang’, ‘Y’, ‘test01’);
Query OK, 1 row affected (0.00 sec)

• 查看添加的權(quán)限

mysql> select Host,User,Select_priv,Db from mysql.db;
+—————-+———————-+——————-+——————————+
| Host | User | Select_priv | Db |
+—————-+———————-+——————-+——————————+
| localhost | xiangyang | Y | test01 |
+—————-+———————-+——————-+——————————+

3 rows in set (0.00 sec)

使用 xiaoyang 用戶查看 test01 數(shù)據(jù)中任意表的數(shù)據(jù)：

• 查看test01數(shù)據(jù)，有權(quán)限

mysql> select * from test01.city;
+——+———————+———+
| id | name | fid |
+——+———————+———+
| 1 | 徐匯區(qū) | 1 |
| 2 | 浦東新區(qū) | 1 |
| 3 | 青浦區(qū) | 1 |
+——+———————+———+
3 rows in set (0.00 sec)

• 查看其他數(shù)據(jù)庫的數(shù)據(jù)，是沒有權(quán)限的

mysql> select * from vue.sp_goods;
ERROR 1142 (42000): SELECT command denied to user ‘xiaoyang’@’localhost’ for table ‘sp_goods’

通過這個案例，我們可以看出數(shù)據(jù)庫級別的權(quán)限只對某一個數(shù)據(jù)庫起作用，而設(shè)置數(shù)據(jù)庫級別的權(quán)限時底層操作的正是 mysql.db 數(shù)據(jù)表，也就是說在 mysql.db 數(shù)據(jù)表中設(shè)置了對應(yīng)的權(quán)限之后，該用戶將對這個數(shù)據(jù)庫中所有的數(shù)據(jù)都擁有該權(quán)限。

在實際應(yīng)用場景中，如果某一個用戶只允許操作某一個數(shù)據(jù)庫，而其他數(shù)據(jù)庫是一個沒有權(quán)限的狀態(tài)，這個時候就需要用到數(shù)據(jù)庫級權(quán)限。

如果要求只允許某個表擁有權(quán)限怎么辦呢？

第四層 ：數(shù)據(jù)表級權(quán)限

數(shù)據(jù)表級權(quán)限是用來定義某個數(shù)據(jù)表的權(quán)限的，具體定義在 mysql.tables_priv 數(shù)據(jù)表中，當數(shù)據(jù)庫級權(quán)限驗證失敗之后就會驗證表級權(quán)限。例如：要求 xiaoyang 這個用戶只允許查看 test01 數(shù)據(jù)庫中的 city 表，這個權(quán)限的實現(xiàn)具體如下。

將 xiaoyang 用戶設(shè)置 test01 數(shù)據(jù)庫中 city 數(shù)據(jù)表的查詢權(quán)限：

• 增加數(shù)據(jù)表級權(quán)限（其他權(quán)限全部清除）

mysql> INSERT INTO mysql.tables_priv (Host, Db, User, Table_name, Table_priv) VALUES (‘localhost’, ‘test01’, ‘xiaoyang’, ‘city’, ‘Select’);
Query OK, 1 row affected (0.00 sec)

使用 xiaoyang 用戶查詢 test01 數(shù)據(jù)庫中 city 數(shù)據(jù)表的數(shù)據(jù)：

• 查看 test01 數(shù)據(jù)庫中 city 數(shù)據(jù)表有權(quán)限

mysql> select * from test01.city;
+——+———————+———+
| id | name | fid |
+——+———————+———+
| 1 | 徐匯區(qū) | 1 |
| 2 | 浦東新區(qū) | 1 |
| 3 | 青浦區(qū) | 1 |
+——+———————+———+
3 rows in set (0.00 sec)

• 查看其他相同數(shù)據(jù)庫中其他表是沒有權(quán)限的

mysql> select * from test01.info;
ERROR 1142 (42000): SELECT command denied to user ‘xiaoyang’@’localhost’ for table ‘info’

通過這個案例，我們可以了解到的是 mysql.tables_priv 數(shù)據(jù)表主要是用來針對某一個數(shù)據(jù)表來設(shè)置權(quán)限的。它比數(shù)據(jù)庫級權(quán)限更加地精細化。

在實際應(yīng)用場景中，如現(xiàn)在有一個訪客瀏覽記錄表，一般要求只允許查看和添加，不允許有其他修改操作；還有例如訂單數(shù)據(jù)表，一般只允許添加、查看和修改，不允許刪除等應(yīng)用場景，數(shù)據(jù)表級權(quán)限有著不可替代的作用。

再試想一下，如果想要 xiaoyang 用戶對訂單數(shù)據(jù)表中的余額字段只能查看，又該如何呢？

第五層：字段級權(quán)限

字段級權(quán)限控制的主要是某一個字段的操作權(quán)限，當數(shù)據(jù)庫需要針對某個具體的字段做權(quán)限控制之時，就需要使用字段級權(quán)限（注意：設(shè)置字段級權(quán)限的數(shù)據(jù)表在 mysql.columns_priv 數(shù)據(jù)表中，但是 mysql.tables_priv 需要首先添加 column_priv 權(quán)限才能生效）。

例如：要求 xiaoyang 這個用戶，只允許查看 sp_order 數(shù)據(jù)表中的 order_price 字段。

設(shè)置 xiaoyang 這個用戶對 sp_order 數(shù)據(jù)表中的 order_price 字段的可讀權(quán)限：

• 在 mysql.tables_priv 數(shù)據(jù)表中添加字段查看權(quán)限

mysql> INSERT INTO mysql.tables_priv (Host, Db, User, Table_name, Column_priv) VALUES (‘localhost’, ‘test01’, ‘xiaoyang’, ‘sp_order’, ‘Select’);
Query OK, 1 row affected (0.00 sec)

• 在 mysql.columns_priv 中添加字段查看權(quán)限

mysql> INSERT INTO mysql.columns_priv (Host, Db, User, Table_name, Column_name, Column_priv) VALUES (‘localhost’, ‘test01’, ‘xiaoyang’, ‘sp_order’, ‘order_price’, ‘Select’);
Query OK, 1 row affected (0.00 sec)

測試該字段的可讀權(quán)限：

• 查看 order_price 字段，可以正常查看

mysql> select order_price from vue.sp_order;
+——————-+
| order_price |
+——————-+
| 222.00 |
+——————-+
27 rows in set (0.00 sec)

• 查看其他字段，沒有權(quán)限

mysql> select id from vue.sp_order;
ERROR 1143 (42000): SELECT command denied to user ‘xiaoyang’@’localhost’ for column ‘id’ in table ‘sp_order’

通過這個案例，可以看出字段級權(quán)限主要是控制某一個字段的權(quán)限。在實際應(yīng)用中，通常用來控制某個字段。例如：控制訂單表中的金額字段無法修改，而其他字段不受影響，這時就可以使用字段級權(quán)限。

第六層：對象級權(quán)限

上文中我們介紹了全局、數(shù)據(jù)庫級、數(shù)據(jù)表級以及字段級的權(quán)限，除此之外，還有一個是用來管理數(shù)據(jù)庫存儲過程和存儲函數(shù)權(quán)限的權(quán)限，及對象級權(quán)限（該權(quán)限相關(guān)的數(shù)據(jù)表是 mysql.procs_priv ）。

舉個例子：超級管理員創(chuàng)建一個名為 select_city 的存儲過程，只給 xiaoyang 這個用戶使用該函數(shù)的權(quán)限，不給修改權(quán)限，具體操作如下。

• root 用戶創(chuàng)建存儲過程：

mysql> delimiter 
mysql> CREATE PROCEDURE select_city(IN city_id INTEGER)
-> BEGIN
-> select * FROM city WHERE id = city_id;
-> END
mysql>CREATEPROCEDUREselect?c??ity(INcity?i??dINTEGER)
−>BEGIN
−>select∗FROMcityWHEREid=city?i??d;
−>END
Query OK, 0 rows affected (0.00 sec)
mysql> delimiter ;

設(shè)置 xiaoyang 用戶的權(quán)限：

mysql> use mysql;

• 增加存儲過程權(quán)限

mysql> INSERT INTO procs_priv (Host, Db, User, Routine_name, Routine_type, Proc_priv) VALUES (‘localhost’, ‘test01’, ‘xiaoyang’, ‘select_city’, ‘PROCEDURE’, ‘Execute’);
Query OK, 1 row affected (0.00 sec)

存儲過程可以正常使用：

• 選擇數(shù)據(jù)庫

mysql> use test01;
Database changed

• 使用存儲過程

mysql> call select_city(1);
+——+—————-+———+
| id | name | fid |
+——+—————-+———+
| 1 | 徐匯區(qū) | 1 |
+——+—————-+———+
1 row in set (0.00 sec)
Query OK, 0 rows affected (0.00 sec)

無法刪除存儲過程：

• 刪除存儲過程顯示無權(quán)限

mysql> DROP PROCEDURE select_city;
ERROR 1370 (42000): alter routine command denied to user ‘xiaoyang’@’localhost’ for routine ‘test01.select_city’

通過上面這個案例可以得出：mysql.procs_priv 數(shù)據(jù)表主要是用來控制存儲過程的權(quán)限的。

在實際應(yīng)用中，我們需要注意存儲過程權(quán)限一旦授予之后，自動會將數(shù)據(jù)庫的查看權(quán)限一并授予，但是會顯示數(shù)據(jù)表為空。

mysql> use test01;
Database changed
mysql> show tables;
Empty set (0.00 sec)

總結(jié)

數(shù)據(jù)的權(quán)限主要分為六個層級：

• 第一層是登錄驗證，驗證失敗則立即退出；
• 第二層的權(quán)限為全局權(quán)限，這個層級的權(quán)限覆蓋整個數(shù)據(jù)庫；
• 第三層的權(quán)限是數(shù)據(jù)庫級別的權(quán)限，這個層級的權(quán)限是針對于某一個數(shù)據(jù)庫的；
• 第四層級是數(shù)據(jù)表級的權(quán)限，這個層級的權(quán)限主要針對于數(shù)據(jù)表；
• 第五層權(quán)限是字段級的權(quán)限，主要針對于某一個字段的，需要注意的是字段級的權(quán)限需要依賴于第四層數(shù)據(jù)表級查看權(quán)限，沒有數(shù)據(jù)表級查看權(quán)限，字段級權(quán)限無法生效；
• 最后一個對象級權(quán)限，這個權(quán)限主要是針對于存儲函數(shù)和存儲過程的，只有擁有該權(quán)限，才能夠操作數(shù)據(jù)庫存儲函數(shù)或存儲過程。

但是，在實際應(yīng)用中，我們不需要將權(quán)限設(shè)置得過于精細化，因為過于精細化容易造成權(quán)限交叉并且設(shè)置權(quán)限的管理也會是相當?shù)穆闊?/p>

以上就是MySQL 中的權(quán)限管理及驗證流程的詳細內(nèi)容，更多關(guān)于MySQL權(quán)限管理驗證的資料請關(guān)注腳本之家其它相關(guān)文章！

最新評論