Bug #20363 Create view on just created view is now denied
Submitted: 9 Jun 2006 16:03 Modified: 15 Jun 2006 3:58
Reporter: Matthias Leich Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Views Severity:S1 (Critical)
Version:5.0 OS:
Assigned to: Georgi Kodinov

[9 Jun 2006 16:03] Matthias Leich
Description:
Some new changes within the privilege system around views
break existing and often applied successful tests 
(NIST - create view V_WORKS3, funcs_1).
Therefore I set the priority to P1.

snip of my protocol:
CREATE USER mysqltest_db1@localhost identified by 'PWD';
GRANT ALL ON mysqltest_db1.* TO mysqltest_db1@localhost 
WITH GRANT OPTION;
connect(localhost,mysqltest_db1,
               PWD,test,<MYSQL_PORT>,<MYSQL_SOCK>);
DROP SCHEMA IF EXISTS mysqltest_db1;
CREATE SCHEMA mysqltest_db1 ;
USE mysqltest_db1 ;
CREATE TABLE t1 (f1 INTEGER);
CREATE VIEW view1 AS
SELECT * FROM t1;
SHOW CREATE VIEW view1;
View	Create View
view1	CREATE ALGORITHM=UNDEFINED DEFINER=`mysqltest_db1`@`localhost` SQL SECURITY DEFINER VIEW `view1` AS select `t1`.`f1` AS `f1` from `t1`
CREATE VIEW view2 AS
SELECT * FROM view1;
############## Here comes a suspicious warning
SHOW CREATE VIEW view2;
View	Create View
view2	CREATE ALGORITHM=UNDEFINED DEFINER=`mysqltest_db1`@`localhost` SQL SECURITY DEFINER VIEW `view2` AS select `view1`.`f1` AS `f1` from `view1`
Warnings:
Warning	1356	View 'mysqltest_db1.view2' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them
# But the view view2 is usable
SELECT * FROM view2;
f1
###### In history this view was successful created
CREATE VIEW view3 AS
SELECT * FROM view2;
ERROR HY000: View 'mysqltest_db1.view2' references invalid table(s) or column(s) or 
function(s) or definer/invoker of view lack rights to use them

The user mysqltest_db1 has all rights (including GRANT) on the
SCHEMA mysqltest_db1. That means he must be able to create
tables and views there.

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.2168, 2006-06-09

How to repeat:
Please use my attached testscript n1.test
  copy it to mysql-test/t
  echo "Dummy" > r/n1.result   # Produce a dummy file with 
                                                  # expected results
  ./mysql-test-run n1
[9 Jun 2006 16:04] Matthias Leich
testcase

Attachment: n1.test (application/test, text), 954 bytes.

[12 Jun 2006 13:37] 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/7540
[12 Jun 2006 15:11] 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/7545
[12 Jun 2006 16:25] Georgi Kodinov
"new changes" is the fix for bug #18681.
pushed in 5.0.23
[15 Jun 2006 3:58] Paul Dubois
Noted in 5.0.23 changelog.