1--source include/not_ubsan.inc 2--source include/platform.inc 3# 4# MDEV-11340 Allow multiple alternative authentication methods for the same user 5# 6--source include/have_unix_socket.inc 7if (`SELECT '$USER' = 'mysqltest1'`) { 8 skip USER is mysqltest1; 9} 10if (!$AUTH_ED25519_SO) { 11 skip No auth_ed25519 plugin; 12} 13 14--let $plugindir=`SELECT @@global.plugin_dir` 15install soname 'auth_ed25519'; 16 17--let $try_auth=$MYSQL_TEST < $MYSQLTEST_VARDIR/tmp/peercred_test.txt 2>&1 18 19--write_file $MYSQLTEST_VARDIR/tmp/peercred_test.txt 20--let $replace1=$USER@localhost 21--let $replace2=$USER@% 22--replace_result $replace1 "USER@localhost" $replace2 "USER@%" 23select user(), current_user(), database(); 24EOF 25 26--let $creplace=create user '$USER' 27--let $dreplace=drop user '$USER' 28 29# 30# socket,password 31# 32--replace_result $creplace "create user 'USER'" 33eval $creplace identified via unix_socket OR mysql_native_password as password("GOOD"); 34create user mysqltest1 identified via unix_socket OR mysql_native_password as password("good"); 35show create user mysqltest1; 36--echo # name match = ok 37--exec $try_auth -u $USER 38--echo # name does not match, password good = ok 39--exec $try_auth -u mysqltest1 -pgood 40--echo # name does not match, password bad = failure 41--error 1 42--exec $try_auth -u mysqltest1 -pbad 43--replace_result $dreplace "drop user 'USER'" 44eval $dreplace, mysqltest1; 45 46# 47# password,socket 48# 49--replace_result $creplace "create user 'USER'" 50eval $creplace identified via mysql_native_password as password("GOOD") OR unix_socket; 51create user mysqltest1 identified via mysql_native_password as password("good") OR unix_socket; 52show create user mysqltest1; 53--echo # name match = ok 54--exec $try_auth -u $USER 55--echo # name does not match, password good = ok 56--exec $try_auth -u mysqltest1 -pgood 57--echo # name does not match, password bad = failure 58--error 1 59--exec $try_auth -u mysqltest1 -pbad 60--replace_result $dreplace "drop user 'USER'" 61eval $dreplace, mysqltest1; 62 63# 64# socket,ed25519 65# 66--replace_result $creplace "create user 'USER'" 67eval $creplace identified via unix_socket OR ed25519 as password("GOOD"); 68create user mysqltest1 identified via unix_socket OR ed25519 as password("good"); 69show create user mysqltest1; 70--echo # name match = ok 71--exec $try_auth -u $USER 72--echo # name does not match, password good = ok 73--exec $try_auth -u mysqltest1 -pgood 74--echo # name does not match, password bad = failure 75--error 1 76--exec $try_auth -u mysqltest1 -pbad 77--replace_result $dreplace "drop user 'USER'" 78eval $dreplace, mysqltest1; 79 80# 81# ed25519,socket 82# 83--replace_result $creplace "create user 'USER'" 84eval $creplace identified via ed25519 as password("GOOD") OR unix_socket; 85create user mysqltest1 identified via ed25519 as password("good") OR unix_socket; 86show create user mysqltest1; 87--echo # name match = ok 88--exec $try_auth -u $USER 89--echo # name does not match, password good = ok 90--exec $try_auth -u mysqltest1 -pgood 91--echo # name does not match, password bad = failure 92--error 1 93--exec $try_auth -u mysqltest1 -pbad 94--replace_result $dreplace "drop user 'USER'" 95eval $dreplace, mysqltest1; 96 97# 98# ed25519,socket,password 99# 100--replace_result $creplace "create user 'USER'" 101eval $creplace identified via ed25519 as password("GOOD") OR unix_socket OR mysql_native_password as password("works"); 102create user mysqltest1 identified via ed25519 as password("good") OR unix_socket OR mysql_native_password as password("works"); 103show create user mysqltest1; 104--echo # name match = ok 105--exec $try_auth -u $USER 106--echo # name does not match, password good = ok 107--exec $try_auth -u mysqltest1 -pgood 108--echo # name does not match, second password works = ok 109--exec $try_auth -u mysqltest1 -pworks 110--echo # name does not match, password bad = failure 111--error 1 112--exec $try_auth -u mysqltest1 -pbad 113--replace_result $dreplace "drop user 'USER'" 114eval $dreplace, mysqltest1; 115 116# 117# password,password 118# 119create user mysqltest1 identified via mysql_native_password as password("good") OR mysql_native_password as password("works"); 120show create user mysqltest1; 121--echo # password good = ok 122--exec $try_auth -u mysqltest1 -pgood 123--echo # second password works = ok 124--exec $try_auth -u mysqltest1 -pworks 125--echo # password bad = failure 126--error 1 127--exec $try_auth -u mysqltest1 -pbad 128drop user mysqltest1; 129 130# 131# show grants, flush privileges, set password, alter user 132# 133create user mysqltest1 identified via ed25519 as password("good") OR unix_socket OR mysql_native_password as password("works"); 134show grants for mysqltest1; 135--replace_regex /password_last_changed": [0-9]*/password_last_changed": #/ 136select json_detailed(priv) from mysql.global_priv where user='mysqltest1'; 137select password,plugin,authentication_string from mysql.user where user='mysqltest1'; 138flush privileges; 139show create user mysqltest1; 140set password for mysqltest1 = password('foobar'); 141show create user mysqltest1; 142alter user mysqltest1 identified via unix_socket OR mysql_native_password as password("some"); 143show create user mysqltest1; 144set password for mysqltest1 = password('foobar'); 145show create user mysqltest1; 146alter user mysqltest1 identified via unix_socket; 147--error ER_SET_PASSWORD_AUTH_PLUGIN 148set password for mysqltest1 = password('bla'); 149alter user mysqltest1 identified via mysql_native_password as password("some") or unix_socket; 150show create user mysqltest1; 151drop user mysqltest1; 152 153--source include/switch_to_mysql_user.inc 154--replace_regex /\d{6}/XX.YY.ZZ/ 155--error ER_COL_COUNT_DOESNT_MATCH_PLEASE_UPDATE 156create user mysqltest1 identified via ed25519 as password("good") OR unix_socket OR mysql_native_password as password("works"); 157--source include/switch_to_mysql_global_priv.inc 158 159# 160# invalid password,socket 161# 162--replace_result $creplace "create user 'USER'" 163eval $creplace identified via mysql_native_password as '1234567890123456789012345678901234567890a' OR unix_socket; 164create user mysqltest1 identified via mysql_native_password as '1234567890123456789012345678901234567890a' OR unix_socket; 165update mysql.global_priv set priv=replace(priv, '1234567890123456789012345678901234567890a', 'invalid password'); 166flush privileges; 167show create user mysqltest1; 168--echo # name match = ok 169--exec $try_auth -u $USER 170--echo # name does not match = failure 171--error 1 172--exec $try_auth -u mysqltest1 173--echo # SET PASSWORD helps 174set password for mysqltest1 = password('bla'); 175--exec $try_auth -u mysqltest1 -pbla 176--replace_result $dreplace "drop user 'USER'" 177eval $dreplace, mysqltest1; 178 179# 180# missing client-side plugin 181# 182create user mysqltest1 identified via ed25519 as password("good"); 183show create user mysqltest1; 184--echo # no plugin = failure 185--replace_result $plugindir <PLUGINDIR> 186--error 1 187--exec $try_auth -u mysqltest1 -pgood --plugin-dir=$plugindir/no 188alter user mysqltest1 identified via ed25519 as password("good") OR mysql_native_password as password("works"); 189show create user mysqltest1; 190--echo # no plugin = failure 191--error 1 192--exec $try_auth -u mysqltest1 -pgood --plugin-dir=$plugindir/no 193--echo # no plugin, second password works = ok 194--exec $try_auth -u mysqltest1 -pworks --plugin-dir=$plugindir/no 195drop user mysqltest1; 196 197uninstall soname 'auth_ed25519'; 198--remove_file $MYSQLTEST_VARDIR/tmp/peercred_test.txt 199 200# 201# MDEV-21928 ALTER USER doesn't remove excess authentication plugins from mysql.global_priv 202# 203create user mysqltest1 identified via mysql_native_password as password("good") OR unix_socket; 204show create user mysqltest1; 205alter user mysqltest1 identified via mysql_native_password as password("better"); 206show create user mysqltest1; 207flush privileges; 208show create user mysqltest1; 209drop user mysqltest1; 210