You can not select more than 25 topics
Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.
509 lines
14 KiB
509 lines
14 KiB
|
|
# 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
|
|
|
|
# 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
|
|
--enable_result_log
|
|
--enable_query_log
|
|
#connect (con1,localhost,root,,"");
|
|
#show tables;
|
|
connect (con1,localhost,root,,mysql);
|
|
show tables where Tables_in_mysql != 'ndb_binlog_index';
|
|
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;
|
|
|
|
create user test@localhost identified by "gambling";
|
|
grant ALL on *.* to test@localhost;
|
|
create user test@127.0.0.1 identified by "gambling";
|
|
grant ALL on *.* to test@127.0.0.1;
|
|
|
|
# Now check this user with different databases
|
|
#connect (con1,localhost,test,gambling,"");
|
|
#show tables;
|
|
connect (con3,localhost,test,gambling,mysql);
|
|
show tables where Tables_in_mysql != 'ndb_binlog_index';
|
|
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,);
|
|
|
|
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,,);
|
|
|
|
|
|
# remove user 'test' so that other tests which may use 'test'
|
|
# do not depend on this test.
|
|
drop user test@localhost;
|
|
drop user test@127.0.0.1;
|
|
|
|
#
|
|
# 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
|
|
CREATE USER 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;
|
|
|
|
# Make Sure Event scheduler is ON (by default)
|
|
SELECT COUNT(*) = 1 FROM information_schema.processlist
|
|
WHERE user = 'event_scheduler' AND command = 'Daemon';
|
|
|
|
--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 # -- 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 # -- 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';
|
|
|
|
# Make Sure Event scheduler is ON (by default)
|
|
SELECT COUNT(*) = 1 FROM information_schema.processlist
|
|
WHERE user = 'event_scheduler' AND command = 'Daemon';
|
|
|
|
--echo
|
|
--echo # -- Closing new connection...
|
|
--disconnect con_1
|
|
--source include/wait_until_disconnected.inc
|
|
--connection default
|
|
|
|
--echo
|
|
--echo # -- End of Bug#35074.
|
|
--echo
|
|
|
|
--echo # ------------------------------------------------------------------
|
|
--echo # -- End of 5.1 tests
|
|
--echo # ------------------------------------------------------------------
|
|
|
|
|
|
--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
|
|
--disable_ps_protocol
|
|
ALTER USER must_change@localhost IDENTIFIED BY 'aha2';
|
|
--enable_ps_protocol
|
|
--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.
|
|
--disable_ps_protocol
|
|
ALTER USER must_change@localhost IDENTIFIED BY 'aha3';
|
|
--enable_ps_protocol
|
|
SELECT USER();
|
|
SELECT last_t1();
|
|
CALL test_t1(last_t1() + 1);
|
|
|
|
--echo # setting a password for the current user works
|
|
ALTER USER must_change@localhost IDENTIFIED BY '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();
|
|
--disable_ps_protocol
|
|
ALTER USER must_change@localhost IDENTIFIED BY 'aha4';
|
|
--enable_ps_protocol
|
|
|
|
connection default;
|
|
disconnect must_change_con;
|
|
|
|
--echo # try a valid+invalid user combo
|
|
--error ER_CANNOT_USER
|
|
ALTER USER
|
|
invalid_user@localhost,
|
|
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)
|
|
SELECT USER();
|
|
|
|
connection default;
|
|
disconnect must_change_con;
|
|
|
|
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, aha4)
|
|
--error ER_MUST_CHANGE_PASSWORD
|
|
SELECT USER();
|
|
|
|
--disable_ps_protocol
|
|
ALTER USER must_change@localhost IDENTIFIED BY 'aha5';
|
|
--enable_ps_protocol
|
|
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 --no-defaults -S $MASTER_MYSOCK -P $MASTER_MYPORT -uwl6587 --password=wl6587 status 2>&1
|
|
|
|
--echo # mysqladmin password should work
|
|
--exec $MYSQLADMIN --no-defaults -S $MASTER_MYSOCK -P $MASTER_MYPORT -uwl6587 --password=wl6587 password wl6587-2 --ssl-mode=REQUIRED 2>&1
|
|
|
|
DROP USER wl6587@localhost;
|
|
|
|
|
|
--echo # ------------------------------------------------------------------
|
|
--echo # -- End of 5.6 tests
|
|
--echo # ------------------------------------------------------------------
|
|
|
|
--echo #
|
|
--echo # BUG#27539838: NOT ALL ABORTED CONNECTS ARE REPORTED TO ERROR.LOG
|
|
--echo # PROPERLY
|
|
--echo #
|
|
|
|
--let LOG_ERR=$MYSQLTEST_VARDIR/tmp/myerror.err
|
|
--let SEARCH_FILE=$MYSQLTEST_VARDIR/tmp/myerror.err
|
|
|
|
--let restart_parameters="restart: --log-error=$LOG_ERR --log-error-verbosity=3"
|
|
--replace_result $LOG_ERR LOG_ERR
|
|
--source include/restart_mysqld.inc
|
|
|
|
SHOW STATUS LIKE 'Aborted_connects';
|
|
|
|
--echo
|
|
--echo # Case 1: Connection attempt by an invalid user
|
|
--replace_result $MASTER_MYSOCK MASTER_SOCKET $MASTER_MYPORT MASTER_PORT
|
|
--error ER_ACCESS_DENIED_ERROR
|
|
connect(con1,localhost,newuser,,);
|
|
|
|
--let SEARCH_PATTERN= Access denied for user 'newuser'@'localhost' \(using password: NO\)
|
|
--source include/search_pattern.inc
|
|
|
|
--echo
|
|
--echo # Case 2: Connection attempt by a valid user with incorrect password
|
|
--replace_result $MASTER_MYSOCK MASTER_SOCKET $MASTER_MYPORT MASTER_PORT
|
|
--error ER_ACCESS_DENIED_ERROR
|
|
connect(con1,localhost,root,1234,);
|
|
|
|
--let SEARCH_PATTERN= Access denied for user 'root'@'localhost' \(using password: YES\)
|
|
--source include/search_pattern.inc
|
|
|
|
# Creating a dummy database and an user with no privileges to access that database
|
|
--connection default
|
|
CREATE DATABASE test1;
|
|
CREATE USER 'new1'@'localhost';
|
|
|
|
--echo
|
|
--echo # Case 3: Connection attempt by a valid user with no privileges to access a database
|
|
--replace_result $MASTER_MYSOCK MASTER_SOCKET $MASTER_MYPORT MASTER_PORT
|
|
--error ER_DBACCESS_DENIED_ERROR
|
|
connect(con1,localhost,new1,,test1);
|
|
|
|
--let SEARCH_PATTERN= Access denied for user 'new1'@'localhost' to database 'test1'
|
|
--source include/search_pattern.inc
|
|
|
|
--echo
|
|
--echo # Case 4: SSL connection attempt without necessary certificates
|
|
--error 1
|
|
--exec $MYSQL --user=root --ssl-mode=VERIFY_CA
|
|
|
|
--let SEARCH_PATTERN= Got an error reading communication packets
|
|
--source include/search_pattern.inc
|
|
|
|
SHOW STATUS LIKE 'Aborted_connects';
|
|
|
|
# Cleanup
|
|
DROP USER 'new1'@'localhost';
|
|
DROP DATABASE test1;
|
|
--remove_file $LOG_ERR
|
|
|
|
# Wait till all disconnects are completed
|
|
--source include/wait_until_count_sessions.inc
|
|
|
|
|