| 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; |