mirror of
https://github.com/MariaDB/server.git
synced 2025-07-31 15:40:45 +02:00
e3d9369774
let's always disconnect a user connection before dropping the said user. MariaDB is traditionally very tolerant to active connections of the dropped user, which isn't the case for most other databases. Let's avoid unintentionally spreading incompatible behavior and disconnect before drop. Except in cases when the test specifically tests such a behavior.
123 lines
3.6 KiB
Text
123 lines
3.6 KiB
Text
# Can't test with embedded server
|
|
-- source include/not_embedded.inc
|
|
|
|
connect (root,localhost,root,,test);
|
|
connection root;
|
|
|
|
create database mysqltest;
|
|
create user mysqltest_1@localhost;
|
|
connect (user1,localhost,mysqltest_1,,"*NO-ONE*");
|
|
connection user1;
|
|
|
|
connection root;
|
|
|
|
create table mysqltest.t1 (a int, b int);
|
|
insert into mysqltest.t1 values (2,10), (1,30);
|
|
create table mysqltest.t2 (c int, d char(32));
|
|
insert into mysqltest.t2 values (1,'xxx'), (1,'zzz');
|
|
|
|
grant select on mysqltest.t1 to mysqltest_1@localhost;
|
|
grant select (c) on mysqltest.t2 to mysqltest_1@localhost;
|
|
|
|
connection user1;
|
|
with t as (select c from mysqltest.t2 where c < 2)
|
|
select t.c,t1.b from t,mysqltest.t1 where t.c=t1.a;
|
|
--error ER_COLUMNACCESS_DENIED_ERROR
|
|
select t.c,t.d,t1.b
|
|
from (select c,d from mysqltest.t2 where c < 2) as t, mysqltest.t1
|
|
where t.c=t1.a;
|
|
--error ER_COLUMNACCESS_DENIED_ERROR
|
|
with t as (select c,d from mysqltest.t2 where c < 2)
|
|
select t.c,t.d,t1.b from t,mysqltest.t1 where t.c=t1.a;
|
|
|
|
connection root;
|
|
|
|
create view mysqltest.v1(f1,f2) as
|
|
with t as (select c from mysqltest.t2 where c < 2)
|
|
select t.c,t1.b from t,mysqltest.t1 where t.c=t1.a;
|
|
create view mysqltest.v2(c,d) as
|
|
with t as (select a from mysqltest.t1 where a>=3)
|
|
select t.a,b from t,mysqltest.t1 where mysqltest.t1.a = t.a;
|
|
|
|
grant select on mysqltest.v1 to mysqltest_1@localhost;
|
|
grant select (c) on mysqltest.v2 to mysqltest_1@localhost;
|
|
grant create view on mysqltest.* to mysqltest_1@localhost;
|
|
|
|
connection user1;
|
|
|
|
create view mysqltest.v3(c,d) as
|
|
with t as (select c from mysqltest.t2 where c < 2)
|
|
select t.c,t1.b from t,mysqltest.t1 where t.c=t1.a;
|
|
--error ER_COLUMNACCESS_DENIED_ERROR
|
|
create view mysqltest.v4(f1,f2,f3) as
|
|
with t as (select c,d from mysqltest.t2 where c < 2)
|
|
select t.c,t.d,t1.b from t,mysqltest.t1 where t.c=t1.a;
|
|
|
|
select * from mysqltest.v1;
|
|
|
|
select c from mysqltest.v2;
|
|
# there are no privileges on column 'd'
|
|
--error ER_COLUMNACCESS_DENIED_ERROR
|
|
select d from mysqltest.v2;
|
|
|
|
--error ER_TABLEACCESS_DENIED_ERROR
|
|
select * from mysqltest.v3;
|
|
connection root;
|
|
grant select on mysqltest.v3 to mysqltest_1@localhost;
|
|
connection user1;
|
|
select * from mysqltest.v3;
|
|
|
|
connection root;
|
|
disconnect user1;
|
|
drop user mysqltest_1@localhost;
|
|
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,,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;
|