mirror of
https://github.com/MariaDB/server.git
synced 2025-08-14 22:41:34 +02: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.
440 lines
14 KiB
Text
440 lines
14 KiB
Text
-- source include/not_embedded.inc
|
|
|
|
#
|
|
# MDEV-6625 SHOW GRANTS for current_user_name@wrong_host_name
|
|
#
|
|
--error ER_NONEXISTING_GRANT
|
|
SHOW GRANTS FOR root@invalid_host;
|
|
|
|
#
|
|
# MDEV-9580 SHOW GRANTS FOR <current_user> fails
|
|
#
|
|
create user test;
|
|
create user foo;
|
|
create role foo;
|
|
grant foo to test;
|
|
--connect (conn_1, localhost, test,,)
|
|
set role foo;
|
|
show grants for test; # user
|
|
show grants for foo; # role
|
|
--error ER_DBACCESS_DENIED_ERROR
|
|
show grants for foo@'%'; # user
|
|
--connection default
|
|
--disconnect conn_1
|
|
drop user test, foo;
|
|
drop role foo;
|
|
|
|
#
|
|
# MDEV-17975 Assertion `! is_set()' or `!is_set() || (m_status == DA_OK_BULK && is_bulk_op())' failed upon REVOKE under LOCK TABLE
|
|
#
|
|
CREATE TABLE t1 (a INT);
|
|
LOCK TABLE t1 WRITE;
|
|
--error ER_TABLE_NOT_LOCKED
|
|
REVOKE EXECUTE ON PROCEDURE sp FROM u;
|
|
--error ER_TABLE_NOT_LOCKED
|
|
REVOKE PROCESS ON *.* FROM u;
|
|
DROP TABLE t1;
|
|
|
|
#
|
|
# MDEV-23010 UPDATE privilege at Database and Table level fail to update with SELECT command denied to user
|
|
#
|
|
create database mysqltest1;
|
|
use mysqltest1;
|
|
create table t1(id int);
|
|
insert t1 values(2);
|
|
create user u1@localhost;
|
|
grant select on mysqltest1.t1 to u1@localhost;
|
|
grant update on mysqltest1.* to u1@localhost;
|
|
connect u1, localhost, u1;
|
|
update mysqltest1.t1 set id=1 where id=2;
|
|
connection default;
|
|
disconnect u1;
|
|
drop user u1@localhost;
|
|
drop database mysqltest1;
|
|
use test;
|
|
|
|
--echo #
|
|
--echo # MDEV-22313: Neither SHOW CREATE USER nor SHOW GRANTS prints a user's default role
|
|
--echo #
|
|
CREATE ROLE test_role;
|
|
CREATE USER test_user;
|
|
GRANT test_role TO test_user;
|
|
SET DEFAULT ROLE test_role FOR test_user;
|
|
SHOW GRANTS FOR test_user;
|
|
SET DEFAULT ROLE NONE for test_user;
|
|
SHOW GRANTS FOR test_user;
|
|
connect test_user, localhost, test_user;
|
|
SET ROLE test_role;
|
|
SET DEFAULT ROLE test_role;
|
|
SHOW GRANTS;
|
|
SET DEFAULT ROLE NONE;
|
|
SHOW GRANTS;
|
|
disconnect test_user;
|
|
connection default;
|
|
DROP USER test_user;
|
|
DROP ROLE test_role;
|
|
|
|
#
|
|
# End of 10.1 tests
|
|
#
|
|
|
|
--echo #
|
|
--echo # MDEV-20076: SHOW GRANTS does not quote role names properly
|
|
--echo #
|
|
|
|
create role 'role1';
|
|
create role 'fetch';
|
|
create role 'role-1';
|
|
create role 'rock\'n\'roll';
|
|
create user 'user1'@'localhost';
|
|
create user 'fetch'@'localhost';
|
|
create user 'user-1'@'localhost';
|
|
create user 'O\'Brien'@'localhost';
|
|
grant select on mysql.user to role1;
|
|
grant select on mysql.user to 'fetch';
|
|
grant select on mysql.user to 'role-1';
|
|
grant select on mysql.user to 'rock\'n\'roll';
|
|
GRANT 'role1' TO 'user1'@'localhost';
|
|
GRANT 'fetch' TO 'fetch'@'localhost';
|
|
GRANT 'role-1' TO 'user-1'@'localhost';
|
|
GRANT 'rock\'n\'roll' TO 'O\'Brien'@'localhost';
|
|
show grants for 'role1';
|
|
show grants for 'fetch';
|
|
show grants for 'role-1';
|
|
show grants for 'rock\'n\'roll';
|
|
show grants for 'user1'@'localhost';
|
|
show grants for 'fetch'@'localhost';
|
|
show grants for 'user-1'@'localhost';
|
|
show grants for 'O\'Brien'@'localhost';
|
|
set @save_sql_quote_show_create= @@sql_quote_show_create;
|
|
set @@sql_quote_show_create= OFF;
|
|
show grants for 'role1';
|
|
show grants for 'fetch';
|
|
show grants for 'role-1';
|
|
show grants for 'rock\'n\'roll';
|
|
show grants for 'user1'@'localhost';
|
|
show grants for 'fetch'@'localhost';
|
|
show grants for 'user-1'@'localhost';
|
|
show grants for 'O\'Brien'@'localhost';
|
|
set @@sql_quote_show_create= @save_sql_quote_show_create;
|
|
drop role 'role1';
|
|
drop role 'fetch';
|
|
drop role 'role-1';
|
|
drop role 'rock\'n\'roll';
|
|
drop user 'user1'@'localhost';
|
|
drop user 'fetch'@'localhost';
|
|
drop user 'user-1'@'localhost';
|
|
drop user 'O\'Brien'@'localhost';
|
|
|
|
--echo #
|
|
--echo # MDEV-26080 SHOW GRANTS does not quote role names properly for DEFAULT ROLE
|
|
--echo #
|
|
|
|
CREATE USER 'test-user';
|
|
CREATE ROLE `r``o'l"e`;
|
|
select user as User from mysql.user where is_role='Y';
|
|
GRANT `r``o'l"e` TO 'test-user';
|
|
SET DEFAULT ROLE `r``o'l"e` FOR 'test-user';
|
|
# it is expected that quotes won't be shown correctly
|
|
SHOW GRANTS FOR 'test-user';
|
|
DROP ROLE `r``o'l"e`;
|
|
DROP USER 'test-user';
|
|
|
|
--echo #
|
|
--echo # MDEV-28548: ER_TABLEACCESS_DENIED_ERROR is missing information about DB
|
|
--echo #
|
|
|
|
create database db1;
|
|
create user foo@localhost;
|
|
grant create on db1.* to foo@localhost;
|
|
|
|
--connect (con1,localhost,foo,,db1)
|
|
create table t(t int);
|
|
--error ER_TABLEACCESS_DENIED_ERROR
|
|
show columns in t;
|
|
--error ER_TABLEACCESS_DENIED_ERROR
|
|
show columns in db1.t;
|
|
# CREATE_VIEW_ACL needed
|
|
--error ER_TABLEACCESS_DENIED_ERROR
|
|
create view t_v as select * from t;
|
|
# show create view needs to have SELECT_ACL and SHOW_VIEW_ACL
|
|
--error ER_TABLEACCESS_DENIED_ERROR
|
|
show create view t_v;
|
|
|
|
create table t2(id int primary key, b int);
|
|
|
|
# Reference non existing DB with wrong DB name
|
|
--error ER_WRONG_DB_NAME
|
|
create table t3(a int, b int, CONSTRAINT `fk_db2_db1_t1`
|
|
FOREIGN KEY (a)
|
|
REFERENCES `db1 `.t1 (a)
|
|
ON DELETE CASCADE
|
|
ON UPDATE RESTRICT);
|
|
|
|
# Reference non-existing DB (with qualified DB name)
|
|
--error ER_TABLEACCESS_DENIED_ERROR
|
|
create table t3(a int, b int, CONSTRAINT `fk_db2_db3_t1`
|
|
FOREIGN KEY (a)
|
|
REFERENCES db3.t1 (a)
|
|
ON DELETE CASCADE
|
|
ON UPDATE RESTRICT);
|
|
|
|
# Reference DB (with not qualified DB name)
|
|
--error ER_TABLEACCESS_DENIED_ERROR
|
|
create table t1(a int, b int, CONSTRAINT `fk_db2_db3_t1`
|
|
FOREIGN KEY (a)
|
|
REFERENCES t2 (id)
|
|
ON DELETE CASCADE
|
|
ON UPDATE RESTRICT);
|
|
|
|
--connection default
|
|
--disconnect con1
|
|
# Add CREATE_VIEW_ACL and SELECT_ACL
|
|
grant create view, select on db1.* to foo@localhost;
|
|
|
|
--connect (con1,localhost,foo,,db1)
|
|
create view t_v as select * from t;
|
|
show grants;
|
|
--error ER_TABLEACCESS_DENIED_ERROR
|
|
show create view t_v;
|
|
|
|
--connection default
|
|
--disconnect con1
|
|
# Add SHOW_VIEW_ACL
|
|
grant show view on db1.* to foo@localhost;
|
|
|
|
--connect (con1,localhost,foo,,db1)
|
|
show grants;
|
|
show create view t_v;
|
|
|
|
--connection default
|
|
--disconnect con1
|
|
drop database db1;
|
|
drop user foo@localhost;
|
|
--echo #
|
|
--echo # MDEV-28455: CREATE TEMPORARY TABLES privilege
|
|
--echo # is insufficient for SHOW COLUMNS
|
|
--echo #
|
|
|
|
create database db;
|
|
create user foo@localhost;
|
|
create user bar@localhost;
|
|
create user buz@localhost;
|
|
grant create temporary tables on db.* to foo@localhost;
|
|
grant create temporary tables on db.* to bar@localhost;
|
|
|
|
--connect (con1,localhost,foo,,db)
|
|
create temporary table tmp (a int, key(a));
|
|
show tables;
|
|
show full tables;
|
|
--replace_column 7 X 8 X 9 X 10 X 11 X 12 X 13 X 14 X 19 X
|
|
show table status;
|
|
show index in tmp;
|
|
show columns in tmp;
|
|
show full columns in tmp;
|
|
--echo # we don't expect to show temporary tables in information_schema.columns
|
|
select * from information_schema.columns where table_schema='db';
|
|
--disconnect con1
|
|
|
|
--connect (con1,localhost,bar,,db)
|
|
# User doesn't have `select` privilege on table
|
|
--error ER_TABLEACCESS_DENIED_ERROR
|
|
show full columns in tmp;
|
|
|
|
--disconnect con1
|
|
|
|
--connection default
|
|
grant select on db.* to bar@localhost;
|
|
|
|
--connect (con1,localhost,bar,,db)
|
|
# Table doesn't exist for this session
|
|
show grants for current_user;
|
|
--error ER_NO_SUCH_TABLE
|
|
show full columns in tmp;
|
|
--disconnect con1
|
|
|
|
--connect (con1,localhost,buz,,)
|
|
--error ER_TABLEACCESS_DENIED_ERROR
|
|
show columns in db.tmp;
|
|
--disconnect con1
|
|
|
|
--connection default
|
|
# Cleanup
|
|
drop database db;
|
|
drop user foo@localhost;
|
|
drop user bar@localhost;
|
|
drop user buz@localhost;
|
|
|
|
CREATE USER foo;
|
|
CREATE DATABASE db;
|
|
CREATE TABLE db.test_getcolpriv(col1 INT, col2 INT);
|
|
|
|
GRANT SELECT (col1,col2) ON db.test_getcolpriv TO foo;
|
|
GRANT INSERT (col1) ON db.test_getcolpriv TO foo;
|
|
|
|
SHOW GRANTS FOR foo;
|
|
REVOKE SELECT (col1,col2) ON db.test_getcolpriv FROM foo;
|
|
SHOW GRANTS FOR foo;
|
|
REVOKE INSERT (col1) ON db.test_getcolpriv FROM foo;
|
|
|
|
SHOW GRANTS FOR foo;
|
|
FLUSH PRIVILEGES;
|
|
SHOW GRANTS FOR foo;
|
|
|
|
DROP USER foo;
|
|
DROP DATABASE db;
|
|
|
|
--echo # End of 10.3 tests
|
|
|
|
#
|
|
# MDEV-12321 authentication plugin: SET PASSWORD support
|
|
#
|
|
error ER_PASSWD_LENGTH;
|
|
create user u1@h identified with 'mysql_native_password' using 'pwd';
|
|
create user u1@h identified with 'mysql_native_password' using password('pwd');
|
|
let p=`select password('pwd')`;
|
|
eval create user u2@h identified with 'mysql_native_password' using '$p';
|
|
create user u3@h identified with 'mysql_native_password';
|
|
error ER_PASSWD_LENGTH;
|
|
set password for u3@h = 'pwd';
|
|
set password for u3@h = password('pwd');
|
|
create user u4@h identified with 'mysql_native_password';
|
|
eval set password for u4@h = '$p';
|
|
error ER_PASSWD_LENGTH;
|
|
create user u5@h identified with 'mysql_old_password' using 'pwd';
|
|
create user u5@h identified with 'mysql_old_password' using password('pwd');
|
|
let p=`select old_password('pwd')`;
|
|
eval create user u6@h identified with 'mysql_old_password' using '$p';
|
|
create user u7@h identified with 'mysql_old_password';
|
|
error ER_PASSWD_LENGTH;
|
|
set password for u7@h = 'pwd';
|
|
set password for u7@h = old_password('pwd');
|
|
create user u8@h identified with 'mysql_old_password';
|
|
eval set password for u8@h = '$p';
|
|
sorted_result;
|
|
select user as User,host as Host,plugin,authentication_string from mysql.user where host='h';
|
|
# test with invalid entries
|
|
update mysql.global_priv set priv=json_set(priv, '$.authentication_string', 'bad') where user='u1';
|
|
update mysql.global_priv set priv=json_set(priv, '$.authentication_string', 'bad') where user='u5';
|
|
update mysql.global_priv set priv=json_set(priv, '$.plugin', 'nonexistent') where user='u8';
|
|
flush privileges;
|
|
show create user u1@h;
|
|
show create user u2@h;
|
|
show create user u3@h;
|
|
show create user u4@h;
|
|
show create user u5@h;
|
|
show create user u6@h;
|
|
show create user u7@h;
|
|
show create user u8@h;
|
|
grant select on *.* to u1@h;
|
|
grant select on *.* to u2@h;
|
|
grant select on *.* to u3@h;
|
|
grant select on *.* to u4@h;
|
|
grant select on *.* to u5@h;
|
|
grant select on *.* to u6@h;
|
|
grant select on *.* to u7@h;
|
|
grant select on *.* to u8@h;
|
|
select user as User,select_priv as Select_priv,plugin,authentication_string from mysql.user where user like 'u_';
|
|
|
|
# but they still can be dropped
|
|
drop user u1@h, u2@h, u3@h, u4@h, u5@h, u6@h, u7@h, u8@h;
|
|
|
|
#
|
|
# MDEV-14735 better matching order for grants
|
|
# MDEV-14732 mysql.db privileges evaluated on order of grants rather than hierarchically
|
|
# MDEV-8269 Correct fix for Bug #20181776 :- ACCESS CONTROL DOESN'T MATCH MOST SPECIFIC HOST WHEN IT CONTAINS WILDCARD
|
|
#
|
|
create database mysqltest_1;
|
|
create user twg@'%' identified by 'test';
|
|
create table mysqltest_1.t1(id int);
|
|
|
|
# MDEV-14732 test case
|
|
grant create, drop on `mysqltest_1%`.* to twg@'%';
|
|
grant all privileges on `mysqltest_1`.* to twg@'%';
|
|
connect conn1,localhost,twg,test,mysqltest_1;
|
|
insert into t1 values(1);
|
|
disconnect conn1;
|
|
connection default;
|
|
|
|
# prefix%suffix
|
|
revoke all privileges, grant option from twg@'%';
|
|
grant create, drop on `mysqlt%`.* to twg@'%';
|
|
grant all privileges on `mysqlt%1`.* to twg@'%';
|
|
connect conn1,localhost,twg,test,mysqltest_1;
|
|
insert into t1 values(1);
|
|
disconnect conn1;
|
|
connection default;
|
|
|
|
# more specific can even have a shorter prefix
|
|
revoke all privileges, grant option from twg@'%';
|
|
grant create, drop on `mysqlt%`.* to twg@'%';
|
|
grant all privileges on `%mysqltest_1`.* to twg@'%';
|
|
connect conn1,localhost,twg,test,mysqltest_1;
|
|
insert into t1 values(1);
|
|
disconnect conn1;
|
|
connection default;
|
|
|
|
drop database mysqltest_1;
|
|
drop user twg@'%';
|
|
|
|
#
|
|
# test the empty db case
|
|
#
|
|
insert mysql.tables_priv (host,db,user,table_name,grantor,table_priv) values ('localhost','','otto','t1','root@localhost','select');
|
|
flush privileges;
|
|
delete from mysql.tables_priv where db='';
|
|
|
|
#
|
|
# MDEV-21560 Assertion `grant_table || grant_table_role' failed in check_grant_all_columns
|
|
#
|
|
create database db;
|
|
create table db.t1 (a int);
|
|
insert into db.t1 values (1);
|
|
create user foo;
|
|
grant delete on db.* to foo;
|
|
--connect (con1,localhost,foo,,)
|
|
show create table db.t1;
|
|
--error ER_COLUMNACCESS_DENIED_ERROR
|
|
delete from db.t1 returning *;
|
|
--disconnect con1
|
|
--connection default
|
|
drop database db;
|
|
drop user foo;
|
|
|
|
#
|
|
# MDEV-23009 SIGSEGV in get_field from acl_load (on optimized builds)
|
|
#
|
|
call mtr.add_suppression('mysql.host table is damaged');
|
|
create table mysql.host (c1 int);
|
|
insert mysql.host values (1);
|
|
--error ER_UNKNOWN_ERROR
|
|
flush privileges;
|
|
drop table mysql.host;
|
|
|
|
--echo #
|
|
--echo # MDEV-30826 Invalid data on mysql.host segfaults the server after an upgrade to 10.4
|
|
--echo #
|
|
|
|
# from mysql_system_tables.sql in 10.3:
|
|
create table mysql.host (host char(60) binary default '' not null, db char(64) binary default '' not null, select_priv enum('n','y') collate utf8_general_ci default 'n' not null, insert_priv enum('n','y') collate utf8_general_ci default 'n' not null, update_priv enum('n','y') collate utf8_general_ci default 'n' not null, delete_priv enum('n','y') collate utf8_general_ci default 'n' not null, create_priv enum('n','y') collate utf8_general_ci default 'n' not null, drop_priv enum('n','y') collate utf8_general_ci default 'n' not null, grant_priv enum('n','y') collate utf8_general_ci default 'n' not null, references_priv enum('n','y') collate utf8_general_ci default 'n' not null, index_priv enum('n','y') collate utf8_general_ci default 'n' not null, alter_priv enum('n','y') collate utf8_general_ci default 'n' not null, create_tmp_table_priv enum('n','y') collate utf8_general_ci default 'n' not null, lock_tables_priv enum('n','y') collate utf8_general_ci default 'n' not null, create_view_priv enum('n','y') collate utf8_general_ci default 'n' not null, show_view_priv enum('n','y') collate utf8_general_ci default 'n' not null, create_routine_priv enum('n','y') collate utf8_general_ci default 'n' not null, alter_routine_priv enum('n','y') collate utf8_general_ci default 'n' not null, execute_priv enum('n','y') collate utf8_general_ci default 'n' not null, trigger_priv enum('n','y') collate utf8_general_ci default 'n' not null, primary key /*host*/ (host,db)) engine=myisam character set utf8 collate utf8_bin comment='host privileges; merged with database privileges';
|
|
insert mysql.host values('10.5.0.0/255.255.0.0','','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','N');
|
|
flush privileges;
|
|
drop table mysql.host;
|
|
|
|
--echo # End of 10.4 tests
|
|
|
|
--echo #
|
|
--echo # MDEV-18151: Skipped error returning for GRANT/SET PASSWORD
|
|
--echo #
|
|
|
|
CREATE USER foo@localhost;
|
|
--error ER_PLUGIN_IS_NOT_LOADED
|
|
GRANT FILE ON *.* TO foo@localhost IDENTIFIED VIA not_installed_plugin;
|
|
|
|
# Cleanup
|
|
DROP USER foo@localhost;
|
|
|
|
--error ER_PLUGIN_IS_NOT_LOADED
|
|
CREATE USER foo@localhost IDENTIFIED VIA not_installed_plugin;
|
|
|
|
--echo # End of 10.5 tests
|