Merge branch '10.6' into 10.7

6 files changed, 118 insertions, 27 deletions

diff --git a/mysql-test/suite/plugins/r/locales.result b/mysql-test/suite/plugins/r/locales.result
index 106bf22923c..2ea88493705 100644
--- a/mysql-test/suite/plugins/r/locales.result
+++ b/mysql-test/suite/plugins/r/locales.result
@@ -17,7 +17,7 @@ ID	NAME	DESCRIPTION	MAX_MONTH_NAME_LENGTH	MAX_DAY_NAME_LENGTH	DECIMAL_POINT	THOU
 14	cs_CZ	Czech - Czech Republic	8	7	,	 	czech
 15	da_DK	Danish - Denmark	9	7	,	.	danish
 16	de_AT	German - Austria	9	10	,		german
-17	es_ES	Spanish - Spain	10	9	,		spanish
+17	es_ES	Spanish - Spain	10	9	,	.	spanish
 18	et_EE	Estonian - Estonia	9	9	,	 	estonian
 19	eu_ES	Basque - Basque	9	10	,		english
 20	fi_FI	Finnish - Finland	9	11	,	 	english
@@ -82,24 +82,24 @@ ID	NAME	DESCRIPTION	MAX_MONTH_NAME_LENGTH	MAX_DAY_NAME_LENGTH	DECIMAL_POINT	THOU
 79	en_ZA	English - South Africa	9	9	.	,	english
 80	en_ZW	English - Zimbabwe	9	9	.	,	english
 81	es_AR	Spanish - Argentina	10	9	,	.	spanish
-82	es_BO	Spanish - Bolivia	10	9	,		spanish
-83	es_CL	Spanish - Chile	10	9	,		spanish
-84	es_CO	Spanish - Columbia	10	9	,		spanish
-85	es_CR	Spanish - Costa Rica	10	9	.		spanish
-86	es_DO	Spanish - Dominican Republic	10	9	.		spanish
-87	es_EC	Spanish - Ecuador	10	9	,		spanish
-88	es_GT	Spanish - Guatemala	10	9	.		spanish
-89	es_HN	Spanish - Honduras	10	9	.		spanish
-90	es_MX	Spanish - Mexico	10	9	.		spanish
-91	es_NI	Spanish - Nicaragua	10	9	.		spanish
-92	es_PA	Spanish - Panama	10	9	.		spanish
-93	es_PE	Spanish - Peru	10	9	.		spanish
-94	es_PR	Spanish - Puerto Rico	10	9	.		spanish
-95	es_PY	Spanish - Paraguay	10	9	,		spanish
-96	es_SV	Spanish - El Salvador	10	9	.		spanish
+82	es_BO	Spanish - Bolivia	10	9	,	.	spanish
+83	es_CL	Spanish - Chile	10	9	,	.	spanish
+84	es_CO	Spanish - Columbia	10	9	,	.	spanish
+85	es_CR	Spanish - Costa Rica	10	9	,	 	spanish
+86	es_DO	Spanish - Dominican Republic	10	9	.	,	spanish
+87	es_EC	Spanish - Ecuador	10	9	,	.	spanish
+88	es_GT	Spanish - Guatemala	10	9	.	,	spanish
+89	es_HN	Spanish - Honduras	10	9	.	,	spanish
+90	es_MX	Spanish - Mexico	10	9	.	,	spanish
+91	es_NI	Spanish - Nicaragua	10	9	.	,	spanish
+92	es_PA	Spanish - Panama	10	9	.	,	spanish
+93	es_PE	Spanish - Peru	10	9	.	,	spanish
+94	es_PR	Spanish - Puerto Rico	10	9	.	,	spanish
+95	es_PY	Spanish - Paraguay	10	9	,	.	spanish
+96	es_SV	Spanish - El Salvador	10	9	.	,	spanish
 97	es_US	Spanish - United States	10	9	.	,	spanish
