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.
123 lines
3.1 KiB
Plaintext
123 lines
3.1 KiB
Plaintext
#
|
|
# 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;
|
|
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;
|
|
|
|
--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 #
|