manual merge to reconcile with MySQL ticket 27145

1 files changed, 362 insertions, 4 deletions

diff --git a/mysql-test/t/view_grant.test b/mysql-test/t/view_grant.test
index 5896e25feda..663501a0a88 100644
--- a/mysql-test/t/view_grant.test
+++ b/mysql-test/t/view_grant.test
@@ -127,21 +127,26 @@ create view mysqltest.v1 (c,d) as select a+1,b+1 from mysqltest.t1;
 create algorithm=temptable view mysqltest.v2 (c,d) as select a+1,b+1 from mysqltest.t1;
 create view mysqltest.v3 (c,d) as select a+1,b+1 from mysqltest.t2;
 create algorithm=temptable view mysqltest.v4 (c,d) as select a+1,b+1 from mysqltest.t2;
+# v5: SHOW VIEW, but no SELECT
+create view mysqltest.v5 (c,d) as select a+1,b+1 from mysqltest.t1;
 grant select on mysqltest.v1 to mysqltest_1@localhost;
 grant select on mysqltest.v2 to mysqltest_1@localhost;
 grant select on mysqltest.v3 to mysqltest_1@localhost;
 grant select on mysqltest.v4 to mysqltest_1@localhost;
+grant show view on mysqltest.v5 to mysqltest_1@localhost;
 
 connection user1;
-# all selects works
+# all SELECTs works, except v5 which lacks SELECT privs
 select c from mysqltest.v1;
 select c from mysqltest.v2;
 select c from mysqltest.v3;
 select c from mysqltest.v4;
+--error ER_TABLEACCESS_DENIED_ERROR
+select c from mysqltest.v5;
 # test of show coluns
 show columns from mysqltest.v1;
 show columns from mysqltest.v2;
-# but explain/show do not
+# explain/show fail
 --error ER_VIEW_NO_EXPLAIN
 explain select c from mysqltest.v1;
 --error ER_TABLEACCESS_DENIED_ERROR
@@ -158,15 +163,33 @@ show create view mysqltest.v3;
 explain select c from mysqltest.v4;
 --error ER_TABLEACCESS_DENIED_ERROR
 show create view mysqltest.v4;
+--error ER_TABLEACCESS_DENIED_ERROR
+explain select c from mysqltest.v5;
+# new in 5.5: SHOW CREATE VIEW needs SELECT now (MySQL Bug#27145)
+--error ER_TABLEACCESS_DENIED_ERROR
+show create view mysqltest.v5;
+connection root;
+grant select on mysqltest.v5 to mysqltest_1@localhost;
+connection user1;
+show create view mysqltest.v5;
 
+# missing SELECT on underlying t1, no SHOW VIEW on v1 either.
+--error ER_VIEW_NO_EXPLAIN
+explain select c from mysqltest.v1;
+# missing SHOW VIEW
+--error ER_TABLEACCESS_DENIED_ERROR
+show create view mysqltest.v1;
 # allow to see one of underlying table
 connection root;
+grant show view on mysqltest.v1 to mysqltest_1@localhost;
 grant select on mysqltest.t1 to mysqltest_1@localhost;
+revoke select on mysqltest.v5 from mysqltest_1@localhost;
 connection user1;
-# EXPLAIN of view on above table works
+# EXPLAIN works
 explain select c from mysqltest.v1;
---error ER_TABLEACCESS_DENIED_ERROR
 show create view mysqltest.v1;
+# missing SHOW VIEW
+--error ER_VIEW_NO_EXPLAIN
 explain select c from mysqltest.v2;
 --error ER_TABLEACCESS_DENIED_ERROR
 show create view mysqltest.v2;
@@ -179,6 +202,9 @@ show create view mysqltest.v3;
 explain select c from mysqltest.v4;
 --error ER_TABLEACCESS_DENIED_ERROR
 show create view mysqltest.v4;