-98	es_UY	Spanish - Uruguay	10	9	,		spanish
-99	es_VE	Spanish - Venezuela	10	9	,		spanish
+98	es_UY	Spanish - Uruguay	10	9	,	.	spanish
+99	es_VE	Spanish - Venezuela	10	9	,	.	spanish
 100	fr_BE	French - Belgium	9	8	,	.	french
 101	fr_CA	French - Canada	9	8	,	 	french
 102	fr_CH	French - Switzerland	9	8	,		french
diff --git a/mysql-test/suite/plugins/r/pam.result b/mysql-test/suite/plugins/r/pam.result
index 40075245d0c..b8588916169 100644
--- a/mysql-test/suite/plugins/r/pam.result
+++ b/mysql-test/suite/plugins/r/pam.result
@@ -40,6 +40,35 @@ test_pam@localhost	pam_test@%	test
 #
 Now, the magic number!
 PIN: 9212
+#
+# MDEV-26339 Account specifics to be handled before proxying
+#
+alter user pam_test account lock;
+alter user pam_test require subject 'foobar';
+alter user pam_test password expire;
+Now, the magic number!
+PIN: 9212
+select user(), current_user(), database();
+user()	current_user()	database()
+test_pam@localhost	pam_test@%	test
+alter user pam_test account unlock;
+alter user pam_test require none;
+alter user pam_test identified by '';
+show create user pam_test;
+CREATE USER for pam_test@%
+CREATE USER `pam_test`@`%`
+alter user test_pam account lock;
+Now, the magic number!
+PIN: 9212
+alter user test_pam account unlock;
+alter user test_pam require subject 'foobar';
+Now, the magic number!
+PIN: 9212
+alter user test_pam require none;
+alter user test_pam password expire;
+Now, the magic number!
+PIN: 9212
+select user(), current_user(), database();
 drop user test_pam;
 drop user pam_test;
 create user PAM_TEST identified via pam using 'mariadb_mtr';
@@ -62,4 +91,21 @@ select user(), current_user(), database();
 user()	current_user()	database()
 PAM_TEST@localhost	PAM_TEST@%	test
 drop user PAM_TEST;
+#
+# MDEV-27341 Use SET PASSWORD to change PAM service
+#
+create user pam_test identified via pam using 'mariadb_mtr';
+Challenge input first.
+Enter: *************************
+Now, the magic number!
+PIN: 9225
+select user(), current_user(), database();
+user()	current_user()	database()
+pam_test@localhost	pam_test@%	test
+set password='foo';
+ERROR HY000: SET PASSWORD is ignored for users authenticating via pam plugin
+show create user;
+CREATE USER for pam_test@%
+CREATE USER `pam_test`@`%` IDENTIFIED VIA pam USING 'mariadb_mtr'
+drop user pam_test;
 uninstall plugin pam;
diff --git a/mysql-test/suite/plugins/r/qc_info.result b/mysql-test/suite/plugins/r/qc_info.result
index 41073359e93..afab7671b5c 100644
--- a/mysql-test/suite/plugins/r/qc_info.result
+++ b/mysql-test/suite/plugins/r/qc_info.result
@@ -27,9 +27,9 @@ set time_zone=@new_time_zone,default_week_format=4,character_set_client='binary'
 select * from t1;
 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;
 select * from information_schema.query_cache_info;
