1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
|
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
# -----------------------------------------------------------------------
|
