142 lines
5.4 KiB
Plaintext
142 lines
5.4 KiB
Plaintext
include/save_binlog_position.inc
|
|
CALL mtr.add_suppression('Following users were specified in ALTER USER IF EXISTS');
|
|
# -----------------------------------------------------------------------
|
|
# Begin : Tests for ALTER USER
|
|
CREATE USER userX, userY, userZ;
|
|
include/save_binlog_position.inc
|
|
SELECT user, authentication_string FROM mysql.user;
|
|
user authentication_string
|
|
userX
|
|
userY
|
|
userZ
|
|
mysql.infoschema $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED
|
|
mysql.session $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED
|
|
mysql.sys $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED
|
|
root
|
|
# Case 1 : Modify multiple users
|
|
ALTER USER userX IDENTIFIED BY 'abcd',
|
|
userY IDENTIFIED BY 'efgh',
|
|
userZ IDENTIFIED BY 'ijkl';
|
|
# This event sequence pattern MUST be present in binlog: !Q(ALTER USER.*userX.*userY.*userZ.*)
|
|
include/assert_binlog_events.inc
|
|
include/save_binlog_position.inc
|
|
mysql: [Warning] Using a password on the command line interface can be insecure.
|
|
CURRENT_USER()
|
|
userX@%
|
|
mysql: [Warning] Using a password on the command line interface can be insecure.
|
|
CURRENT_USER()
|
|
userY@%
|
|
mysql: [Warning] Using a password on the command line interface can be insecure.
|
|
CURRENT_USER()
|
|
userZ@%
|
|
# Case 2 : Try to modify property of an non-existing user
|
|
ALTER USER userW IDENTIFIED BY 'haha',
|
|
userX IDENTIFIED BY '',
|
|
userV IDENTIFIED BY 'haha';
|
|
ERROR HY000: Operation ALTER USER failed for 'userW'@'%','userV'@'%'
|
|
# This event sequence pattern MUST NOT be present in binlog: !Q(ALTER USER.*userW.*userX.*userV.*)
|
|
include/assert_binlog_events.inc
|
|
mysql: [Warning] Using a password on the command line interface can be insecure.
|
|
CURRENT_USER()
|
|
userX@%
|
|
mysql: [Warning] Using a password on the command line interface can be insecure.
|
|
CURRENT_USER()
|
|
userY@%
|
|
mysql: [Warning] Using a password on the command line interface can be insecure.
|
|
CURRENT_USER()
|
|
userZ@%
|
|
# Case 3 : Remove one of the entries from mysql.user but don't flush privileges.
|
|
DELETE FROM mysql.user WHERE user LIKE 'userX';
|
|
include/save_binlog_position.inc
|
|
ALTER USER userY IDENTIFIED BY '',
|
|
userX IDENTIFIED BY 'haha';
|
|
ERROR HY000: Operation ALTER USER failed for 'userX'@'%'
|
|
# Check binlog : Must not have any entry
|
|
# This event sequence pattern MUST NOT be present in binlog: !Q(ALTER USER.*userY.*userX.*)
|
|
include/assert_binlog_events.inc
|
|
FLUSH PRIVILEGES;
|
|
include/save_binlog_position.inc
|
|
mysql: [Warning] Using a password on the command line interface can be insecure.
|
|
CURRENT_USER()
|
|
userY@%
|
|
mysql: [Warning] Using a password on the command line interface can be insecure.
|
|
CURRENT_USER()
|
|
userZ@%
|
|
# Case 4 : Try to expire own password in a failing DDL. We update
|
|
# current session's state as well. However, since DDL is going fail,
|
|
# session's state must not be altered and user should be able to
|
|
# execute other statements without any need to change password.
|
|
ALTER USER root@localhost, userW PASSWORD EXPIRE;
|
|
ERROR HY000: Operation ALTER USER failed for 'userW'@'%'
|
|
# Must succeed
|
|
SELECT user FROM mysql.user;
|
|
user
|
|
userY
|
|
userZ
|
|
mysql.infoschema
|
|
mysql.session
|
|
mysql.sys
|
|
root
|
|
# This event sequence pattern MUST NOT be present in binlog: !Q(ALTER USER.*root.*userW.*)
|
|
include/assert_binlog_events.inc
|
|
DROP USER userY, userZ;
|
|
include/save_binlog_position.inc
|
|
# End : Tests for ALTER USER
|
|
# -----------------------------------------------------------------------
|
|
# 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;
|
|
include/save_binlog_position.inc
|
|
# 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';
|
|
Warnings:
|
|
Note 3162 Authorization ID 'userW'@'%' does not exist.
|
|
Note 3162 Authorization ID 'userZ'@'%' does not exist.
|
|
# This event sequence pattern MUST be present in binlog: !Q(ALTER USER.*userW.*userX.*userY.*userZ.*)
|
|
include/assert_binlog_events.inc
|
|
include/save_binlog_position.inc
|
|
mysql: [Warning] Using a password on the command line interface can be insecure.
|
|
CURRENT_USER()
|
|
userX@%
|
|
mysql: [Warning] Using a password on the command line interface can be insecure.
|
|
CURRENT_USER()
|
|
userY@%
|
|
# Case 2 : Remove one of the user from mysql.user table
|
|
DELETE FROM mysql.user WHERE user LIKE 'userX';
|
|
include/save_binlog_position.inc
|
|
# Must throw warning for userX
|
|
ALTER USER IF EXISTS userX IDENTIFIED BY 'abcd',
|
|
userY IDENTIFIED BY '';
|
|
Warnings:
|
|
Note 3162 Authorization ID 'userX'@'%' does not exist.
|
|
# This event sequence pattern MUST be present in binlog: !Q(ALTER USER.*userX.*userY.*)
|
|
include/assert_binlog_events.inc
|
|
FLUSH PRIVILEGES;
|
|
include/save_binlog_position.inc
|
|
# Case 3 : Current session's password must be expired
|
|
GRANT ALL ON *.* TO userY;
|
|
ALTER USER IF EXISTS userY, userW PASSWORD EXPIRE;
|
|
SELECT user FROM mysql.user;
|
|
ERROR HY000: You must reset your password using ALTER USER statement before executing this statement.
|
|
include/save_binlog_position.inc
|
|
# Must throw warning
|
|
ALTER USER IF EXISTS userY IDENTIFIED BY 'abcd', userW IDENTIFIED BY 'haha';
|
|
Warnings:
|
|
Note 3162 Authorization ID 'userW'@'%' does not exist.
|
|
SELECT user FROM mysql.user;
|
|
user
|
|
userY
|
|
mysql.infoschema
|
|
mysql.session
|
|
mysql.sys
|
|
root
|
|
# This event sequence pattern MUST be present in binlog: !Q(ALTER USER.*userY.*userW.*)
|
|
include/assert_binlog_events.inc
|
|
DROP USER userY;
|
|
include/save_binlog_position.inc
|
|
# End : Tests for ALTER USER IF EXISTS
|
|
# -----------------------------------------------------------------------
|