mysql-testsuite-5.6 5.6.16-1~exp1 / usr / lib / mysql-testsuite / t / connect.test

# This test is to check various cases of connections
# with right and wrong password, with and without database
# Unfortunately the check is incomplete as we can't connect without database

# This test makes no sense with the embedded server
--source include/not_embedded.inc

# Save the initial number of concurrent sessions
--source include/count_sessions.inc


--disable_warnings
drop table if exists t1,t2;
--enable_warnings

--disable_query_log
--disable_result_log
call mtr.add_suppression("Did not write failed .*");
--enable_result_log
--enable_query_log
#connect (con1,localhost,root,,"");
#show tables;
connect (con1,localhost,root,,mysql);
show tables;
connect (con2,localhost,root,,test);
show tables;

--replace_result $MASTER_MYSOCK MASTER_SOCKET $MASTER_MYPORT MASTER_PORT
--error ER_ACCESS_DENIED_ERROR
connect (fail_con,localhost,root,z,test2);
--replace_result $MASTER_MYSOCK MASTER_SOCKET $MASTER_MYPORT MASTER_PORT
--error ER_ACCESS_DENIED_ERROR
connect (fail_con,localhost,root,z,);

disconnect con1;
disconnect con2;
--source include/wait_until_disconnected.inc
connection default;

grant ALL on *.* to test@localhost identified by "gambling";
grant ALL on *.* to test@127.0.0.1 identified by "gambling";

# Now check this user with different databases
#connect (con1,localhost,test,gambling,"");
#show tables;
connect (con3,localhost,test,gambling,mysql);
show tables;
connect (con4,localhost,test,gambling,test);
show tables;

disconnect con3;
disconnect con4;
--source include/wait_until_disconnected.inc
connection default;

--replace_result $MASTER_MYSOCK MASTER_SOCKET $MASTER_MYPORT MASTER_PORT
--error ER_ACCESS_DENIED_ERROR
connect (fail_con,localhost,test,,test2);
--replace_result $MASTER_MYSOCK MASTER_SOCKET $MASTER_MYPORT MASTER_PORT
--error ER_ACCESS_DENIED_ERROR
# Need to protect "" within '' so it's interpreted literally
connect (fail_con,localhost,test,,'""');
--replace_result $MASTER_MYSOCK MASTER_SOCKET $MASTER_MYPORT MASTER_PORT
--error ER_ACCESS_DENIED_ERROR
connect (fail_con,localhost,test,zorro,test2);
--replace_result $MASTER_MYSOCK MASTER_SOCKET $MASTER_MYPORT MASTER_PORT
--error ER_ACCESS_DENIED_ERROR
connect (fail_con,localhost,test,zorro,);

# check if old password version also works
SET GLOBAL secure_auth = OFF;
update mysql.user set password=old_password("gambling2"), plugin="mysql_old_password" where user=_binary"test";
flush privileges;
connect (con10,localhost,test,gambling2,,,,SKIPSECUREAUTH);
connect (con5,localhost,test,gambling2,mysql,,,SKIPSECUREAUTH);
connection con5;
set password="";
--error ER_PASSWD_LENGTH
set password='gambling3';
set password=old_password('gambling3');
show tables;
connect (con6,localhost,test,gambling3,test,,,SKIPSECUREAUTH);
show tables;

disconnect con10;
disconnect con5;
disconnect con6;
--source include/wait_until_disconnected.inc
connection default;

--replace_result $MASTER_MYSOCK MASTER_SOCKET $MASTER_MYPORT MASTER_PORT
--error ER_ACCESS_DENIED_ERROR
connect (fail_con,localhost,test,,test2);
--replace_result $MASTER_MYSOCK MASTER_SOCKET $MASTER_MYPORT MASTER_PORT
--error ER_ACCESS_DENIED_ERROR
connect (fail_con,localhost,test,,);
--replace_result $MASTER_MYSOCK MASTER_SOCKET $MASTER_MYPORT MASTER_PORT
--error ER_ACCESS_DENIED_ERROR
connect (fail_con,localhost,test,zorro,test2,,,SKIPSECUREAUTH);
--replace_result $MASTER_MYSOCK MASTER_SOCKET $MASTER_MYPORT MASTER_PORT
--error ER_ACCESS_DENIED_ERROR
connect (fail_con,localhost,test,zorro,,,,SKIPSECUREAUTH);


