mirror of
https://github.com/MariaDB/server.git
synced 2025-08-07 00:04:31 +03: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.
109 lines
4.0 KiB
Plaintext
109 lines
4.0 KiB
Plaintext
SET @@SQL_MODE = REPLACE(@@SQL_MODE, 'NO_AUTO_CREATE_USER', '');
|
|
create database mysqltest_1;
|
|
use mysqltest_1;
|
|
grant all on mysqltest_1.* to 'normal'@'%';
|
|
grant select on mysqltest_1.* to 'read_only'@'%';
|
|
grant select,insert on mysqltest_1.* to 'read_write'@'%';
|
|
grant select,insert,alter on mysqltest_1.* to 'alter'@'%';
|
|
grant alter on mysqltest_1.* to only_alter@'%';
|
|
connect normal,localhost,normal,,mysqltest_1;
|
|
connect read_only,localhost,read_only,,mysqltest_1;
|
|
connect read_write,localhost,read_write,,mysqltest_1;
|
|
connect alter,localhost,alter,,mysqltest_1;
|
|
connect only_alter, localhost, only_alter,,mysqltest_1;
|
|
connection normal;
|
|
create sequence s1;
|
|
select next value for s1;
|
|
next value for s1
|
|
1
|
|
alter sequence s1 restart= 11;
|
|
select * from s1;
|
|
next_not_cached_value minimum_value maximum_value start_value increment cache_size cycle_option cycle_count
|
|
11 1 9223372036854775806 1 1 1000 0 0
|
|
connection read_only;
|
|
select next value for s1;
|
|
ERROR 42000: INSERT command denied to user 'read_only'@'localhost' for table `mysqltest_1`.`s1`
|
|
alter sequence s1 restart= 11;
|
|
ERROR 42000: ALTER command denied to user 'read_only'@'localhost' for table `mysqltest_1`.`s1`
|
|
select * from s1;
|
|
next_not_cached_value minimum_value maximum_value start_value increment cache_size cycle_option cycle_count
|
|
11 1 9223372036854775806 1 1 1000 0 0
|
|
connection read_write;
|
|
select next value for s1;
|
|
next value for s1
|
|
11
|
|
alter sequence s1 restart= 11;
|
|
ERROR 42000: ALTER command denied to user 'read_write'@'localhost' for table `mysqltest_1`.`s1`
|
|
select * from s1;
|
|
next_not_cached_value minimum_value maximum_value start_value increment cache_size cycle_option cycle_count
|
|
1011 1 9223372036854775806 1 1 1000 0 0
|
|
connection alter;
|
|
select next value for s1;
|
|
next value for s1
|
|
12
|
|
alter sequence s1 restart= 11;
|
|
select * from s1;
|
|
next_not_cached_value minimum_value maximum_value start_value increment cache_size cycle_option cycle_count
|
|
11 1 9223372036854775806 1 1 1000 0 0
|
|
connection only_alter;
|
|
select next value for s1;
|
|
ERROR 42000: SELECT, INSERT command denied to user 'only_alter'@'localhost' for table `mysqltest_1`.`s1`
|
|
alter sequence s1 restart= 11;
|
|
select * from s1;
|
|
ERROR 42000: SELECT command denied to user 'only_alter'@'localhost' for table `mysqltest_1`.`s1`
|
|
connection default;
|
|
disconnect normal;
|
|
disconnect read_only;
|
|
disconnect read_write;
|
|
disconnect alter;
|
|
disconnect only_alter;
|
|
drop user 'normal'@'%';
|
|
drop user 'read_only'@'%';
|
|
drop user 'read_write'@'%';
|
|
drop user 'alter'@'%';
|
|
drop user 'only_alter'@'%';
|
|
drop sequence s1;
|
|
#
|
|
# MDEV-36413 User without any privileges to a sequence can read from
|
|
# it and modify it via column default
|
|
#
|
|
create sequence s1;
|
|
create sequence s2;
|
|
select * from s2;
|
|
next_not_cached_value minimum_value maximum_value start_value increment cache_size cycle_option cycle_count
|
|
1 1 9223372036854775806 1 1 1000 0 0
|
|
create table t2 (a int not null default(nextval(s1)));
|
|
insert into t2 values();
|
|
create user u;
|
|
grant create, insert, select, drop on mysqltest_1.t1 to u;
|
|
grant insert, select on mysqltest_1.s1 to u;
|
|
grant select on mysqltest_1.t2 to u;
|
|
connect con1,localhost,u,,mysqltest_1;
|
|
select nextval(s2);
|
|
ERROR 42000: SELECT, INSERT command denied to user 'u'@'localhost' for table `mysqltest_1`.`s2`
|
|
show create sequence s2;
|
|
ERROR 42000: SHOW command denied to user 'u'@'localhost' for table `mysqltest_1`.`s2`
|
|
create table t1 (a int not null default(nextval(s1)));
|
|
drop table t1;
|
|
create table t1 (a int not null default(nextval(s1))) select a from t2;
|
|
insert into t1 values();
|
|
select * from t1;
|
|
a
|
|
1
|
|
2
|
|
drop table t1;
|
|
create table t1 (a int not null default(nextval(s1))) select a from (select t2.a from t2,t2 as t3 where t2.a=t3.a) as t4;
|
|
drop table t1;
|
|
create table t1 (a int not null default(nextval(s2)));
|
|
ERROR 42000: SELECT, INSERT command denied to user 'u'@'localhost' for table `mysqltest_1`.`s2`
|
|
create table t1 (a int not null default(nextval(s1)),
|
|
b int not null default(nextval(s2)));
|
|
ERROR 42000: SELECT, INSERT command denied to user 'u'@'localhost' for table `mysqltest_1`.`s2`
|
|
disconnect con1;
|
|
connection default;
|
|
drop user u;
|
|
drop database mysqltest_1;
|
|
#
|
|
# End of 10.11 tests
|
|
#
|