-STATEMENT_SCHEMA	STATEMENT_TEXT	RESULT_BLOCKS_COUNT	RESULT_BLOCKS_SIZE	RESULT_BLOCKS_SIZE_USED	LIMIT	MAX_SORT_LENGTH	GROUP_CONCAT_MAX_LENGTH	CHARACTER_SET_CLIENT	CHARACTER_SET_RESULT	COLLATION	TIMEZONE	DEFAULT_WEEK_FORMAT	DIV_PRECISION_INCREMENT	SQL_MODE	LC_TIME_NAMES	CLIENT_LONG_FLAG	CLIENT_PROTOCOL_41	PROTOCOL_TYPE	MORE_RESULTS_EXISTS	IN_TRANS	AUTOCOMMIT	PACKET_NUMBER	HITS
-test	select * from t1	1	512	#	-1	1011	513	binary	utf32	utf32_bin	Europe/Moscow	4	7	STRICT_ALL_TABLES	ar_SD	1	1	#	0	0	0	#	0
-test	select * from t1	1	512	#	-1	1024	1048576	latin1	latin1	latin1_swedish_ci	SYSTEM	0	4	STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION	en_US	1	1	#	0	0	1	#	0
+STATEMENT_SCHEMA	STATEMENT_TEXT	RESULT_BLOCKS_COUNT	RESULT_BLOCKS_SIZE	RESULT_BLOCKS_SIZE_USED	LIMIT	MAX_SORT_LENGTH	GROUP_CONCAT_MAX_LENGTH	CHARACTER_SET_CLIENT	CHARACTER_SET_RESULT	COLLATION	TIMEZONE	DEFAULT_WEEK_FORMAT	DIV_PRECISION_INCREMENT	SQL_MODE	LC_TIME_NAMES	CLIENT_LONG_FLAG	CLIENT_PROTOCOL_41	CLIENT_EXTENDED_METADATA	PROTOCOL_TYPE	MORE_RESULTS_EXISTS	IN_TRANS	AUTOCOMMIT	PACKET_NUMBER	HITS
+test	select * from t1	1	512	#	-1	1011	513	binary	utf32	utf32_bin	Europe/Moscow	4	7	STRICT_ALL_TABLES	ar_SD	1	1	1	#	0	0	0	#	0
+test	select * from t1	1	512	#	-1	1024	1048576	latin1	latin1	latin1_swedish_ci	SYSTEM	0	4	STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION	en_US	1	1	1	#	0	0	1	#	0
 reset query cache;
 select * from t1;
 a
@@ -49,7 +49,7 @@ select statement_schema, statement_text, result_blocks_count, result_blocks_size
 statement_schema	statement_text	result_blocks_count	result_blocks_size
 set global query_cache_size = 0;
 select * from information_schema.query_cache_info;
-STATEMENT_SCHEMA	STATEMENT_TEXT	RESULT_BLOCKS_COUNT	RESULT_BLOCKS_SIZE	RESULT_BLOCKS_SIZE_USED	LIMIT	MAX_SORT_LENGTH	GROUP_CONCAT_MAX_LENGTH	CHARACTER_SET_CLIENT	CHARACTER_SET_RESULT	COLLATION	TIMEZONE	DEFAULT_WEEK_FORMAT	DIV_PRECISION_INCREMENT	SQL_MODE	LC_TIME_NAMES	CLIENT_LONG_FLAG	CLIENT_PROTOCOL_41	PROTOCOL_TYPE	MORE_RESULTS_EXISTS	IN_TRANS	AUTOCOMMIT	PACKET_NUMBER	HITS
+STATEMENT_SCHEMA	STATEMENT_TEXT	RESULT_BLOCKS_COUNT	RESULT_BLOCKS_SIZE	RESULT_BLOCKS_SIZE_USED	LIMIT	MAX_SORT_LENGTH	GROUP_CONCAT_MAX_LENGTH	CHARACTER_SET_CLIENT	CHARACTER_SET_RESULT	COLLATION	TIMEZONE	DEFAULT_WEEK_FORMAT	DIV_PRECISION_INCREMENT	SQL_MODE	LC_TIME_NAMES	CLIENT_LONG_FLAG	CLIENT_PROTOCOL_41	CLIENT_EXTENDED_METADATA	PROTOCOL_TYPE	MORE_RESULTS_EXISTS	IN_TRANS	AUTOCOMMIT	PACKET_NUMBER	HITS
 set global query_cache_size= default;
 set global query_cache_type=default;
 show query_cache_info;
diff --git a/mysql-test/suite/plugins/r/qc_info_priv.result b/mysql-test/suite/plugins/r/qc_info_priv.result
index 78ea78a33b8..9aa15885563 100644
--- a/mysql-test/suite/plugins/r/qc_info_priv.result
+++ b/mysql-test/suite/plugins/r/qc_info_priv.result
@@ -27,9 +27,9 @@ set time_zone=@new_time_zone,default_week_format=4,character_set_client='binary'
 select * from t1;
 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;
 select * from information_schema.query_cache_info;
