160 lines
5.0 KiB
Plaintext
160 lines
5.0 KiB
Plaintext
--source include/have_log_bin.inc
|
|
--source include/save_binlog_position.inc
|
|
|
|
# Save the initial number of concurrent sessions
|
|
--source include/count_sessions.inc
|
|
|
|
CALL mtr.add_suppression('Following users were specified in ALTER USER IF EXISTS');
|
|
|
|
--echo # -----------------------------------------------------------------------
|
|
|
|
--echo # Begin : Tests for ALTER USER
|
|
|
|
CREATE USER userX, userY, userZ;
|
|
--source include/save_binlog_position.inc
|
|
|
|
SELECT user, authentication_string FROM mysql.user;
|
|
|
|
--echo # Case 1 : Modify multiple users
|
|
ALTER USER userX IDENTIFIED BY 'abcd',
|
|
userY IDENTIFIED BY 'efgh',
|
|
userZ IDENTIFIED BY 'ijkl';
|
|
|
|
--let $event= !Q(ALTER USER.*userX.*userY.*userZ.*)
|
|
--source ../include/auth_sec_assert_binlog_events.inc
|
|
--source include/save_binlog_position.inc
|
|
|
|
--exec $MYSQL -uuserX -pabcd -e "SELECT CURRENT_USER()" 2>&1
|
|
--exec $MYSQL -uuserY -pefgh -e "SELECT CURRENT_USER()" 2>&1
|
|
--exec $MYSQL -uuserZ -pijkl -e "SELECT CURRENT_USER()" 2>&1
|
|
|
|
--echo # Case 2 : Try to modify property of an non-existing user
|
|
|
|
--error ER_CANNOT_USER
|
|
ALTER USER userW IDENTIFIED BY 'haha',
|
|
userX IDENTIFIED BY '',
|
|
userV IDENTIFIED BY 'haha';
|
|
|
|
--let $invert= 1
|
|
--let $event= !Q(ALTER USER.*userW.*userX.*userV.*)
|
|
--source ../include/auth_sec_assert_binlog_events.inc
|
|
--let $invert= 0
|
|
|
|
--exec $MYSQL -uuserX -pabcd -e "SELECT CURRENT_USER()" 2>&1
|
|
--exec $MYSQL -uuserY -pefgh -e "SELECT CURRENT_USER()" 2>&1
|
|
--exec $MYSQL -uuserZ -pijkl -e "SELECT CURRENT_USER()" 2>&1
|
|
|
|
--echo # Case 3 : Remove one of the entries from mysql.user but don't flush privileges.
|
|
DELETE FROM mysql.user WHERE user LIKE 'userX';
|
|
|
|
--source include/save_binlog_position.inc
|
|
|
|
--error ER_CANNOT_USER
|
|
ALTER USER userY IDENTIFIED BY '',
|
|
userX IDENTIFIED BY 'haha';
|
|
|
|
--echo # Check binlog : Must not have any entry
|
|
--let $invert= 1
|
|
--let $event= !Q(ALTER USER.*userY.*userX.*)
|
|
--source ../include/auth_sec_assert_binlog_events.inc
|
|
--let $invert= 0
|
|
|
|
# reload ACL caches
|
|
FLUSH PRIVILEGES;
|
|
--source include/save_binlog_position.inc
|
|
|
|
--exec $MYSQL -uuserY -pefgh -e "SELECT CURRENT_USER()" 2>&1
|
|
--exec $MYSQL -uuserZ -pijkl -e "SELECT CURRENT_USER()" 2>&1
|
|
|
|
--echo # Case 4 : Try to expire own password in a failing DDL. We update
|
|
--echo # current session's state as well. However, since DDL is going fail,
|
|
--echo # session's state must not be altered and user should be able to
|
|
--echo # execute other statements without any need to change password.
|
|
|
|
--error ER_CANNOT_USER
|
|
ALTER USER root@localhost, userW PASSWORD EXPIRE;
|
|
|
|
--echo # Must succeed
|
|
SELECT user FROM mysql.user;
|
|
|
|
--let $invert= 1
|
|
--let $event= !Q(ALTER USER.*root.*userW.*)
|
|
--source ../include/auth_sec_assert_binlog_events.inc
|
|
--let $invert= 0
|
|
|
|
DROP USER userY, userZ;
|
|
|
|
--source include/save_binlog_position.inc
|
|
|
|
--echo # End : Tests for ALTER USER
|
|
|
|
--echo # -----------------------------------------------------------------------
|
|
|
|
--echo # Begin : Tests for ALTER USER IF EXISTS
|
|
|
|
CALL mtr.add_suppression("Following users were specified in ALTER USER IF NOT EXISTS but they do not exist.");
|
|
|
|
CREATE USER userX, userY;
|
|
--source include/save_binlog_position.inc
|
|
|
|
--echo # Case 1 : Must throw warning for userW and userZ
|
|
ALTER USER IF EXISTS userW IDENTIFIED BY 'abcd',
|
|
userX IDENTIFIED BY 'efgh',
|
|
userY IDENTIFIED BY 'ijkl',
|
|
userZ IDENTIFIED BY 'mnop';
|
|
|
|
--let $event= !Q(ALTER USER.*userW.*userX.*userY.*userZ.*)
|
|
--source ../include/auth_sec_assert_binlog_events.inc
|
|
--source include/save_binlog_position.inc
|
|
|
|
--exec $MYSQL -uuserX -pefgh -e "SELECT CURRENT_USER()" 2>&1
|
|
--exec $MYSQL -uuserY -pijkl -e "SELECT CURRENT_USER()" 2>&1
|
|
|
|
--echo # Case 2 : Remove one of the user from mysql.user table
|
|
DELETE FROM mysql.user WHERE user LIKE 'userX';
|
|
--source include/save_binlog_position.inc
|
|
|
|
--echo # Must throw warning for userX
|
|
ALTER USER IF EXISTS userX IDENTIFIED BY 'abcd',
|
|
userY IDENTIFIED BY '';
|
|
|
|
--let $event= !Q(ALTER USER.*userX.*userY.*)
|
|
--source ../include/auth_sec_assert_binlog_events.inc
|
|
|
|
FLUSH PRIVILEGES;
|
|
--source include/save_binlog_position.inc
|
|
|
|
--echo # Case 3 : Current session's password must be expired
|
|
GRANT ALL ON *.* TO userY;
|
|
connect(conn_userY, localhost, userY,,,,,);
|
|
connection conn_userY;
|
|
--disable_warnings
|
|
ALTER USER IF EXISTS userY, userW PASSWORD EXPIRE;
|
|
--enable_warnings
|
|
|
|
--error ER_MUST_CHANGE_PASSWORD
|
|
SELECT user FROM mysql.user;
|
|
|
|
connection default;
|
|
--source include/save_binlog_position.inc
|
|
|
|
connection conn_userY;
|
|
--echo # Must throw warning
|
|
ALTER USER IF EXISTS userY IDENTIFIED BY 'abcd', userW IDENTIFIED BY 'haha';
|
|
|
|
SELECT user FROM mysql.user;
|
|
--let $event= !Q(ALTER USER.*userY.*userW.*)
|
|
--source ../include/auth_sec_assert_binlog_events.inc
|
|
|
|
connection default;
|
|
disconnect conn_userY;
|
|
DROP USER userY;
|
|
--source include/save_binlog_position.inc
|
|
|
|
--echo # End : Tests for ALTER USER IF EXISTS
|
|
|
|
--echo # -----------------------------------------------------------------------
|
|
|
|
# Wait till we reached the initial number of concurrent sessions
|
|
--source include/wait_until_count_sessions.inc
|