1install soname 'auth_ed25519'; 2create user 'USER' identified via unix_socket OR mysql_native_password as password("GOOD"); 3create user mysqltest1 identified via unix_socket OR mysql_native_password as password("good"); 4show create user mysqltest1; 5CREATE USER for mysqltest1@% 6CREATE USER `mysqltest1`@`%` IDENTIFIED VIA unix_socket OR mysql_native_password USING '*8409037B3E362D6DAE24C8E667F4D3B66716144E' 7# name match = ok 8select user(), current_user(), database(); 9user() current_user() database() 10USER@localhost USER@% test 11# name does not match, password good = ok 12select user(), current_user(), database(); 13user() current_user() database() 14mysqltest1@localhost mysqltest1@% test 15# name does not match, password bad = failure 16mysqltest: Could not open connection 'default': 1045 Access denied for user 'mysqltest1'@'localhost' (using password: YES) 17drop user 'USER', mysqltest1; 18create user 'USER' identified via mysql_native_password as password("GOOD") OR unix_socket; 19create user mysqltest1 identified via mysql_native_password as password("good") OR unix_socket; 20show create user mysqltest1; 21CREATE USER for mysqltest1@% 22CREATE USER `mysqltest1`@`%` IDENTIFIED VIA mysql_native_password USING '*8409037B3E362D6DAE24C8E667F4D3B66716144E' OR unix_socket 23# name match = ok 24select user(), current_user(), database(); 25user() current_user() database() 26USER@localhost USER@% test 27# name does not match, password good = ok 28select user(), current_user(), database(); 29user() current_user() database() 30mysqltest1@localhost mysqltest1@% test 31# name does not match, password bad = failure 32mysqltest: Could not open connection 'default': 1698 Access denied for user 'mysqltest1'@'localhost' 33drop user 'USER', mysqltest1; 34create user 'USER' identified via unix_socket OR ed25519 as password("GOOD"); 35create user mysqltest1 identified via unix_socket OR ed25519 as password("good"); 36show create user mysqltest1; 37CREATE USER for mysqltest1@% 38CREATE USER `mysqltest1`@`%` IDENTIFIED VIA unix_socket OR ed25519 USING 'F4aF8bw7130VaRbdLCl4f/P/wkjDmgJXwWvpJ5gmsZc' 39# name match = ok 40select user(), current_user(), database(); 41user() current_user() database() 42USER@localhost USER@% test 43# name does not match, password good = ok 44select user(), current_user(), database(); 45user() current_user() database() 46mysqltest1@localhost mysqltest1@% test 47# name does not match, password bad = failure 48mysqltest: Could not open connection 'default': 1045 Access denied for user 'mysqltest1'@'localhost' (using password: YES) 49drop user 'USER', mysqltest1; 50create user 'USER' identified via ed25519 as password("GOOD") OR unix_socket; 51create user mysqltest1 identified via ed25519 as password("good") OR unix_socket; 52show create user mysqltest1; 53CREATE USER for mysqltest1@% 54CREATE USER `mysqltest1`@`%` IDENTIFIED VIA ed25519 USING 'F4aF8bw7130VaRbdLCl4f/P/wkjDmgJXwWvpJ5gmsZc' OR unix_socket 55# name match = ok 56select user(), current_user(), database(); 57user() current_user() database() 58USER@localhost USER@% test 59# name does not match, password good = ok 60select user(), current_user(), database(); 61user() current_user() database() 62mysqltest1@localhost mysqltest1@% test 63# name does not match, password bad = failure 64mysqltest: Could not open connection 'default': 1698 Access denied for user 'mysqltest1'@'localhost' 65drop user 'USER', mysqltest1; 66create user 'USER' identified via ed25519 as password("GOOD") OR unix_socket OR mysql_native_password as password("works"); 67create user mysqltest1 identified via ed25519 as password("good") OR unix_socket OR mysql_native_password as password("works"); 68show create user mysqltest1; 69CREATE USER for mysqltest1@% 70CREATE USER `mysqltest1`@`%` IDENTIFIED VIA ed25519 USING 'F4aF8bw7130VaRbdLCl4f/P/wkjDmgJXwWvpJ5gmsZc' OR unix_socket OR mysql_native_password USING '*7D8C3DF236D9163B6C274A9D47704BC496988460' 71# name match = ok 72select user(), current_user(), database(); 73user() current_user() database() 74USER@localhost USER@% test 75# name does not match, password good = ok 76select user(), current_user(), database(); 77user() current_user() database() 78mysqltest1@localhost mysqltest1@% test 79# name does not match, second password works = ok 80select user(), current_user(), database(); 81user() current_user() database() 82mysqltest1@localhost mysqltest1@% test 83# name does not match, password bad = failure 84mysqltest: Could not open connection 'default': 1045 Access denied for user 'mysqltest1'@'localhost' (using password: YES) 85drop user 'USER', mysqltest1; 86create user mysqltest1 identified via mysql_native_password as password("good") OR mysql_native_password as password("works"); 87show create user mysqltest1; 88CREATE USER for mysqltest1@% 89CREATE USER `mysqltest1`@`%` IDENTIFIED VIA mysql_native_password USING '*8409037B3E362D6DAE24C8E667F4D3B66716144E' OR mysql_native_password USING '*7D8C3DF236D9163B6C274A9D47704BC496988460' 90# password good = ok 91select user(), current_user(), database(); 92user() current_user() database() 93mysqltest1@localhost mysqltest1@% test 94# second password works = ok 95select user(), current_user(), database(); 96user() current_user() database() 97mysqltest1@localhost mysqltest1@% test 98# password bad = failure 99mysqltest: Could not open connection 'default': 1045 Access denied for user 'mysqltest1'@'localhost' (using password: YES) 100drop user mysqltest1; 101create user mysqltest1 identified via ed25519 as password("good") OR unix_socket OR mysql_native_password as password("works"); 102show grants for mysqltest1; 103Grants for mysqltest1@% 104GRANT USAGE ON *.* TO `mysqltest1`@`%` IDENTIFIED VIA ed25519 USING 'F4aF8bw7130VaRbdLCl4f/P/wkjDmgJXwWvpJ5gmsZc' OR unix_socket OR mysql_native_password USING '*7D8C3DF236D9163B6C274A9D47704BC496988460' 105select json_detailed(priv) from mysql.global_priv where user='mysqltest1'; 106json_detailed(priv) 107{ 108 "access": 0, 109 "version_id": VERSION_ID, 110 "plugin": "mysql_native_password", 111 "authentication_string": "*7D8C3DF236D9163B6C274A9D47704BC496988460", 112 "auth_or": 113 [ 114 115 { 116 "plugin": "ed25519", 117 "authentication_string": "F4aF8bw7130VaRbdLCl4f/P/wkjDmgJXwWvpJ5gmsZc" 118 }, 119 120 { 121 "plugin": "unix_socket" 122 }, 123 124 { 125 } 126 ], 127 "password_last_changed": # 128} 129select password,plugin,authentication_string from mysql.user where user='mysqltest1'; 130Password plugin authentication_string 131*7D8C3DF236D9163B6C274A9D47704BC496988460 mysql_native_password *7D8C3DF236D9163B6C274A9D47704BC496988460 132flush privileges; 133show create user mysqltest1; 134CREATE USER for mysqltest1@% 135CREATE USER `mysqltest1`@`%` IDENTIFIED VIA ed25519 USING 'F4aF8bw7130VaRbdLCl4f/P/wkjDmgJXwWvpJ5gmsZc' OR unix_socket OR mysql_native_password USING '*7D8C3DF236D9163B6C274A9D47704BC496988460' 136set password for mysqltest1 = password('foobar'); 137show create user mysqltest1; 138CREATE USER for mysqltest1@% 139CREATE USER `mysqltest1`@`%` IDENTIFIED VIA ed25519 USING 'qv2mG6HWCuy32Slb5xhV4THStewNz2VINVPbgk+XAJ8' OR unix_socket OR mysql_native_password USING '*7D8C3DF236D9163B6C274A9D47704BC496988460' 140alter user mysqltest1 identified via unix_socket OR mysql_native_password as password("some"); 141show create user mysqltest1; 142CREATE USER for mysqltest1@% 143CREATE USER `mysqltest1`@`%` IDENTIFIED VIA unix_socket OR mysql_native_password USING '*BFE3F4604CFD21E6595080A261D92EF0183B5971' 144set password for mysqltest1 = password('foobar'); 145show create user mysqltest1; 146CREATE USER for mysqltest1@% 147CREATE USER `mysqltest1`@`%` IDENTIFIED VIA unix_socket OR mysql_native_password USING '*9B500343BC52E2911172EB52AE5CF4847604C6E5' 148alter user mysqltest1 identified via unix_socket; 149set password for mysqltest1 = password('bla'); 150ERROR HY000: SET PASSWORD is ignored for users authenticating via unix_socket plugin 151alter user mysqltest1 identified via mysql_native_password as password("some") or unix_socket; 152show create user mysqltest1; 153CREATE USER for mysqltest1@% 154CREATE USER `mysqltest1`@`%` IDENTIFIED VIA mysql_native_password USING '*BFE3F4604CFD21E6595080A261D92EF0183B5971' OR unix_socket 155drop user mysqltest1; 156# switching from mysql.global_priv to mysql.user 157create user mysqltest1 identified via ed25519 as password("good") OR unix_socket OR mysql_native_password as password("works"); 158ERROR HY000: Column count of mysql.user is wrong. Expected 3, found 47. Created with MariaDB XX.YY.ZZ, now running XX.YY.ZZ. Please use mariadb-upgrade to fix this error 159# switching back from mysql.user to mysql.global_priv 160create user 'USER' identified via mysql_native_password as '1234567890123456789012345678901234567890a' OR unix_socket; 161create user mysqltest1 identified via mysql_native_password as '1234567890123456789012345678901234567890a' OR unix_socket; 162update mysql.global_priv set priv=replace(priv, '1234567890123456789012345678901234567890a', 'invalid password'); 163flush privileges; 164show create user mysqltest1; 165CREATE USER for mysqltest1@% 166CREATE USER `mysqltest1`@`%` IDENTIFIED VIA mysql_native_password USING 'invalid password' OR unix_socket 167# name match = ok 168select user(), current_user(), database(); 169user() current_user() database() 170USER@localhost USER@% test 171# name does not match = failure 172mysqltest: Could not open connection 'default': 1698 Access denied for user 'mysqltest1'@'localhost' 173# SET PASSWORD helps 174set password for mysqltest1 = password('bla'); 175select user(), current_user(), database(); 176user() current_user() database() 177mysqltest1@localhost mysqltest1@% test 178drop user 'USER', mysqltest1; 179create user mysqltest1 identified via ed25519 as password("good"); 180show create user mysqltest1; 181CREATE USER for mysqltest1@% 182CREATE USER `mysqltest1`@`%` IDENTIFIED VIA ed25519 USING 'F4aF8bw7130VaRbdLCl4f/P/wkjDmgJXwWvpJ5gmsZc' 183# no plugin = failure 184mysqltest: Could not open connection 'default': 1045 Plugin client_ed25519 could not be loaded: <PLUGINDIR>/no/client_ed25519.so: cannot open shared object file: No such file or directory 185alter user mysqltest1 identified via ed25519 as password("good") OR mysql_native_password as password("works"); 186show create user mysqltest1; 187CREATE USER for mysqltest1@% 188CREATE USER `mysqltest1`@`%` IDENTIFIED VIA ed25519 USING 'F4aF8bw7130VaRbdLCl4f/P/wkjDmgJXwWvpJ5gmsZc' OR mysql_native_password USING '*7D8C3DF236D9163B6C274A9D47704BC496988460' 189# no plugin = failure 190mysqltest: Could not open connection 'default': 1045 Access denied for user 'mysqltest1'@'localhost' (using password: YES) 191# no plugin, second password works = ok 192select user(), current_user(), database(); 193user() current_user() database() 194mysqltest1@localhost mysqltest1@% test 195drop user mysqltest1; 196uninstall soname 'auth_ed25519'; 197create user mysqltest1 identified via mysql_native_password as password("good") OR unix_socket; 198show create user mysqltest1; 199CREATE USER for mysqltest1@% 200CREATE USER `mysqltest1`@`%` IDENTIFIED VIA mysql_native_password USING '*8409037B3E362D6DAE24C8E667F4D3B66716144E' OR unix_socket 201alter user mysqltest1 identified via mysql_native_password as password("better"); 202show create user mysqltest1; 203CREATE USER for mysqltest1@% 204CREATE USER `mysqltest1`@`%` IDENTIFIED BY PASSWORD '*6E6CABC9967AB586F009616BA69DAC2953849C88' 205flush privileges; 206show create user mysqltest1; 207CREATE USER for mysqltest1@% 208CREATE USER `mysqltest1`@`%` IDENTIFIED BY PASSWORD '*6E6CABC9967AB586F009616BA69DAC2953849C88' 209drop user mysqltest1; 210