mirror/mariadb
1
0
Fork 0
mirror of https://github.com/MariaDB/server.git synced 2025-07-06 03:18:17 +02:00
Code Issues Projects Releases Packages Wiki Activity
mariadb/mysql-test/suite/sql_sequence/gtid.result
Sergei Golubchik f99586668a MDEV-36380 User has unauthorized access to a sequence through a view with security invoker 
check sequence privileges in Item_func_nextval::fix_fields(),
just like column privileges are checked in Item_field::fix_fields()

remove sequence specific hacks that kinda made sequence privilege
checks works, but not in all cases. And they were too lax,
didn't requre SELECT privilege for NEXTVAL. Also INSERT privilege looks
wrong here, UPDATE would've been more appropriate, but won't
change that for compatibility reasons.

also fixes

MDEV-36413 User without any privileges to a sequence can read from it and modify it via column default
2025-04-17 17:18:55 +02:00

831 lines
20 KiB
Text
Raw Permalink Blame History

include/master-slave.inc
[connection master]
connection master;
create database s_db;
grant all on s_db.* to normal_1@'%' identified by 'pass';
grant all on test.* to normal_2@'%' identified by 'pass';
grant all on s_db.* to normal_3@'%' identified by 'pass';
grant all on test.* to normal_4@'%' identified by 'pass';
grant select on test.* to normal_5@'%' identified by 'pass';
flush status;
connection slave;
connect m_normal_1, 127.0.0.1, normal_1, pass, s_db, $MASTER_MYPORT;
connect m_normal_2, 127.0.0.1, normal_2, pass, test, $MASTER_MYPORT;
connect m_normal_3, 127.0.0.1, normal_5, pass, test, $MASTER_MYPORT;
connect s_normal_3, 127.0.0.1, normal_3, pass, s_db, $SLAVE_MYPORT;
connect s_normal_4, 127.0.0.1, normal_4, pass, test, $SLAVE_MYPORT;
connection slave;
set global read_only=on;
###########################################
master and slave sync sequence.
###########################################
connection master;
use s_db;
create sequence s1;
show create table s1;
Table Create Table
s1 CREATE TABLE `s1` (
`next_not_cached_value` bigint(21) NOT NULL,
`minimum_value` bigint(21) NOT NULL,
`maximum_value` bigint(21) NOT NULL,
`start_value` bigint(21) NOT NULL COMMENT 'start value when sequences is created or value if RESTART is used',
`increment` bigint(21) NOT NULL COMMENT 'increment value',
`cache_size` bigint(21) unsigned NOT NULL,
`cycle_option` tinyint(1) unsigned NOT NULL COMMENT '0 if no cycles are allowed, 1 if the sequence should begin a new cycle when maximum_value is passed',
`cycle_count` bigint(21) NOT NULL COMMENT 'How many cycles have been done'
) ENGINE=MyISAM SEQUENCE=1
connection slave;
use s_db;
show create table s1;
Table Create Table
s1 CREATE TABLE `s1` (
`next_not_cached_value` bigint(21) NOT NULL,
`minimum_value` bigint(21) NOT NULL,
`maximum_value` bigint(21) NOT NULL,
`start_value` bigint(21) NOT NULL COMMENT 'start value when sequences is created or value if RESTART is used',
`increment` bigint(21) NOT NULL COMMENT 'increment value',
`cache_size` bigint(21) unsigned NOT NULL,
`cycle_option` tinyint(1) unsigned NOT NULL COMMENT '0 if no cycles are allowed, 1 if the sequence should begin a new cycle when maximum_value is passed',
`cycle_count` bigint(21) NOT NULL COMMENT 'How many cycles have been done'
) ENGINE=MyISAM SEQUENCE=1
connection master;
use s_db;
drop sequence s1;
###########################################
not support create table engine=sequence.
###########################################
connection master;
create table t(id int)engine=sequence;
ERROR 42000: Unknown storage engine 'sequence'
create table t(id int)engine=innodb;
alter table t engine=sequence;
ERROR 42000: Unknown storage engine 'sequence'
drop table t;
###########################################
not support alter sequence table.
###########################################
connection master;
create sequence s2;
alter table s2 add id int;
ERROR HY000: Sequence 's_db.s2' table structure is invalid (Wrong number of columns)
alter table s2 add index ind_x(start);
ERROR HY000: Sequence 's_db.s2' table structure is invalid (Sequence tables cannot have any keys)
drop sequence s2;
###########################################
Support create temporary sequence.
###########################################
connection master;
create temporary sequence s2;
drop temporary sequence s2;
###########################################
all invalid sequence value
###########################################
connection master;
use s_db;
create sequence s2 start with 1
minvalue 1
maxvalue 100000
increment by 1
cache 10000
cycle;
drop sequence s2;
create sequence s2 start with 1
minvalue 1
maxvalue 100000
increment by 1
cache 10000
nocycle;
drop sequence s2;
create sequence s2 start with 1
minvalue 1
maxvalue 100000
increment by 1
nocache
nocycle;
drop sequence s2;
create sequence s2 start with 1
minvalue 5
maxvalue 100000
increment by 1
nocache
nocycle;
ERROR HY000: Sequence 's_db.s2' has out of range value for options
create sequence s2 start with 1
minvalue 5
maxvalue 5
increment by 1
nocache
nocycle;
ERROR HY000: Sequence 's_db.s2' has out of range value for options
create sequence s2 start with 1
minvalue 5
maxvalue 4
increment by 1
nocache
nocycle;
ERROR HY000: Sequence 's_db.s2' has out of range value for options
create sequence s2 start with 1
minvalue 5
maxvalue 4
increment by 0
nocache
nocycle;
ERROR HY000: Sequence 's_db.s2' has out of range value for options
###########################################
global read lock prevent query sequence
###########################################
connection master;
use s_db;
create sequence s_db.s1;
flush table with read lock;
select next value for s1;
ERROR HY000: Can't execute the query because you have a conflicting read lock
unlock tables;
drop sequence s_db.s1;
###########################################
query cache test
###########################################
connection master;
use s_db;
show global variables like 'query_cache_type';
Variable_name Value
query_cache_type ON
show status like 'Qcache_hits';
Variable_name Value
Qcache_hits 0
show status like 'Qcache_inserts';
Variable_name Value
Qcache_inserts 0
###########################################
priv test
###########################################
connection m_normal_1;
create sequence s_db.s1;
select * from s_db.s1;
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
select next value for s_db.s1;
next value for s_db.s1
1
select * from s_db.s1;
next_not_cached_value minimum_value maximum_value start_value increment cache_size cycle_option cycle_count
1001 1 9223372036854775806 1 1 1000 0 0
create sequence s_db.s2;
drop sequence s_db.s2;
connection m_normal_2;
select next value for s_db.s1;
ERROR 42000: SELECT, INSERT command denied to user 'normal_2'@'localhost' for table `s_db`.`s1`
create sequence s_db.s2;
ERROR 42000: CREATE command denied to user 'normal_2'@'localhost' for table `s_db`.`s2`
connection m_normal_1;
drop sequence s_db.s1;
###########################################
run out sequence value
###########################################
connection m_normal_1;
use s_db;
create sequence s_t start with 1 cache 2 maxvalue 5;
create table t(id int);
insert into t values(1111);
insert into t select next value for s_t;
insert into t select next value for s_t;
insert into t select next value for s_t;
insert into t select next value for s_t;
insert into t select next value for s_t;
insert into t select next value for s_t;
ERROR HY000: Sequence 's_db.s_t' has run out
insert into t select next value for s_t;
ERROR HY000: Sequence 's_db.s_t' has run out
commit;
select * from t;
id
1111
1
2
3
4
5
connection master;
connection slave;
connection s_normal_3;
use s_db;
select * from t;
id
1111
1
2
3
4
5
connection m_normal_1;
use s_db;
drop sequence s_t;
drop table t;
###########################################
read_only prevent query sequence
###########################################
connection m_normal_1;
create sequence s_db.s1;
show global variables like 'read_only';
Variable_name Value
read_only OFF
select next value for s_db.s1;
next value for s_db.s1
1
connection master;
connection slave;
connection s_normal_3;
show global variables like 'read_only';
Variable_name Value
read_only ON
select next value for s_db.s1;
ERROR HY000: The MariaDB server is running with the --read-only option so it cannot execute this statement
connection m_normal_1;
drop sequence s_db.s1;
###########################################
update based table
###########################################
connection m_normal_1;
use s_db;
create sequence s_t start with 1 minvalue 1 maxvalue 20 increment by 1 cache 5 cycle;
connection master;
connection slave;
connection s_normal_3;
use s_db;
select * from s_t;
next_not_cached_value minimum_value maximum_value start_value increment cache_size cycle_option cycle_count
1 1 20 1 1 5 1 0
connection m_normal_1;
select next value for s_t;
next value for s_t
1
select * from s_t;
next_not_cached_value minimum_value maximum_value start_value increment cache_size cycle_option cycle_count
6 1 20 1 1 5 1 0
connection master;
connection slave;
connection s_normal_3;
select * from s_t;
next_not_cached_value minimum_value maximum_value start_value increment cache_size cycle_option cycle_count
6 1 20 1 1 5 1 0
------------------------------------------
master update nextval;
------------------------------------------
connection m_normal_1;
select next value for s_t;
next value for s_t
2
update s_t set next_not_cached_value= 11;
ERROR HY000: Storage engine SEQUENCE of the table `s_db`.`s_t` doesn't have this option
alter sequence s_t restart=11;
commit;
select * from s_t;
next_not_cached_value minimum_value maximum_value start_value increment cache_size cycle_option cycle_count
11 1 20 1 1 5 1 0
connection master;
connection slave;
------------------------------------------
show slave nextval;
------------------------------------------
connection s_normal_3;
select * from s_t;
next_not_cached_value minimum_value maximum_value start_value increment cache_size cycle_option cycle_count
11 1 20 1 1 5 1 0
connection m_normal_1;
select next value for s_t;
next value for s_t
11
select * from s_t;
next_not_cached_value minimum_value maximum_value start_value increment cache_size cycle_option cycle_count
16 1 20 1 1 5 1 0
connection master;
connection slave;
connection s_normal_3;
select * from s_t;
next_not_cached_value minimum_value maximum_value start_value increment cache_size cycle_option cycle_count
16 1 20 1 1 5 1 0
------------------------------------------
update into invalid sequence
------------------------------------------
connection m_normal_1;
select * from s_t;
next_not_cached_value minimum_value maximum_value start_value increment cache_size cycle_option cycle_count
16 1 20 1 1 5 1 0
update s_t set next_not_cached_value= 11,start_value=10, minimum_value=11;
ERROR HY000: Storage engine SEQUENCE of the table `s_db`.`s_t` doesn't have this option
ALTER SEQUENCE s_t restart with 11 start=10 minvalue=11;
ERROR HY000: Sequence 's_db.s_t' has out of range value for options
commit;
create table t_1(id int);
insert into t_1 value(1111);
select next value for s_t;
next value for s_t
12
insert into t_1 select next value for s_t;
commit;
select * from t_1;
id
1111
13
------------------------------------------
delete sequence row
------------------------------------------
connection m_normal_1;
delete from s_t;
ERROR HY000: Storage engine SEQUENCE of the table `s_db`.`s_t` doesn't have this option
commit;
select next value for s_t;
next value for s_t
14
connection m_normal_1;
drop sequence s_t;
drop table t_1;
###########################################
test transaction context (innodb)
###########################################
------------------------------------------
transaction table and sequence
normal transaction commit
------------------------------------------
connection m_normal_1;
use s_db;
create sequence s_1 cache 5;
create table t_1(id int)engine=innodb;
begin;
insert into t_1 values(1111);
insert into t_1 select next value for s_1;
insert into t_1 select next value for s_1;
insert into t_1 values(2222);
commit;
select * from t_1;
id
1111
1
2
2222
connection master;
connection slave;
connection s_normal_3;
use s_db;
select * from t_1;
id
1111
1
2
2222
------------------------------------------
normal transaction rollback
------------------------------------------
connection m_normal_1;
begin;
insert into t_1 values(3333);
insert into t_1 select next value for s_1;
insert into t_1 select next value for s_1;
insert into t_1 select next value for s_1;
insert into t_1 select next value for s_1;
insert into t_1 select next value for s_1;
insert into t_1 select next value for s_1;
insert into t_1 select next value for s_1;
insert into t_1 select next value for s_1;
select * from t_1;
id
1111
1
2
2222
3333
3
4
5
6
7
8
9
10
rollback;
select * from t_1;
id
1111
1
2
2222
select next value for s_1;
next value for s_1
11
connection master;
connection slave;
connection s_normal_3;
use s_db;
select * from t_1;
id
1111
1
2
2222
connection m_normal_1;
use s_db;
drop sequence s_1;
drop table t_1;
###########################################
test transaction context (myisam)
###########################################
------------------------------------------
transaction table and sequence
normal transaction commit
------------------------------------------
connection m_normal_1;
use s_db;
create sequence s_1 cache 5;
create table t_1(id int)engine=myisam;
begin;
insert into t_1 values(1111);
insert into t_1 select next value for s_1;
insert into t_1 select next value for s_1;
insert into t_1 values(2222);
commit;
select * from t_1;
id
1111
1
2
2222
connection master;
connection slave;
connection s_normal_3;
use s_db;
select * from t_1;
id
1111
1
2
2222
------------------------------------------
normal transaction rollback
------------------------------------------
connection m_normal_1;
begin;
insert into t_1 values(3333);
insert into t_1 select next value for s_1;
insert into t_1 select next value for s_1;
insert into t_1 select next value for s_1;
insert into t_1 select next value for s_1;
insert into t_1 select next value for s_1;
insert into t_1 select next value for s_1;
insert into t_1 select next value for s_1;
insert into t_1 select next value for s_1;
select * from t_1;
id
1111
1
2
2222
3333
3
4
5
6
7
8
9
10
rollback;
Warnings:
Warning 1196 Some non-transactional changed tables couldn't be rolled back
select * from t_1;
id
1111
1
2
2222
3333
3
4
5
6
7
8
9
10
select next value for s_1;
next value for s_1
11
connection master;
connection slave;
connection s_normal_3;
use s_db;
select * from t_1;
id
1111
1
2
2222
3333
3
4
5
6
7
8
9
10
connection m_normal_1;
use s_db;
drop sequence s_1;
drop table t_1;
###########################################
close binlog
###########################################
connection m_normal_1;
use s_db;
create sequence s1 cache 2;
select next value for s1;
next value for s1
1
select next value for s1;
next value for s1
2
select next value for s1;
next value for s1
3
select next value for s1;
next value for s1
4
commit;
select * from s1;
next_not_cached_value minimum_value maximum_value start_value increment cache_size cycle_option cycle_count
5 1 9223372036854775806 1 1 2 0 0
connection master;
connection slave;
connection slave;
use s_db;
select * from s1;
next_not_cached_value minimum_value maximum_value start_value increment cache_size cycle_option cycle_count
5 1 9223372036854775806 1 1 2 0 0
------------------------------------------
close session binlog.
------------------------------------------
connection master;
set session sql_log_bin=off;
select next value for s1;
next value for s1
5
select next value for s1;
next value for s1
6
select next value for s1;
next value for s1
7
select next value for s1;
next value for s1
8
set session sql_log_bin=on;
select * from s1;
next_not_cached_value minimum_value maximum_value start_value increment cache_size cycle_option cycle_count
9 1 9223372036854775806 1 1 2 0 0
connection master;
connection slave;
connection slave;
use s_db;
select * from s1;
next_not_cached_value minimum_value maximum_value start_value increment cache_size cycle_option cycle_count
5 1 9223372036854775806 1 1 2 0 0
connection m_normal_1;
use s_db;
drop sequence s1;
###########################################
statement binlog
###########################################
------------------------------------------
set binlog_format=statement
------------------------------------------
connection master;
set session binlog_format=statement;
select @@session.binlog_format;
@@session.binlog_format
STATEMENT
create sequence s1 cache 2;
select next value for s1;
ERROR HY000: Cannot execute statement: impossible to write to binary log since BINLOG_FORMAT = STATEMENT and at least one table uses a storage engine limited to row-based logging.
set session binlog_format=row;
select next value for s1;
next value for s1
1
select * from s1;
next_not_cached_value minimum_value maximum_value start_value increment cache_size cycle_option cycle_count
3 1 9223372036854775806 1 1 2 0 0
connection master;
connection slave;
use s_db;
select * from s1;
next_not_cached_value minimum_value maximum_value start_value increment cache_size cycle_option cycle_count
3 1 9223372036854775806 1 1 2 0 0
connection m_normal_1;
use s_db;
drop sequence s1;
###########################################
test savepoint
###########################################
connection master;
set session binlog_format=row;
create sequence s1 cache 2;
create table t1(id int)engine=innodb;
begin;
insert into t1 values(1111);
savepoint sp1;
insert into t1 select next value for s1;
insert into t1 select next value for s1;
insert into t1 select next value for s1;
insert into t1 values(2222);
select * from t1;
id
1111
1
2
3
2222
rollback to sp1;
select * from t1;
id
1111
select next value for s1;
next value for s1
4
commit;
drop sequence s1;
drop table t1;
###########################################
test proc
###########################################
connection m_normal_1;
use s_db;
create table t(id int)engine=innodb;
create procedure p1()
begin
create sequence s1 cache 2;
end//
create procedure p2()
begin
insert into t select next value for s1;
commit;
end//
call p1();
call p2();
call p2();
call p2();
call p2();
select * from t;
id
1
2
3
4
connection master;
connection slave;
use s_db;
select * from t;
id
1
2
3
4
connection m_normal_1;
drop table t;
drop sequence s1;
drop procedure p1;
drop procedure p2;
###########################################
test trigger
###########################################
connection m_normal_1;
use s_db;
create sequence s1 cache 2;
create table t1(id int)engine=innodb;
create table t2(id int)engine=innodb;
CREATE TRIGGER tri_1
before INSERT ON t2 FOR EACH ROW
BEGIN
INSERT INTO t1 select next value for s1;
END//
begin;
insert into t2 values(1111);
insert into t2 values(1111);
insert into t2 values(1111);
insert into t2 values(1111);
select * from t2;
id
1111
1111
1111
1111
select * from t1;
id
1
2
3
4
rollback;
select * from t2;
id
select * from t1;
id
select next value for s1;
next value for s1
5
drop trigger tri_1;
drop table t1;
drop table t2;
drop sequence s1;
###########################################
test function
###########################################
connection m_normal_1;
use s_db;
create sequence s1 cache 2;
create table t1(id int)engine=innodb;
CREATE function f1() returns int
BEGIN
INSERT INTO t1 select next value for s1;
return (1);
END//
begin;
select f1();
f1()
1
select f1();
f1()
1
select f1();
f1()
1
select f1();
f1()
1
select * from t1;
id
1
2
3
4
rollback;
select * from t1;
id
select next value for s1;
next value for s1
5
drop function f1;
drop table t1;
drop sequence s1;
##############
Test GRANT
##############
connection m_normal_2;
create table t1 (a int);
create sequence s1;
select next value for s1;
next value for s1
1
insert into t1 values (1);
connection m_normal_3;
select * from t1;
a
1
select * from s1;
next_not_cached_value minimum_value maximum_value start_value increment cache_size cycle_option cycle_count
1001 1 9223372036854775806 1 1 1000 0 0
select previous value for s1;
previous value for s1
NULL
insert into t1 values (2);
ERROR 42000: INSERT command denied to user 'normal_5'@'localhost' for table `test`.`t1`
select next value for s1;
ERROR 42000: INSERT command denied to user 'normal_5'@'localhost' for table `test`.`s1`
do setval(s1,1000,0);
ERROR 42000: INSERT command denied to user 'normal_5'@'localhost' for table `test`.`s1`
connection master;
grant insert on test.* to normal_5@'%' identified by 'pass';
disconnect m_normal_3;
connect m_normal_3, 127.0.0.1, normal_5, pass, test, $MASTER_MYPORT;
insert into t1 values (2);
select t1.*, (next value for s1) from t1;
a (next value for s1)
1 2
2 3
do setval(s1,10000,0);
select * from s1;
next_not_cached_value minimum_value maximum_value start_value increment cache_size cycle_option cycle_count
10000 1 9223372036854775806 1 1 1000 0 0
connection m_normal_2;
drop table t1;
drop sequence s1;
#
# Cleanup
#
connection master;
use s_db;
drop database s_db;
drop user normal_1@'%';
drop user normal_2@'%';
drop user normal_3@'%';
drop user normal_4@'%';
drop user normal_5@'%';
include/rpl_end.inc
Reference in a new issue View git blame Copy permalink