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
117 lines
3 KiB
Text
117 lines
3 KiB
Text
#
|
|
# Test some grants with sequences
|
|
# Note that replication.test also does some grant testing
|
|
#
|
|
|
|
# Grant tests not performed with embedded server
|
|
-- source include/not_embedded.inc
|
|
|
|
|
|
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);
|
|
|
|
--disable_ps2_protocol
|
|
connection normal;
|
|
create sequence s1;
|
|
select next value for s1;
|
|
alter sequence s1 restart= 11;
|
|
select * from s1;
|
|
|
|
connection read_only;
|
|
--error ER_TABLEACCESS_DENIED_ERROR
|
|
select next value for s1;
|
|
--error ER_TABLEACCESS_DENIED_ERROR
|
|
alter sequence s1 restart= 11;
|
|
select * from s1;
|
|
|
|
connection read_write;
|
|
select next value for s1;
|
|
--error ER_TABLEACCESS_DENIED_ERROR
|
|
alter sequence s1 restart= 11;
|
|
select * from s1;
|
|
|
|
connection alter;
|
|
select next value for s1;
|
|
alter sequence s1 restart= 11;
|
|
select * from s1;
|
|
|
|
connection only_alter;
|
|
--error ER_TABLEACCESS_DENIED_ERROR
|
|
select next value for s1;
|
|
alter sequence s1 restart= 11;
|
|
--error ER_TABLEACCESS_DENIED_ERROR
|
|
select * from s1;
|
|
--enable_ps2_protocol
|
|
|
|
#
|
|
# Cleanup
|
|
#
|
|
|
|
connection default;
|
|
drop user 'normal'@'%';
|
|
drop user 'read_only'@'%';
|
|
drop user 'read_write'@'%';
|
|
drop user 'alter'@'%';
|
|
drop user 'only_alter'@'%';
|
|
drop sequence s1;
|
|
|
|
--echo #
|
|
--echo # MDEV-36413 User without any privileges to a sequence can read from
|
|
--echo # it and modify it via column default
|
|
--echo #
|
|
|
|
create sequence s1;
|
|
create sequence s2;
|
|
select * from s2;
|
|
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)
|
|
--error ER_TABLEACCESS_DENIED_ERROR
|
|
select nextval(s2);
|
|
--error ER_TABLEACCESS_DENIED_ERROR
|
|
show create sequence 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;
|
|
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;
|
|
--error ER_TABLEACCESS_DENIED_ERROR
|
|
create table t1 (a int not null default(nextval(s2)));
|
|
--error ER_TABLEACCESS_DENIED_ERROR
|
|
create table t1 (a int not null default(nextval(s1)),
|
|
b int not null default(nextval(s2)));
|
|
--disconnect con1
|
|
--connection default
|
|
drop user u;
|
|
|
|
#
|
|
# Cleanup
|
|
#
|
|
|
|
drop database mysqltest_1;
|
|
|
|
--echo #
|
|
--echo # End of 10.11 tests
|
|
--echo #
|