假设我们需要创建一个名为test的用户，该用户需要拥有在数据库kerry中创建表、查询、更新和删除表的权限。以下是实现步骤：

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
    -> collate utf8mb4_general_ci;
Query OK, 1 row affected (0.02 sec)

mysql> create user 'test_user'@'%' identified by 'TestPass123!';
Query OK, 0 rows affected (0.02 sec)

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

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

当我们使用test_user登录并尝试执行以下SQL语句时，你会发现不仅能够删除表，还能删除整个数据库kerry。

mysql> select current_user();
+-------------------+
| current_user()    |
+-------------------+
| test_user@%       |
+-------------------+
1 row in set (0.00 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)

实际上，授予CREATE和DROP权限不仅限于表操作，还包括数据库级别的操作。这可能会导致安全隐患。例如，通过SQL注入攻击，攻击者可以利用此漏洞删除整个数据库。 为了避免这种情况，我们可以采取以下措施来限制权限：

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

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

mysql> create role drop_table_role;
Query OK, 0 rows affected (0.01 sec)

mysql> select 
    ->      table_name
    ->     ,concat('grant drop on ', table_schema, '.', table_name , ' to ''test_user''@''%'';') as grant_cmd
    -> from information_schema.tables
    -> where table_schema='kerry';
+------------+--------------------------------------+
| TABLE_NAME | grant_cmd                            |
+------------+--------------------------------------+
| t1         | grant drop on kerry.t1 to 'test_user'@'%'; |
| t2         | grant drop on kerry.t2 to 'test_user'@'%'; |
+------------+--------------------------------------+
2 rows in set (0.00 sec)

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

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

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

现在，test_user只能删除特定的表，而不能删除整个数据库kerry。