mysql 视图定义者_MySQL视图定义者权限和错误1356

Background

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

+----+------+

| id | j |

+----+------+

| 1 | 5 |

| 2 | 9 |

| 3 | 4 |

| 4 | 7 |

+----+------+

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 |

...

The Problem

I want to use this view based only on 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

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.

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

> show grants;

...

| GRANT ALL PRIVILEGES ON `G`.* TO 'l'@'%'

...

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

解决方案

Instead of "create view..." try "create SQL SECURITY INVOKER view..."

I was having the same problem until I made this change.