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
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
|
let $REGEX_VERSION_ID=/$mysql_get_server_version/VERSION_ID/;
let $REGEX_PASSWORD_LAST_CHANGED=/password_last_changed": [0-9]*/password_last_changed": #/;
let $REGEX_GLOBAL_PRIV=$REGEX_PASSWORD_LAST_CHANGED $REGEX_VERSION_ID;
#
# MDEV-11340 Allow multiple alternative authentication methods for the same user
#
--source include/have_unix_socket.inc
if (`SELECT '$USER' = 'mysqltest1'`) {
skip USER is mysqltest1;
}
if (!$AUTH_ED25519_SO) {
skip No auth_ed25519 plugin;
}
--let $plugindir=`SELECT @@global.plugin_dir`
install soname 'auth_ed25519';
--let $try_auth=$MYSQL_TEST < $MYSQLTEST_VARDIR/tmp/peercred_test.txt 2>&1
--write_file $MYSQLTEST_VARDIR/tmp/peercred_test.txt
--let $replace1=$USER@localhost
--let $replace2=$USER@%
--replace_result $replace1 "USER@localhost" $replace2 "USER@%"
select user(), current_user(), database();
EOF
--let $creplace=create user $USER
--let $dreplace=drop user $USER
#
# socket,password
#
--replace_result $creplace "create user USER"
eval $creplace identified via unix_socket OR mysql_native_password as password("GOOD");
create user mysqltest1 identified via unix_socket OR mysql_native_password as password("good");
show create user mysqltest1;
--echo # name match = ok
--exec $try_auth -u $USER
--echo # name does not match, password good = ok
--exec $try_auth -u mysqltest1 -pgood
--echo # name does not match, password bad = failure
--error 1
--exec $try_auth -u mysqltest1 -pbad
--replace_result $dreplace "drop user USER"
eval $dreplace, mysqltest1;
#
# password,socket
#
--replace_result $creplace "create user USER"
eval $creplace identified via mysql_native_password as password("GOOD") OR unix_socket;
create user mysqltest1 identified via mysql_native_password as password("good") OR unix_socket;
show create user mysqltest1;
--echo # name match = ok
--exec $try_auth -u $USER
--echo # name does not match, password good = ok
--exec $try_auth -u mysqltest1 -pgood
--echo # name does not match, password bad = failure
--error 1
--exec $try_auth -u mysqltest1 -pbad
--replace_result $dreplace "drop user USER"
eval $dreplace, mysqltest1;
#
# socket,ed25519
#
--replace_result $creplace "create user USER"
eval $creplace identified via unix_socket OR ed25519 as password("GOOD");
create user mysqltest1 identified via unix_socket OR ed25519 as password("good");
show create user mysqltest1;
--echo # name match = ok
--exec $try_auth -u $USER
--echo # name does not match, password good = ok
--exec $try_auth -u mysqltest1 -pgood
--echo # name does not match, password bad = failure
--error 1
--exec $try_auth -u mysqltest1 -pbad
--replace_result $dreplace "drop user USER"
eval $dreplace, mysqltest1;
#
# ed25519,socket
#
--replace_result $creplace "create user USER"
eval $creplace identified via ed25519 as password("GOOD") OR unix_socket;
create user mysqltest1 identified via ed25519 as password("good") OR unix_socket;
show create user mysqltest1;
--echo # name match = ok
--exec $try_auth -u $USER
--echo # name does not match, password good = ok
--exec $try_auth -u mysqltest1 -pgood
--echo # name does not match, password bad = failure
--error 1
--exec $try_auth -u mysqltest1 -pbad
--replace_result $dreplace "drop user USER"
eval $dreplace, mysqltest1;
#
# ed25519,socket,password
#
--replace_result $creplace "create user USER"
eval $creplace identified via ed25519 as password("GOOD") OR unix_socket OR mysql_native_password as password("works");
create user mysqltest1 identified via ed25519 as password("good") OR unix_socket OR mysql_native_password as password("works");
show create user mysqltest1;
--echo # name match = ok
--exec $try_auth -u $USER
--echo # name does not match, password good = ok
--exec $try_auth -u mysqltest1 -pgood
--echo # name does not match, second password works = ok
--exec $try_auth -u mysqltest1 -pworks
--echo # name does not match, password bad = failure
--error 1
--exec $try_auth -u mysqltest1 -pbad
--replace_result $dreplace "drop user USER"
eval $dreplace, mysqltest1;
#
# password,password
#
create user mysqltest1 identified via mysql_native_password as password("good") OR mysql_native_password as password("works");
show create user mysqltest1;
--echo # password good = ok
--exec $try_auth -u mysqltest1 -pgood
--echo # second password works = ok
--exec $try_auth -u mysqltest1 -pworks
--echo # password bad = failure
--error 1
--exec $try_auth -u mysqltest1 -pbad
drop user mysqltest1;
#
# show grants, flush privileges, set password, alter user
#
create user mysqltest1 identified via ed25519 as password("good") OR unix_socket OR mysql_native_password as password("works");
show grants for mysqltest1;
--replace_regex $REGEX_GLOBAL_PRIV
select json_detailed(priv) from mysql.global_priv where user='mysqltest1';
select password,plugin,authentication_string from mysql.user where user='mysqltest1';
flush privileges;
show create user mysqltest1;
set password for mysqltest1 = password('foobar');
show create user mysqltest1;
alter user mysqltest1 identified via unix_socket OR mysql_native_password as password("some");
show create user mysqltest1;
set password for mysqltest1 = password('foobar');
show create user mysqltest1;
alter user mysqltest1 identified via unix_socket;
--error ER_SET_PASSWORD_AUTH_PLUGIN
set password for mysqltest1 = password('bla');
alter user mysqltest1 identified via mysql_native_password as password("some") or unix_socket;
show create user mysqltest1;
drop user mysqltest1;
--source include/switch_to_mysql_user.inc
--replace_regex /\d{6}/XX.YY.ZZ/
--error ER_COL_COUNT_DOESNT_MATCH_PLEASE_UPDATE
create user mysqltest1 identified via ed25519 as password("good") OR unix_socket OR mysql_native_password as password("works");
--source include/switch_to_mysql_global_priv.inc
#
# invalid password,socket
#
--replace_result $creplace "create user USER"
eval $creplace identified via mysql_native_password as '1234567890123456789012345678901234567890a' OR unix_socket;
create user mysqltest1 identified via mysql_native_password as '1234567890123456789012345678901234567890a' OR unix_socket;
update mysql.global_priv set priv=replace(priv, '1234567890123456789012345678901234567890a', 'invalid password');
flush privileges;
show create user mysqltest1;
--echo # name match = ok
--exec $try_auth -u $USER
--echo # name does not match = failure
--error 1
--exec $try_auth -u mysqltest1
--echo # SET PASSWORD helps
set password for mysqltest1 = password('bla');
--exec $try_auth -u mysqltest1 -pbla
--replace_result $dreplace "drop user USER"
eval $dreplace, mysqltest1;
#
# missing client-side plugin
#
create user mysqltest1 identified via ed25519 as password("good");
show create user mysqltest1;
--echo # no plugin = failure
--replace_result $plugindir <PLUGINDIR>
--error 1
--exec $try_auth -u mysqltest1 -pgood --plugin-dir=$plugindir/no
alter user mysqltest1 identified via ed25519 as password("good") OR mysql_native_password as password("works");
show create user mysqltest1;
--echo # no plugin = failure
--error 1
--exec $try_auth -u mysqltest1 -pgood --plugin-dir=$plugindir/no
--echo # no plugin, second password works = ok
--exec $try_auth -u mysqltest1 -pworks --plugin-dir=$plugindir/no
drop user mysqltest1;
uninstall soname 'auth_ed25519';
--remove_file $MYSQLTEST_VARDIR/tmp/peercred_test.txt
#
# MDEV-21928 ALTER USER doesn't remove excess authentication plugins from mysql.global_priv
#
create user mysqltest1 identified via mysql_native_password as password("good") OR unix_socket;
show create user mysqltest1;
alter user mysqltest1 identified via mysql_native_password as password("better");
show create user mysqltest1;
flush privileges;
show create user mysqltest1;
drop user mysqltest1;
|
