Bug #20482 failure on Create join view with sources views/tables in different schemas
Submitted: 15 Jun 2006 14:44 Modified: 6 Jul 2006 22:38
Reporter: Matthias Leich Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Views Severity:S1 (Critical)
Version: OS:
Assigned to: Georgi Kodinov CPU Architecture:Any

[15 Jun 2006 14:44] Matthias Leich 
Description:
Some new changes within the privilege system around views
break existing and often applied successful tests 
(funcs_1   Testcase 3.3.1.31 - 3.3.1.37 New Implementation).
Therefore I set the priority to P1.

Example:
CREATE DATABASE test1;
CREATE DATABASE test2;
USE test;
CREATE TABLE test1.t0 (f1 VARCHAR(20));
CREATE TABLE test2.t1 (f1 VARCHAR(20));
CREATE VIEW  test2.t3 AS SELECT * FROM test1.t0;
CREATE OR REPLACE VIEW test.v1 AS
SELECT ta.f1 AS col1,
tb.f1 AS col2
FROM test2.t3 ta, test2.t1 tb;
ERROR 42000: create view command denied to user 'root'@'localhost' for column 'col2' in table 'v1'

My environment:
   - PC(Pentium M, x86-32Bit) with Linux(SuSE 9.3)
   - MySQL compiled (compile-pentium-debug-max) from source
        Version 5.0 ChangeSet@1.2177, 2006-06-14

How to repeat:
Please use my attached files
  cp m1.test t
  cp m1.result r
  ./mysql-test-run m1

m1.result contains the results expected in the testsuite funcs_1
[15 Jun 2006 14:45] Matthias Leich 
test script

Attachment: m1.test (application/test, text), 3.24 KiB.
[15 Jun 2006 14:46] Matthias Leich 
expected (correct) results

Attachment: m1.result (application/octet-stream, text), 6.27 KiB.
[16 Jun 2006 15:20] 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/7768
[21 Jun 2006 9:08] 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/7998
[6 Jul 2006 15:34] Evgeny Potemkin 
The function check_one_table_access() called to check access to tables in 
SELECT/INSERT/UPDATE was doing additional checks/modifications that don't hold
in the context of setup_tables_and_check_access().
That's why the check_one_table() was split into two : the functionality needed by
setup_tables_and_check_access() into check_single_table_access() and the rest of 
the functionality stays in check_one_table_access() that is made to call the new
check_single_table_access() function.

Fixed in 5.0.24, 5.1.12
[6 Jul 2006 22:38] Paul DuBois 
Noted in 5.0.24, 5.1.12 changelogs.

Creation of a view as a join of views or tables could fail if the views or tables are in different databases.
[13 Jul 2006 3:36] Paul DuBois 
5.0.x fix went to 5.0.25 instead.