diff options
Diffstat (limited to '')
46 files changed, 2178 insertions, 0 deletions
diff --git a/mysql-test/suite/plugins/t/audit_null.test b/mysql-test/suite/plugins/t/audit_null.test new file mode 100644 index 00000000..63f5f51e --- /dev/null +++ b/mysql-test/suite/plugins/t/audit_null.test @@ -0,0 +1,61 @@ + +--source include/not_embedded.inc + +if (!$ADT_NULL_SO) { + skip No NULL_AUDIT plugin; +} + +set @old_global_general_log=@@global.general_log; +set global general_log=OFF; + +create user testuser@localhost; +grant select on *.* to testuser@localhost; + +--disable_ps_protocol +install plugin audit_null soname 'adt_null'; + +select 1; +--error 1054 +select foobar; + +show status like 'audit_null%'; + +create procedure au1(x char(16)) select concat("test1", x); +call au1("-12"); + +show status like 'audit_null%'; + +create table t1 (a int); +insert t1 values (1), (2); +select * from t1; +rename table t1 to t2; +alter table t2 add column b int; + +create definer=testuser@localhost view v1 as select t2.a+1, t2_copy.a+2 from t2, t2 as t2_copy; +select * from v1; +drop view v1; + +# temp table generates no audit events +create temporary table t2 (a date); +insert t2 values ('2020-10-09'); +select * from t2; +alter table t2 add column b int; # MDEV-4565 ALTER on a temporary table generates an audit event +drop table t2; + +# internal temp table generates no audit events +explain select distinct * from t2; +select distinct * from t2; + +drop table t2; + +uninstall plugin audit_null; +--enable_ps_protocol + +drop procedure au1; +drop user testuser@localhost; +set global general_log=@old_global_general_log; + +let $MYSQLD_DATADIR= `SELECT @@datadir`; +--replace_regex /::1// /127.0.0.1// +cat_file $MYSQLD_DATADIR/audit_null_tables.log; + diff --git a/mysql-test/suite/plugins/t/audit_null_debug.test b/mysql-test/suite/plugins/t/audit_null_debug.test new file mode 100644 index 00000000..52c50f13 --- /dev/null +++ b/mysql-test/suite/plugins/t/audit_null_debug.test @@ -0,0 +1,36 @@ +--source include/have_debug.inc +--source include/not_embedded.inc + +if (!$ADT_NULL_SO) { + skip No NULL_AUDIT plugin; +} + +alter table mysql.plugin engine=myisam; + +set @old_dbug=@@debug_dbug; +call mtr.add_suppression("Index for table.*mysql.plugin.MYI"); +call mtr.add_suppression("Index for table 'plugin' is corrupt; try to repair it"); + +# +# MySQL BUG#14485479 - INSTALL AUDIT PLUGIN HANGS IF WE TRY TO DISABLE AND ENABLED DURING DDL OPERATION +# (a.k.a. audit event caused by the table access during audit plugin initialization) +# +SET debug_dbug='+d,myisam_pretend_crashed_table_on_usage'; +--replace_result \\ / +--error 126 +install plugin audit_null soname 'adt_null'; +SET debug_dbug=@old_dbug; + +install plugin audit_null soname 'adt_null'; +SET debug_dbug='+d,myisam_pretend_crashed_table_on_usage'; +--replace_result \\ / +--error 126 +uninstall plugin audit_null; +SET debug_dbug=@old_dbug; + +uninstall plugin audit_null; +--error ER_SP_DOES_NOT_EXIST +uninstall plugin audit_null; + +delete from mysql.plugin where name='audit_null'; +alter table mysql.plugin engine=aria; diff --git a/mysql-test/suite/plugins/t/auth_ed25519.test b/mysql-test/suite/plugins/t/auth_ed25519.test new file mode 100644 index 00000000..8e0bdd1d --- /dev/null +++ b/mysql-test/suite/plugins/t/auth_ed25519.test @@ -0,0 +1,57 @@ +# +# MDEV-12160 Modern alternative to the SHA1 authentication plugin +# +source include/not_embedded.inc; +if (!$AUTH_ED25519_SO) { + skip No auth_ed25519 plugin; +} + +replace_result dll so; +eval create function ed25519_password returns string soname "$AUTH_ED25519_SO"; +error ER_CANT_INITIALIZE_UDF; +select ed25519_password(); +error ER_CANT_INITIALIZE_UDF; +select ed25519_password(1); +error ER_CANT_INITIALIZE_UDF; +select ed25519_password("foo", "bar"); +error ER_CANT_INITIALIZE_UDF; +select ed25519_password("foo"); + +install soname 'auth_ed25519'; +select ed25519_password("foo"); +select ed25519_password("foobar"); +select ed25519_password("foo bar"); +select ed25519_password(NULL); + +replace_result dll so; +query_vertical select * from information_schema.plugins where plugin_name='ed25519'; +let $pwd=`select ed25519_password("secret")`; +eval create user test1@localhost identified via ed25519 using '$pwd'; +show grants for test1@localhost; +drop user test1@localhost; +--error ER_PASSWD_LENGTH +create user test1@localhost identified via ed25519 using 'foo'; +--error ER_PASSWD_LENGTH +create user test1@localhost identified via ed25519 using '>>>1234567890123456789012345678901234567890'; +create user test1@localhost identified via ed25519 using password('foo'); +show grants for test1@localhost; +select ed25519_password('foo'); +set password for test1@localhost = password('bar'); +show create user test1@localhost; +select ed25519_password('bar'); +eval set password for test1@localhost = '$pwd'; +show create user test1@localhost; + +replace_result $MASTER_MYPORT PORT $MASTER_MYSOCK SOCKET; +error ER_ACCESS_DENIED_ERROR; +connect con1, localhost, test1, public; +connect con1, localhost, test1, secret; +select current_user(); +disconnect con1; +connection default; + +drop user test1@localhost; +uninstall plugin ed25519; +error ER_CANT_INITIALIZE_UDF; +select ed25519_password("foo"); +drop function ed25519_password; diff --git a/mysql-test/suite/plugins/t/auth_v0100.test b/mysql-test/suite/plugins/t/auth_v0100.test new file mode 100644 index 00000000..5d0f6f40 --- /dev/null +++ b/mysql-test/suite/plugins/t/auth_v0100.test @@ -0,0 +1,28 @@ +--source include/not_embedded.inc + +if (!$AUTH_0X0100_SO) { + skip No auth_0x0100 plugin; +} + +install soname 'auth_0x0100'; + +select plugin_name, plugin_type_version from information_schema.plugins where +plugin_type='authentication' and plugin_library is not null order by plugin_name; + +create user tt identified via auth_0x0100; +create user zzzzzzzzzzzzzzzz; +grant select on test.* to zzzzzzzzzzzzzzzz; + +--replace_result $MASTER_MYSOCK MASTER_MYSOCK $MASTER_MYPORT MASTER_MYPORT +--error ER_ACCESS_DENIED_ERROR +connect (c0,localhost,tt); + +grant proxy on zzzzzzzzzzzzzzzz to tt; +connect (c1,localhost,tt); +connection c1; + +--query_vertical select user(), current_user(), @@external_user +connection default; +drop user tt; +drop user zzzzzzzzzzzzzzzz; +uninstall plugin auth_0x0100; diff --git a/mysql-test/suite/plugins/t/binlog-simple_plugin_check.test b/mysql-test/suite/plugins/t/binlog-simple_plugin_check.test new file mode 100644 index 00000000..773dafe8 --- /dev/null +++ b/mysql-test/suite/plugins/t/binlog-simple_plugin_check.test @@ -0,0 +1,31 @@ +--source include/not_embedded.inc +--source include/have_binlog_format_statement.inc + +if (!$SIMPLE_PASSWORD_CHECK_SO) { + skip No SIMPLE_PASSWORD_CHECK plugin; +} + +INSTALL SONAME "simple_password_check"; +SELECT PLUGIN_NAME FROM INFORMATION_SCHEMA.PLUGINS +WHERE PLUGIN_NAME='simple_password_check'; + + +--echo # +--echo # MDEV-14031 Password policy causes replication failure +--echo # + +--disable_query_log +RESET MASTER; # get rid of previous tests binlog +--enable_query_log + +CREATE USER user1@localhost IDENTIFIED BY 'BsG9#9.cem#!85'; + +--error ER_NOT_VALID_PASSWORD +CREATE USER user2@localhost IDENTIFIED BY 'bsg9#d.cem#!85'; + +DROP USER user1@localhost; + +--let $binlog_file = LAST +source include/show_binlog_events.inc; + +UNINSTALL PLUGIN simple_password_check; diff --git a/mysql-test/suite/plugins/t/compression.combinations b/mysql-test/suite/plugins/t/compression.combinations new file mode 100644 index 00000000..645ca60e --- /dev/null +++ b/mysql-test/suite/plugins/t/compression.combinations @@ -0,0 +1,29 @@ +[innodb-bzip2] +innodb +plugin-load-add=$PROVIDER_BZIP2_SO +loose-provider-bzip2 + +[innodb-lz4] +innodb +plugin-load-add=$PROVIDER_LZ4_SO +loose-provider-lz4 + +[innodb-lzma] +innodb +plugin-load-add=$PROVIDER_LZMA_SO +loose-provider-lzma + +[innodb-lzo] +innodb +plugin-load-add=$PROVIDER_LZO_SO +loose-provider-lzo + +[innodb-snappy] +innodb +plugin-load-add=$PROVIDER_SNAPPY_SO +loose-provider-snappy + +[mroonga-lz4] +plugin-load-add=$HA_MROONGA_SO +plugin-load-add=$PROVIDER_LZ4_SO +loose-provider-lz4 diff --git a/mysql-test/suite/plugins/t/compression.test b/mysql-test/suite/plugins/t/compression.test new file mode 100644 index 00000000..c97c5725 --- /dev/null +++ b/mysql-test/suite/plugins/t/compression.test @@ -0,0 +1,52 @@ +let $engine=`select regexp_replace('$MTR_COMBINATIONS', '-.*', '')`; +let $alg=`select regexp_replace('$MTR_COMBINATIONS', '.*-', '')`; + +if (`select count(*) = 0 from information_schema.plugins where plugin_name = '$engine' and plugin_status='active'`) +{ + skip Needs $engine engine; +} + +if (`select count(*) = 0 from information_schema.plugins where plugin_name = 'provider_$alg' and plugin_status='active'`) +{ + skip Needs provider_$alg plugin; +} + +--echo # +--echo # Testing $alg compression provider with $engine +--echo # + +call mtr.add_suppression("MariaDB tried to use the .+ compression, but its provider plugin is not loaded"); +if ($engine == "innodb") { + eval set global innodb_compression_algorithm = $alg; + let $table_params = page_compressed = 1; + call mtr.add_suppression("Background Page read failed to read, uncompress, or decrypt"); + call mtr.add_suppression("Table is compressed or encrypted but uncompress or decrypt failed"); + call mtr.add_suppression("Table .*t1.* is compressed with (\\w+), which is not currently loaded. Please load the \\1 provider plugin to open the table"); +} +if ($engine == "mroonga") { + let $column_params = `select upper('COMMENT \'flags "COLUMN_SCALAR|COMPRESS_$alg"\'')`; + let $table_params = charset = utf8; +} + +eval create table t1 (a int, b text $column_params) engine = $engine $table_params; + +insert t1 (a, b) values (0, repeat("abc", 100)); +insert t1 (a, b) values (1, repeat("def", 1000)); +insert t1 (a, b) values (2, repeat("ghi", 10000)); +select a, left(b, 9), length(b) from t1; + +let $restart_parameters = --disable-provider-$alg; +source include/restart_mysqld.inc; + +if ($engine == "innodb") { + error ER_PROVIDER_NOT_LOADED; + select a, left(b, 9), length(b) from t1; +} +if ($engine == "mroonga"){ + select a, left(b, 9), length(b) from t1; + select a, left(b, 9), length(b) from t1; # a warning once per statement +} +drop table t1; + +let $restart_parameters =; +source include/restart_mysqld.inc; diff --git a/mysql-test/suite/plugins/t/compression_load.test b/mysql-test/suite/plugins/t/compression_load.test new file mode 100644 index 00000000..d67c204e --- /dev/null +++ b/mysql-test/suite/plugins/t/compression_load.test @@ -0,0 +1,24 @@ +source include/have_innodb.inc; + +if (!$PROVIDER_LZ4_SO) { + skip Requires provider_lz4 plugin; +} + +select plugin_name, plugin_status from information_schema.plugins where plugin_name='provider_lz4'; +--error ER_WRONG_VALUE_FOR_VAR +set global innodb_compression_algorithm = lz4; +install plugin provider_lz4 soname 'provider_lz4'; +select plugin_name, plugin_status from information_schema.plugins where plugin_name='provider_lz4'; +set global innodb_compression_algorithm = lz4; +create table t1 (a int, b text) engine=innodb page_compressed=1; +insert t1 (a, b) values (0, repeat("abc", 100)); +insert t1 (a, b) values (1, repeat("def", 1000)); +insert t1 (a, b) values (2, repeat("ghi", 10000)); +select a, left(b, 9), length(b) from t1; +uninstall plugin provider_lz4; +select plugin_name, plugin_status from information_schema.plugins where plugin_name='provider_lz4'; +set global innodb_compression_algorithm = default; +set global innodb_compression_algorithm = lz4; +set global innodb_compression_algorithm = default; +select a, left(b, 9), length(b) from t1; +drop table t1; diff --git a/mysql-test/suite/plugins/t/cracklib_password_check.test b/mysql-test/suite/plugins/t/cracklib_password_check.test new file mode 100644 index 00000000..0fbef757 --- /dev/null +++ b/mysql-test/suite/plugins/t/cracklib_password_check.test @@ -0,0 +1,53 @@ +--source include/not_embedded.inc + +if (!$CRACKLIB_PASSWORD_CHECK_SO) { + skip No CRACKLIB_PASSWORD_CHECK plugin; +} + +install soname "cracklib_password_check"; + +--vertical_results +--replace_result .dll .so +select * from information_schema.plugins where plugin_name='cracklib_password_check'; +--horizontal_results + +--error ER_NOT_VALID_PASSWORD +grant select on *.* to foocar identified by 'foocar'; +show warnings; + +--error ER_NOT_VALID_PASSWORD +grant select on *.* to foocar identified by 'racoof'; +show warnings; + +--error ER_NOT_VALID_PASSWORD +grant select on *.* to foo@barbar identified by 'barbar'; +show warnings; + +--error ER_NOT_VALID_PASSWORD +grant select on *.* to foobar identified by 'qwerty'; +show warnings; + +--error ER_NOT_VALID_PASSWORD +grant select on *.* to foocar@localhost identified by 'localhost'; +show warnings; + +--error ER_NOT_VALID_PASSWORD +grant select on *.* to foocar@localhost identified by 'foocar@localhost'; +show warnings; + +grant select on *.* to foobar identified by 'q$%^&*rty'; +drop user foobar; + +--echo # +--echo # MDEV-9851: CREATE USER w/o IDENTIFIED BY clause causes crash +--echo # when using cracklib plugin +--echo # + +--error ER_NOT_VALID_PASSWORD +create user 'newuser'@'localhost'; + +uninstall plugin cracklib_password_check; + +create user foo1 identified by 'pwd'; +drop user foo1; + diff --git a/mysql-test/suite/plugins/t/dialog.test b/mysql-test/suite/plugins/t/dialog.test new file mode 100644 index 00000000..efababa9 --- /dev/null +++ b/mysql-test/suite/plugins/t/dialog.test @@ -0,0 +1,53 @@ +# +# test for the client "dialog" plugin +# + +--source include/not_embedded.inc + +if (!$DIALOG_SO) { + skip No dialog auth plugin; +} +if (!$DIALOG_EXAMPLES_SO) { + skip No dialog auth plugin; +} + +--replace_result .dll .so +eval install plugin three_attempts soname '$DIALOG_EXAMPLES_SO'; +create user test_dialog identified via three_attempts using 'SECRET'; +GRANT SELECT ON test.* TO test_dialog; + +--write_file $MYSQLTEST_VARDIR/tmp/dialog_good.txt +foo +1234 +SECRET +select user(), current_user(); +EOF + +--write_file $MYSQLTEST_VARDIR/tmp/dialog_bad.txt +foo +1234 +wrong +SECRET +EOF + +--echo # +--echo # -pSECRET is picked up, no questions asked. +--echo # +--exec echo "select user(), current_user();"|$MYSQL_TEST -u test_dialog -pSECRET + +--echo # +--echo # without -p. up to three questions are asked on the stdin. +--echo # athentication is successful, the correct pasword is on the third line +--echo # +--exec $MYSQL_TEST -u test_dialog < $MYSQLTEST_VARDIR/tmp/dialog_good.txt + +--echo # +--echo # athentication is unsuccessful, first three lines are all wrong +--echo # +--error 1 +--exec $MYSQL_TEST -u test_dialog < $MYSQLTEST_VARDIR/tmp/dialog_bad.txt + +--remove_file $MYSQLTEST_VARDIR/tmp/dialog_good.txt +--remove_file $MYSQLTEST_VARDIR/tmp/dialog_bad.txt +drop user test_dialog; +uninstall plugin three_attempts; diff --git a/mysql-test/suite/plugins/t/false_dupes-6543.test b/mysql-test/suite/plugins/t/false_dupes-6543.test new file mode 100644 index 00000000..ca278685 --- /dev/null +++ b/mysql-test/suite/plugins/t/false_dupes-6543.test @@ -0,0 +1,19 @@ +source include/not_embedded.inc; +# +# MDEV-6543 Crash if enable 'federatedx' when 'federated' plugin already enabled, and vice-versa +# +if(!$HA_FEDERATED_SO) { + skip Needs ha_federated.so; +} +if(!$HA_FEDERATEDX_SO) { + skip Needs ha_federatedx.so; +} + +install soname 'ha_federated'; +# note: no error below! install soname ignores already loaded plugins +install soname 'ha_federated'; +# note: an error here, even though plugin name is the same! +--error ER_UDF_EXISTS +install soname 'ha_federatedx'; +uninstall soname 'ha_federated'; + diff --git a/mysql-test/suite/plugins/t/feedback_plugin_install.opt b/mysql-test/suite/plugins/t/feedback_plugin_install.opt new file mode 100644 index 00000000..a711ae94 --- /dev/null +++ b/mysql-test/suite/plugins/t/feedback_plugin_install.opt @@ -0,0 +1 @@ +--loose-feedback diff --git a/mysql-test/suite/plugins/t/feedback_plugin_install.test b/mysql-test/suite/plugins/t/feedback_plugin_install.test new file mode 100644 index 00000000..559dcebf --- /dev/null +++ b/mysql-test/suite/plugins/t/feedback_plugin_install.test @@ -0,0 +1,17 @@ +--source include/not_embedded.inc + +if (`select length('$FEEDBACK_SO') = 0`) { + skip No feedback plugin; +} + +--replace_regex /\.dll/.so/ +eval install plugin feedback soname '$FEEDBACK_SO'; +select plugin_status from information_schema.plugins where plugin_name='feedback'; +--replace_result https http +--sorted_result +select * from information_schema.feedback where variable_name like 'feed%' + and variable_name not like '%_uid' + and variable_name not like '%debug%'; + +uninstall plugin feedback; + diff --git a/mysql-test/suite/plugins/t/feedback_plugin_load.opt b/mysql-test/suite/plugins/t/feedback_plugin_load.opt new file mode 100644 index 00000000..6bf7594c --- /dev/null +++ b/mysql-test/suite/plugins/t/feedback_plugin_load.opt @@ -0,0 +1,2 @@ +--loose-feedback +--plugin-load-add=$FEEDBACK_SO diff --git a/mysql-test/suite/plugins/t/feedback_plugin_load.test b/mysql-test/suite/plugins/t/feedback_plugin_load.test new file mode 100644 index 00000000..f67397af --- /dev/null +++ b/mysql-test/suite/plugins/t/feedback_plugin_load.test @@ -0,0 +1,51 @@ +if (`select count(*) = 0 from information_schema.plugins where plugin_name = 'feedback' and plugin_status='active'`) +{ + --skip Feedback plugin is not active +} +if (`select @@feedback_url = ""`) +{ + --skip Feedback plugin is not active +} + +select plugin_status from information_schema.plugins where plugin_name='feedback'; + +# Every SELECT from INFORMATION_SCHEMA.FEEDBACK increases the value of 'FEEDBACK used'. +# We cannot record the actual value, because the test can be executed more than once, +# but we can check that the value indeed increases as expected. +# There is still a room for some race condition, e.g. if at the very moment +# between first SELECT to store the value and the next SELECT to check that it increases, +# the feedback plugin is activated. But the probability of it is close to 0, +# so lets get back to it if it ever happens. + +# Lets say the plugin was used X times before this SELECT +SELECT variable_value INTO @feedback_used FROM information_schema.feedback where variable_name = 'FEEDBACK used'; + +# Now $feedback_used == X+1, and 'FEEDBACK used' is also X+1. And variable_value is increased again when we run the next SELECT +SELECT variable_value = @feedback_used + 1 as 'MUST BE 1' FROM information_schema.feedback where variable_name = 'FEEDBACK used'; + +# Now when we are happy with 'FEEDBACK used', we can check everything else + +--replace_result https http +--sorted_result +select * from information_schema.feedback where variable_name like 'feed%' + and variable_name not like '%_uid' and variable_name not like 'FEEDBACK used' + and variable_name not like '%debug%'; + +# Embedded server does not use the table mysqld.user and thus +# does not automatically use utf8mb4 on startup. Use it manually. +--disable_query_log +if (`SELECT VERSION() LIKE '%embedded%'`) +{ + create temporary table t1 (a json); +} +--enable_query_log +SELECT VARIABLE_VALUE>0, VARIABLE_NAME FROM INFORMATION_SCHEMA.FEEDBACK +WHERE VARIABLE_NAME LIKE 'Collation used %' +ORDER BY VARIABLE_NAME; + +prepare stmt from "SELECT VARIABLE_VALUE>0, VARIABLE_NAME FROM INFORMATION_SCHEMA.FEEDBACK WHERE VARIABLE_NAME LIKE 'Collation used %' ORDER BY VARIABLE_NAME"; + +execute stmt; +execute stmt; + +deallocate prepare stmt; diff --git a/mysql-test/suite/plugins/t/feedback_plugin_send.test b/mysql-test/suite/plugins/t/feedback_plugin_send.test new file mode 100644 index 00000000..0ea1814e --- /dev/null +++ b/mysql-test/suite/plugins/t/feedback_plugin_send.test @@ -0,0 +1,43 @@ +# Restart of server does not work for embedded. +--source include/not_embedded.inc + +source feedback_plugin_load.test; + +if (!$MTR_FEEDBACK_PLUGIN) { + skip MTR_FEEDBACK_PLUGIN is not set; +} + +# +# Yep. The plugin waits 5 minutes before sending anything, +# and there's no way to force it to send anything sooner. +# Let's wait, and hope that mtr is started with --parallel and +# is doing some work in other workers. +# + +sleep 100; +set global sql_mode=ONLY_FULL_GROUP_BY; +sleep 210; + + +# The test expects that the plugin will send a report at least 2 times, +# now (5 min after loading) and on server shutdown which happens below. +# Since we have already waited for 5 min, let's be generous +# and make sure the server has enough time to shut down properly. +# We won't lose anything if the shutdown is fast, but if it's slow, the plugin +# will still be able to finish the job and write about it in the error log. + +--let $shutdown_timeout= 60 +source include/restart_mysqld.inc; + +replace_result https http 2 6; +perl; + $log_error= $ENV{'MYSQLTEST_VARDIR'} . '/log/mysqld.1.err'; + open(LOG, '<', $log_error) or die "open(< $log_error): $!"; + + %logg=(); + while ($_=<LOG>) { + $logg{$&}++ if /feedback plugin:.*/; + } + print "$_\n" for sort keys %logg; + close LOG; +EOF diff --git a/mysql-test/suite/plugins/t/fulltext_plugin.test b/mysql-test/suite/plugins/t/fulltext_plugin.test new file mode 100644 index 00000000..e9b4343e --- /dev/null +++ b/mysql-test/suite/plugins/t/fulltext_plugin.test @@ -0,0 +1,17 @@ +--source include/have_simple_parser.inc + +# +# BUG#39746 - Debug flag breaks struct definition (server crash) +# +--replace_result .dll .so +eval INSTALL PLUGIN simple_parser SONAME '$MYPLUGLIB_SO'; +CREATE TABLE t1(a TEXT, b TEXT, FULLTEXT(a) WITH PARSER simple_parser); +ALTER TABLE t1 ADD FULLTEXT(b) WITH PARSER simple_parser; +DROP TABLE t1; +UNINSTALL PLUGIN simple_parser; + +# +# Bug #69682 - mysqld crashes after uninstall of plugin with "first" status var +# +show status like 'a%status'; + diff --git a/mysql-test/suite/plugins/t/locales.opt b/mysql-test/suite/plugins/t/locales.opt new file mode 100644 index 00000000..21f53ca3 --- /dev/null +++ b/mysql-test/suite/plugins/t/locales.opt @@ -0,0 +1,3 @@ +--loose-locale +--plugin-load-add=$LOCALES_SO + diff --git a/mysql-test/suite/plugins/t/locales.test b/mysql-test/suite/plugins/t/locales.test new file mode 100644 index 00000000..2b4d5643 --- /dev/null +++ b/mysql-test/suite/plugins/t/locales.test @@ -0,0 +1,14 @@ +if (`select count(*) = 0 from information_schema.plugins where plugin_name = 'locales' and plugin_status='active'`) +{ + --skip LOCALES plugin is not active +} +SET names utf8; + +select * from information_schema.locales; +show locales; +--error ER_PARSE_ERROR +show locales like '%spanish%'; +show locales where description like '%spanish%'; +--error ER_PARSE_ERROR +flush locales; + diff --git a/mysql-test/suite/plugins/t/max_password_errors_auth_named_pipe.opt b/mysql-test/suite/plugins/t/max_password_errors_auth_named_pipe.opt new file mode 100644 index 00000000..52bf94f3 --- /dev/null +++ b/mysql-test/suite/plugins/t/max_password_errors_auth_named_pipe.opt @@ -0,0 +1 @@ +--loose-enable-named-pipe --plugin-load=$AUTH_NAMED_PIPE_SO diff --git a/mysql-test/suite/plugins/t/max_password_errors_auth_named_pipe.test b/mysql-test/suite/plugins/t/max_password_errors_auth_named_pipe.test new file mode 100644 index 00000000..79aeb7d3 --- /dev/null +++ b/mysql-test/suite/plugins/t/max_password_errors_auth_named_pipe.test @@ -0,0 +1,22 @@ +# Tests that max_password_errors has no effect on login errors with +# passwordless plugins (Windows version / auth_named_pipe) + +--source include/not_embedded.inc +--source include/have_auth_named_pipe.inc +if (`SELECT '$USERNAME' = 'nosuchuser'`) { + skip skipped for nosuchuser; +} +set @old_max_password_errors=@@max_password_errors; +create user nosuchuser identified with 'named_pipe'; + +set global max_password_errors=1; +--replace_result $MASTER_MYSOCK MASTER_SOCKET $MASTER_MYPORT MASTER_PORT +error ER_ACCESS_DENIED_NO_PASSWORD_ERROR; +connect(pipe_con,localhost,nosuchuser,,,,,PIPE); +--replace_result $MASTER_MYSOCK MASTER_SOCKET $MASTER_MYPORT MASTER_PORT +error ER_ACCESS_DENIED_NO_PASSWORD_ERROR; +connect(pipe_con,localhost,nosuchuser,,,,,PIPE); + +DROP USER nosuchuser; +FLUSH PRIVILEGES; +set global max_password_errors=@old_max_password_errors; diff --git a/mysql-test/suite/plugins/t/max_password_errors_auth_socket.opt b/mysql-test/suite/plugins/t/max_password_errors_auth_socket.opt new file mode 100644 index 00000000..91bb73e3 --- /dev/null +++ b/mysql-test/suite/plugins/t/max_password_errors_auth_socket.opt @@ -0,0 +1 @@ +--loose-enable-named-pipe --plugin-load=$AUTH_SOCKET_SO diff --git a/mysql-test/suite/plugins/t/max_password_errors_auth_socket.test b/mysql-test/suite/plugins/t/max_password_errors_auth_socket.test new file mode 100644 index 00000000..495a68a0 --- /dev/null +++ b/mysql-test/suite/plugins/t/max_password_errors_auth_socket.test @@ -0,0 +1,23 @@ +# Tests that max_password_errors has no effect on login errors with +# passwordless plugins (Unix version / auth_unix_socket) + +--source include/not_embedded.inc +--source include/have_unix_socket.inc + +if (`SELECT '$USER' = 'nosuchuser'`) { + skip USER is nosuchuser; +} +set @old_max_password_errors=@@max_password_errors; +create user nosuchuser identified with 'unix_socket'; + +set global max_password_errors=1; +--replace_result $MASTER_MYSOCK MASTER_SOCKET $MASTER_MYPORT MASTER_PORT +error ER_ACCESS_DENIED_NO_PASSWORD_ERROR; +connect(pipe_con,localhost,nosuchuser); +--replace_result $MASTER_MYSOCK MASTER_SOCKET $MASTER_MYPORT MASTER_PORT +error ER_ACCESS_DENIED_NO_PASSWORD_ERROR; +connect(pipe_con,localhost,nosuchuser); + +DROP USER nosuchuser; +FLUSH PRIVILEGES; +set global max_password_errors=@old_max_password_errors; diff --git a/mysql-test/suite/plugins/t/multiauth.test b/mysql-test/suite/plugins/t/multiauth.test new file mode 100644 index 00000000..d2a93cf1 --- /dev/null +++ b/mysql-test/suite/plugins/t/multiauth.test @@ -0,0 +1,235 @@ +--source include/not_ubsan.inc + +let $REGEX_VERSION_ID=/$mysql_get_server_version/VERSION_ID/; +let $REGEX_PASSWORD_LAST_CHANGED=/password_last_changed": [0-9]*/password_last_changed": #/; +let $REGEX_GLOBAL_PRIV=$REGEX_PASSWORD_LAST_CHANGED $REGEX_VERSION_ID; + +# +# MDEV-11340 Allow multiple alternative authentication methods for the same user +# +--source include/have_unix_socket.inc +if (`SELECT '$USER' = 'mysqltest1'`) { + skip USER is mysqltest1; +} +if (!$AUTH_ED25519_SO) { + skip No auth_ed25519 plugin; +} + +--let $plugindir=`SELECT @@global.plugin_dir` +install soname 'auth_ed25519'; + +--let $try_auth=$MYSQL_TEST < $MYSQLTEST_VARDIR/tmp/peercred_test.txt 2>&1 + +--write_file $MYSQLTEST_VARDIR/tmp/peercred_test.txt +--let $replace1=$USER@localhost +--let $replace2=$USER@% +--replace_result $replace1 "USER@localhost" $replace2 "USER@%" +select user(), current_user(), database(); +EOF + +--let $creplace=create user '$USER' +--let $greplace=grant select on test.* to '$USER' +--let $dreplace=drop user '$USER' + +# +# socket,password +# +--replace_result $creplace "create user 'USER'" +eval $creplace identified via unix_socket OR mysql_native_password as password("GOOD"); +--replace_result $greplace "grant select on test.* to 'USER'" +eval $greplace ; +create user mysqltest1 identified via unix_socket OR mysql_native_password as password("good"); +grant select on test.* to mysqltest1; +show create user mysqltest1; +--echo # name match = ok +--exec $try_auth -u $USER +--echo # name does not match, password good = ok +--exec $try_auth -u mysqltest1 -pgood +--echo # name does not match, password bad = failure +--error 1 +--exec $try_auth -u mysqltest1 -pbad +--replace_result $dreplace "drop user 'USER'" +eval $dreplace, mysqltest1; + +# +# password,socket +# +--replace_result $creplace "create user 'USER'" +eval $creplace identified via mysql_native_password as password("GOOD") OR unix_socket; +--replace_result $greplace "grant select on test.* to 'USER'" +eval $greplace ; +create user mysqltest1 identified via mysql_native_password as password("good") OR unix_socket; +grant select on test.* to mysqltest1; +show create user mysqltest1; +--echo # name match = ok +--exec $try_auth -u $USER +--echo # name does not match, password good = ok +--exec $try_auth -u mysqltest1 -pgood +--echo # name does not match, password bad = failure +--error 1 +--exec $try_auth -u mysqltest1 -pbad +--replace_result $dreplace "drop user 'USER'" +eval $dreplace, mysqltest1; + +# +# socket,ed25519 +# +--replace_result $creplace "create user 'USER'" +eval $creplace identified via unix_socket OR ed25519 as password("GOOD"); +--replace_result $greplace "grant select on test.* to 'USER'" +eval $greplace ; +create user mysqltest1 identified via unix_socket OR ed25519 as password("good"); +grant select on test.* to mysqltest1; +show create user mysqltest1; +--echo # name match = ok +--exec $try_auth -u $USER +--echo # name does not match, password good = ok +--exec $try_auth -u mysqltest1 -pgood +--echo # name does not match, password bad = failure +--error 1 +--exec $try_auth -u mysqltest1 -pbad +--replace_result $dreplace "drop user 'USER'" +eval $dreplace, mysqltest1; + +# +# ed25519,socket +# +--replace_result $creplace "create user 'USER'" +eval $creplace identified via ed25519 as password("GOOD") OR unix_socket; +--replace_result $greplace "grant select on test.* to 'USER'" +eval $greplace ; +create user mysqltest1 identified via ed25519 as password("good") OR unix_socket; +grant select on test.* to mysqltest1; +show create user mysqltest1; +--echo # name match = ok +--exec $try_auth -u $USER +--echo # name does not match, password good = ok +--exec $try_auth -u mysqltest1 -pgood +--echo # name does not match, password bad = failure +--error 1 +--exec $try_auth -u mysqltest1 -pbad +--replace_result $dreplace "drop user 'USER'" +eval $dreplace, mysqltest1; + +# +# ed25519,socket,password +# +--replace_result $creplace "create user 'USER'" +eval $creplace identified via ed25519 as password("GOOD") OR unix_socket OR mysql_native_password as password("works"); +--replace_result $greplace "grant select on test.* to 'USER'" +eval $greplace ; +create user mysqltest1 identified via ed25519 as password("good") OR unix_socket OR mysql_native_password as password("works"); +grant select on test.* to mysqltest1; +show create user mysqltest1; +--echo # name match = ok +--exec $try_auth -u $USER +--echo # name does not match, password good = ok +--exec $try_auth -u mysqltest1 -pgood +--echo # name does not match, second password works = ok +--exec $try_auth -u mysqltest1 -pworks +--echo # name does not match, password bad = failure +--error 1 +--exec $try_auth -u mysqltest1 -pbad +--replace_result $dreplace "drop user 'USER'" +eval $dreplace, mysqltest1; + +# +# password,password +# +create user mysqltest1 identified via mysql_native_password as password("good") OR mysql_native_password as password("works"); +grant select on test.* to mysqltest1; +show create user mysqltest1; +--echo # password good = ok +--exec $try_auth -u mysqltest1 -pgood +--echo # second password works = ok +--exec $try_auth -u mysqltest1 -pworks +--echo # password bad = failure +--error 1 +--exec $try_auth -u mysqltest1 -pbad +drop user mysqltest1; + +# +# show grants, flush privileges, set password, alter user +# +create user mysqltest1 identified via ed25519 as password("good") OR unix_socket OR mysql_native_password as password("works"); +show grants for mysqltest1; +--replace_regex $REGEX_GLOBAL_PRIV +select json_detailed(priv) from mysql.global_priv where user='mysqltest1'; +select password,plugin,authentication_string from mysql.user where user='mysqltest1'; +flush privileges; +show create user mysqltest1; +set password for mysqltest1 = password('foobar'); +show create user mysqltest1; +alter user mysqltest1 identified via unix_socket OR mysql_native_password as password("some"); +show create user mysqltest1; +set password for mysqltest1 = password('foobar'); +show create user mysqltest1; +alter user mysqltest1 identified via unix_socket; +--error ER_SET_PASSWORD_AUTH_PLUGIN +set password for mysqltest1 = password('bla'); +alter user mysqltest1 identified via mysql_native_password as password("some") or unix_socket; +show create user mysqltest1; +drop user mysqltest1; + +--source include/switch_to_mysql_user.inc +--replace_regex /\d{6}/XX.YY.ZZ/ +--error ER_COL_COUNT_DOESNT_MATCH_PLEASE_UPDATE +create user mysqltest1 identified via ed25519 as password("good") OR unix_socket OR mysql_native_password as password("works"); +--source include/switch_to_mysql_global_priv.inc + +# +# invalid password,socket +# +--replace_result $creplace "create user 'USER'" +eval $creplace identified via mysql_native_password as '1234567890123456789012345678901234567890a' OR unix_socket; +--replace_result $greplace "grant select on test.* to 'USER'" +eval $greplace ; +create user mysqltest1 identified via mysql_native_password as '1234567890123456789012345678901234567890a' OR unix_socket; +grant select on test.* to mysqltest1; +update mysql.global_priv set priv=replace(priv, '1234567890123456789012345678901234567890a', 'invalid password'); +flush privileges; +show create user mysqltest1; +--echo # name match = ok +--exec $try_auth -u $USER +--echo # name does not match = failure +--error 1 +--exec $try_auth -u mysqltest1 +--echo # SET PASSWORD helps +set password for mysqltest1 = password('bla'); +--exec $try_auth -u mysqltest1 -pbla +--replace_result $dreplace "drop user 'USER'" +eval $dreplace, mysqltest1; + +# +# missing client-side plugin +# +create user mysqltest1 identified via ed25519 as password("good"); +grant select on test.* to mysqltest1; +show create user mysqltest1; +--echo # no plugin = failure +# covers Linux (1st re), FreeBSD (2nd), AIX (3rd and 4th) +--replace_regex /loaded: .*client_ed25519.so: cannot open shared object file: No such file or directory/loaded: no such file/ /loaded: Cannot open.*client_ed25519.so./loaded: no such file/ /loaded: .*Could not load module.*client_ed25519.so.\n/loaded: no such file/ /System error: No such file or directory// +--error 1 +--exec $try_auth -u mysqltest1 -pgood --plugin-dir=$plugindir/no +alter user mysqltest1 identified via ed25519 as password("good") OR mysql_native_password as password("works"); +show create user mysqltest1; +--echo # no plugin = failure +--error 1 +--exec $try_auth -u mysqltest1 -pgood --plugin-dir=$plugindir/no +--echo # no plugin, second password works = ok +--exec $try_auth -u mysqltest1 -pworks --plugin-dir=$plugindir/no +drop user mysqltest1; + +uninstall soname 'auth_ed25519'; +--remove_file $MYSQLTEST_VARDIR/tmp/peercred_test.txt + +# +# MDEV-21928 ALTER USER doesn't remove excess authentication plugins from mysql.global_priv +# +create user mysqltest1 identified via mysql_native_password as password("good") OR unix_socket; +show create user mysqltest1; +alter user mysqltest1 identified via mysql_native_password as password("better"); +show create user mysqltest1; +flush privileges; +show create user mysqltest1; +drop user mysqltest1; diff --git a/mysql-test/suite/plugins/t/pam.test b/mysql-test/suite/plugins/t/pam.test new file mode 100644 index 00000000..c953e05f --- /dev/null +++ b/mysql-test/suite/plugins/t/pam.test @@ -0,0 +1,128 @@ +let $PAM_PLUGIN_VERSION= $AUTH_PAM_SO; +--source pam_init.inc + +--write_file $MYSQLTEST_VARDIR/tmp/pam_good.txt +not very secret challenge +9225 +select user(), current_user(), database(); +EOF + +--write_file $MYSQLTEST_VARDIR/tmp/pam_bad.txt +not very secret challenge +9224 +select user(), current_user(), database(); +EOF + +--write_file $MYSQLTEST_VARDIR/tmp/pam_ugly.txt +crash pam module +616 +select user(), current_user(), database(); +EOF + +--write_file $MYSQLTEST_VARDIR/tmp/pam_good2.txt +9212 +select user(), current_user(), database(); +EOF + +--echo # +--echo # athentication is successful, challenge/pin are ok +--echo # note that current_user() differs from user() +--echo # +--exec $MYSQL_TEST -u test_pam < $MYSQLTEST_VARDIR/tmp/pam_good.txt + +--echo # +--echo # athentication is unsuccessful +--echo # +--error 1 +--exec $MYSQL_TEST -u test_pam < $MYSQLTEST_VARDIR/tmp/pam_bad.txt + +--echo # +--echo # athentication is unsuccessful +--echo # +--error 1 +--exec $MYSQL_TEST -u test_pam < $MYSQLTEST_VARDIR/tmp/pam_ugly.txt + +--echo # +--echo # athentication is successful +--echo # +--exec $MYSQL_TEST -u test_pam -pgoodpassword < $MYSQLTEST_VARDIR/tmp/pam_good2.txt + +--echo # +--echo # athentication is unsuccessful +--echo # +--error 1 +--exec $MYSQL_TEST -u test_pam -pbadpassword < $MYSQLTEST_VARDIR/tmp/pam_good2.txt + +--echo # +--echo # MDEV-26339 Account specifics to be handled before proxying +--echo # + +# one can connect if the proxy account is locked +alter user pam_test account lock; +alter user pam_test require subject 'foobar'; +alter user pam_test password expire; +--error 0 +--exec $MYSQL_TEST -u test_pam -pgoodpassword < $MYSQLTEST_VARDIR/tmp/pam_good2.txt +alter user pam_test account unlock; +alter user pam_test require none; +alter user pam_test identified by ''; +show create user pam_test; + +#one cannot connect if the proxied account is locked +alter user test_pam account lock; +--error 1 +--exec $MYSQL_TEST -u test_pam -pgoodpassword < $MYSQLTEST_VARDIR/tmp/pam_good2.txt +alter user test_pam account unlock; + +alter user test_pam require subject 'foobar'; +--error 1 +--exec $MYSQL_TEST -u test_pam -pgoodpassword < $MYSQLTEST_VARDIR/tmp/pam_good2.txt +alter user test_pam require none; + +alter user test_pam password expire; +--error 1 +--exec $MYSQL_TEST -u test_pam -pgoodpassword < $MYSQLTEST_VARDIR/tmp/pam_good2.txt + +drop user test_pam; +drop user pam_test; +create user PAM_TEST identified via pam using 'mariadb_mtr'; +grant all on test.* to PAM_TEST; + +--echo # +--echo # athentication is unsuccessful +--echo # +--error 1 +--exec $MYSQL_TEST -u PAM_TEST < $MYSQLTEST_VARDIR/tmp/pam_good.txt + +set global pam_winbind_workaround=1; +--echo # +--echo # athentication is successful +--echo # +--exec $MYSQL_TEST -u PAM_TEST < $MYSQLTEST_VARDIR/tmp/pam_good.txt + +--remove_file $MYSQLTEST_VARDIR/tmp/pam_good.txt +--remove_file $MYSQLTEST_VARDIR/tmp/pam_good2.txt +--remove_file $MYSQLTEST_VARDIR/tmp/pam_bad.txt +--remove_file $MYSQLTEST_VARDIR/tmp/pam_ugly.txt +drop user PAM_TEST; + +--echo # +--echo # MDEV-27341 Use SET PASSWORD to change PAM service +--echo # +create user pam_test identified via pam using 'mariadb_mtr'; +grant all on test.* to pam_test; +--write_file $MYSQLTEST_VARDIR/tmp/setpwd.txt +not very secret challenge +9225 +select user(), current_user(), database(); +error ER_SET_PASSWORD_AUTH_PLUGIN; +set password='foo'; +show create user; +EOF +--exec $MYSQL_TEST -u pam_test < $MYSQLTEST_VARDIR/tmp/setpwd.txt +--remove_file $MYSQLTEST_VARDIR/tmp/setpwd.txt +drop user pam_test; + +let $count_sessions= 1; +--source include/wait_until_count_sessions.inc +uninstall plugin pam; diff --git a/mysql-test/suite/plugins/t/pam_cleartext.opt b/mysql-test/suite/plugins/t/pam_cleartext.opt new file mode 100644 index 00000000..aa270885 --- /dev/null +++ b/mysql-test/suite/plugins/t/pam_cleartext.opt @@ -0,0 +1 @@ +--loose-pam-use-cleartext-plugin diff --git a/mysql-test/suite/plugins/t/pam_cleartext.test b/mysql-test/suite/plugins/t/pam_cleartext.test new file mode 100644 index 00000000..0824ee8f --- /dev/null +++ b/mysql-test/suite/plugins/t/pam_cleartext.test @@ -0,0 +1,37 @@ +let $PAM_PLUGIN_VERSION= $AUTH_PAM_SO; +--source pam_init.inc + +show variables like 'pam_use_%'; + +--write_file $MYSQLTEST_VARDIR/tmp/pam_good.txt +not very secret challenge +9225 +select user(), current_user(), database(); +EOF + +--echo # +--echo # same test as in pam.test now fails +--echo # +--error 1 +--exec $MYSQL_TEST -u test_pam < $MYSQLTEST_VARDIR/tmp/pam_good.txt +--error 1 +--exec $MYSQL_TEST -u test_pam -p'something' < $MYSQLTEST_VARDIR/tmp/pam_good.txt + +--remove_file $MYSQLTEST_VARDIR/tmp/pam_good.txt + +--echo # +--echo # success +--echo # +--exec $MYSQL -u test_pam -p'cleartext good' -e 'select user(), current_user(), database()' + +--echo # +--echo # failure +--echo # +--error 1 +--exec $MYSQL -u test_pam -p'cleartext bad' -e 'select user(), current_user(), database()' + +drop user test_pam; +drop user pam_test; +let $count_sessions= 1; +--source include/wait_until_count_sessions.inc +uninstall plugin pam; diff --git a/mysql-test/suite/plugins/t/pam_init.inc b/mysql-test/suite/plugins/t/pam_init.inc new file mode 100644 index 00000000..f3a94842 --- /dev/null +++ b/mysql-test/suite/plugins/t/pam_init.inc @@ -0,0 +1,16 @@ + +--source include/not_embedded.inc + +if (!$PAM_PLUGIN_VERSION) { + skip No pam auth plugin; +} + +eval install plugin pam soname '$PAM_PLUGIN_VERSION'; +create user test_pam identified via pam using 'mariadb_mtr'; +grant all on test.* to test_pam; +create user pam_test; +grant all on test.* to pam_test; +grant proxy on pam_test to test_pam; + +let $plugindir=`SELECT @@global.plugin_dir`; + diff --git a/mysql-test/suite/plugins/t/pam_v1.test b/mysql-test/suite/plugins/t/pam_v1.test new file mode 100644 index 00000000..75425116 --- /dev/null +++ b/mysql-test/suite/plugins/t/pam_v1.test @@ -0,0 +1,52 @@ +let $PAM_PLUGIN_VERSION= $AUTH_PAM_V1_SO; +--source pam_init.inc + +--write_file $MYSQLTEST_VARDIR/tmp/pam_good.txt +not very secret challenge +9225 +select user(), current_user(), database(); +EOF + +--write_file $MYSQLTEST_VARDIR/tmp/pam_bad.txt +not very secret challenge +9224 +select user(), current_user(), database(); +EOF + +--write_file $MYSQLTEST_VARDIR/tmp/pam_good2.txt +9212 +select user(), current_user(), database(); +EOF + +--echo # +--echo # athentication is successful, challenge/pin are ok +--echo # note that current_user() differs from user() +--echo # +--exec $MYSQL_TEST -u test_pam < $MYSQLTEST_VARDIR/tmp/pam_good.txt + +--echo # +--echo # athentication is unsuccessful +--echo # +--error 1 +--exec $MYSQL_TEST -u test_pam < $MYSQLTEST_VARDIR/tmp/pam_bad.txt + +--echo # +--echo # athentication is successful +--echo # +--error 0 +--exec $MYSQL_TEST -u test_pam -pgoodpassword < $MYSQLTEST_VARDIR/tmp/pam_good2.txt + +--echo # +--echo # athentication is unsuccessful +--echo # +--error 1 +--exec $MYSQL_TEST -u test_pam -pbadpassword < $MYSQLTEST_VARDIR/tmp/pam_good2.txt + +--remove_file $MYSQLTEST_VARDIR/tmp/pam_good.txt +--remove_file $MYSQLTEST_VARDIR/tmp/pam_good2.txt +--remove_file $MYSQLTEST_VARDIR/tmp/pam_bad.txt +drop user test_pam; +drop user pam_test; +let $count_sessions= 1; +--source include/wait_until_count_sessions.inc +uninstall plugin pam; diff --git a/mysql-test/suite/plugins/t/password_reuse_check.test b/mysql-test/suite/plugins/t/password_reuse_check.test new file mode 100644 index 00000000..16ff21dc --- /dev/null +++ b/mysql-test/suite/plugins/t/password_reuse_check.test @@ -0,0 +1,100 @@ +--source include/not_embedded.inc + +if (!$PASSWORD_REUSE_CHECK_SO) { + skip No PASSWORD_REUSE_CHECK plugin; +} + +install soname "password_reuse_check"; + +set global password_reuse_check_interval= 0; + +--echo # Default value (sould be unlimited i.e. 0) +SHOW GLOBAL VARIABLES like "password_reuse_check%"; + +--echo # insert user +grant select on *.* to user_name@localhost identified by 'test_pwd'; + +--error ER_NOT_VALID_PASSWORD +grant select on *.* to user_name@localhost identified by 'test_pwd'; +show warnings; + +--error ER_CANNOT_USER +alter user user_name@localhost identified by 'test_pwd'; +show warnings; + +# Plugin does not work for it +#--error ER_NOT_VALID_PASSWORD +#SET PASSWORD FOR user_name@localhost = PASSWORD('test_pwd'); + +--echo # check exparation + +set global password_reuse_check_interval= 10; + +--error ER_CANNOT_USER +alter user user_name@localhost identified by 'test_pwd'; +show warnings; +select hex(hash) from mysql.password_reuse_check_history; + +--echo # emulate old password +update mysql.password_reuse_check_history set time= date_sub(now(), interval +11 day); + +alter user user_name@localhost identified by 'test_pwd'; +show warnings; + +drop user user_name@localhost; + +show create table mysql.password_reuse_check_history; +select count(*) from mysql.password_reuse_check_history; + +drop table mysql.password_reuse_check_history; + +--echo # test error messages + +set global password_reuse_check_interval= 0; + +drop table if exists mysql.password_reuse_check_history; + +--echo # test error messages + +create table mysql.password_reuse_check_history (wrong_structure int); + +--error ER_NOT_VALID_PASSWORD +grant select on *.* to user_name@localhost identified by 'test_pwd'; +show warnings; + +set global password_reuse_check_interval= 10; + +--error ER_NOT_VALID_PASSWORD +grant select on *.* to user_name@localhost identified by 'test_pwd'; +show warnings; + +drop table mysql.password_reuse_check_history; + +--echo # +--echo # MDEV-28838: password_reuse_check plugin mixes username and password +--echo # + +grant select on *.* to user_name@localhost identified by 'test_pwd'; + +grant select on *.* to user_nam@localhost identified by 'etest_pwd'; +show warnings; + +drop user user_name@localhost; +drop user user_nam@localhost; +drop table mysql.password_reuse_check_history; + +grant select on *.* to user_name@localhost identified by 'test_pwd'; + +grant select on *.* to tuser_name@localhos identified by 'test_pwd'; +show warnings; + +drop user user_name@localhost; +drop user tuser_name@localhos; + +--echo # +--echo # End of 10.7 tests +--echo # + +drop table mysql.password_reuse_check_history; +uninstall plugin password_reuse_check; diff --git a/mysql-test/suite/plugins/t/processlist.test b/mysql-test/suite/plugins/t/processlist.test new file mode 100644 index 00000000..17a51e78 --- /dev/null +++ b/mysql-test/suite/plugins/t/processlist.test @@ -0,0 +1,20 @@ +# +# MDEV-15359 Thread stay in "cleaning up" status after finishing +# +source include/have_innodb.inc; + +create table t1 (a int) engine=innodb; +start transaction; +insert t1 values (1); +let id=`select connection_id()`; +connect con2,localhost,root; +let $wait_condition=select state='' from information_schema.processlist where id = $id; +--source include/wait_condition.inc +replace_regex /\"/-/; #" +let s=`show engine innodb status`; +disable_query_log; +eval select regexp_replace("$s", '(?s)^.*MariaDB thread id $id,.*root([^\n]*)\n.*', '\\\\1') as `state from show engine innodb status, must be empty`; +enable_query_log; +disconnect con2; +connection default; +drop table t1; diff --git a/mysql-test/suite/plugins/t/qc_info.test b/mysql-test/suite/plugins/t/qc_info.test new file mode 100644 index 00000000..de3e9d59 --- /dev/null +++ b/mysql-test/suite/plugins/t/qc_info.test @@ -0,0 +1,28 @@ +set @save_query_cache_size=@@global.query_cache_size; +--source include/default_charset.inc +--source qc_info_init.inc + +# test that hits are correctly incremented +reset query cache; +--disable_ps2_protocol +select * from t1; +select * from t1; +--enable_ps2_protocol +select hits, statement_text from information_schema.query_cache_info; + +drop table t1; +# the query was invalidated +select statement_schema, statement_text, result_blocks_count, result_blocks_size from information_schema.query_cache_info; + +set global query_cache_size = 0; +select * from information_schema.query_cache_info; + +set global query_cache_size= default; +set global query_cache_type=default; + +--error ER_PARSE_ERROR +show query_cache_info; +--error ER_PARSE_ERROR +flush query_cache_info; + +set @@global.query_cache_size=@save_query_cache_size; diff --git a/mysql-test/suite/plugins/t/qc_info_init.inc b/mysql-test/suite/plugins/t/qc_info_init.inc new file mode 100644 index 00000000..c69ffa07 --- /dev/null +++ b/mysql-test/suite/plugins/t/qc_info_init.inc @@ -0,0 +1,38 @@ +if (`select count(*) = 0 from information_schema.plugins where plugin_name = 'query_cache_info' and plugin_status='active'`) +{ + --skip QUERY_CACHE_INFO plugin is not active +} + +set global query_cache_type=ON; +set local query_cache_type=ON; +set global query_cache_size=1355776; + +create table t1 (a int not null); +insert into t1 values (1),(2),(3); +--disable_ps2_protocol +select * from t1; +--enable_ps2_protocol +select statement_schema, statement_text, result_blocks_count, result_blocks_size from information_schema.query_cache_info; + +select @@time_zone into @time_zone; +select @@default_week_format into @default_week_format; +select @@character_set_client into @character_set_client; +select @@character_set_results into @character_set_results; +select @@sql_mode into @sql_mode; +select @@div_precision_increment into @div_precision_increment; +select @@lc_time_names into @lc_time_names; +select @@max_sort_length into @max_sort_length; +select @@autocommit into @autocommit; +select @@group_concat_max_len into @group_concat_max_len; +select Name into @new_time_zone from mysql.time_zone_name limit 1; +set time_zone=@new_time_zone,default_week_format=4,character_set_client='binary',character_set_results='utf32',collation_connection='utf32_bin',sql_mode='STRICT_ALL_TABLES',div_precision_increment=7,lc_time_names='ar_SD',autocommit=0, group_concat_max_len=513, max_sort_length=1011; +--disable_result_log +--disable_ps2_protocol +select * from t1; +--enable_ps2_protocol +--enable_result_log +set time_zone= @time_zone, default_week_format= @default_week_format, character_set_client= @character_set_client,character_set_results= @character_set_results, sql_mode= @sql_mode, div_precision_increment= @div_precision_increment, lc_time_names= @lc_time_names, autocommit= @autocommit, group_concat_max_len= @group_concat_max_len, max_sort_length= @max_sort_length; + +--sorted_result +--replace_column 5 # 20 # 24 # +select * from information_schema.query_cache_info; diff --git a/mysql-test/suite/plugins/t/qc_info_init.opt b/mysql-test/suite/plugins/t/qc_info_init.opt new file mode 100644 index 00000000..53b4ff31 --- /dev/null +++ b/mysql-test/suite/plugins/t/qc_info_init.opt @@ -0,0 +1,2 @@ +--loose-query_cache_info +--plugin-load-add=$QUERY_CACHE_INFO_SO diff --git a/mysql-test/suite/plugins/t/qc_info_priv.test b/mysql-test/suite/plugins/t/qc_info_priv.test new file mode 100644 index 00000000..f5052702 --- /dev/null +++ b/mysql-test/suite/plugins/t/qc_info_priv.test @@ -0,0 +1,17 @@ +set @save_query_cache_size=@@global.query_cache_size; +--source include/not_embedded.inc +--source qc_info_init.inc + +# try an unprivileged user +create user mysqltest; +grant select on test.* to mysqltest; +connect (conn1,localhost,mysqltest,,); +connection conn1; +select a from t1; +select count(*) from information_schema.query_cache_info; +connection default; +drop user mysqltest; +drop table t1; + +set @@global.query_cache_size=@save_query_cache_size; +set global query_cache_type=default; diff --git a/mysql-test/suite/plugins/t/server_audit.opt b/mysql-test/suite/plugins/t/server_audit.opt new file mode 100644 index 00000000..089ed94b --- /dev/null +++ b/mysql-test/suite/plugins/t/server_audit.opt @@ -0,0 +1,2 @@ +--thread_handling='one-thread-per-connection' + diff --git a/mysql-test/suite/plugins/t/server_audit.test b/mysql-test/suite/plugins/t/server_audit.test new file mode 100644 index 00000000..ce4d96d7 --- /dev/null +++ b/mysql-test/suite/plugins/t/server_audit.test @@ -0,0 +1,250 @@ +--source include/have_plugin_auth.inc +--source include/not_embedded.inc + +if (!$SERVER_AUDIT_SO) { + skip No SERVER_AUDIT plugin; +} + +--disable_ps2_protocol +# An unfortunate wait for check-testcase.test to complete disconnect. +let count_sessions= 1; +source include/wait_until_count_sessions.inc; + +let $MYSQLD_DATADIR= `SELECT @@datadir`; +let SEARCH_FILE= $MYSQLD_DATADIR/server_audit.log; + +install plugin server_audit soname 'server_audit'; + +show variables like 'server_audit%'; +set global server_audit_file_path=null; +set global server_audit_incl_users=null; +set global server_audit_file_path='server_audit.log'; +set global server_audit_output_type=file; +set global server_audit_logging=on; + +--error ER_WRONG_VALUE_FOR_VAR +set global server_audit_incl_users= repeat("'root',", 10000); +show variables like 'server_audit_incl_users'; +--error ER_WRONG_VALUE_FOR_VAR +set global server_audit_excl_users= repeat("'root',", 10000); +show variables like 'server_audit_excl_users'; +let SEARCH_COUNT= 5; +source include/wait_for_line_count_in_file.inc; + +connect (con1,localhost,root,,mysql); +disconnect con1; +let SEARCH_COUNT= 7; +source include/wait_for_line_count_in_file.inc; + +--replace_result $MASTER_MYSOCK MASTER_SOCKET $MASTER_MYPORT MASTER_PORT +--error ER_ACCESS_DENIED_ERROR +connect (con1,localhost,no_such_user,,mysql); +let SEARCH_COUNT= 9; +source include/wait_for_line_count_in_file.inc; + +connection default; +set global server_audit_incl_users='odin, dva, tri'; +create table t1 (id int); +set global server_audit_incl_users='odin, root, dva, tri'; +create table t2 (id int); +set global server_audit_excl_users='odin, dva, tri'; +insert into t1 values (1), (2); +select * from t1; +set global server_audit_incl_users='odin, root, dva, tri'; +insert into t2 values (1), (2); +select * from t2; +alter table t1 rename renamed_t1; +set global server_audit_events='connect,query'; +select 1, + 2, +# comment + 3; +insert into t2 values (1), (2); +select * from t2; +--disable_ps_protocol +--error ER_NO_SUCH_TABLE +select * from t_doesnt_exist; +--enable_ps_protocol +--error 1064 +syntax_error_query; +drop table renamed_t1, t2; +show variables like 'server_audit%'; +set global server_audit_mode=1; +set global server_audit_events=''; +create database sa_db; +let SEARCH_COUNT= 47; +source include/wait_for_line_count_in_file.inc; + +connect (con1,localhost,root,,test); +create table t1 (id2 int); +insert into t1 values (1), (2); +select * from t1; +drop table t1; +use sa_db; +create table sa_t1(id int); +insert into sa_t1 values (1), (2); +drop table sa_t1; +drop database sa_db; +disconnect con1; +let SEARCH_COUNT= 80; +source include/wait_for_line_count_in_file.inc; + +connection default; +create database sa_db; +use sa_db; +CREATE USER u1 IDENTIFIED BY 'pwd-123'; +GRANT ALL ON sa_db TO u2 IDENTIFIED BY "pwd-321"; +SET PASSWORD FOR u1 = PASSWORD('pwd 098'); +CREATE USER u3 IDENTIFIED BY ''; +ALTER USER u3 IDENTIFIED BY 'pwd-456'; +drop user u1, u2, u3; + +set global server_audit_events='query_ddl'; +create table t1(id int); +insert into t1 values (1), (2); +select * from t1; +select 2; +(select 2); +/*! select 2*/; +/*comment*/ select 2; +drop table t1; + +create procedure pr1() insert into test.t1 values ("foo", 42); +create function fn1(i int) returns int deterministic return i+1; +drop procedure pr1; +drop function fn1; + +set global server_audit_events='query_ddl,query_dml'; +create table t1(id int); +insert into t1 values (1), (2); +select * from t1; +select 2; +drop table t1; +set global server_audit_events='query_dml'; +create table t1(id int); +insert into t1 values (1), (2); +select * from t1; +select 2; +(select 2); +/*! select 2*/; +/*comment*/ select 2; +drop table t1; +set global server_audit_events='query_dcl'; +create table t1(id int); +insert into t1 values (1), (2); +select * from t1; +CREATE USER u1 IDENTIFIED BY 'pwd-123'; +GRANT ALL ON sa_db TO u2 IDENTIFIED BY "pwd-321"; +SET PASSWORD +# comment +FOR u1 = PASSWORD('pwd 098'); +--error 1064 +SET PASSWORD FOR u1=<secret>; +CREATE USER u3 IDENTIFIED BY ''; +drop user u1, u2, u3; +select 2; +(select 2); +/*! select 2*/; +/*comment*/ select 2; +drop table t1; +set global server_audit_events='query_dml_no_select'; +create table t1(id int); +insert into t1 values (1), (2); +select * from t1; +select 2; +drop table t1; +create procedure pr1() insert into test.t1 values ("foo", 42); +create function fn1(i int) returns int deterministic return i+1; +drop procedure pr1; +drop function fn1; + +create procedure pr1() insert into test.t1 values ("foo", 42); +create function fn1(i int) returns int deterministic return i+1; +drop procedure pr1; +drop function fn1; + +set global server_audit_events='table'; +set global server_audit_incl_users='user1'; + +create user user1@localhost; +grant all on sa_db.* to user1@localhost; + +connect (cn1,localhost,user1,,sa_db); +connection cn1; + +create table t1(id int) engine=myisam; +insert delayed into t1 values (1); +connection default; +--echo # Waiting until INSERT DELAYED thread does the insert. +let $wait_condition= SELECT COUNT(*) = 1 FROM t1; +--source include/wait_condition.inc +drop table t1; + +set global server_audit_logging= off; +set global server_audit_incl_users='root'; +set global server_audit_logging= on; +disconnect cn1; +let $count_sessions=1; +source include/wait_until_count_sessions.inc; + +drop user user1@localhost; + +set global server_audit_events=''; +set global server_audit_incl_users='root, plug_dest'; + +CREATE USER plug IDENTIFIED WITH 'test_plugin_server' AS 'plug_dest'; +CREATE USER plug_dest IDENTIFIED BY 'plug_dest_passwd'; +--sleep 2 +--replace_result $MASTER_MYPORT MYSQL_PORT $MASTER_MYSOCK MYSQL_SOCK +--error ER_ACCESS_DENIED_ERROR : this should fail : no grant +connect(plug_con,localhost,plug,plug_dest); +--sleep 2 +GRANT PROXY ON plug_dest TO plug; +--sleep 2 +connect(plug_con,localhost,plug,plug_dest,"*NO-ONE*"); +connection plug_con; +select USER(),CURRENT_USER(); +connection default; +disconnect plug_con; +--sleep 2 +--sleep 2 +DROP USER plug; +DROP USER plug_dest; + +set global server_audit_query_log_limit= 15; +select (1), (2), (3), (4); +select 'A', 'B', 'C', 'D'; +set global server_audit_query_log_limit= 1024; +drop database sa_db; + +select length('01234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789'); + +CREATE TABLE test.t1 (a char(4)); +set sql_mode=""; +insert into test.t1 value("12345"); +set sql_mode=default; +drop table test.t1; + +set global server_audit_file_path='.'; +--replace_regex /\.[\\\/]/HOME_DIR\// +show status like 'server_audit_current_log'; +set global server_audit_file_path=''; +show status like 'server_audit_current_log'; +set global server_audit_file_path=' '; +show status like 'server_audit_current_log'; +set global server_audit_file_path='nonexisting_dir/'; +show status like 'server_audit_current_log'; +show variables like 'server_audit%'; +uninstall plugin server_audit; + +# replace the timestamp and the hostname with constant values +--replace_regex /[0-9]* [0-9][0-9]:[0-9][0-9]:[0-9][0-9]\,[^,]*\,/TIME,HOSTNAME,/ /\,[1-9][0-9]*\,/,1,/ /\,[1-9][0-9]*/,ID/ +cat_file $MYSQLD_DATADIR/server_audit.log; +remove_file $MYSQLD_DATADIR/server_audit.log; +--enable_ps2_protocol + +--echo # +--echo # MDEV-27631 Assertion `global_status_var.global_memory_used == 0' failed in mysqld_exit +--echo # +install plugin server_audit soname 'server_audit'; +uninstall plugin server_audit; diff --git a/mysql-test/suite/plugins/t/show_all_plugins.test b/mysql-test/suite/plugins/t/show_all_plugins.test new file mode 100644 index 00000000..171bcc93 --- /dev/null +++ b/mysql-test/suite/plugins/t/show_all_plugins.test @@ -0,0 +1,32 @@ +--source include/have_udf.inc +if (!$DIALOG_EXAMPLES_SO) { skip requires dialog_examples.so; } +if (!$HA_EXAMPLE_SO) { skip requires ha_examples.so; } +if (!$LIBDAEMON_EXAMPLE_SO) { skip requires libdaemon_examples.so; } +if (!$EXAMPLE_KEY_MANAGEMENT_SO) { skip requires example_key_management.so; } +if (`SELECT VERSION() LIKE '%embedded%'`) { skip Disabled for embedded until MDEV-8664 is resolved; } + +flush status; +show status like '%libraries%'; +#--sorted_result +#select * from information_schema.all_plugins; +#show status like '%libraries%'; +--replace_result .dll .so +--replace_column 5 MYSQL_VERSION_ID +--disable_ps2_protocol +eval select * from information_schema.all_plugins where plugin_library='$HA_EXAMPLE_SO'; +--enable_ps2_protocol +show status like '%libraries%'; +--sorted_result +--replace_result .dll .so +eval show plugins soname '$HA_EXAMPLE_SO'; +show status like '%libraries%'; +--sorted_result +--replace_result .dll .so +show plugins soname like '%example%'; +--replace_result 8 7 +show status like '%libraries%'; +--sorted_result +--replace_result .dll .so +eval show plugins soname where library = '$HA_EXAMPLE_SO'; +select variable_value > 10 from information_schema.global_status where variable_name like '%libraries%'; + diff --git a/mysql-test/suite/plugins/t/simple_password_check.test b/mysql-test/suite/plugins/t/simple_password_check.test new file mode 100644 index 00000000..442585e8 --- /dev/null +++ b/mysql-test/suite/plugins/t/simple_password_check.test @@ -0,0 +1,170 @@ +--source include/not_embedded.inc + +if (!$SIMPLE_PASSWORD_CHECK_SO) { + skip No SIMPLE_PASSWORD_CHECK plugin; +} + +install soname "simple_password_check"; + +--vertical_results +--replace_result .dll .so +select * from information_schema.plugins where plugin_name='simple_password_check'; + +select * from information_schema.system_variables where variable_name like 'simple_password_check%' order by 1; +--horizontal_results + +--error ER_NOT_VALID_PASSWORD +create user foo1 identified by 'pwd'; +show warnings; + +# Create user with no password. +--error ER_NOT_VALID_PASSWORD +create user foo1; +show warnings; + +--error ER_NOT_VALID_PASSWORD +grant select on *.* to foo1 identified by 'pwd'; +show warnings; + +--error ER_NOT_VALID_PASSWORD +grant select on *.* to `FooBar1!` identified by 'FooBar1!'; +show warnings; + +grant select on *.* to `BarFoo1!` identified by 'FooBar1!'; +drop user `BarFoo1!`; + +create user foo1 identified by 'aA.12345'; +grant select on *.* to foo1; +drop user foo1; + +set global simple_password_check_digits=3; +set global simple_password_check_letters_same_case=3; +set global simple_password_check_other_characters=3; +show variables like 'simple_password_check_%'; + +create user foo1 identified by '123:qwe:ASD!'; +drop user foo1; + +--error ER_NOT_VALID_PASSWORD +create user foo1 identified by '-23:qwe:ASD!'; +show warnings; + +--error ER_NOT_VALID_PASSWORD +create user foo1 identified by '123:4we:ASD!'; +show warnings; + +--error ER_NOT_VALID_PASSWORD +create user foo1 identified by '123:qwe:4SD!'; +show warnings; + +--error ER_NOT_VALID_PASSWORD +create user foo1 identified by '123:qwe:ASD4'; +show warnings; + +create user foo1 identified by '123:qwe:ASD!'; +--error ER_NOT_VALID_PASSWORD +set password for foo1 = password('qwe:-23:ASD!'); +show warnings; +--error ER_NOT_VALID_PASSWORD +set password for foo1 = old_password('4we:123:ASD!'); +--error ER_NOT_VALID_PASSWORD +set password for foo1 = password('qwe:123:4SD!'); +show warnings; +--error ER_NOT_VALID_PASSWORD +set password for foo1 = old_password('qwe:123:ASD4'); +show warnings; +set password for foo1 = password('qwe:123:ASD!'); + +# now, strict_password_validation +select @@strict_password_validation; + +--error ER_NOT_VALID_PASSWORD +set password for foo1 = ''; +show warnings; +--error ER_OPTION_PREVENTS_STATEMENT +set password for foo1 = '2222222222222222'; +--error ER_OPTION_PREVENTS_STATEMENT +set password for foo1 = '11111111111111111111111111111111111111111'; +--error ER_OPTION_PREVENTS_STATEMENT +create user foo2 identified by password '11111111111111111111111111111111111111111'; +--error ER_OPTION_PREVENTS_STATEMENT +grant select on *.* to foo2 identified by password '2222222222222222'; +--error ER_OPTION_PREVENTS_STATEMENT +create user foo2 identified with mysql_native_password using '11111111111111111111111111111111111111111'; +--error ER_OPTION_PREVENTS_STATEMENT +grant select on *.* to foo2 identified with mysql_old_password using '2222222222222222'; +--error ER_NOT_VALID_PASSWORD +create user foo2 identified with mysql_native_password using ''; +show warnings; +--error ER_NOT_VALID_PASSWORD +grant select on *.* to foo2 identified with mysql_old_password using ''; +--error ER_NOT_VALID_PASSWORD +grant select on *.* to foo2 identified with mysql_old_password; + +# direct updates are not protected +update mysql.global_priv set priv=json_set(priv, '$.authentication_string', 'xxx') where user='foo1'; + +set global strict_password_validation=0; + +--error ER_NOT_VALID_PASSWORD +set password for foo1 = ''; +show warnings; +set password for foo1 = '2222222222222222'; +set password for foo1 = '11111111111111111111111111111111111111111'; +create user foo2 identified by password '11111111111111111111111111111111111111111'; +drop user foo2; +grant select on *.* to foo2 identified by password '2222222222222222'; +drop user foo2; +create user foo2 identified with mysql_native_password using '11111111111111111111111111111111111111111'; +drop user foo2; +grant select on *.* to foo2 identified with mysql_old_password using '2222222222222222'; +drop user foo2; + +set global strict_password_validation=1; +drop user foo1; + +# +# MDEV-9940 CREATE ROLE blocked by password validation plugin +# +create role r1; +drop role r1; + +flush privileges; + +uninstall plugin simple_password_check; + +create user foo1 identified by 'pwd'; +drop user foo1; + +--echo # +--echo # MDEV-26650: Failed ALTER USER/GRANT statement removes the +--echo # password from the cache +--echo # +create user foo1@localhost identified by '<GDFH:3ghj'; +show grants for foo1@localhost; +install soname "simple_password_check"; +--error ER_CANNOT_USER +ALTER USER foo1@localhost identified by 'foo1'; +show grants for foo1@localhost; +flush privileges; +show grants for foo1@localhost; +drop user foo1@localhost; +uninstall plugin simple_password_check; + + +--echo # +--echo # MDEV-22418 mysqladmin wrong error with simple_password_check +--echo # + +install soname "simple_password_check"; + +--replace_regex /.*[\/\\]// /(mysqladmin|mariadb-admin)(\.exe)?/MARIADB-ADMIN/ +--error 1 +--exec $MYSQLADMIN -uroot password foo 2>&1 + +--echo # All done +uninstall plugin simple_password_check; + +--echo # +--echo # End of 10.4 tests +--echo # diff --git a/mysql-test/suite/plugins/t/sql_error_log.test b/mysql-test/suite/plugins/t/sql_error_log.test new file mode 100644 index 00000000..03d49403 --- /dev/null +++ b/mysql-test/suite/plugins/t/sql_error_log.test @@ -0,0 +1,80 @@ +--source include/not_embedded.inc + +if (!$SQL_ERRLOG_SO) { + skip No SQL_ERROR_LOG plugin; +} + +--disable_warnings +drop procedure if exists test_error; +drop table if exists t1; +--enable_warnings + +install plugin SQL_ERROR_LOG soname 'sql_errlog'; + +show variables like 'sql_error_log%'; +set global sql_error_log_rate=1; +--error ER_NO_SUCH_TABLE +select * from t_doesnt_exist; +--error 1064 +syntax_error_query; + +delimiter |; + +CREATE PROCEDURE test_error() +BEGIN +DECLARE CONTINUE HANDLER +FOR 1146 +BEGIN +RESIGNAL SQLSTATE '40000' SET +MYSQL_ERRNO = 1000, +MESSAGE_TEXT = 'new message'; +END; +SELECT `c` FROM `temptab`; +END| + +delimiter ;| + +--error 1000 +CALL test_error(); +drop procedure test_error; + +SET SQL_MODE = STRICT_ALL_TABLES; +create table t1(id int); +--error 1366 +insert into t1 values ('aa'); +SET SQL_MODE = ''; +drop table t1; + +SELECT TABLE_NAME FROM information_schema.TABLES WHERE TABLE_SCHEMA = 'not_exists' AND TABLE_NAME = 'not_exists'; + +# +# MDEV-6421 SQL_ERROR_LOG doesn't log comments in Events +# actually testing SP call is enough for that. + +DELIMITER |; + +CREATE procedure e1() +BEGIN + START TRANSACTION; + INSERT INTO test.non_exists VALUES (0,0,0) /* e1 */; + COMMIT; +END| + +DELIMITER ;| +--error ER_NO_SUCH_TABLE +CALL e1(); +DROP PROCEDURE e1; + +CREATE TABLE t1 (a char(4)); +set sql_mode=""; +insert into t1 value("12345"); +set sql_mode=default; +drop table t1; + +uninstall plugin SQL_ERROR_LOG; + +let $MYSQLD_DATADIR= `SELECT @@datadir`; +# replace the timestamp and the hostname with constant values +--replace_regex /[1-9][0-9][0-9][0-9]-[0-9][0-9]-[0-9][0-9] [ 0-9][0-9]:[0-9][0-9]:[0-9][0-9] [^EW]*/TIME HOSTNAME / +cat_file $MYSQLD_DATADIR/sql_errors.log; +remove_file $MYSQLD_DATADIR/sql_errors.log; diff --git a/mysql-test/suite/plugins/t/test_sql_service.opt b/mysql-test/suite/plugins/t/test_sql_service.opt new file mode 100644 index 00000000..089ed94b --- /dev/null +++ b/mysql-test/suite/plugins/t/test_sql_service.opt @@ -0,0 +1,2 @@ +--thread_handling='one-thread-per-connection' + diff --git a/mysql-test/suite/plugins/t/test_sql_service.test b/mysql-test/suite/plugins/t/test_sql_service.test new file mode 100644 index 00000000..2f53c1d5 --- /dev/null +++ b/mysql-test/suite/plugins/t/test_sql_service.test @@ -0,0 +1,83 @@ +--source include/not_embedded.inc +--source include/have_log_bin.inc + +reset master; # clear binlogs + +if (!$TEST_SQL_SERVICE_SO) { + skip No TEST_SQL_SERVICE plugin; +} + +# An unfortunate wait for check-testcase.test to complete disconnect. +let count_sessions= 1; +source include/wait_until_count_sessions.inc; + +install plugin test_sql_service soname 'test_sql_service'; +show status like 'test_sql_service_passed'; + +set global test_sql_service_execute_sql_global= 'create table test.t1 select 1 as a, @@SQL_LOG_BIN'; +set global test_sql_service_execute_sql_local= 'insert into test.t1 select 2 as a, @@SQL_LOG_BIN'; +set global test_sql_service_execute_sql_global= 'SET SQL_LOG_BIN=1'; +set global test_sql_service_execute_sql_global= 'insert into test.t1 select 3 as a, @@SQL_LOG_BIN'; +set global test_sql_service_execute_sql_global= 'SET SQL_LOG_BIN=0'; +set global test_sql_service_execute_sql_global= 'insert into test.t1 select 4 as a, @@SQL_LOG_BIN'; +set global test_sql_service_execute_sql_global= 'SET sql_auto_is_null=1'; +set global test_sql_service_execute_sql_global= 'insert into test.t1 select 5 as a, @@sql_auto_is_null'; +set global test_sql_service_execute_sql_global= 'SET sql_auto_is_null=0'; +set global test_sql_service_execute_sql_global= 'insert into test.t1 select 6 as a, @@sql_auto_is_null'; +select * from t1 order by a; +drop table t1; +SET SQL_LOG_BIN=0; + +set global test_sql_service_run_test= 1; +show status like 'test_sql_service_passed'; + +set global test_sql_service_execute_sql_local= 'create table test.t1(id int)'; +show status like 'test_sql_query_result'; + +set global test_sql_service_execute_sql_local= 'insert into test.t1 values (1), (2)'; +show status like 'test_sql_query_result'; + +set global test_sql_service_execute_sql_local= 'select * from test.t1'; +show status like 'test_sql_query_result'; + +set global test_sql_service_execute_sql_local= 'drop table test.t1'; +show status like 'test_sql_query_result'; + +set global test_sql_service_execute_sql_local= 'drop table test.t1'; +show status like 'test_sql_query_result'; + +set global test_sql_service_execute_sql_global= 'create table test.t1(id int)'; +show status like 'test_sql_query_result'; + +set global test_sql_service_execute_sql_global= 'insert into test.t1 values (1), (2)'; +show status like 'test_sql_query_result'; + +set global test_sql_service_execute_sql_global= 'select * from test.t1'; +show status like 'test_sql_query_result'; + +set global test_sql_service_execute_sql_global= 'drop table test.t1'; +show status like 'test_sql_query_result'; + +set global test_sql_service_execute_sql_global= 'drop table test.t1'; +show status like 'test_sql_query_result'; + +create table t1 (id int, time timestamp not null default now()); +insert into t1 values (1, NULL), (2, NULL), (3, NULL), (4, NULL), (5, NULL); +set global test_sql_service_execute_sql_global= 'select * FROM test.t1 WHERE time < DATE_SUB(NOW(), interval 5 minute)'; +show status like 'test_sql_query_result'; +set global test_sql_service_execute_sql_global= 'select * FROM test.t1 WHERE time <= NOW()'; +show status like 'test_sql_query_result'; +set global test_sql_service_execute_sql_local= 'select * FROM test.t1 WHERE time < DATE_SUB(NOW(), interval 5 minute)'; +show status like 'test_sql_query_result'; +set global test_sql_service_execute_sql_local= 'select * FROM test.t1 WHERE time <= NOW()'; +show status like 'test_sql_query_result'; +drop table t1; + +uninstall plugin test_sql_service; + +# Check that statements were executed/binlogged in correct order. +source include/show_binlog_events.inc; +# --replace_column 2 # 5 # +# --replace_regex /xid=[0-9]+/xid=XX/ /GTID [0-9]+-[0-9]+-[0-9]+/GTID #-#-#/ +# SHOW BINLOG EVENTS LIMIT 3,100; + diff --git a/mysql-test/suite/plugins/t/thread_pool_server_audit.opt b/mysql-test/suite/plugins/t/thread_pool_server_audit.opt new file mode 100644 index 00000000..30953d0c --- /dev/null +++ b/mysql-test/suite/plugins/t/thread_pool_server_audit.opt @@ -0,0 +1,2 @@ +--thread_handling=pool-of-threads + diff --git a/mysql-test/suite/plugins/t/thread_pool_server_audit.test b/mysql-test/suite/plugins/t/thread_pool_server_audit.test new file mode 100644 index 00000000..c12ef29f --- /dev/null +++ b/mysql-test/suite/plugins/t/thread_pool_server_audit.test @@ -0,0 +1,151 @@ +--source include/not_embedded.inc +--source include/have_pool_of_threads.inc + +if (!$SERVER_AUDIT_SO) { + skip No SERVER_AUDIT plugin; +} + +install plugin server_audit soname 'server_audit'; + +--disable_ps2_protocol +show variables like 'server_audit%'; +set global server_audit_file_path=null; +set global server_audit_incl_users=null; +set global server_audit_file_path='server_audit.log'; +set global server_audit_output_type=file; +set global server_audit_logging=on; +--sleep 2 +connect (con1,localhost,root,,mysql); +connection default; +disconnect con1; +--sleep 2 +--sleep 2 +--replace_result $MASTER_MYSOCK MASTER_SOCKET $MASTER_MYPORT MASTER_PORT +--error ER_ACCESS_DENIED_ERROR +connect (con1,localhost,no_such_user,,mysql); +connection default; +--sleep 2 +set global server_audit_incl_users='odin, dva, tri'; +create table t1 (id int); +set global server_audit_incl_users='odin, root, dva, tri'; +create table t2 (id int); +set global server_audit_excl_users='odin, dva, tri'; +insert into t1 values (1), (2); +select * from t1; +set global server_audit_incl_users='odin, root, dva, tri'; +insert into t2 values (1), (2); +select * from t2; +alter table t1 rename renamed_t1; +set global server_audit_events='connect,query'; +select 1, + 2, +# comment + 3; +insert into t2 values (1), (2); +select * from t2; +--disable_ps_protocol +--error ER_NO_SUCH_TABLE +select * from t_doesnt_exist; +--enable_ps_protocol +--error 1064 +syntax_error_query; +drop table renamed_t1, t2; +show variables like 'server_audit%'; +set global server_audit_mode=1; +set global server_audit_events=''; +create database sa_db; +--sleep 2 +connect (con1,localhost,root,,test); +connection con1; +--sleep 2 +--sleep 2 +create table t1 (id2 int); +insert into t1 values (1), (2); +select * from t1; +drop table t1; +use sa_db; +create table sa_t1(id int); +insert into sa_t1 values (1), (2); +drop table sa_t1; +drop database sa_db; +connection default; +disconnect con1; +--sleep 2 +--sleep 2 +create database sa_db; +use sa_db; +CREATE USER u1 IDENTIFIED BY 'pwd-123'; +GRANT ALL ON sa_db TO u2 IDENTIFIED BY "pwd-321"; +SET PASSWORD FOR u1 = PASSWORD('pwd 098'); +CREATE USER u3 IDENTIFIED BY ''; +drop user u1, u2, u3; + +set global server_audit_events='query_ddl'; +create table t1(id int); +insert into t1 values (1), (2); +select * from t1; +select 2; +(select 2); +/*! select 2*/; +/*comment*/ select 2; +drop table t1; +set global server_audit_events='query_ddl,query_dml'; +create table t1(id int); +insert into t1 values (1), (2); +select * from t1; +select 2; +drop table t1; +set global server_audit_events='query_dml'; +create table t1(id int); +insert into t1 values (1), (2); +select * from t1; +select 2; +(select 2); +/*! select 2*/; +/*comment*/ select 2; +drop table t1; +set global server_audit_events='query_dcl'; +create table t1(id int); +insert into t1 values (1), (2); +select * from t1; +CREATE USER u1 IDENTIFIED BY 'pwd-123'; +GRANT ALL ON sa_db TO u2 IDENTIFIED BY "pwd-321"; +SET PASSWORD +# comment +FOR u1 = PASSWORD('pwd 098'); +--error 1064 +SET PASSWORD FOR u1=<secret>; +CREATE USER u3 IDENTIFIED BY ''; +drop user u1, u2, u3; +select 2; +(select 2); +/*! select 2*/; +/*comment*/ select 2; +drop table t1; +set global server_audit_events=''; + +set global server_audit_query_log_limit= 15; +select (1), (2), (3), (4); +select 'A', 'B', 'C', 'D'; +set global server_audit_query_log_limit= 1024; +drop database sa_db; + +set global server_audit_file_path='.'; +--replace_regex /\.[\\\/]/HOME_DIR\// +show status like 'server_audit_current_log'; +set global server_audit_file_path=''; +show status like 'server_audit_current_log'; +set global server_audit_file_path=' '; +show status like 'server_audit_current_log'; +set global server_audit_file_path='nonexisting_dir/'; +show status like 'server_audit_current_log'; +show variables like 'server_audit%'; +uninstall plugin server_audit; +--enable_ps2_protocol + +let $MYSQLD_DATADIR= `SELECT @@datadir`; +# replace the timestamp and the hostname with constant values +--replace_regex /[0-9]* [0-9][0-9]:[0-9][0-9]:[0-9][0-9]\,[^,]*\,/TIME,HOSTNAME,/ /\,[1-9][0-9]*\,/,1,/ /\,[1-9][0-9]*/,ID/ +cat_file $MYSQLD_DATADIR/server_audit.log; +remove_file $MYSQLD_DATADIR/server_audit.log; + diff --git a/mysql-test/suite/plugins/t/two_password_validations.test b/mysql-test/suite/plugins/t/two_password_validations.test new file mode 100644 index 00000000..89a5c7ae --- /dev/null +++ b/mysql-test/suite/plugins/t/two_password_validations.test @@ -0,0 +1,38 @@ +--source include/not_embedded.inc + +if (!$CRACKLIB_PASSWORD_CHECK_SO) { + skip No CRACKLIB_PASSWORD_CHECK plugin; +} + +if (!$SIMPLE_PASSWORD_CHECK_SO) { + skip No SIMPLE_PASSWORD_CHECK plugin; +} + +let $only_simple=grant select on *.* to Fff_fff1 identified by '1fff_ffF'; +let $only_crack=grant select on *.* to foobar identified by 'q-%^&*rty'; + +install soname "simple_password_check"; + +eval $only_simple; +drop user Fff_fff1; + +install soname "cracklib_password_check"; + +grant select on *.* to foobar identified by 'q$%^&*R1234ty'; +drop user foobar; + +--error ER_NOT_VALID_PASSWORD +eval $only_simple; +show warnings; + +--error ER_NOT_VALID_PASSWORD +eval $only_crack; +show warnings; + +uninstall plugin simple_password_check; + +eval $only_crack; +drop user foobar; + +uninstall plugin cracklib_password_check; + diff --git a/mysql-test/suite/plugins/t/unix_socket.test b/mysql-test/suite/plugins/t/unix_socket.test new file mode 100644 index 00000000..892b6f75 --- /dev/null +++ b/mysql-test/suite/plugins/t/unix_socket.test @@ -0,0 +1,56 @@ +--source include/have_unix_socket.inc + +--echo # +--echo # with named user +--echo # + +--let $replace=create user '$USER' +--let $greplace=grant select on test.* to '$USER' +--replace_result $replace "create user 'USER'" +eval create user '$USER' identified via unix_socket; +--replace_result $greplace "grant select on test.* to 'USER'" +eval grant select on test.* to '$USER'; + +--write_file $MYSQLTEST_VARDIR/tmp/peercred_test.txt +--let $replace1=$USER@localhost +--let $replace2=$USER@% +--replace_result $replace1 "USER@localhost" $replace2 "USER@%" +select user(), current_user(), database(); +EOF + +--echo # +--echo # name match = ok +--echo # +--exec $MYSQL_TEST -u $USER < $MYSQLTEST_VARDIR/tmp/peercred_test.txt + +--echo # +--echo # name does not match = failure +--echo # +--error 1 +--exec $MYSQL_TEST -u foobar < $MYSQLTEST_VARDIR/tmp/peercred_test.txt + +--let $replace=drop user '$USER' +--replace_result $replace "drop user 'USER'" +eval drop user '$USER'; + +--echo # +--echo # and now with anonymous user +--echo # +grant SELECT ON test.* TO '' identified via unix_socket; +--echo # +--echo # name match = ok +--echo # +--exec $MYSQL_TEST -u $USER < $MYSQLTEST_VARDIR/tmp/peercred_test.txt + +--echo # +--echo # name does not match = failure +--echo # +--error 1 +--exec $MYSQL_TEST -u foobar < $MYSQLTEST_VARDIR/tmp/peercred_test.txt + +# restoring mysql.user to the original state. +delete from mysql.user where user=''; +FLUSH PRIVILEGES; +--remove_file $MYSQLTEST_VARDIR/tmp/peercred_test.txt + +delete from mysql.db; |