有没有更好的方法为MySQL中的临时表分配权限？
我们的用户以相当低级别的用户身份登录生产数据库，并在数据库级别授予 SELECT 权限，并在他们需要访问的特定表上授予 INSERT/UPDATE/DELETE 权限。
他们还有权创建临时表（我们需要它们来执行一些更复杂的查询）。问题是，虽然他们可以创建临时表，但他们无权插入临时表！
我们发现的一种解决方法是创建一个同名（但只有一个字段）的“真实”（持久？）表，并授予它们插入该表的访问权限。然后，当创建同名临时表时，系统将使用该临时表，而不是持久表。
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 身份），则不能：
mysql> GRANT INSERT ON testdb.testing TO 'stduser'@'%';
ERROR 1146 (42S02): Table 'testdb.testing' doesn't exist
所以我的问题基本上是，我们可以在没有临时表的“持久”表的情况下授予对临时表的 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.
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'
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
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?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
根据 MySQL 参考：
因此，尝试在上面的授予语句中包含 CREATE 权限。
提示：如果您在测试新权限时遇到“访问被拒绝”或“创建权限被拒绝”错误，请断开连接，然后再次使用目标用户连接到 MySQL 数据库。看起来有些权限只有在执行此操作时才会“更新”，创建临时表就是其中之一。
According to the MySQL reference :
So, try including CREATE permission on the grant statement above.
Hint: If you get 'Access Denied' or 'CREATE Permission Denied' errors while testing the new privilege, disconnect and connect again to your MySQL database with the intended user. It looks like some privileges only get 'updated' when you do this, the CREATE TEMPORARY TABLES is one of them.
解决此问题的一种方法是创建一个特殊数据库并授予用户对其的写访问权限，然后让它在该特殊数据库中创建这些临时表。
One way around this will be to create a special database and grant the user write access to it, and then have it create those temporary tables in that special database.
MySQL 授予独立于实际现有的对象 - 您可以对尚不存在的表授予权限，并且如果要创建表，这些权限将分配给该表。这意味着您可以授予用户创建特定表的权限。
我很想为临时表（称为 temp）创建一个数据库，并授予用户访问该数据库的权限。基于数据库的权限比每个对象的权限更容易管理。
MySQL grants are independent of the object actually existing - you can grant on tables which don't (yet) exist and those permissions would be assigned to a table if it were to be created. This means that you can grant a user permission to create a specific table.
I'd be tempted to create a database just for temp tables, (called, say, temp) and grant the users access to that database. Database-based permissions are much easier to manage than per-object ones.