# remove user 'test' so that other tests which may use 'test'
# do not depend on this test.
delete from mysql.user where user=_binary"test";
flush privileges;
SET GLOBAL secure_auth = DEFAULT;

#
# Bug#12517 Clear user variables and replication events before
#           closing temp tables in thread cleanup.
connect (con7,localhost,root,,test);
connection con7;
let $connection_id= `select connection_id()`;
create table t1 (id integer not null auto_increment primary key);
create temporary table t2(id integer not null auto_increment primary key);
set @id := 1;
delete from t1 where id like @id;

disconnect con7;
--source include/wait_until_disconnected.inc
connection default;

# Wait till the session con7 is disconnected
let $wait_condition =
  SELECT COUNT(*) = 0
  FROM information_schema.processlist
  WHERE  id = '$connection_id';
--source include/wait_condition.inc
drop table t1;

--echo # ------------------------------------------------------------------
--echo # -- End of 4.1 tests
--echo # ------------------------------------------------------------------

###########################################################################

--echo
--echo # -- Bug#33507: Event scheduler creates more threads than max_connections
--echo # -- which results in user lockout.

--echo
GRANT USAGE ON *.* TO mysqltest_u1@localhost;

# NOTE: if the test case fails sporadically due to spurious connections,
# consider disabling all users.

--echo
let $saved_max_connections = `SELECT @@global.max_connections`;
SET GLOBAL max_connections = 3;
SET GLOBAL event_scheduler = ON;

--echo
--echo # -- Waiting for Event Scheduler to start...
let $wait_condition =
  SELECT COUNT(*) = 1
  FROM information_schema.processlist
  WHERE user = 'event_scheduler';
--source include/wait_condition.inc

--echo
--echo # -- Disconnecting default connection...
--disconnect default
--source include/wait_until_disconnected.inc

--echo
--echo # -- Check that we allow exactly three user connections, no matter how
--echo # -- many threads are running.

--echo
--echo # -- Connecting (1)...
let $con_name = con_1;
let $con_user_name = mysqltest_u1;
--source include/connect2.inc

--echo
--echo # -- Connecting (2)...
let $con_name = con_2;
let $con_user_name = mysqltest_u1;
--source include/connect2.inc

--echo
--echo # -- Connecting (3)...
let $con_name = con_3;
let $con_user_name = mysqltest_u1;
--source include/connect2.inc

--echo
--echo # -- Connecting (4) [should fail]...
let $con_name = con_4;
let $con_user_name = mysqltest_u1;
let $wait_timeout = 5;
--source include/connect2.inc

--echo
--echo # -- Check that we allow one extra SUPER-user connection.

--echo
--echo # -- Connecting super (1)...
let $con_name = con_super_1;
let $con_user_name = root;
--source include/connect2.inc

--echo
--echo # -- Connecting super (2) [should fail]...
let $con_name = con_super_2;
let $con_user_name = root;
let $wait_timeout = 5;
--source include/connect2.inc

--echo
--echo # -- Ensure that we have Event Scheduler thread, 3 ordinary user
--echo # -- connections and one extra super-user connection.
SELECT user FROM information_schema.processlist ORDER BY id;

--echo
--echo # -- Resetting variables...
--eval SET GLOBAL max_connections = $saved_max_connections

--echo
--echo # -- Stopping Event Scheduler...
SET GLOBAL event_scheduler = OFF;

--echo
--echo # -- Waiting for Event Scheduler to stop...
let $wait_condition =
  SELECT COUNT(*) = 0
  FROM information_schema.processlist
  WHERE user = 'event_scheduler';
--source include/wait_condition.inc

--echo
--echo # -- That's it. Closing connections...
--disconnect con_1
--disconnect con_2
--disconnect con_3
--disconnect con_super_1
--source include/wait_until_disconnected.inc
--echo
--echo # -- Restoring default connection...
--connect (default,localhost,root,,test)

--echo
--echo # -- Waiting for connections to close...
let $wait_condition =
  SELECT COUNT(*) = 1
  FROM information_schema.processlist
  WHERE db = 'test';
