MySQL Bugs: #20989: View '(null).(null)' references invalid table(s)... on SQL SECURITY INVOKER

Bug #20989	View '(null).(null)' references invalid table(s)... on SQL SECURITY INVOKER
Submitted:	12 Jul 2006 11:13	Modified:	4 Aug 2006 21:35
Reporter:	Beat Vontobel (Silver Quality Contributor)
Status:	Closed
Category:	Server: Privileges	Severity:	S2 (Serious)
Version:	5.0.23,5.1	OS:	Any (any)
Assigned to:	Tatjana A. Nuernberg	Target Version:

Description:
5.0.23 seems to have introduced a new bug on VIEWs (that breaks existing applications at
our site): Certain INSERT/REPLACE queries SELECTing from VIEWs with an SQL SECURITY of
INVOKER now return "ERROR 1356: View '(null).(null)' references invalid table(s) or
column(s) or function(s) or definer/invoker of view lack rights to use them" even if the
user trying to access the VIEW has all rights to SELECT from the VIEW and all underlying
tables.

I'm sorry, I didn't have absolutely no time to create a neat and stripped down test case
this morning - but I just attached the real world scenario, as I thought it could also
make it into 5.0.24 if I'm really quick.

How to repeat:
1. Load the attached setup.sql dumpfile that will create three databases (ms, mn, s) with
a total of 4 TABLES and one VIEW.

2. Create a test user:
GRANT SELECT ON ms.* TO t;
GRANT SELECT ON mn.* TO t;
GRANT SELECT ON s.* TO t;
GRANT INSERT, DELETE ON ms.messwerte_ready;

3. Run the also attached query.sql

Suggested fix:
-

Query that worked in versions < 5.0.23 and fails in 5.0.23 (to be run as user t)

Attachment: query.sql (application/octet-stream, text), 1.75 KiB.

Sorry, there's a typo in the last line of the GRANT statements, it should of course also
read "TO t" in the end.

I was able to reproduce this bug report with 5.0.23.

Steps to reproduce:
1) restore database and tables from dump file

2) create test user
GRANT SELECT ON ms.* TO t;
GRANT SELECT ON mn.* TO t;
GRANT SELECT ON s.* TO t;
GRANT INSERT, DELETE ON ms.messwerte_ready TO t;

3) connect as test user and execute REPLACE query from file.

When I connect as user 't' I can execute SELECT part of REPLACE without any error, I also
can do REPLACE into table ms.messwerte_ready if I specify constant values, for example. I
only get an error if I use REPLACE with SELECT .. FROM view.

The same error with latest 5.0.

oops, need INSERT on STATIONS and COUNTRIES?

Hi Tatjana, thanks for your excellent and quick reaction to bug #21014. What do you
actually mean by your last question for this bug? Something wrong with the test case?

I can reproduce your results. The above note was mostly to myself, but I figured that
since the error message is not as clear as one might wish, I'd show what was the hangup
internally. Whether this is useful information to hotfix until .24 is out of the door
depends 100 % on your use case of course, so maybe it was confusing rather than helpful.
I'll try to make the next addendum "fixed in 5.0.24", then. Have a nice weekend!

Cool, I hope you still have a nice weekend ahead of you as well and won't spend all the
time on fixing bugs. ;-)

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/9328

new and improved, with many funny test cases.
pushed to -relase (5.0.24) and -main (5.0.25)

Noted in 5.0.24 changelog.

REPLACE ... SELECT for a view required the INSERT privilege
for tables other than the table being modified.

Setting bug report back to NDI pending push of fix in to 5.1.

Pushed to 5.1; will be in 5.1.13.

Oops, I apoligize; this will be in 5.1.12.

Noted in 5.1.12 changelog.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/10501

ChangeSet@1.2246, 2006-08-15 21:45:24+04:00, evgen@sunlight.local +9 -0
  Fixed bug#21261: Wrong access rights was required for an insert into a view
  
  SELECT right instead of INSERT right was required for an insert into to a view.
  This wrong behaviour appeared after the fix for bug #20989. Its intention was
  to ask only SELECT right for all tables except the very first for a complex
  INSERT query. But that patch has done it in a wrong way and lead to asking 
  a wrong access right for an insert into a view.
  
  The setup_tables_and_check_access() function now accepts two want_access
  parameters. One will be used for the first table and the second for other
  tables.

Fixed in 5.0.25