mirror of
https://github.com/MariaDB/server.git
synced 2025-10-10 17:59:15 +02:00
eeb00ceffd
Follow-up patch with adjustments of test files and updates of result files for tests. Some of tests were rewritten slighlty. Everywhere where common pattern used: ----- CREATE USER userA; --connect con1 ... userA ... <sql statements...> --disconnect con1 DROP USER userA; ----- the DROP USER statement has been eclosed into the directive --disable_warnings --enable_warnings This change is caused by the race conddition between --disconnect and DROP USER since a number of currently running sessions established on behalf the user being dropped is counted by holding the rw_lock THD_list_iterator::lock that is not acquired on execution the DROP USER statement but the lock is taken as the last step on handling disconnection (when the client is already sending the next statement). Therefore, for the cases where the command --disconnect precedes the DROP USER statement we hide the possible warnings about presence of active sessions for the user being deleted to make tests deterministic.
113 lines
4.6 KiB
Text
113 lines
4.6 KiB
Text
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;
|
|
drop user 'normal'@'%';
|
|
Warnings:
|
|
Note 4226 Dropped users ['normal'@'%'] have active connections. Use KILL CONNECTION if they should not be used anymore.
|
|
drop user 'read_only'@'%';
|
|
Warnings:
|
|
Note 4226 Dropped users ['read_only'@'%'] have active connections. Use KILL CONNECTION if they should not be used anymore.
|
|
drop user 'read_write'@'%';
|
|
Warnings:
|
|
Note 4226 Dropped users ['read_write'@'%'] have active connections. Use KILL CONNECTION if they should not be used anymore.
|
|
drop user 'alter'@'%';
|
|
Warnings:
|
|
Note 4226 Dropped users ['alter'@'%'] have active connections. Use KILL CONNECTION if they should not be used anymore.
|
|
drop user 'only_alter'@'%';
|
|
Warnings:
|
|
Note 4226 Dropped users ['only_alter'@'%'] have active connections. Use KILL CONNECTION if they should not be used anymore.
|
|
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
|
|
#
|