MySQL中是否有一种有效的方法可以在TRUNCATE或DROP TABLE时进行GRANT？

Question

我最近在MySQL 5.5.x中尝试了这个：

GRANT
    SELECT, INSERT, UPDATE, DELETE, TRUNCATE ON crawler.*
    TO 'my_user'@'localhost' WITH GRANT OPTION;

这会导致一个错误:

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'TRUNCATE ON crawler.* TO 'my_user'@'localhost' WITH GRANT OPTION' at line 2

在我添加TRUNCATE之前，这个命令可以正常工作，经过一番研究，我发现MySQL不支持TRUNCATE。

原因是TRUNCATE被归类为DDL操作，因此它不使用DELETE内部，而是使用DROP。好的，所以我想限制这个用户删除表（在安全漏洞的情况下，至少恶意用户需要确定表的名称并单独删除它们）。

然而，事实证明我需要授予该用户 DROP 权限，这将使用户也能够删除整个数据库。鉴于没有 单独表的授权，还有其他方法可以做到这一点吗？我想我可以将其移交给具有不同用户的另一个进程，但对于这样一个小问题来说，感觉有点繁琐。

目前，我将坚持使用 DELETE，即使它非常缓慢！（在我的笔记本电脑上，删除 160 万行小数据需要约 55 秒，而截断相同数据只需几分之一秒）。但是，如果有更快速和安全的替代方案，我会听取建议。

- halfer

Answer 1

为了在MySQL中授予特定用户在特定数据库中特定表的DROP权限，您可以使用如下GRANT语句。（假设表fi存在于数据库fee中，这是您想要允许用户'fo'@'%'能够TRUNCATE的表）：

  GRANT DROP ON TABLE fee.fi TO 'fo'@'%'

为了查看用户是否有权限截断特定的表：

  SHOW GRANTS FOR 'fo'@'%' ;

并使用用户'fo'@'%'进行连接以进行测试：

  TRUNCATE TABLE fee.fi ;

显然，用户也有权限删除同一张表。但这就是MySQL的方式。

---

作为替代方案，允许用户仅对该特定表执行TRUNCATE操作，而无需授予用户表的DROP权限...

创建一个保存过程，执行TRUNCATE fee.fi;（它可能需要动态执行，因为它是DDL）。要创建该过程，需要使用DEFINER权限，并由具有所需权限的用户创建。

然后您可以将过程的执行权限授予用户：

  GRANT EXECUTE ON fee.truncate_table_fee_fi TO 'fo'@'%';

那么用户 'fo'@'%' 可以

  CALL fee.truncate_table_fee_fi 

- spencer7593

---

太好了，我没想到可以使用 GRANT DROP 影响单个表 - 我会试一下的！谢谢你。 （我想我已经习惯了 ON fee.*，以至于我忘记了 * 可以与单个表名交换）。 - halfer

有趣的是：为了能够执行“DROP DATABASE”，用户是否只需要拥有删除所有表格的权限，即在“GRANT”中使用星号授予“DROP”权限？ - halfer

@halfer：我只会以特权用户（例如root）的身份删除数据库，该用户在mysql.user表中具有Drop_priv=Y。我认为这相当于GRANT/REVOKE语句中的**on *.*。我不认为grant drop on fee.***会赋予用户删除数据库fee的权限，但我从未尝试/测试过。（显然，它将使用户有权清空数据库，删除数据库中的所有对象。） - spencer7593

好的，谢谢。我尝试在用户只有删除单个表的权限时删除数据库，正如预期的那样，这被拒绝了 - 这正是我想要的。 - halfer