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
|
install soname 'auth_ed25519';
create user 'USER' 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;
CREATE USER for mysqltest1@%
CREATE USER `mysqltest1`@`%` IDENTIFIED VIA unix_socket OR mysql_native_password USING '*8409037B3E362D6DAE24C8E667F4D3B66716144E'
# name match = ok
select user(), current_user(), database();
user() current_user() database()
USER@localhost USER@% test
# name does not match, password good = ok
select user(), current_user(), database();
user() current_user() database()
mysqltest1@localhost mysqltest1@% test
# name does not match, password bad = failure
mysqltest: Could not open connection 'default': 1045 Access denied for user 'mysqltest1'@'localhost' (using password: YES)
drop user 'USER', mysqltest1;
create user 'USER' 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;
CREATE USER for mysqltest1@%
CREATE USER `mysqltest1`@`%` IDENTIFIED VIA mysql_native_password USING '*8409037B3E362D6DAE24C8E667F4D3B66716144E' OR unix_socket
# name match = ok
select user(), current_user(), database();
user() current_user() database()
USER@localhost USER@% test
# name does not match, password good = ok
select user(), current_user(), database();
user() current_user() database()
mysqltest1@localhost mysqltest1@% test
# name does not match, password bad = failure
mysqltest: Could not open connection 'default': 1698 Access denied for user 'mysqltest1'@'localhost'
drop user 'USER', mysqltest1;
create user 'USER' 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;
CREATE USER for mysqltest1@%
CREATE USER `mysqltest1`@`%` IDENTIFIED VIA unix_socket OR ed25519 USING 'F4aF8bw7130VaRbdLCl4f/P/wkjDmgJXwWvpJ5gmsZc'
# name match = ok
select user(), current_user(), database();
user() current_user() database()
USER@localhost USER@% test
# name does not match, password good = ok
select user(), current_user(), database();
user() current_user() database()
mysqltest1@localhost mysqltest1@% test
# name does not match, password bad = failure
mysqltest: Could not open connection 'default': 1045 Access denied for user 'mysqltest1'@'localhost' (using password: YES)
drop user 'USER', mysqltest1;
create user 'USER' 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;
CREATE USER for mysqltest1@%
CREATE USER `mysqltest1`@`%` IDENTIFIED VIA ed25519 USING 'F4aF8bw7130VaRbdLCl4f/P/wkjDmgJXwWvpJ5gmsZc' OR unix_socket
# name match = ok
select user(), current_user(), database();
user() current_user() database()
USER@localhost USER@% test
# name does not match, password good = ok
select user(), current_user(), database();
user() current_user() database()
mysqltest1@localhost mysqltest1@% test
# name does not match, password bad = failure
mysqltest: Could not open connection 'default': 1698 Access denied for user 'mysqltest1'@'localhost'
drop user 'USER', mysqltest1;
create user 'USER' 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;
CREATE USER for mysqltest1@%
CREATE USER `mysqltest1`@`%` IDENTIFIED VIA ed25519 USING 'F4aF8bw7130VaRbdLCl4f/P/wkjDmgJXwWvpJ5gmsZc' OR unix_socket OR mysql_native_password USING '*7D8C3DF236D9163B6C274A9D47704BC496988460'
# name match = ok
select user(), current_user(), database();
user() current_user() database()
USER@localhost USER@% test
# name does not match, password good = ok
select user(), current_user(), database();
user() current_user() database()
mysqltest1@localhost mysqltest1@% test
# name does not match, second password works = ok
select user(), current_user(), database();
user() current_user() database()
mysqltest1@localhost mysqltest1@% test
# name does not match, password bad = failure
mysqltest: Could not open connection 'default': 1045 Access denied for user 'mysqltest1'@'localhost' (using password: YES)
drop user 'USER', mysqltest1;
create user mysqltest1 identified via mysql_native_password as password("good") OR mysql_native_password as password("works");
show create user mysqltest1;
CREATE USER for mysqltest1@%
CREATE USER `mysqltest1`@`%` IDENTIFIED VIA mysql_native_password USING '*8409037B3E362D6DAE24C8E667F4D3B66716144E' OR mysql_native_password USING '*7D8C3DF236D9163B6C274A9D47704BC496988460'
# password good = ok
select user(), current_user(), database();
user() current_user() database()
mysqltest1@localhost mysqltest1@% test
# second password works = ok
select user(), current_user(), database();
user() current_user() database()
mysqltest1@localhost mysqltest1@% test
# password bad = failure
mysqltest: Could not open connection 'default': 1045 Access denied for user 'mysqltest1'@'localhost' (using password: YES)
drop user mysqltest1;
create user mysqltest1 identified via ed25519 as password("good") OR unix_socket OR mysql_native_password as password("works");
show grants for mysqltest1;
Grants for mysqltest1@%
GRANT USAGE ON *.* TO `mysqltest1`@`%` IDENTIFIED VIA ed25519 USING 'F4aF8bw7130VaRbdLCl4f/P/wkjDmgJXwWvpJ5gmsZc' OR unix_socket OR mysql_native_password USING '*7D8C3DF236D9163B6C274A9D47704BC496988460'
select json_detailed(priv) from mysql.global_priv where user='mysqltest1';
json_detailed(priv)
{
"access": 0,
"version_id": VERSION_ID,
"plugin": "mysql_native_password",
"authentication_string": "*7D8C3DF236D9163B6C274A9D47704BC496988460",
"auth_or":
[
{
"plugin": "ed25519",
"authentication_string": "F4aF8bw7130VaRbdLCl4f/P/wkjDmgJXwWvpJ5gmsZc"
},
{
"plugin": "unix_socket"
},
{
}
],
"password_last_changed": #
}
select password,plugin,authentication_string from mysql.user where user='mysqltest1';
Password plugin authentication_string
*7D8C3DF236D9163B6C274A9D47704BC496988460 mysql_native_password *7D8C3DF236D9163B6C274A9D47704BC496988460
flush privileges;
show create user mysqltest1;
CREATE USER for mysqltest1@%
CREATE USER `mysqltest1`@`%` IDENTIFIED VIA ed25519 USING 'F4aF8bw7130VaRbdLCl4f/P/wkjDmgJXwWvpJ5gmsZc' OR unix_socket OR mysql_native_password USING '*7D8C3DF236D9163B6C274A9D47704BC496988460'
set password for mysqltest1 = password('foobar');
show create user mysqltest1;
CREATE USER for mysqltest1@%
CREATE USER `mysqltest1`@`%` IDENTIFIED VIA ed25519 USING 'qv2mG6HWCuy32Slb5xhV4THStewNz2VINVPbgk+XAJ8' OR unix_socket OR mysql_native_password USING '*7D8C3DF236D9163B6C274A9D47704BC496988460'
alter user mysqltest1 identified via unix_socket OR mysql_native_password as password("some");
show create user mysqltest1;
CREATE USER for mysqltest1@%
CREATE USER `mysqltest1`@`%` IDENTIFIED VIA unix_socket OR mysql_native_password USING '*BFE3F4604CFD21E6595080A261D92EF0183B5971'
set password for mysqltest1 = password('foobar');
show create user mysqltest1;
CREATE USER for mysqltest1@%
CREATE USER `mysqltest1`@`%` IDENTIFIED VIA unix_socket OR mysql_native_password USING '*9B500343BC52E2911172EB52AE5CF4847604C6E5'
alter user mysqltest1 identified via unix_socket;
set password for mysqltest1 = password('bla');
ERROR HY000: SET PASSWORD is ignored for users authenticating via unix_socket plugin
alter user mysqltest1 identified via mysql_native_password as password("some") or unix_socket;
show create user mysqltest1;
CREATE USER for mysqltest1@%
CREATE USER `mysqltest1`@`%` IDENTIFIED VIA mysql_native_password USING '*BFE3F4604CFD21E6595080A261D92EF0183B5971' OR unix_socket
drop user mysqltest1;
# switching from mysql.global_priv to mysql.user
create user mysqltest1 identified via ed25519 as password("good") OR unix_socket OR mysql_native_password as password("works");
ERROR HY000: Column count of mysql.user is wrong. Expected 3, found 47. Created with MariaDB XX.YY.ZZ, now running XX.YY.ZZ. Please use mariadb-upgrade to fix this error
# switching back from mysql.user to mysql.global_priv
create user 'USER' 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;
CREATE USER for mysqltest1@%
CREATE USER `mysqltest1`@`%` IDENTIFIED VIA mysql_native_password USING 'invalid password' OR unix_socket
# name match = ok
select user(), current_user(), database();
user() current_user() database()
USER@localhost USER@% test
# name does not match = failure
mysqltest: Could not open connection 'default': 1698 Access denied for user 'mysqltest1'@'localhost'
# SET PASSWORD helps
set password for mysqltest1 = password('bla');
select user(), current_user(), database();
user() current_user() database()
mysqltest1@localhost mysqltest1@% test
drop user 'USER', mysqltest1;
create user mysqltest1 identified via ed25519 as password("good");
show create user mysqltest1;
CREATE USER for mysqltest1@%
CREATE USER `mysqltest1`@`%` IDENTIFIED VIA ed25519 USING 'F4aF8bw7130VaRbdLCl4f/P/wkjDmgJXwWvpJ5gmsZc'
# no plugin = failure
mysqltest: Could not open connection 'default': 1045 Plugin client_ed25519 could not be loaded: no such file
alter user mysqltest1 identified via ed25519 as password("good") OR mysql_native_password as password("works");
show create user mysqltest1;
CREATE USER for mysqltest1@%
CREATE USER `mysqltest1`@`%` IDENTIFIED VIA ed25519 USING 'F4aF8bw7130VaRbdLCl4f/P/wkjDmgJXwWvpJ5gmsZc' OR mysql_native_password USING '*7D8C3DF236D9163B6C274A9D47704BC496988460'
# no plugin = failure
mysqltest: Could not open connection 'default': 1045 Access denied for user 'mysqltest1'@'localhost' (using password: YES)
# no plugin, second password works = ok
select user(), current_user(), database();
user() current_user() database()
mysqltest1@localhost mysqltest1@% test
drop user mysqltest1;
uninstall soname 'auth_ed25519';
create user mysqltest1 identified via mysql_native_password as password("good") OR unix_socket;
show create user mysqltest1;
CREATE USER for mysqltest1@%
CREATE USER `mysqltest1`@`%` IDENTIFIED VIA mysql_native_password USING '*8409037B3E362D6DAE24C8E667F4D3B66716144E' OR unix_socket
alter user mysqltest1 identified via mysql_native_password as password("better");
show create user mysqltest1;
CREATE USER for mysqltest1@%
CREATE USER `mysqltest1`@`%` IDENTIFIED BY PASSWORD '*6E6CABC9967AB586F009616BA69DAC2953849C88'
flush privileges;
show create user mysqltest1;
CREATE USER for mysqltest1@%
CREATE USER `mysqltest1`@`%` IDENTIFIED BY PASSWORD '*6E6CABC9967AB586F009616BA69DAC2953849C88'
drop user mysqltest1;
|
