假設有這麼一個需求,開發人員要求你創建一個賬號test,要求這個賬號有創建表,查詢,更新,刪除表的權限, 如下例子所示

mysql> select version();
+-----------+
| version() |
+-----------+
| 8.4.5     |
+-----------+
1 row in set (0.00 sec)

mysql> create database if not exists kerry   
    -> default character set utf8mb4
    -> default collate utf8mb4_general_ci;
query ok, 1 row affected (0.02 sec)


mysql> create user `test`@`%` identified by  'Test@#$123456';
Query OK, 0 rows affected (0.02 sec)

mysql> 

mysql> grant create, drop ,select,update on kerry.* to test@'%';
Query OK, 0 rows affected (0.00 sec)

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

mysql>

然後我們以test用户登錄數據庫, 此時你執行下面SQL,你會發現,你不光有drop掉表的權限,甚至連數據庫kerry都可以drop掉.如下所示

mysql> select current_user();
+----------------+
| current_user() |
+----------------+
| test@%         |
+----------------+
1 row in set (0.00 sec)

mysql> create table t1(id int, name varchar(12));
Query OK, 0 rows affected (0.02 sec)

mysql> drop table t1;
Query OK, 0 rows affected (0.01 sec)

mysql> drop database kerry;
Query OK, 0 rows affected (0.01 sec)

mysql> 

然後你會發現,test用户不光有drop掉數據庫kerry的權限,而且有創建數據庫kerry的權限(僅僅是創建/刪除kerry這個數據庫.沒有創建/刪除其它數據庫的權限),如下所示:

mysql> select current_user();
+----------------+
| current_user() |
+----------------+
| test@%         |
+----------------+
1 row in set (0.00 sec)

mysql> create database if not exists kerry
    -> default character set utf8mb4
    -> default collate utf8mb4_general_ci;
query ok, 1 row affected (0.01 sec)

mysql> drop database kerry;
query ok, 0 rows affected (0.00 sec)

mysql> create database kkk;
error 1044 (42000): access denied for user 'test'@'%' to database 'kkk' 

mysql> drop database k2;
ERROR 1044 (42000): Access denied for user 'test'@'%' to database 'k2'
mysql>

其實具體原因是你沒有留意MySQL官方文檔關於CREATE/DROP權限的詳細説明,你以為的CREATE權限是創建表的權限,DROP權限是DROP表的權限.其實不然, 如下截圖所示,CREATE權限包含創建數據庫、表或索引的權限,而DROP權限包DROP數據庫、表或視圖的權限. 至於為什麼MySQL沒有細化這些權限.我們也不清楚.但是從上面實驗來看, 我都傾向於這個是一個邏輯上的"Bug".

那麼如果這樣授權,會有什麼問題呢? **如果賬號授予了DROP權限,那麼這就是一個安全權限的"漏洞", SQL注入式攻擊都可以利用這個"漏洞"直接將數據庫給刪除了. **

解決方案

MySQL中沒有單獨的CREATE TABLE, DROP TABLE的權限,我們只能另避蹊徑,先收回賬號test的DROP權限,然後創建一個角色drop_tab,這個角色授予數據庫kerry中具體每一個表的DROP權限,最後授予用户這個角色. 如下所示:

mysql> select current_user();
+----------------+
| current_user() |
+----------------+
| root@localhost |
+----------------+
1 row in set (0.01 sec)

mysql> show grants for test@'%';
+---------------------------------------------------------------+
| Grants for test@%                                             |
+---------------------------------------------------------------+
| GRANT USAGE ON *.* TO `test`@`%`                              |
| GRANT SELECT, UPDATE, CREATE, DROP ON `kerry`.* TO `test`@`%` |
+---------------------------------------------------------------+
2 rows in set (0.00 sec)

mysql> revoke drop on  `kerry`.* from `test`@`%`;
Query OK, 0 rows affected (0.06 sec)

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

mysql> create role drop_tab;
Query OK, 0 rows affected (0.01 sec)
mysql> select 
    ->      table_name
    ->     ,table_type
    ->     ,concat('grant drop on ', table_schema, '.', table_name , ' to test@''%'';') as grant_cmd
    -> from information_schema.tables
    -> where table_schema='kerry';
+------------+------------+-------------------------------------+
| TABLE_NAME | TABLE_TYPE | grant_cmd                           |
+------------+------------+-------------------------------------+
| t1         | BASE TABLE | grant drop on kerry.t1 to test@'%'; |
| t2         | BASE TABLE | grant drop on kerry.t2 to test@'%'; |
+------------+------------+-------------------------------------+
2 rows in set (0.00 sec)

mysql> 

mysql> grant drop on kerry.t1 to test@'%';
Query OK, 0 rows affected (0.01 sec)

mysql> grant drop on kerry.t2 to test@'%';
Query OK, 0 rows affected (0.00 sec)


mysql> grant drop_tab to test@'%';
Query OK, 0 rows affected (0.00 sec)

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

mysql> 

此時,賬號test就沒有刪除數據庫kerry的權限了.

mysql> select current_user();
+----------------+
| current_user() |
+----------------+
| test@%         |
+----------------+
1 row in set (0.00 sec)
mysql> drop table t1;
Query OK, 0 rows affected (0.02 sec)
mysql> drop database kerry;
ERROR 1044 (42000): Access denied for user 'test'@'%' to database 'kerry'
mysql> 

總結

關於數據庫的權限,我們還是要細心與謹慎. 數據庫授權,一般要秉着權限越小越好的原則,避免權限過大帶來不必要的麻煩. 另外,就是MySQL這種權限設計, 其實是設計上的一大缺陷.很容易讓人踩一個大坑.