-STATEMENT_SCHEMA	STATEMENT_TEXT	RESULT_BLOCKS_COUNT	RESULT_BLOCKS_SIZE	RESULT_BLOCKS_SIZE_USED	LIMIT	MAX_SORT_LENGTH	GROUP_CONCAT_MAX_LENGTH	CHARACTER_SET_CLIENT	CHARACTER_SET_RESULT	COLLATION	TIMEZONE	DEFAULT_WEEK_FORMAT	DIV_PRECISION_INCREMENT	SQL_MODE	LC_TIME_NAMES	CLIENT_LONG_FLAG	CLIENT_PROTOCOL_41	PROTOCOL_TYPE	MORE_RESULTS_EXISTS	IN_TRANS	AUTOCOMMIT	PACKET_NUMBER	HITS
-test	select * from t1	1	512	#	-1	1011	513	binary	utf32	utf32_bin	Europe/Moscow	4	7	STRICT_ALL_TABLES	ar_SD	1	1	#	0	0	0	#	0
-test	select * from t1	1	512	#	-1	1024	1048576	latin1	latin1	latin1_swedish_ci	SYSTEM	0	4	STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION	en_US	1	1	#	0	0	1	#	0
+STATEMENT_SCHEMA	STATEMENT_TEXT	RESULT_BLOCKS_COUNT	RESULT_BLOCKS_SIZE	RESULT_BLOCKS_SIZE_USED	LIMIT	MAX_SORT_LENGTH	GROUP_CONCAT_MAX_LENGTH	CHARACTER_SET_CLIENT	CHARACTER_SET_RESULT	COLLATION	TIMEZONE	DEFAULT_WEEK_FORMAT	DIV_PRECISION_INCREMENT	SQL_MODE	LC_TIME_NAMES	CLIENT_LONG_FLAG	CLIENT_PROTOCOL_41	CLIENT_EXTENDED_METADATA	PROTOCOL_TYPE	MORE_RESULTS_EXISTS	IN_TRANS	AUTOCOMMIT	PACKET_NUMBER	HITS
+test	select * from t1	1	512	#	-1	1011	513	binary	utf32	utf32_bin	Europe/Moscow	4	7	STRICT_ALL_TABLES	ar_SD	1	1	1	#	0	0	0	#	0
+test	select * from t1	1	512	#	-1	1024	1048576	latin1	latin1	latin1_swedish_ci	SYSTEM	0	4	STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION	en_US	1	1	1	#	0	0	1	#	0
 create user mysqltest;
 connect  conn1,localhost,mysqltest,,;
 connection conn1;
diff --git a/mysql-test/suite/plugins/t/pam.test b/mysql-test/suite/plugins/t/pam.test
index 2611f25ee63..dbe108d1efa 100644
--- a/mysql-test/suite/plugins/t/pam.test
+++ b/mysql-test/suite/plugins/t/pam.test
@@ -45,7 +45,6 @@ EOF
 --echo #
 --echo # athentication is successful
 --echo #
---error 0
 --exec $MYSQL_TEST -u test_pam -pgoodpassword < $MYSQLTEST_VARDIR/tmp/pam_good2.txt
 
 --echo #
@@ -54,6 +53,36 @@ EOF
 --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';
@@ -76,6 +105,22 @@ set global pam_winbind_workaround=1;
 --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';
+--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/qc_info_init.inc b/mysql-test/suite/plugins/t/qc_info_init.inc
index c3f700f08d0..ad93e5c1547 100644
--- a/mysql-test/suite/plugins/t/qc_info_init.inc
+++ b/mysql-test/suite/plugins/t/qc_info_init.inc
@@ -30,5 +30,5 @@ select * from t1;
 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 # 19 # 23 #
+--replace_column 5 # 20 # 24 #
 select * from information_schema.query_cache_info;