Bug #20989 View '(null).(null)' references invalid table(s)... on SQL SECURITY INVOKER
Submitted: 12 Jul 2006 9:13 Modified: 4 Aug 2006 19:35
Reporter: Beat Vontobel (Silver Quality Contributor) (OCA) Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Security: Privileges Severity:S2 (Serious)
Version:5.0.23,5.1 OS:Any (any)
Assigned to: Tatiana Azundris Nuernberg CPU Architecture:Any

[12 Jul 2006 9:13] Beat Vontobel
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:
-
[12 Jul 2006 9:27] Beat Vontobel
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.

[12 Jul 2006 9:31] Beat Vontobel
Sorry, there's a typo in the last line of the GRANT statements, it should of course also read "TO t" in the end.
[12 Jul 2006 10:39] MySQL Verification Team
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.
[12 Jul 2006 13:30] MySQL Verification Team
The same error with latest 5.0.
[14 Jul 2006 9:09] Tatiana Azundris Nuernberg
oops, need INSERT on STATIONS and COUNTRIES?
[14 Jul 2006 9:18] Beat Vontobel
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?
[14 Jul 2006 9:28] Tatiana Azundris Nuernberg
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!
[14 Jul 2006 9:36] Beat Vontobel
Cool, I hope you still have a nice weekend ahead of you as well and won't spend all the time on fixing bugs. ;-)
[19 Jul 2006 9:49] Bugs System
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
[19 Jul 2006 17:57] Tatiana Azundris Nuernberg
new and improved, with many funny test cases.
pushed to -relase (5.0.24) and -main (5.0.25)
[20 Jul 2006 17:26] Paul DuBois
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.
[4 Aug 2006 18:40] Timothy Smith
Pushed to 5.1; will be in 5.1.13.
[4 Aug 2006 19:17] Timothy Smith
Oops, I apoligize; this will be in 5.1.12.
[4 Aug 2006 19:35] Paul DuBois
Noted in 5.1.12 changelog.
[15 Aug 2006 17:46] Bugs System
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.
[29 Aug 2006 13:23] Evgeny Potemkin
Fixed in 5.0.25