mirror of
https://github.com/MariaDB/server.git
synced 2025-07-05 19:08:12 +02:00
f99586668a
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
103 lines
3.9 KiB
Text
103 lines
3.9 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'@'%';
|
|
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
|
|
#
|