Am 05.09.20 um 10:37 schrieb Sergei Golubchik:
Hi, Chris!
On Sep 04, Chris Ross (cross2) wrote:
Hello there. We have scripts to restore credentials to MySQL databases from external store. The mechanism that was in use, however, stores usernames and passwords, without consideration of the scope (host) of that auth record. In older systems, UPDATE mysql.user SET password = PASSWORD(‘rawpassword’) WHERE user = ‘username’ worked, updating it for all values of that user that might exist in the table.
But, I’m not sure how to do this for MariaDB 10.5. Is there way to form an “ALTER USER” statement such that it will set the password for any and all userspecs that exist with the given username? We don’t have that many, and I could iterate the known configurations with “ALTER USER IF EXISTS”, but I worry that might miss things added in the future.
Yes, you can do an ALTER USER statement, something like
for x in (select host from mysql.global_priv where user='username') do execute immediate concat('alter user ', 'username', '@`', x.host, '` identified ...and so on' ); end for
wow is that ugly
you can do an UPDATE too, like
update mysql.global_priv set priv=json_set(priv, 'authentication_string', password(‘rawpassword’))
this is rather fragile and of course not recommended.
well, why in the world was a clear structure replaced with some json-like crap?
But I think what you're doing is somewhat strange. You have multiple accounts with the same username and different hosts, and you want the same password for them all? Why do you have multiple accounts in the first place?
i guess beause not everybody likes % when a user should only have access from 3 hosts - defense in depth