--source include/wait_condition.inc

--echo
DROP USER mysqltest_u1@localhost;

--echo
--echo # -- End of Bug#33507.
--echo

###########################################################################

--echo # -- Bug#35074: max_used_connections is not correct.
--echo

FLUSH STATUS;

--echo
SHOW STATUS LIKE 'max_used_connections';

--echo
--echo # -- Starting Event Scheduler...
SET GLOBAL event_scheduler = ON;

--echo # -- Waiting for Event Scheduler to start...
--source include/running_event_scheduler.inc

# NOTE: We should use a new connection here instead of reconnect in order to
# avoid races (we can not for sure when the connection being disconnected is
# actually disconnected on the server).

--echo
--echo # -- Opening a new connection to check max_used_connections...
--connect (con_1,localhost,root)

--echo
--echo # -- Check that max_used_connections hasn't changed.
SHOW STATUS LIKE 'max_used_connections';

--echo
--echo # -- Closing new connection...
--disconnect con_1
--source include/wait_until_disconnected.inc
--connection default

--echo
--echo # -- Stopping Event Scheduler...
SET GLOBAL event_scheduler = OFF;

--echo # -- Waiting for Event Scheduler to stop...
--source include/no_running_events.inc

--echo
--echo # -- End of Bug#35074.
--echo

--echo # ------------------------------------------------------------------
--echo # -- End of 5.1 tests
--echo # ------------------------------------------------------------------

--echo #
--echo # Bug #13586336 : SRVGEN - HIBISCUS: ISSUE DEPRECATION WARNING FOR
--echo # LEGACY AUTHENTICATION METHOD
--echo #

--echo #Expect 1
SELECT @@global.secure_auth;

SET old_passwords=1;

--echo #Expect deprecation warning
CREATE USER old_pwd@localhost IDENTIFIED WITH 'mysql_old_password';
SET PASSWORD FOR old_pwd@localhost= OLD_PASSWORD('aha');

--echo #Expect deprecation warning
SET PASSWORD FOR old_pwd@localhost = OLD_PASSWORD('aha2');

--echo #Expect deprecation warning
--error ER_PASSWORD_FORMAT
GRANT SELECT on test.* TO old_gpwd@localhost 
  IDENTIFIED BY 'aha3';

--echo #Expect deprecation warning
GRANT SELECT on test.* TO old_gpwd@localhost 
  IDENTIFIED WITH 'mysql_old_password';
SET PASSWORD FOR old_gpwd@localhost= OLD_PASSWORD('aha4');

--echo #Expected normal flush, no warnings  
FLUSH PRIVILEGES;  

DROP USER old_pwd@localhost, old_gpwd@localhost;  
SET old_passwords=default;


--echo #
--echo # WL#2392: Change Password at next login
--echo #

CREATE USER must_change@localhost IDENTIFIED BY 'aha';
SELECT password_expired FROM mysql.user 
  WHERE user='must_change' and host = 'localhost';
--connect(must_change_con, localhost, must_change, aha)
SELECT USER();
CREATE TABLE t1 (A INT);
CREATE PROCEDURE TEST_t1(new_a INT) INSERT INTO t1 VALUES (new_a);
CREATE FUNCTION last_t1() RETURNS INT RETURN (SELECT MAX(A) FROM t1);
--echo # Initialize the table
CALL test_t1(1);
connection default;
UPDATE mysql.user SET password_expired='Y'
  WHERE user='must_change' and host = 'localhost';

--echo # without FLUSH the field has no effect
disconnect must_change_con;
--connect(must_change_con, localhost, must_change, aha)
--echo # must not throw an error
SELECT USER();
--echo # must not throw an error
SELECT last_t1();
--echo # must not throw an error
CALL test_t1(last_t1() + 1);

connection default;
FLUSH PRIVILEGES;

--echo # existing connections continue as before even after flush
connection must_change_con;
--echo # must not throw an error
SELECT USER();
--echo # must not throw an error
SELECT last_t1();
--echo # must not throw an error
CALL test_t1(last_t1() + 1);

