MySQL 中的許可權管理及驗證流程

2022-09-14 22:12:41

引言

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

這樣的賬號就涉及到了資料庫的許可權問題，下面我們一起來討論一下 MySQL 中的許可權管理。

許可權的驗證流程

通常，我們提及資料庫中的許可權的時候，我們想到的可能是 MySQL 資料庫中的 user 表。但是，我想告訴你的是 MySQL 資料庫中的許可權驗證不僅僅只有一個 user 表這麼簡單，我們可以通過下圖深入瞭解一下 MySQL 資料庫的認證體系。

通過上圖，我們可以清晰地看到，MySQL 資料的許可權認證過程並不是只有一個 user 表在起作用。

第一層：使用者登入

在使用者登入 MySQL 資料庫的時候，首先會將使用者輸入的使用者名稱密碼以及 Host 跟 mysql 資料庫中的 user 表中的 Host、User 以及 Password 三個欄位相匹配，這一步是判斷使用者是否擁有登入許可權。

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

[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)

第二層：全域性許可權

當用戶通過了第一層使用者登入驗證之後，將會直接在 user 表中匹配全域性許可權，一旦匹配成功之後就會對全域性所有的資料庫都擁有相應的許可權。例如，只給 xiaoyang 這個使用者設定一個全域性可讀許可權，那麼 xiaoyang 將擁有全域性可讀許可權。

使用 root 使用者建立 xiaoyang 使用者：

建立一個使用者，Create User建立的使用者預設沒有任何許可權

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

使用 xiaoyang 使用者測試檢視許可權：

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

使用 root 使用者給 xiaoyang 使用者授權：

將 xiaoyang 這個使用者設定一個全域性可讀許可權

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

重新整理許可權，使其生效

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

測試 xiaoyang 檢視許可權：

測試檢視許可權

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

通過上面的例子可以看出，當 xiaoyang 這個使用者擁有一個全域性可讀許可權之後，就可以檢視所有資料庫中的所有資料了。設想一下，如果 xiaoyang 這個使用者沒有全域性許可權，怎麼辦？

第三層：資料庫級別許可權

如果全域性許可權驗證失敗，將會進入資料庫級許可權驗證，這個層級的許可權是設定某個使用者針對於某個資料庫的許可權。例如：xiaoyang 這個使用者只允許操作 test01 這個資料庫，我們來看一下它的實現過程。

將 xiaoyang 使用者設定 test01 資料庫的查詢許可權：

新增檢視許可權(清空了其他的所有許可權之後新增)

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

檢視新增的許可權

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 資料中任意表的資料：

檢視test01資料，有許可權

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

檢視其他資料庫的資料，是沒有許可權的

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

通過這個案例，我們可以看出資料庫級別的許可權只對某一個資料庫起作用，而設定資料庫級別的許可權時底層操作的正是 mysql.db 資料表，也就是說在 mysql.db 資料表中設定了對應的許可權之後，該使用者將對這個資料庫中所有的資料都擁有該許可權。

在實際應用場景中，如果某一個使用者只允許操作某一個資料庫，而其他資料庫是一個沒有許可權的狀態，這個時候就需要用到資料庫級許可權。

如果要求只允許某個表擁有許可權怎麼辦呢？

第四層：資料表級許可權

資料表級許可權是用來定義某個資料表的許可權的，具體定義在 mysql.tables_priv 資料表中，當資料庫級許可權驗證失敗之後就會驗證表級許可權。例如：要求 xiaoyang 這個使用者只允許檢視 test01 資料庫中的 city 表，這個許可權的實現具體如下。

將 xiaoyang 使用者設定 test01 資料庫中 city 資料表的查詢許可權：

增加資料表級許可權（其他許可權全部清除）

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 資料庫中 city 資料表的資料：

檢視 test01 資料庫中 city 資料表有許可權

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

檢視其他相同資料庫中其他表是沒有許可權的

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

通過這個案例，我們可以瞭解到的是 mysql.tables_priv 資料表主要是用來針對某一個資料表來設定許可權的。它比資料庫級許可權更加地精細化。

