MySQL视图定义者权限和错误1356 [英] MySQL view definer permissions and error 1356

问题描述

我有一个如下所示的MySQL表G.devTest:

I have a MySQL table G.devTest that looks like this:

+----+------+
| id | j    |
+----+------+
|  1 |    5 |
|  2 |    9 |
|  3 |    4 |
|  4 |    7 |
+----+------+

以用户l身份登录，我可以基于该表创建并从简单视图中进行选择，而不会出现问题:

Logged in as my user l, I can create and select from a simple view based on this table without problems:

> create view devTestV as select * from devTest;
Query OK, 0 rows affected (0.02 sec)
> select * from devTestV;
+----+------+
| id | j    |
...

问题

我只想基于devTest使用此视图:

> create view devTestV2 as select a.id, a.j, b.avg from devTest a cross 
join (select avg(j) avg from devTest) b;

首先看起来不错，但是当我尝试从中选择内容时，事情就出现了问题:

That first appears to work fine, but when I try to select from it, things go awry:

> select * from devTestV2;
ERROR 1356 (HY000): View 'G.devTestV2' references invalid table(s) 
or column(s) or function(s) or definer/invoker of view lack rights to use 
them

分析

我们知道devTest存在，列id和j也存在，因此错误的定义者/调用者"部分必须是相关的.我的用户l，视图的定义者(默认情况下使用定义者的权限)必须缺少一些权限.根用户创建相同的视图即可访问这一事实，从而证实了这一结论.

Analysis

We know devTest exists, as do its columns id and j, so the "definer/invoker" part of the error must be the relevant one. My user l, the definer of the view (which by default uses the definer's permissions) must lack some permission. This conclusion is corroborated by the fact that the same view is accessible when created by the root user.

但是，我的用户对G数据库中的所有对象都具有所有权限:

However, my user has all permissions for all objects in the G database:

> show grants;
...
| GRANT ALL PRIVILEGES ON `G`.* TO 'l'@'%'
...

那么，我的用户缺少什么阻止它从devTestV2中进行选择?

What, then, does my user lack that prevents it from selecting from devTestV2?

推荐答案

而不是"create view ..."，请尝试"create SQL SECURITY INVOKER view ..." 在进行此更改之前，我一直遇到相同的问题.

Instead of "create view..." try "create SQL SECURITY INVOKER view..." I was having the same problem until I made this change.

这篇关于MySQL视图定义者权限和错误1356的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！