Debian Codegraph @

mysql-test/suite/plugins/r/multiauth.result

raw
1 install soname 'auth_ed25519';
2 create user 'USER' identified via unix_socket OR mysql_native_password as password("GOOD");
3 grant select on test.* to 'USER' ;
4 create user mysqltest1 identified via unix_socket OR mysql_native_password as password("good");
5 grant select on test.* to mysqltest1;
6 show create user mysqltest1;
7 CREATE USER for mysqltest1@%
8 CREATE USER `mysqltest1`@`%` IDENTIFIED VIA unix_socket OR mysql_native_password USING '*8409037B3E362D6DAE24C8E667F4D3B66716144E'
9 # name match = ok
10 select user(), current_user(), database();
11 user() current_user() database()
12 USER@localhost USER@% test
13 # name does not match, password good = ok
14 select user(), current_user(), database();
15 user() current_user() database()
16 mysqltest1@localhost mysqltest1@% test
17 # name does not match, password bad = failure
18 mysqltest: Could not open connection 'default': 1045 Access denied for user 'mysqltest1'@'localhost' (using password: YES)
19 drop user 'USER', mysqltest1;
20 create user 'USER' identified via mysql_native_password as password("GOOD") OR unix_socket;
21 grant select on test.* to 'USER' ;
22 create user mysqltest1 identified via mysql_native_password as password("good") OR unix_socket;
23 grant select on test.* to mysqltest1;
24 show create user mysqltest1;
25 CREATE USER for mysqltest1@%
26 CREATE USER `mysqltest1`@`%` IDENTIFIED VIA mysql_native_password USING '*8409037B3E362D6DAE24C8E667F4D3B66716144E' OR unix_socket
27 # name match = ok
28 select user(), current_user(), database();
29 user() current_user() database()
30 USER@localhost USER@% test
31 # name does not match, password good = ok
32 select user(), current_user(), database();
33 user() current_user() database()
34 mysqltest1@localhost mysqltest1@% test
35 # name does not match, password bad = failure
36 mysqltest: Could not open connection 'default': 1698 Access denied for user 'mysqltest1'@'localhost'
37 drop user 'USER', mysqltest1;
38 create user 'USER' identified via unix_socket OR ed25519 as password("GOOD");
39 grant select on test.* to 'USER' ;
40 create user mysqltest1 identified via unix_socket OR ed25519 as password("good");
41 grant select on test.* to mysqltest1;
42 show create user mysqltest1;
43 CREATE USER for mysqltest1@%
44 CREATE USER `mysqltest1`@`%` IDENTIFIED VIA unix_socket OR ed25519 USING 'F4aF8bw7130VaRbdLCl4f/P/wkjDmgJXwWvpJ5gmsZc'
45 # name match = ok
46 select user(), current_user(), database();
47 user() current_user() database()
48 USER@localhost USER@% test
49 # name does not match, password good = ok
50 select user(), current_user(), database();
51 user() current_user() database()
52 mysqltest1@localhost mysqltest1@% test
53 # name does not match, password bad = failure
54 mysqltest: Could not open connection 'default': 1045 Access denied for user 'mysqltest1'@'localhost' (using password: YES)
55 drop user 'USER', mysqltest1;
56 create user 'USER' identified via ed25519 as password("GOOD") OR unix_socket;
57 grant select on test.* to 'USER' ;
58 create user mysqltest1 identified via ed25519 as password("good") OR unix_socket;
59 grant select on test.* to mysqltest1;
60 show create user mysqltest1;
61 CREATE USER for mysqltest1@%
62 CREATE USER `mysqltest1`@`%` IDENTIFIED VIA ed25519 USING 'F4aF8bw7130VaRbdLCl4f/P/wkjDmgJXwWvpJ5gmsZc' OR unix_socket
63 # name match = ok
64 select user(), current_user(), database();
65 user() current_user() database()
66 USER@localhost USER@% test
67 # name does not match, password good = ok
68 select user(), current_user(), database();
69 user() current_user() database()
70 mysqltest1@localhost mysqltest1@% test
71 # name does not match, password bad = failure
72 mysqltest: Could not open connection 'default': 1698 Access denied for user 'mysqltest1'@'localhost'
73 drop user 'USER', mysqltest1;
74 create user 'USER' identified via ed25519 as password("GOOD") OR unix_socket OR mysql_native_password as password("works");
75 grant select on test.* to 'USER' ;
76 create user mysqltest1 identified via ed25519 as password("good") OR unix_socket OR mysql_native_password as password("works");
77 grant select on test.* to mysqltest1;
78 show create user mysqltest1;
79 CREATE USER for mysqltest1@%
80 CREATE USER `mysqltest1`@`%` IDENTIFIED VIA ed25519 USING 'F4aF8bw7130VaRbdLCl4f/P/wkjDmgJXwWvpJ5gmsZc' OR unix_socket OR mysql_native_password USING '*7D8C3DF236D9163B6C274A9D47704BC496988460'
81 # name match = ok
82 select user(), current_user(), database();
83 user() current_user() database()
84 USER@localhost USER@% test
85 # name does not match, password good = ok
86 select user(), current_user(), database();
87 user() current_user() database()
88 mysqltest1@localhost mysqltest1@% test
89 # name does not match, second password works = ok
90 select user(), current_user(), database();
91 user() current_user() database()
92 mysqltest1@localhost mysqltest1@% test
93 # name does not match, password bad = failure
94 mysqltest: Could not open connection 'default': 1045 Access denied for user 'mysqltest1'@'localhost' (using password: YES)
95 drop user 'USER', mysqltest1;
96 create user mysqltest1 identified via mysql_native_password as password("good") OR mysql_native_password as password("works");
97 grant select on test.* to mysqltest1;
98 show create user mysqltest1;
99 CREATE USER for mysqltest1@%
100 CREATE USER `mysqltest1`@`%` IDENTIFIED VIA mysql_native_password USING '*8409037B3E362D6DAE24C8E667F4D3B66716144E' OR mysql_native_password USING '*7D8C3DF236D9163B6C274A9D47704BC496988460'
101 # password good = ok
102 select user(), current_user(), database();
103 user() current_user() database()
104 mysqltest1@localhost mysqltest1@% test
105 # second password works = ok
106 select user(), current_user(), database();
107 user() current_user() database()
108 mysqltest1@localhost mysqltest1@% test
109 # password bad = failure
110 mysqltest: Could not open connection 'default': 1045 Access denied for user 'mysqltest1'@'localhost' (using password: YES)
111 drop user mysqltest1;
112 create user mysqltest1 identified via ed25519 as password("good") OR unix_socket OR mysql_native_password as password("works");
113 show grants for mysqltest1;
114 Grants for mysqltest1@%
115 GRANT USAGE ON *.* TO `mysqltest1`@`%` IDENTIFIED VIA ed25519 USING 'F4aF8bw7130VaRbdLCl4f/P/wkjDmgJXwWvpJ5gmsZc' OR unix_socket OR mysql_native_password USING '*7D8C3DF236D9163B6C274A9D47704BC496988460'
116 select json_detailed(priv) from mysql.global_priv where user='mysqltest1';
117 json_detailed(priv)
118 {
119 "access": 0,
120 "version_id": VERSION_ID,
121 "plugin": "mysql_native_password",
122 "authentication_string": "*7D8C3DF236D9163B6C274A9D47704BC496988460",
123 "auth_or":
124 [
125 {
126 "plugin": "ed25519",
127 "authentication_string": "F4aF8bw7130VaRbdLCl4f/P/wkjDmgJXwWvpJ5gmsZc"
128 },
129 {
130 "plugin": "unix_socket"
131 },
132 {
133 }
134 ],
135 "password_last_changed": #
136 }
137 select password,plugin,authentication_string from mysql.user where user='mysqltest1';
138 Password plugin authentication_string
139 *7D8C3DF236D9163B6C274A9D47704BC496988460 mysql_native_password *7D8C3DF236D9163B6C274A9D47704BC496988460
140 flush privileges;
141 show create user mysqltest1;
142 CREATE USER for mysqltest1@%
143 CREATE USER `mysqltest1`@`%` IDENTIFIED VIA ed25519 USING 'F4aF8bw7130VaRbdLCl4f/P/wkjDmgJXwWvpJ5gmsZc' OR unix_socket OR mysql_native_password USING '*7D8C3DF236D9163B6C274A9D47704BC496988460'
144 set password for mysqltest1 = password('foobar');
145 show create user mysqltest1;
146 CREATE USER for mysqltest1@%
147 CREATE USER `mysqltest1`@`%` IDENTIFIED VIA ed25519 USING 'qv2mG6HWCuy32Slb5xhV4THStewNz2VINVPbgk+XAJ8' OR unix_socket OR mysql_native_password USING '*7D8C3DF236D9163B6C274A9D47704BC496988460'
148 alter user mysqltest1 identified via unix_socket OR mysql_native_password as password("some");
149 show create user mysqltest1;
150 CREATE USER for mysqltest1@%
151 CREATE USER `mysqltest1`@`%` IDENTIFIED VIA unix_socket OR mysql_native_password USING '*BFE3F4604CFD21E6595080A261D92EF0183B5971'
152 set password for mysqltest1 = password('foobar');
153 ERROR HY000: SET PASSWORD is not applicable for users authenticating via unix_socket plugin
154 show create user mysqltest1;
155 CREATE USER for mysqltest1@%
156 CREATE USER `mysqltest1`@`%` IDENTIFIED VIA unix_socket OR mysql_native_password USING '*9B500343BC52E2911172EB52AE5CF4847604C6E5'
157 alter user mysqltest1 identified via unix_socket;
158 set password for mysqltest1 = password('bla');
159 ERROR HY000: SET PASSWORD is not applicable for users authenticating via unix_socket plugin
160 alter user mysqltest1 identified via mysql_native_password as password("some") or unix_socket;
161 show create user mysqltest1;
162 CREATE USER for mysqltest1@%
163 CREATE USER `mysqltest1`@`%` IDENTIFIED VIA mysql_native_password USING '*BFE3F4604CFD21E6595080A261D92EF0183B5971' OR unix_socket
164 drop user mysqltest1;
165 # switching from mysql.global_priv to mysql.user
166 create user mysqltest1 identified via ed25519 as password("good") OR unix_socket OR mysql_native_password as password("works");
167 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
168 # switching back from mysql.user to mysql.global_priv
169 create user 'USER' identified via mysql_native_password as '1234567890123456789012345678901234567890a' OR unix_socket;
170 grant select on test.* to 'USER' ;
171 create user mysqltest1 identified via mysql_native_password as '1234567890123456789012345678901234567890a' OR unix_socket;
172 grant select on test.* to mysqltest1;
173 update mysql.global_priv set priv=replace(priv, '1234567890123456789012345678901234567890a', 'invalid password');
174 flush privileges;
175 show create user mysqltest1;
176 CREATE USER for mysqltest1@%
177 CREATE USER `mysqltest1`@`%` IDENTIFIED VIA mysql_native_password USING 'invalid password' OR unix_socket
178 # name match = ok
179 select user(), current_user(), database();
180 user() current_user() database()
181 USER@localhost USER@% test
182 # name does not match = failure
183 mysqltest: Could not open connection 'default': 1698 Access denied for user 'mysqltest1'@'localhost'
184 # SET PASSWORD helps
185 set password for mysqltest1 = password('bla');
186 select user(), current_user(), database();
187 user() current_user() database()
188 mysqltest1@localhost mysqltest1@% test
189 drop user 'USER', mysqltest1;
190 create user mysqltest1 identified via ed25519 as password("good");
191 grant select on test.* to mysqltest1;
192 show create user mysqltest1;
193 CREATE USER for mysqltest1@%
194 CREATE USER `mysqltest1`@`%` IDENTIFIED VIA ed25519 USING 'F4aF8bw7130VaRbdLCl4f/P/wkjDmgJXwWvpJ5gmsZc'
195 # no plugin = failure
196 mysqltest: Could not open connection 'default': 1045 Plugin client_ed25519 could not be loaded: no such file
197 alter user mysqltest1 identified via ed25519 as password("good") OR mysql_native_password as password("works");
198 show create user mysqltest1;
199 CREATE USER for mysqltest1@%
200 CREATE USER `mysqltest1`@`%` IDENTIFIED VIA ed25519 USING 'F4aF8bw7130VaRbdLCl4f/P/wkjDmgJXwWvpJ5gmsZc' OR mysql_native_password USING '*7D8C3DF236D9163B6C274A9D47704BC496988460'
201 # no plugin = failure
202 mysqltest: Could not open connection 'default': 1045 Access denied for user 'mysqltest1'@'localhost' (using password: YES)
203 # no plugin, second password works = ok
204 select user(), current_user(), database();
205 user() current_user() database()
206 mysqltest1@localhost mysqltest1@% test
207 drop user mysqltest1;
208 uninstall soname 'auth_ed25519';
209 create user mysqltest1 identified via mysql_native_password as password("good") OR unix_socket;
210 show create user mysqltest1;
211 CREATE USER for mysqltest1@%
212 CREATE USER `mysqltest1`@`%` IDENTIFIED VIA mysql_native_password USING '*8409037B3E362D6DAE24C8E667F4D3B66716144E' OR unix_socket
213 alter user mysqltest1 identified via mysql_native_password as password("better");
214 show create user mysqltest1;
215 CREATE USER for mysqltest1@%
216 CREATE USER `mysqltest1`@`%` IDENTIFIED BY PASSWORD '*6E6CABC9967AB586F009616BA69DAC2953849C88'
217 flush privileges;
218 show create user mysqltest1;
219 CREATE USER for mysqltest1@%
220 CREATE USER `mysqltest1`@`%` IDENTIFIED BY PASSWORD '*6E6CABC9967AB586F009616BA69DAC2953849C88'
221 drop user mysqltest1;