--echo # new connections are blocked until a password reset
connection default;
disconnect must_change_con;
--connect(must_change_con, localhost, must_change, aha)
--error ER_MUST_CHANGE_PASSWORD
SELECT USER();
--error ER_MUST_CHANGE_PASSWORD
SELECT last_t1();
--error ER_MUST_CHANGE_PASSWORD
CALL test_t1(last_t1() + 1);

--echo # setting a password unlocks it
SET PASSWORD = PASSWORD('aha2');
--echo # must not throw an error
SELECT USER();
--echo # must not throw an error
SELECT last_t1();
--echo # must not throw an error
CALL test_t1(last_t1() + 1);

connection default;
disconnect must_change_con;
--echo # check if SET PASSWORD resets the column
SELECT password_expired FROM mysql.user
  WHERE user='must_change' and host = 'localhost';

UPDATE mysql.user SET password_expired='Y'
  WHERE user='must_change' and host = 'localhost';
FLUSH PRIVILEGES;

--connect(must_change_con, localhost, must_change, aha2)
--error ER_MUST_CHANGE_PASSWORD
SELECT USER();
--error ER_MUST_CHANGE_PASSWORD
SELECT last_t1();
--error ER_MUST_CHANGE_PASSWORD
CALL test_t1(last_t1() + 1);

--echo # setting a password with a user name is ok if it exactly matches the
--echo # authenticated user.
SET PASSWORD FOR must_change@localhost = PASSWORD('aha3');
SELECT USER();
SELECT last_t1();
CALL test_t1(last_t1() + 1);

--echo # setting a password for the current user works
SET PASSWORD FOR CURRENT_USER() = PASSWORD('aha3');
SELECT USER();
SELECT last_t1();
CALL test_t1(last_t1() + 1);

--echo # testing the ALTER USER command
connection default;
disconnect must_change_con;

--echo # try a single user
ALTER USER must_change@localhost PASSWORD EXPIRE;

SELECT password_expired FROM mysql.user
  WHERE user='must_change' and host = 'localhost';

--connect(must_change_con, localhost, must_change, aha3)
--error ER_MUST_CHANGE_PASSWORD
SELECT USER();
SET PASSWORD = PASSWORD('aha4');

connection default;
disconnect must_change_con;

--echo # try a valid+invalid user combo
--error ER_CANNOT_USER
ALTER USER
  invalid_user@localhost PASSWORD EXPIRE,
  must_change@localhost PASSWORD EXPIRE;

SELECT password_expired FROM mysql.user
  WHERE user='must_change' and host = 'localhost';

--connect(must_change_con, localhost, must_change, aha4)
--error ER_MUST_CHANGE_PASSWORD
SELECT USER();
SET PASSWORD = PASSWORD('aha5');
SELECT USER();

connection default;
disconnect must_change_con;

--echo # Password change must be persistent when reconnecting
--connect(must_change_con, localhost, must_change, aha5)
SELECT USER();

--echo # cleanup
connection default;
disconnect must_change_con;
DROP PROCEDURE test_t1;
DROP FUNCTION last_t1;
DROP TABLE t1;
DROP USER must_change@localhost;


--echo #
--echo # WL#6587:  Protocol support for password expiration
--echo #

CREATE USER wl6587@localhost IDENTIFIED BY 'wl6587';
ALTER USER wl6587@localhost PASSWORD EXPIRE;

--echo # non-interactive mysql should fail
--error 1
--exec $MYSQL -uwl6587 --password=wl6587 test -e "SELECT USER()" 2>&1

--echo # mysqladmin non-password should fail
--replace_regex /.*mysqladmin.*: connect/mysqladmin: connect/
--error 1
--exec $MYSQLADMIN -S $MASTER_MYSOCK -P $MASTER_MYPORT -uwl6587 --password=wl6587 status 2>&1

--echo # mysqladmin password should work
--exec $MYSQLADMIN -S $MASTER_MYSOCK -P $MASTER_MYPORT -uwl6587 --password=wl6587 password wl6587-2 2>&1

DROP USER wl6587@localhost;


--echo # ------------------------------------------------------------------
--echo # -- End of 5.6 tests
--echo # ------------------------------------------------------------------


# Wait till all disconnects are completed
--source include/wait_until_count_sessions.inc