有没有更好的方法来为 MySQL 中的临时表分配权限? [英] Is there a better way to assign permissions to temporary tables in MySQL?

问题描述

我们的用户以相当低级别的用户身份登录生产数据库，在数据库级别授予 SELECT 权限，并在他们需要访问的特定表上授予 INSERT/UPDATE/DELETE 权限.

Our users log into the production database as a fairly low-level user, with SELECT granted at the database level, and INSERT/UPDATE/DELETE granted on the specific tables they need access to.

他们也有创建临时表的权限(我们需要它们来处理一些更复杂的查询).问题是，虽然他们可以创建临时表，但他们无权将 INSERT 插入其中！

They also have permissions to create temporary tables (we need them for some of the more complicated queries). The problem is that whilst they can create the temporary tables, they don't have access to INSERT into them!

我们发现的一种解决方法是创建一个同名(但只有一个字段)的真实"(持久性?)表，并授予他们插入其中的访问权限.然后，当创建同名的临时表时，系统将使用它，而不是持久表.

A workaround we have found is to create a "real" (persistent?) table of the same name (but with only one field) and grant access for them to insert into that. Then, when the temporary table is created with the same name, the system will use that, not the persistent table.

mysql> CREATE TEMPORARY TABLE `testing` (`id` INTEGER AUTO_INCREMENT PRIMARY KEY, `name` VARCHAR(30));
Query OK, 0 rows affected (0.04 sec)
mysql> INSERT INTO `testing` (`name`) VALUES ('testing');
ERROR 1142 (42000): INSERT command denied to user 'stduser'@'office.companyname.co.uk' for table 'testing'

如果您尝试授予对该表的访问权限(在另一个会话中，以 root 身份)，则不能:

If you try to grant access to the table (in another session, as root), you can't:

mysql> GRANT INSERT ON testdb.testing TO 'stduser'@'%';
ERROR 1146 (42S02): Table 'testdb.testing' doesn't exist

所以我的问题是，基本上，我们可以在临时表上授予 INSERT/UPDATE/DELETE 权限，而不会挂有同名的持久"表吗?

So my question is, basically, can we grant INSERT/UPDATE/DELETE on temporary tables without having a "persistent" table of the same name hanging around?

推荐答案

根据 MySQL 参考 :

MySQL 允许您授予权限在没有的数据库或表上存在.对于表，特权被授予必须包括 CREATE特权.

MySQL enables you to grant privileges on databases or tables that do not exist. For tables, the privileges to be granted must include the CREATE privilege.

因此，尝试在上面的授权语句中包含 CREATE 权限.

So, try including CREATE permission on the grant statement above.

这篇关于有没有更好的方法来为 MySQL 中的临时表分配权限?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！