1set global log_error_verbosity= 1; 2drop table if exists t1,t2; 3show tables; 4Tables_in_mysql 5columns_priv 6db 7engine_cost 8event 9func 10general_log 11gtid_executed 12help_category 13help_keyword 14help_relation 15help_topic 16innodb_index_stats 17innodb_table_stats 18ndb_binlog_index 19plugin 20proc 21procs_priv 22proxies_priv 23server_cost 24servers 25slave_master_info 26slave_relay_log_info 27slave_worker_info 28slow_log 29tables_priv 30time_zone 31time_zone_leap_second 32time_zone_name 33time_zone_transition 34time_zone_transition_type 35user 36show tables; 37Tables_in_test 38connect(localhost,root,z,test2,MASTER_PORT,MASTER_SOCKET); 39ERROR 28000: Access denied for user 'root'@'localhost' (using password: YES) 40connect(localhost,root,z,test,MASTER_PORT,MASTER_SOCKET); 41ERROR 28000: Access denied for user 'root'@'localhost' (using password: YES) 42create user test@localhost identified by "gambling"; 43grant ALL on *.* to test@localhost; 44create user test@127.0.0.1 identified by "gambling"; 45grant ALL on *.* to test@127.0.0.1; 46show tables; 47Tables_in_mysql 48columns_priv 49db 50engine_cost 51event 52func 53general_log 54gtid_executed 55help_category 56help_keyword 57help_relation 58help_topic 59innodb_index_stats 60innodb_table_stats 61ndb_binlog_index 62plugin 63proc 64procs_priv 65proxies_priv 66server_cost 67servers 68slave_master_info 69slave_relay_log_info 70slave_worker_info 71slow_log 72tables_priv 73time_zone 74time_zone_leap_second 75time_zone_name 76time_zone_transition 77time_zone_transition_type 78user 79show tables; 80Tables_in_test 81connect(localhost,test,,test2,MASTER_PORT,MASTER_SOCKET); 82ERROR 28000: Access denied for user 'test'@'localhost' (using password: NO) 83connect(localhost,test,,"",MASTER_PORT,MASTER_SOCKET); 84ERROR 28000: Access denied for user 'test'@'localhost' (using password: NO) 85connect(localhost,test,zorro,test2,MASTER_PORT,MASTER_SOCKET); 86ERROR 28000: Access denied for user 'test'@'localhost' (using password: YES) 87connect(localhost,test,zorro,test,MASTER_PORT,MASTER_SOCKET); 88ERROR 28000: Access denied for user 'test'@'localhost' (using password: YES) 89connect(localhost,test,,test2,MASTER_PORT,MASTER_SOCKET); 90ERROR 28000: Access denied for user 'test'@'localhost' (using password: NO) 91connect(localhost,test,,test,MASTER_PORT,MASTER_SOCKET); 92ERROR 28000: Access denied for user 'test'@'localhost' (using password: NO) 93delete from mysql.user where user=_binary"test"; 94flush privileges; 95create table t1 (id integer not null auto_increment primary key); 96create temporary table t2(id integer not null auto_increment primary key); 97set @id := 1; 98delete from t1 where id like @id; 99drop table t1; 100# ------------------------------------------------------------------ 101# -- End of 4.1 tests 102# ------------------------------------------------------------------ 103 104# -- Bug#33507: Event scheduler creates more threads than max_connections 105# -- which results in user lockout. 106 107CREATE USER mysqltest_u1@localhost; 108 109SET GLOBAL max_connections = 3; 110SET GLOBAL event_scheduler = ON; 111 112# -- Waiting for Event Scheduler to start... 113 114# -- Disconnecting default connection... 115 116# -- Check that we allow exactly three user connections, no matter how 117# -- many threads are running. 118 119# -- Connecting (1)... 120# -- Establishing connection 'con_1' (user: mysqltest_u1)... 121# -- Connection 'con_1' has been established. 122 123# -- Connecting (2)... 124# -- Establishing connection 'con_2' (user: mysqltest_u1)... 125# -- Connection 'con_2' has been established. 126 127# -- Connecting (3)... 128# -- Establishing connection 'con_3' (user: mysqltest_u1)... 129# -- Connection 'con_3' has been established. 130 131# -- Connecting (4) [should fail]... 132# -- Establishing connection 'con_4' (user: mysqltest_u1)... 133# -- Error: can not establish connection 'con_4'. 134 135# -- Check that we allow one extra SUPER-user connection. 136 137# -- Connecting super (1)... 138# -- Establishing connection 'con_super_1' (user: root)... 139# -- Connection 'con_super_1' has been established. 140 141# -- Connecting super (2) [should fail]... 142# -- Establishing connection 'con_super_2' (user: root)... 143# -- Error: can not establish connection 'con_super_2'. 144 145# -- Ensure that we have Event Scheduler thread, 3 ordinary user 146# -- connections and one extra super-user connection. 147SELECT user FROM information_schema.processlist ORDER BY id; 148user 149event_scheduler 150mysqltest_u1 151mysqltest_u1 152mysqltest_u1 153root 154 155# -- Resetting variables... 156SET GLOBAL max_connections = 151; 157 158# -- Stopping Event Scheduler... 159SET GLOBAL event_scheduler = OFF; 160 161# -- Waiting for Event Scheduler to stop... 162 163# -- That's it. Closing connections... 164 165# -- Restoring default connection... 166 167# -- Waiting for connections to close... 168 169DROP USER mysqltest_u1@localhost; 170 171# -- End of Bug#33507. 172 173# -- Bug#35074: max_used_connections is not correct. 174 175FLUSH STATUS; 176 177SHOW STATUS LIKE 'max_used_connections'; 178Variable_name Value 179Max_used_connections 1 180 181# -- Starting Event Scheduler... 182SET GLOBAL event_scheduler = ON; 183# -- Waiting for Event Scheduler to start... 184 185# -- Opening a new connection to check max_used_connections... 186 187# -- Check that max_used_connections hasn't changed. 188SHOW STATUS LIKE 'max_used_connections'; 189Variable_name Value 190Max_used_connections 2 191 192# -- Closing new connection... 193 194# -- Stopping Event Scheduler... 195SET GLOBAL event_scheduler = OFF; 196# -- Waiting for Event Scheduler to stop... 197 198# -- End of Bug#35074. 199 200# ------------------------------------------------------------------ 201# -- End of 5.1 tests 202# ------------------------------------------------------------------ 203# 204# WL#2392: Change Password at next login 205# 206CREATE USER must_change@localhost IDENTIFIED BY 'aha'; 207SELECT password_expired FROM mysql.user 208WHERE user='must_change' and host = 'localhost'; 209password_expired 210N 211SELECT USER(); 212USER() 213must_change@localhost 214CREATE TABLE t1 (A INT); 215CREATE PROCEDURE TEST_t1(new_a INT) INSERT INTO t1 VALUES (new_a); 216CREATE FUNCTION last_t1() RETURNS INT RETURN (SELECT MAX(A) FROM t1); 217# Initialize the table 218CALL test_t1(1); 219UPDATE mysql.user SET password_expired='Y' 220 WHERE user='must_change' and host = 'localhost'; 221# without FLUSH the field has no effect 222# must not throw an error 223SELECT USER(); 224USER() 225must_change@localhost 226# must not throw an error 227SELECT last_t1(); 228last_t1() 2291 230# must not throw an error 231CALL test_t1(last_t1() + 1); 232FLUSH PRIVILEGES; 233# existing connections continue as before even after flush 234# must not throw an error 235SELECT USER(); 236USER() 237must_change@localhost 238# must not throw an error 239SELECT last_t1(); 240last_t1() 2412 242# must not throw an error 243CALL test_t1(last_t1() + 1); 244# new connections are blocked until a password reset 245SELECT USER(); 246ERROR HY000: You must reset your password using ALTER USER statement before executing this statement. 247SELECT last_t1(); 248ERROR HY000: You must reset your password using ALTER USER statement before executing this statement. 249CALL test_t1(last_t1() + 1); 250ERROR HY000: You must reset your password using ALTER USER statement before executing this statement. 251# setting a password unlocks it 252ALTER USER must_change@localhost IDENTIFIED BY 'aha2'; 253# must not throw an error 254SELECT USER(); 255USER() 256must_change@localhost 257# must not throw an error 258SELECT last_t1(); 259last_t1() 2603 261# must not throw an error 262CALL test_t1(last_t1() + 1); 263# check if SET PASSWORD resets the column 264SELECT password_expired FROM mysql.user 265WHERE user='must_change' and host = 'localhost'; 266password_expired 267N 268UPDATE mysql.user SET password_expired='Y' 269 WHERE user='must_change' and host = 'localhost'; 270FLUSH PRIVILEGES; 271SELECT USER(); 272ERROR HY000: You must reset your password using ALTER USER statement before executing this statement. 273SELECT last_t1(); 274ERROR HY000: You must reset your password using ALTER USER statement before executing this statement. 275CALL test_t1(last_t1() + 1); 276ERROR HY000: You must reset your password using ALTER USER statement before executing this statement. 277# setting a password with a user name is ok if it exactly matches the 278# authenticated user. 279ALTER USER must_change@localhost IDENTIFIED BY 'aha3'; 280SELECT USER(); 281USER() 282must_change@localhost 283SELECT last_t1(); 284last_t1() 2854 286CALL test_t1(last_t1() + 1); 287# setting a password for the current user works 288ALTER USER must_change@localhost IDENTIFIED BY 'aha3'; 289SELECT USER(); 290USER() 291must_change@localhost 292SELECT last_t1(); 293last_t1() 2945 295CALL test_t1(last_t1() + 1); 296# testing the ALTER USER command 297# try a single user 298ALTER USER must_change@localhost PASSWORD EXPIRE; 299SELECT password_expired FROM mysql.user 300WHERE user='must_change' and host = 'localhost'; 301password_expired 302Y 303SELECT USER(); 304ERROR HY000: You must reset your password using ALTER USER statement before executing this statement. 305ALTER USER must_change@localhost IDENTIFIED BY 'aha4'; 306# try a valid+invalid user combo 307ALTER USER 308invalid_user@localhost, 309must_change@localhost PASSWORD EXPIRE; 310ERROR HY000: Operation ALTER USER failed for 'invalid_user'@'localhost' 311SELECT password_expired FROM mysql.user 312WHERE user='must_change' and host = 'localhost'; 313password_expired 314Y 315SELECT USER(); 316ERROR HY000: You must reset your password using ALTER USER statement before executing this statement. 317ALTER USER must_change@localhost IDENTIFIED BY 'aha5'; 318SELECT USER(); 319USER() 320must_change@localhost 321# Password change must be persistent when reconnecting 322SELECT USER(); 323USER() 324must_change@localhost 325# cleanup 326DROP PROCEDURE test_t1; 327DROP FUNCTION last_t1; 328DROP TABLE t1; 329DROP USER must_change@localhost; 330# 331# WL#6587: Protocol support for password expiration 332# 333CREATE USER wl6587@localhost IDENTIFIED BY 'wl6587'; 334ALTER USER wl6587@localhost PASSWORD EXPIRE; 335# non-interactive mysql should fail 336mysql: [Warning] Using a password on the command line interface can be insecure. 337Please use --connect-expired-password option or invoke mysql in interactive mode. 338# mysqladmin non-password should fail 339mysqladmin: [Warning] Using a password on the command line interface can be insecure. 340mysqladmin: connect to server at 'localhost' failed 341error: 'Your password has expired. To log in you must change it using a client that supports expired passwords.' 342# mysqladmin password should work 343mysqladmin: [Warning] Using a password on the command line interface can be insecure. 344DROP USER wl6587@localhost; 345# ------------------------------------------------------------------ 346# -- End of 5.6 tests 347# ------------------------------------------------------------------ 348# 349# BUG#27539838: NOT ALL ABORTED CONNECTS ARE REPORTED TO ERROR.LOG 350# PROPERLY 351# 352# restart: --log-error=LOG_ERR --log-error-verbosity=3 353SHOW STATUS LIKE 'Aborted_connects'; 354Variable_name Value 355Aborted_connects 0 356 357# Case 1: Connection attempt by an invalid user 358connect(localhost,newuser,,test,MASTER_PORT,MASTER_SOCKET); 359ERROR 28000: Access denied for user 'newuser'@'localhost' (using password: NO) 360 361# Case 2: Connection attempt by a valid user with incorrect password 362connect(localhost,root,1234,test,MASTER_PORT,MASTER_SOCKET); 363ERROR 28000: Access denied for user 'root'@'localhost' (using password: YES) 364CREATE DATABASE test1; 365CREATE USER 'new1'@'localhost'; 366 367# Case 3: Connection attempt by a valid user with no privileges to access a database 368connect(localhost,new1,,test1,MASTER_PORT,MASTER_SOCKET); 369ERROR 42000: Access denied for user 'new1'@'localhost' to database 'test1' 370 371# Case 4: SSL connection attempt without necessary certificates 372SHOW STATUS LIKE 'Aborted_connects'; 373Variable_name Value 374Aborted_connects 4 375DROP USER 'new1'@'localhost'; 376DROP DATABASE test1; 377set global log_error_verbosity= STARTVAL; 378