diff options
author | Sergei Golubchik <serg@mariadb.org> | 2017-11-20 15:03:44 +0100 |
---|---|---|
committer | Sergei Golubchik <serg@mariadb.org> | 2017-11-21 20:03:57 +0100 |
commit | 5dd505b70995baf2db9baaa6bf971eb3495a04ad (patch) | |
tree | 752aa6ede8f85cbeb6b09252b177eff35ae9d87a | |
parent | 7f1900705b28a432a91595726f7d58e6d968cbd5 (diff) | |
download | mariadb-git-5dd505b70995baf2db9baaa6bf971eb3495a04ad.tar.gz |
MDEV-14428 main.cte_nonrecursive failed in --embedded
move the privilege related test to main.cte_grant
-rw-r--r-- | mysql-test/r/cte_grant.result | 58 | ||||
-rw-r--r-- | mysql-test/r/cte_nonrecursive.result | 58 | ||||
-rw-r--r-- | mysql-test/t/cte_grant.test | 53 | ||||
-rw-r--r-- | mysql-test/t/cte_nonrecursive.test | 51 |
4 files changed, 110 insertions, 110 deletions
diff --git a/mysql-test/r/cte_grant.result b/mysql-test/r/cte_grant.result index 1282a98fdef..2ee31be3435 100644 --- a/mysql-test/r/cte_grant.result +++ b/mysql-test/r/cte_grant.result @@ -63,3 +63,61 @@ connection root; revoke all privileges on mysqltest.v1 from mysqltest_1@localhost; drop user mysqltest_1@localhost; drop database mysqltest; +# +# MDEV-13453: privileges checking for CTE +# +create database db; +use db; +create table t1 (i int); +insert into t1 +values (3), (7), (1), (4), (2), (3), (1); +create table t2 (a int, b int); +insert into t2 +values (3,10), (7,11), (1,17), (4,15), (2,11), (3,10), (1,15); +create user foo@localhost; +grant SELECT on db.t1 to foo@localhost; +grant SELECT(a) on db.t2 to foo@localhost; +connect con1,localhost,foo,,; +use db; +with cte as (select * from t1 where i < 4) +select * from cte; +i +3 +1 +2 +3 +1 +with cte as (select * from t1 where i < 4 group by i) +select * from cte; +i +1 +2 +3 +with cte as (select * from t1 where i < 4) +select * from cte cte1 where i < 2 union select * from cte cte2 where i > 2; +i +1 +3 +with cte as (select * from t1 where i < 4 group by i) +select * from cte cte1 where i < 2 union select * from cte cte2 where i > 2; +i +1 +3 +with cte as (select b from t2 where a < 4) +select * from cte cte1 where b < 15 union select * from cte cte2 where b > 15; +ERROR 42000: SELECT command denied to user 'foo'@'localhost' for column 'b' in table 't2' +with cte as (select a from t2 where a < 4) +select * from cte cte1 where a < 2 union select * from cte cte2 where a > 2; +a +1 +3 +connection default; +revoke SELECT on db.t1 from foo@localhost; +connection con1; +with cte as (select * from t1 where i < 4) +select * from cte; +ERROR 42000: SELECT command denied to user 'foo'@'localhost' for table 't1' +disconnect con1; +connection default; +drop database db; +drop user foo@localhost; diff --git a/mysql-test/r/cte_nonrecursive.result b/mysql-test/r/cte_nonrecursive.result index 21a1fa3164f..ebe1aae1e8f 100644 --- a/mysql-test/r/cte_nonrecursive.result +++ b/mysql-test/r/cte_nonrecursive.result @@ -1147,61 +1147,3 @@ SELECT * FROM cte_test; a 1 DROP VIEW cte_test; -# -# MDEV-13453: privileges checking for CTE -# -create database db; -use db; -create table t1 (i int); -insert into t1 -values (3), (7), (1), (4), (2), (3), (1); -create table t2 (a int, b int); -insert into t2 -values (3,10), (7,11), (1,17), (4,15), (2,11), (3,10), (1,15); -create user foo@localhost; -grant SELECT on db.t1 to foo@localhost; -grant SELECT(a) on db.t2 to foo@localhost; -connect con1,localhost,foo,,; -use db; -with cte as (select * from t1 where i < 4) -select * from cte; -i -3 -1 -2 -3 -1 -with cte as (select * from t1 where i < 4 group by i) -select * from cte; -i -1 -2 -3 -with cte as (select * from t1 where i < 4) -select * from cte cte1 where i < 2 union select * from cte cte2 where i > 2; -i -1 -3 -with cte as (select * from t1 where i < 4 group by i) -select * from cte cte1 where i < 2 union select * from cte cte2 where i > 2; -i -1 -3 -with cte as (select b from t2 where a < 4) -select * from cte cte1 where b < 15 union select * from cte cte2 where b > 15; -ERROR 42000: SELECT command denied to user 'foo'@'localhost' for column 'b' in table 't2' -with cte as (select a from t2 where a < 4) -select * from cte cte1 where a < 2 union select * from cte cte2 where a > 2; -a -1 -3 -connection default; -revoke SELECT on db.t1 from foo@localhost; -connection con1; -with cte as (select * from t1 where i < 4) -select * from cte; -ERROR 42000: SELECT command denied to user 'foo'@'localhost' for table 't1' -disconnect con1; -connection default; -drop database db; -drop user foo@localhost; diff --git a/mysql-test/t/cte_grant.test b/mysql-test/t/cte_grant.test index 44fd4a0bc6e..c6627c05829 100644 --- a/mysql-test/t/cte_grant.test +++ b/mysql-test/t/cte_grant.test @@ -76,4 +76,55 @@ select * from mysqltest.v3; connection root; revoke all privileges on mysqltest.v1 from mysqltest_1@localhost; drop user mysqltest_1@localhost; -drop database mysqltest;
\ No newline at end of file +drop database mysqltest; + +--echo # +--echo # MDEV-13453: privileges checking for CTE +--echo # + +create database db; +use db; +create table t1 (i int); +insert into t1 + values (3), (7), (1), (4), (2), (3), (1); + +create table t2 (a int, b int); +insert into t2 + values (3,10), (7,11), (1,17), (4,15), (2,11), (3,10), (1,15); + +create user foo@localhost; +grant SELECT on db.t1 to foo@localhost; +grant SELECT(a) on db.t2 to foo@localhost; + +--connect (con1,localhost,foo,,) +use db; +with cte as (select * from t1 where i < 4) + select * from cte; +with cte as (select * from t1 where i < 4 group by i) + select * from cte; +with cte as (select * from t1 where i < 4) + select * from cte cte1 where i < 2 union select * from cte cte2 where i > 2; +with cte as (select * from t1 where i < 4 group by i) + select * from cte cte1 where i < 2 union select * from cte cte2 where i > 2; + +--error ER_COLUMNACCESS_DENIED_ERROR +with cte as (select b from t2 where a < 4) + select * from cte cte1 where b < 15 union select * from cte cte2 where b > 15; +with cte as (select a from t2 where a < 4) + select * from cte cte1 where a < 2 union select * from cte cte2 where a > 2; + +--connection default +revoke SELECT on db.t1 from foo@localhost; + +--connection con1 + +--error ER_TABLEACCESS_DENIED_ERROR +with cte as (select * from t1 where i < 4) + select * from cte; + +# Cleanup +--disconnect con1 + +--connection default +drop database db; +drop user foo@localhost; diff --git a/mysql-test/t/cte_nonrecursive.test b/mysql-test/t/cte_nonrecursive.test index 03e6e298fa8..742e8f6e4d7 100644 --- a/mysql-test/t/cte_nonrecursive.test +++ b/mysql-test/t/cte_nonrecursive.test @@ -790,54 +790,3 @@ SHOW CREATE VIEW cte_test; SELECT * FROM cte_test; DROP VIEW cte_test; - ---echo # ---echo # MDEV-13453: privileges checking for CTE ---echo # - -create database db; -use db; -create table t1 (i int); -insert into t1 - values (3), (7), (1), (4), (2), (3), (1); - -create table t2 (a int, b int); -insert into t2 - values (3,10), (7,11), (1,17), (4,15), (2,11), (3,10), (1,15); - -create user foo@localhost; -grant SELECT on db.t1 to foo@localhost; -grant SELECT(a) on db.t2 to foo@localhost; - ---connect (con1,localhost,foo,,) -use db; -with cte as (select * from t1 where i < 4) - select * from cte; -with cte as (select * from t1 where i < 4 group by i) - select * from cte; -with cte as (select * from t1 where i < 4) - select * from cte cte1 where i < 2 union select * from cte cte2 where i > 2; -with cte as (select * from t1 where i < 4 group by i) - select * from cte cte1 where i < 2 union select * from cte cte2 where i > 2; - ---error ER_COLUMNACCESS_DENIED_ERROR -with cte as (select b from t2 where a < 4) - select * from cte cte1 where b < 15 union select * from cte cte2 where b > 15; -with cte as (select a from t2 where a < 4) - select * from cte cte1 where a < 2 union select * from cte cte2 where a > 2; - ---connection default -revoke SELECT on db.t1 from foo@localhost; - ---connection con1 - ---error ER_TABLEACCESS_DENIED_ERROR -with cte as (select * from t1 where i < 4) - select * from cte; - -# Cleanup ---disconnect con1 - ---connection default -drop database db; -drop user foo@localhost; |