1set global secure_auth=0; 2create user natauth@localhost identified via 'mysql_native_password' using '*94BDCEBE19083CE2A1F959FD02F964C7AF4CFC29'; 3create user invalidauth@localhost identified via 'mysql_native_password' using 'invalid'; 4create user newpass@localhost identified by password '*94BDCEBE19083CE2A1F959FD02F964C7AF4CFC29'; 5create user invalidpass@localhost identified by password 'invalid'; 6create user newpassnat@localhost identified via 'mysql_native_password'; 7set password for newpassnat@localhost = '*94BDCEBE19083CE2A1F959FD02F964C7AF4CFC29'; 8create user invalidpassnat@localhost identified by password 'invalid'; 9set password for invalidpassnat@localhost = 'invalid'; 10create user oldauth@localhost identified with 'mysql_old_password' using '378b243e220ca493'; 11create user oldpass@localhost identified by password '378b243e220ca493'; 12create user oldpassold@localhost identified with 'mysql_old_password'; 13set password for oldpassold@localhost = '378b243e220ca493'; 14create user invalidmysql57auth@localhost identified via 'mysql_native_password' using '*THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE'; 15select user, host, password, plugin, authentication_string from mysql.user where user != 'root'; 16User Host Password plugin authentication_string 17invalidauth localhost invalid mysql_native_password invalid 18invalidmysql57auth localhost *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE mysql_native_password *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE 19invalidpass localhost invalid mysql_native_password invalid 20invalidpassnat localhost invalid mysql_native_password invalid 21mariadb.sys localhost mysql_native_password 22natauth localhost *94BDCEBE19083CE2A1F959FD02F964C7AF4CFC29 mysql_native_password *94BDCEBE19083CE2A1F959FD02F964C7AF4CFC29 23newpass localhost *94BDCEBE19083CE2A1F959FD02F964C7AF4CFC29 mysql_native_password *94BDCEBE19083CE2A1F959FD02F964C7AF4CFC29 24newpassnat localhost *94BDCEBE19083CE2A1F959FD02F964C7AF4CFC29 mysql_native_password *94BDCEBE19083CE2A1F959FD02F964C7AF4CFC29 25oldauth localhost 378b243e220ca493 mysql_old_password 378b243e220ca493 26oldpass localhost 378b243e220ca493 mysql_old_password 378b243e220ca493 27oldpassold localhost 378b243e220ca493 mysql_old_password 378b243e220ca493 28connect con,localhost,natauth,test,; 29select current_user(); 30current_user() 31natauth@localhost 32disconnect con; 33connect con,localhost,newpass,test,; 34select current_user(); 35current_user() 36newpass@localhost 37disconnect con; 38connect con,localhost,newpassnat,test,; 39select current_user(); 40current_user() 41newpassnat@localhost 42disconnect con; 43connect con,localhost,oldauth,test,; 44select current_user(); 45current_user() 46oldauth@localhost 47disconnect con; 48connect con,localhost,oldpass,test,; 49select current_user(); 50current_user() 51oldpass@localhost 52disconnect con; 53connect con,localhost,oldpassold,test,; 54select current_user(); 55current_user() 56oldpassold@localhost 57disconnect con; 58connection default; 59flush privileges; 60connect con,localhost,natauth,test,; 61select current_user(); 62current_user() 63natauth@localhost 64disconnect con; 65connect con,localhost,newpass,test,; 66select current_user(); 67current_user() 68newpass@localhost 69disconnect con; 70connect con,localhost,newpassnat,test,; 71select current_user(); 72current_user() 73newpassnat@localhost 74disconnect con; 75connect con,localhost,oldauth,test,; 76select current_user(); 77current_user() 78oldauth@localhost 79disconnect con; 80connect con,localhost,oldpass,test,; 81select current_user(); 82current_user() 83oldpass@localhost 84disconnect con; 85connect con,localhost,oldpassold,test,; 86select current_user(); 87current_user() 88oldpassold@localhost 89disconnect con; 90connection default; 91set password for natauth@localhost = PASSWORD('test2'); 92set password for newpass@localhost = PASSWORD('test2'); 93set password for newpassnat@localhost = PASSWORD('test2'); 94set password for oldauth@localhost = PASSWORD('test2'); 95set password for oldpass@localhost = PASSWORD('test2'); 96set password for oldpassold@localhost = PASSWORD('test2'); 97select user, host, password, plugin, authentication_string from mysql.user where user != 'root'; 98User Host Password plugin authentication_string 99invalidauth localhost invalid mysql_native_password invalid 100invalidmysql57auth localhost *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE mysql_native_password *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE 101invalidpass localhost invalid mysql_native_password invalid 102invalidpassnat localhost invalid mysql_native_password invalid 103mariadb.sys localhost mysql_native_password 104natauth localhost *7CEB3FDE5F7A9C4CE5FBE610D7D8EDA62EBE5F4E mysql_native_password *7CEB3FDE5F7A9C4CE5FBE610D7D8EDA62EBE5F4E 105newpass localhost *7CEB3FDE5F7A9C4CE5FBE610D7D8EDA62EBE5F4E mysql_native_password *7CEB3FDE5F7A9C4CE5FBE610D7D8EDA62EBE5F4E 106newpassnat localhost *7CEB3FDE5F7A9C4CE5FBE610D7D8EDA62EBE5F4E mysql_native_password *7CEB3FDE5F7A9C4CE5FBE610D7D8EDA62EBE5F4E 107oldauth localhost *7CEB3FDE5F7A9C4CE5FBE610D7D8EDA62EBE5F4E mysql_native_password *7CEB3FDE5F7A9C4CE5FBE610D7D8EDA62EBE5F4E 108oldpass localhost *7CEB3FDE5F7A9C4CE5FBE610D7D8EDA62EBE5F4E mysql_native_password *7CEB3FDE5F7A9C4CE5FBE610D7D8EDA62EBE5F4E 109oldpassold localhost *7CEB3FDE5F7A9C4CE5FBE610D7D8EDA62EBE5F4E mysql_native_password *7CEB3FDE5F7A9C4CE5FBE610D7D8EDA62EBE5F4E 110connect con,localhost,natauth,test2,; 111select current_user(); 112current_user() 113natauth@localhost 114disconnect con; 115connect con,localhost,newpass,test2,; 116select current_user(); 117current_user() 118newpass@localhost 119disconnect con; 120connect con,localhost,newpassnat,test2,; 121select current_user(); 122current_user() 123newpassnat@localhost 124disconnect con; 125connect con,localhost,oldauth,test2,; 126select current_user(); 127current_user() 128oldauth@localhost 129disconnect con; 130connect con,localhost,oldpass,test2,; 131select current_user(); 132current_user() 133oldpass@localhost 134disconnect con; 135connect con,localhost,oldpassold,test2,; 136select current_user(); 137current_user() 138oldpassold@localhost 139disconnect con; 140connection default; 141flush privileges; 142connect con,localhost,natauth,test2,; 143select current_user(); 144current_user() 145natauth@localhost 146disconnect con; 147connect con,localhost,newpass,test2,; 148select current_user(); 149current_user() 150newpass@localhost 151disconnect con; 152connect con,localhost,newpassnat,test2,; 153select current_user(); 154current_user() 155newpassnat@localhost 156disconnect con; 157connect(localhost,invalidauth,invalid,test,MASTER_PORT,MASTER_SOCKET); 158connect con,localhost,invalidauth,invalid,; 159ERROR 28000: Access denied for user 'invalidauth'@'localhost' (using password: YES) 160connect(localhost,invalidpass,invalid,test,MASTER_PORT,MASTER_SOCKET); 161connect con,localhost,invalidpass,invalid,; 162ERROR 28000: Access denied for user 'invalidpass'@'localhost' (using password: YES) 163connect(localhost,invalidpassnat,invalid,test,MASTER_PORT,MASTER_SOCKET); 164connect con,localhost,invalidpassnat,invalid,; 165ERROR 28000: Access denied for user 'invalidpassnat'@'localhost' (using password: YES) 166connect(localhost,invalidmysql57auth,invalid,test,MASTER_PORT,MASTER_SOCKET); 167connect con,localhost,invalidmysql57auth,invalid,; 168ERROR 28000: Access denied for user 'invalidmysql57auth'@'localhost' (using password: YES) 169connect con,localhost,oldauth,test2,; 170select current_user(); 171current_user() 172oldauth@localhost 173disconnect con; 174connect con,localhost,oldpass,test2,; 175select current_user(); 176current_user() 177oldpass@localhost 178disconnect con; 179connect con,localhost,oldpassold,test2,; 180select current_user(); 181current_user() 182oldpassold@localhost 183disconnect con; 184connection default; 185drop user natauth@localhost, newpass@localhost, newpassnat@localhost; 186drop user invalidauth@localhost, invalidpass@localhost, invalidpassnat@localhost,invalidmysql57auth@localhost; 187drop user oldauth@localhost, oldpass@localhost, oldpassold@localhost; 188set global secure_auth=default; 189# switching from mysql.global_priv to mysql.user 190create user foo@localhost identified with mysql_native_password; 191update mysql.user set authentication_string=password('foo'), plugin='mysql_native_password' where user='foo' and host='localhost'; 192set password for 'foo'@'localhost' = password('bar'); 193flush privileges; 194connect foo, localhost, foo, bar; 195select user(), current_user(); 196user() current_user() 197foo@localhost foo@localhost 198show grants; 199Grants for foo@localhost 200GRANT USAGE ON *.* TO `foo`@`localhost` IDENTIFIED BY PASSWORD '*E8D46CE25265E545D225A8A6F1BAF642FEBEE5CB' 201disconnect foo; 202connection default; 203select user,host,password,plugin,authentication_string from mysql.user where user='foo'; 204user host password plugin authentication_string 205foo localhost mysql_native_password *E8D46CE25265E545D225A8A6F1BAF642FEBEE5CB 206set password for 'foo'@'localhost' = ''; 207select user,host,password,plugin,authentication_string from mysql.user where user='foo'; 208user host password plugin authentication_string 209foo localhost mysql_native_password 210drop user foo@localhost; 211# switching back from mysql.user to mysql.global_priv 212