summaryrefslogtreecommitdiff
diff options
context:
space:
mode:
Diffstat
-rw-r--r--mysql-test/r/grant.result75
-rw-r--r--mysql-test/t/grant.test123
2 files changed, 198 insertions, 0 deletions
diff --git a/mysql-test/r/grant.result b/mysql-test/r/grant.result
index 3f3325354ee..494c68e5731 100644
--- a/mysql-test/r/grant.result
+++ b/mysql-test/r/grant.result
@@ -867,3 +867,78 @@ insert into mysql.user select * from t2;
flush privileges;
drop table t2;
drop table t1;
+CREATE DATABASE mysqltest3;
+use mysqltest3;
+CREATE TABLE t_nn (c1 INT);
+CREATE VIEW v_nn AS SELECT * FROM t_nn;
+CREATE DATABASE mysqltest2;
+use mysqltest2;
+CREATE TABLE t_nn (c1 INT);
+CREATE VIEW v_nn AS SELECT * FROM t_nn;
+CREATE VIEW v_yn AS SELECT * FROM t_nn;
+CREATE VIEW v_gy AS SELECT * FROM t_nn;
+CREATE VIEW v_ny AS SELECT * FROM t_nn;
+CREATE VIEW v_yy AS SELECT * FROM t_nn WHERE c1=55;
+GRANT SHOW VIEW ON mysqltest2.v_ny TO 'mysqltest_1'@'localhost' IDENTIFIED BY 'mysqltest_1';
+GRANT SELECT ON mysqltest2.v_yn TO 'mysqltest_1'@'localhost' IDENTIFIED BY 'mysqltest_1';
+GRANT SELECT ON mysqltest2.* TO 'mysqltest_1'@'localhost' IDENTIFIED BY 'mysqltest_1';
+GRANT SHOW VIEW,SELECT ON mysqltest2.v_yy TO 'mysqltest_1'@'localhost' IDENTIFIED BY 'mysqltest_1';
+SHOW CREATE VIEW mysqltest2.v_nn;
+ERROR 42000: SHOW VIEW command denied to user 'mysqltest_1'@'localhost' for table 'v_nn'
+SHOW CREATE TABLE mysqltest2.v_nn;
+ERROR 42000: SHOW VIEW command denied to user 'mysqltest_1'@'localhost' for table 'v_nn'
+SHOW CREATE VIEW mysqltest2.v_yn;
+ERROR 42000: SHOW VIEW command denied to user 'mysqltest_1'@'localhost' for table 'v_yn'
+SHOW CREATE TABLE mysqltest2.v_yn;
+ERROR 42000: SHOW VIEW command denied to user 'mysqltest_1'@'localhost' for table 'v_yn'
+SHOW CREATE TABLE mysqltest2.v_ny;
+View Create View
+v_ny CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `mysqltest2`.`v_ny` AS select `mysqltest2`.`t_nn`.`c1` AS `c1` from `mysqltest2`.`t_nn`
+SHOW CREATE VIEW mysqltest2.v_ny;
+View Create View
+v_ny CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `mysqltest2`.`v_ny` AS select `mysqltest2`.`t_nn`.`c1` AS `c1` from `mysqltest2`.`t_nn`
+SHOW CREATE VIEW mysqltest3.t_nn;
+ERROR 42000: SELECT command denied to user 'mysqltest_1'@'localhost' for table 't_nn'
+SHOW CREATE TABLE mysqltest3.t_nn;
+ERROR 42000: SELECT command denied to user 'mysqltest_1'@'localhost' for table 't_nn'
+SHOW CREATE VIEW mysqltest3.v_nn;
+ERROR 42000: SELECT command denied to user 'mysqltest_1'@'localhost' for table 'v_nn'
+SHOW CREATE TABLE mysqltest3.v_nn;
+ERROR 42000: SELECT command denied to user 'mysqltest_1'@'localhost' for table 'v_nn'
+SHOW CREATE TABLE mysqltest2.t_nn;
+Table Create Table
+t_nn CREATE TABLE `t_nn` (
+ `c1` int(11) default NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+SHOW CREATE VIEW mysqltest2.t_nn;
+ERROR HY000: 'mysqltest2.t_nn' is not VIEW
+SHOW CREATE VIEW mysqltest2.v_yy;
+View Create View
+v_yy CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `mysqltest2`.`v_yy` AS select `mysqltest2`.`t_nn`.`c1` AS `c1` from `mysqltest2`.`t_nn` where (`mysqltest2`.`t_nn`.`c1` = 55)
+SHOW CREATE TABLE mysqltest2.v_yy;
+View Create View
+v_yy CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `mysqltest2`.`v_yy` AS select `mysqltest2`.`t_nn`.`c1` AS `c1` from `mysqltest2`.`t_nn` where (`mysqltest2`.`t_nn`.`c1` = 55)
+SHOW CREATE TABLE mysqltest2.v_nn;
+View Create View
+v_nn CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v_nn` AS select `t_nn`.`c1` AS `c1` from `t_nn`
+SHOW CREATE VIEW mysqltest2.v_nn;
+View Create View
+v_nn CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v_nn` AS select `t_nn`.`c1` AS `c1` from `t_nn`
+SHOW CREATE TABLE mysqltest2.t_nn;
+Table Create Table
+t_nn CREATE TABLE `t_nn` (
+ `c1` int(11) default NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+SHOW CREATE VIEW mysqltest2.t_nn;
+ERROR HY000: 'mysqltest2.t_nn' is not VIEW
+DROP VIEW mysqltest2.v_nn;
+DROP VIEW mysqltest2.v_yn;
+DROP VIEW mysqltest2.v_ny;
+DROP VIEW mysqltest2.v_yy;
+DROP TABLE mysqltest2.t_nn;
+DROP DATABASE mysqltest2;
+DROP VIEW mysqltest3.v_nn;
+DROP TABLE mysqltest3.t_nn;
+DROP DATABASE mysqltest3;
+REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'mysqltest_1'@'localhost';
+DROP USER 'mysqltest_1'@'localhost';
diff --git a/mysql-test/t/grant.test b/mysql-test/t/grant.test
index a9d52f559ca..1156c670934 100644
--- a/mysql-test/t/grant.test
+++ b/mysql-test/t/grant.test
@@ -681,3 +681,126 @@ drop table t2;
drop table t1;
+
+#
+# Bug#20214: Incorrect error when user calls SHOW CREATE VIEW on non
+# privileged view
+#
+
+connection master;
+
+CREATE DATABASE mysqltest3;
+use mysqltest3;
+
+CREATE TABLE t_nn (c1 INT);
+CREATE VIEW v_nn AS SELECT * FROM t_nn;
+
+CREATE DATABASE mysqltest2;
+use mysqltest2;
+
+CREATE TABLE t_nn (c1 INT);
+CREATE VIEW v_nn AS SELECT * FROM t_nn;
+CREATE VIEW v_yn AS SELECT * FROM t_nn;
+CREATE VIEW v_gy AS SELECT * FROM t_nn;
+CREATE VIEW v_ny AS SELECT * FROM t_nn;
+CREATE VIEW v_yy AS SELECT * FROM t_nn WHERE c1=55;
+
+GRANT SHOW VIEW ON mysqltest2.v_ny TO 'mysqltest_1'@'localhost' IDENTIFIED BY 'mysqltest_1';
+GRANT SELECT ON mysqltest2.v_yn TO 'mysqltest_1'@'localhost' IDENTIFIED BY 'mysqltest_1';
+GRANT SELECT ON mysqltest2.* TO 'mysqltest_1'@'localhost' IDENTIFIED BY 'mysqltest_1';
+GRANT SHOW VIEW,SELECT ON mysqltest2.v_yy TO 'mysqltest_1'@'localhost' IDENTIFIED BY 'mysqltest_1';
+
+connect (mysqltest_1, localhost, mysqltest_1, mysqltest_1,);
+
+# fail because of missing SHOW VIEW (have generic SELECT)
+--error ER_TABLEACCESS_DENIED_ERROR
+SHOW CREATE VIEW mysqltest2.v_nn;
+--error ER_TABLEACCESS_DENIED_ERROR
+SHOW CREATE TABLE mysqltest2.v_nn;
+
+
+
+# fail because of missing SHOW VIEW
+--error ER_TABLEACCESS_DENIED_ERROR
+SHOW CREATE VIEW mysqltest2.v_yn;
+--error ER_TABLEACCESS_DENIED_ERROR
+SHOW CREATE TABLE mysqltest2.v_yn;
+
+
+
+# succeed (despite of missing SELECT, having SHOW VIEW bails us out)
+SHOW CREATE TABLE mysqltest2.v_ny;
+
+# succeed (despite of missing SELECT, having SHOW VIEW bails us out)
+SHOW CREATE VIEW mysqltest2.v_ny;
+
+
+
+# fail because of missing (specific or generic) SELECT
+--error ER_TABLEACCESS_DENIED_ERROR
+SHOW CREATE TABLE mysqltest3.t_nn;
+
+# fail because of missing (specific or generic) SELECT (not because it's not a view!)
+--error ER_TABLEACCESS_DENIED_ERROR
+SHOW CREATE VIEW mysqltest3.t_nn;
+
+
+
+# fail because of missing missing (specific or generic) SELECT (and SHOW VIEW)
+--error ER_TABLEACCESS_DENIED_ERROR
+SHOW CREATE VIEW mysqltest3.v_nn;
+--error ER_TABLEACCESS_DENIED_ERROR
+SHOW CREATE TABLE mysqltest3.v_nn;
+
+
+
+# succeed thanks to generic SELECT
+SHOW CREATE TABLE mysqltest2.t_nn;
+
+# fail because it's not a view! (have generic SELECT though)
+--error ER_WRONG_OBJECT
+SHOW CREATE VIEW mysqltest2.t_nn;
+
+
+
+# succeed, have SELECT and SHOW VIEW
+SHOW CREATE VIEW mysqltest2.v_yy;
+
+# succeed, have SELECT and SHOW VIEW
+SHOW CREATE TABLE mysqltest2.v_yy;
+
+
+
+#clean-up
+connection master;
+
+# succeed, we're root
+SHOW CREATE TABLE mysqltest2.v_nn;
+SHOW CREATE VIEW mysqltest2.v_nn;
+
+SHOW CREATE TABLE mysqltest2.t_nn;
+
+# fail because it's not a view!
+--error ER_WRONG_OBJECT
+SHOW CREATE VIEW mysqltest2.t_nn;
+
+
+
+DROP VIEW mysqltest2.v_nn;
+DROP VIEW mysqltest2.v_yn;
+DROP VIEW mysqltest2.v_ny;
+DROP VIEW mysqltest2.v_yy;
+
+DROP TABLE mysqltest2.t_nn;
+
+DROP DATABASE mysqltest2;
+
+
+
+DROP VIEW mysqltest3.v_nn;
+DROP TABLE mysqltest3.t_nn;
+
+DROP DATABASE mysqltest3;
+
+REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'mysqltest_1'@'localhost';
+DROP USER 'mysqltest_1'@'localhost';
View Lorry Controller Status