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?