在實際應用場景中，如現在有一個訪客瀏覽記錄表，一般要求只允許檢視和新增，不允許有其他修改操作；還有例如訂單資料表，一般只允許新增、檢視和修改，不允許刪除等應用場景，資料表級許可權有著不可替代的作用。

再試想一下，如果想要 xiaoyang 使用者對訂單資料表中的餘額欄位只能檢視，又該如何呢？

第五層：欄位級許可權

欄位級許可權控制的主要是某一個欄位的操作許可權，當資料庫需要針對某個具體的欄位做許可權控制之時，就需要使用欄位級許可權（注意：設定欄位級許可權的資料表在 mysql.columns_priv 資料表中，但是 mysql.tables_priv 需要首先新增 column_priv 許可權才能生效）。

例如：要求 xiaoyang 這個使用者，只允許檢視 sp_order 資料表中的 order_price 欄位。

設定 xiaoyang 這個使用者對 sp_order 資料表中的 order_price 欄位的可讀許可權：

在 mysql.tables_priv 資料表中新增欄位檢視許可權

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 中新增欄位檢視許可權

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)

測試該欄位的可讀許可權：

檢視 order_price 欄位，可以正常檢視

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

檢視其他欄位，沒有許可權

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

通過這個案例，可以看出欄位級許可權主要是控制某一個欄位的許可權。在實際應用中，通常用來控制某個欄位。例如：控制訂單表中的金額欄位無法修改，而其他欄位不受影響，這時就可以使用欄位級許可權。

第六層：物件級許可權

上文中我們介紹了全域性、資料庫級、資料表級以及欄位級的許可權，除此之外，還有一個是用來管理資料庫儲存過程和儲存函數許可權的許可權，及物件級許可權（該許可權相關的資料表是 mysql.procs_priv ）。

舉個例子：超級管理員建立一個名為 select_city 的儲存過程，只給 xiaoyang 這個使用者使用該函數的許可權，不給修改許可權，具體操作如下。

root 使用者建立儲存過程：

mysql> delimiter
mysql> CREATE PROCEDURE select_city(IN city_id INTEGER)
-> BEGIN
-> select * FROM city WHERE id = city_id;
-> END
mysql>CREATEPROCEDUREselectcity(INcityidINTEGER)
−>BEGIN
−>select∗FROMcityWHEREid=cityid;
−>END
Query OK, 0 rows affected (0.00 sec)
mysql> delimiter ;

設定 xiaoyang 使用者的許可權：

mysql> use mysql;

增加儲存過程許可權

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)

儲存過程可以正常使用：

選擇資料庫

mysql> use test01;
Database changed

使用儲存過程

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

無法刪除儲存過程：

刪除儲存過程顯示無許可權

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

通過上面這個案例可以得出：mysql.procs_priv 資料表主要是用來控制儲存過程的許可權的。

在實際應用中，我們需要注意儲存過程許可權一旦授予之後，自動會將資料庫的檢視許可權一併授予，但是會顯示資料表為空。

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

總結

資料的許可權主要分為六個層級：

第一層是登入驗證，驗證失敗則立即退出；
第二層的許可權為全域性許可權，這個層級的許可權覆蓋整個資料庫；
第三層的許可權是資料庫級別的許可權，這個層級的許可權是針對於某一個資料庫的；
第四層級是資料表級的許可權，這個層級的許可權主要針對於資料表；
第五層許可權是欄位級的許可權，主要針對於某一個欄位的，需要注意的是欄位級的許可權需要依賴於第四層資料表級檢視許可權，沒有資料表級檢視許可權，欄位級許可權無法生效；
最後一個物件級許可權，這個許可權主要是針對於儲存函數和儲存過程的，只有擁有該許可權，才能夠運算元據庫儲存函數或儲存過程。

但是，在實際應用中，我們不需要將許可權設定得過於精細化，因為過於精細化容易造成許可權交叉並且設定許可權的管理也會是相當的麻煩。

以上就是MySQL 中的許可權管理及驗證流程的詳細內容，更多關於MySQL許可權管理驗證的資料請關注it145.com其它相關文章！