+# we have SHOW VIEW on v5, and  SELECT on t1 -- not enough
+--error ER_TABLEACCESS_DENIED_ERROR
+explain select c from mysqltest.v5;
 
 # allow to see any view in mysqltest database
 connection root;
@@ -188,8 +214,12 @@ explain select c from mysqltest.v1;
 show create view mysqltest.v1;
 explain select c from mysqltest.v2;
 show create view mysqltest.v2;
+# have SHOW VIEW | SELECT on v3, but no SELECT on t2
+--error ER_VIEW_NO_EXPLAIN
 explain select c from mysqltest.v3;
 show create view mysqltest.v3;
+# have SHOW VIEW | SELECT on v4, but no SELECT on t2
+--error ER_VIEW_NO_EXPLAIN
 explain select c from mysqltest.v4;
 show create view mysqltest.v4;
 
@@ -1237,6 +1267,334 @@ DROP VIEW db1.v1;
 DROP TABLE db1.t1;
 DROP DATABASE db1;
 
+connection default;
+
+
+--echo Bug #11765687/#58677: 
+--echo No privilege on table/view, but can know #rows / underlying table's name
+
+# As a root-like user
+connect (root,localhost,root,,test);
+connection root;
+
+create database mysqltest1;
+create table mysqltest1.t1 (i int);
+create table mysqltest1.t2 (j int);
+create table mysqltest1.t3 (k int, secret int);
+
+create user alice@localhost;
+create user bob@localhost;
+create user cecil@localhost;
+create user dan@localhost;
+create user eugene@localhost;
+create user fiona@localhost;
+create user greg@localhost;
+create user han@localhost;
+create user inga@localhost;
+create user jamie@localhost;
+create user karl@localhost;
+create user lena@localhost;
+create user mhairi@localhost;
+create user noam@localhost;
+create user olga@localhost;
+create user pjotr@localhost;
+create user quintessa@localhost;
+
+grant all privileges on mysqltest1.* to alice@localhost with grant option;
+
+#
+--echo ... as alice
+connect (test11765687,localhost,alice,,mysqltest1);
+connection test11765687;
+
+create view v1 as select * from t1;
+create view v2 as select * from v1, t2;
+create view v3 as select k from t3;
+
+grant select            on mysqltest1.v1 to bob@localhost;
+
+grant show view         on mysqltest1.v1 to cecil@localhost;
+
+grant select, show view on mysqltest1.v1 to dan@localhost;
+grant select            on mysqltest1.t1 to dan@localhost;
+
+grant select            on mysqltest1.*  to eugene@localhost;
+
+grant select, show view on mysqltest1.v2 to fiona@localhost;
+
+grant select, show view on mysqltest1.v2 to greg@localhost;
+grant         show view on mysqltest1.v1 to greg@localhost;
+
+grant select(k)         on mysqltest1.t3 to han@localhost;
+grant select, show view on mysqltest1.v3 to han@localhost;
+
+grant select            on mysqltest1.t1 to inga@localhost;
+grant select            on mysqltest1.t2 to inga@localhost;
+grant select            on mysqltest1.v1 to inga@localhost;
+grant select, show view on mysqltest1.v2 to inga@localhost;
+
+grant select            on mysqltest1.t1 to jamie@localhost;
+grant select            on mysqltest1.t2 to jamie@localhost;
+grant         show view on mysqltest1.v1 to jamie@localhost;
+grant select, show view on mysqltest1.v2 to jamie@localhost;
+
+grant select            on mysqltest1.t1 to karl@localhost;
+grant select            on mysqltest1.t2 to karl@localhost;
+grant select, show view on mysqltest1.v1 to karl@localhost;
+grant select            on mysqltest1.v2 to karl@localhost;
+
+grant select            on mysqltest1.t1 to lena@localhost;
+grant select            on mysqltest1.t2 to lena@localhost;
+grant select, show view on mysqltest1.v1 to lena@localhost;
+grant         show view on mysqltest1.v2 to lena@localhost;
+
+grant select            on mysqltest1.t1 to mhairi@localhost;
+grant select            on mysqltest1.t2 to mhairi@localhost;
+grant select, show view on mysqltest1.v1 to mhairi@localhost;
+grant select, show view on mysqltest1.v2 to mhairi@localhost;
+
+grant select            on mysqltest1.t1 to noam@localhost;
+grant select, show view on mysqltest1.v1 to noam@localhost;
+grant select, show view on mysqltest1.v2 to noam@localhost;
+
+grant select            on mysqltest1.t2 to olga@localhost;
+grant select, show view on mysqltest1.v1 to olga@localhost;
+grant select, show view on mysqltest1.v2 to olga@localhost;
+
+grant select            on mysqltest1.t1 to pjotr@localhost;
+grant select            on mysqltest1.t2 to pjotr@localhost;
+grant select, show view on mysqltest1.v2 to pjotr@localhost;
+
+grant select, show view on mysqltest1.v1 to quintessa@localhost;
+
+disconnect test11765687;
+
+#
+--echo ... as bob
+connect (test11765687,localhost,bob,,mysqltest1);
+connection test11765687;
+
+select * from v1; # Should succeed.
+--error ER_VIEW_NO_EXPLAIN
+explain select * from v1; # fail, no SHOW_VIEW
+
+disconnect test11765687;
+
+#
+--echo ... as cecil
+connect (test11765687,localhost,cecil,,mysqltest1);
+connection test11765687;
+
+--error ER_TABLEACCESS_DENIED_ERROR
+select * from v1; # fail, no SELECT
+--error ER_TABLEACCESS_DENIED_ERROR
+explain select * from v1; # fail, no SELECT
+
+disconnect test11765687;
+
+#
+--echo ... as dan
+connect (test11765687,localhost,dan,,mysqltest1);
+connection test11765687;
+
+select * from v1; # Should succeed.
+explain select * from v1; # Should succeed.
+
+disconnect test11765687;
+
+#
+--echo ... as eugene
+connect (test11765687,localhost,eugene,,mysqltest1);
+connection test11765687;
+
+select * from v1; # Should succeed.
+--error ER_VIEW_NO_EXPLAIN
+explain select * from v1; # fail, no SHOW_VIEW
+
+disconnect test11765687;
+
+#
+--echo ... as fiona
+connect (test11765687,localhost,fiona,,mysqltest1);
+connection test11765687;
+
+select * from v2; # Should succeed.
+show create view v2; # Should succeed, but...
+--error ER_TABLEACCESS_DENIED_ERROR
+explain select * from t1; # fail, shouldn't see t1!
+--error ER_TABLEACCESS_DENIED_ERROR
+# err msg must give view name, no table names!!
+explain select * from v1; # fail, have no privs on v1!
+--error ER_TABLEACCESS_DENIED_ERROR
+explain select * from t2; # fail, have no privs on t2!
+--error ER_VIEW_NO_EXPLAIN
+explain select * from v2; # fail, shouldn't see t2!
+
+disconnect test11765687;
+
+#
+--echo ... as greg
+connect (test11765687,localhost,greg,,mysqltest1);
+connection test11765687;
+
+select * from v2; # Should succeed.
+--error ER_TABLEACCESS_DENIED_ERROR
+explain select * from v1; # fail; no SELECT on v1!
+--error ER_VIEW_NO_EXPLAIN
+explain select * from v2; # fail; no SELECT on v1!
+
+disconnect test11765687;
+
+#
+--echo ... as han
+connect (test11765687,localhost,han,,mysqltest1);
+connection test11765687;
+
+--error ER_TABLEACCESS_DENIED_ERROR
+select * from t3; # don't have privs on all columns,
+--error ER_TABLEACCESS_DENIED_ERROR
+explain select * from t3; # so EXPLAIN on "forbidden" columns should fail.
+select k from t3; # but we do have SELECT on column k though,
+explain select k from t3; # so EXPLAIN just on k should work,
+select * from v3; # and so should SELECT on view only using allowed columns
+explain select * from v3; # as should the associated EXPLAIN
+
+disconnect test11765687;
+
+#
+--echo ... as inga
+connect (test11765687,localhost,inga,,mysqltest1);
+connection test11765687;
+
+select * from v2;
+# has sel/show on v2, sel on t1/t2, only sel v1
+# fail: lacks show on v1
+--error ER_VIEW_NO_EXPLAIN
+explain select * from v2;
+disconnect test11765687;
+
+#
+--echo ... as jamie
+connect (test11765687,localhost,jamie,,mysqltest1);
+connection test11765687;
+
+select * from v2;
+# has sel/show on v2, sel on t1/t2, only show v1
+# fail: lacks sel on v1
+--error ER_VIEW_NO_EXPLAIN
+explain select * from v2;
+disconnect test11765687;
+
+#
+--echo ... as karl
+connect (test11765687,localhost,karl,,mysqltest1);
+connection test11765687;
+
+select * from v2;
+# has sel only on v2, sel on t1/t2, sel/show v1
+# fail: lacks show on v2
+--error ER_VIEW_NO_EXPLAIN
+explain select * from v2;
+disconnect test11765687;
+
+#
+--echo ... as lena
+
+connect (test11765687,localhost,lena,,mysqltest1);
+connection test11765687;
+--error ER_TABLEACCESS_DENIED_ERROR
+select * from v2;
+# has show only on v2, sel on t1/t2, sel/show v1
+# fail: lacks sel on v2
+--error ER_TABLEACCESS_DENIED_ERROR
+explain select * from v2;
+disconnect test11765687;
+
+#
+--echo ... as mhairi
+connect (test11765687,localhost,mhairi,,mysqltest1);
+connection test11765687;
+
+select * from v2;
+# has sel/show on v2, sel on t1/t2, sel/show v1
+explain select * from v2;
+disconnect test11765687;
+
+#
+--echo ... as noam
+connect (test11765687,localhost,noam,,mysqltest1);
+connection test11765687;
+
+select * from v2;
+# has sel/show on v2, sel only on t1, sel/show v1 (no sel on t2!)
+--error ER_VIEW_NO_EXPLAIN
+explain select * from v2;
+disconnect test11765687;
+
+#
+--echo ... as olga
+connect (test11765687,localhost,olga,,mysqltest1);
+connection test11765687;
+
+select * from v2;
+# has sel/show on v2, sel only on t2, sel/show v1 (no sel on t1!)
+--error ER_VIEW_NO_EXPLAIN
+explain select * from v2;
+disconnect test11765687;
+
+#
+--echo ... as pjotr
+connect (test11765687,localhost,pjotr,,mysqltest1);
+connection test11765687;
+
+select * from v2;
+# has sel/show on v2, sel only on t2, nothing on v1
+# fail: lacks show on v1
+--error ER_VIEW_NO_EXPLAIN
+explain select * from v2;
+disconnect test11765687;
+
+#
+--echo ... as quintessa
+connect (test11765687,localhost,quintessa,,mysqltest1);
+connection test11765687;
+
+select * from v1; # Should succeed.
+--error ER_VIEW_NO_EXPLAIN
+explain select * from v1; # fail: lacks select on t1
+
+disconnect test11765687;
+
+# cleanup
+
+#
+--echo ... as root again at last: clean-up time!
+connection root;
+
+drop user alice@localhost;
+drop user bob@localhost;
+drop user cecil@localhost;
+drop user dan@localhost;
+drop user eugene@localhost;
+drop user fiona@localhost;
+drop user greg@localhost;
+drop user han@localhost;
+drop user inga@localhost;
+drop user jamie@localhost;
+drop user karl@localhost;
+drop user lena@localhost;
+drop user mhairi@localhost;
+drop user noam@localhost;
+drop user olga@localhost;
+drop user pjotr@localhost;
+drop user quintessa@localhost;
+
+drop database mysqltest1;
+
+disconnect root;
+
+connection default;
+
 --echo End of 5.0 tests.