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 _______________________________________________ Mailing list: https://launchpad.net/~maria-discuss Post to : maria-discuss@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-discuss More help : https://help.launchpad.net/ListHelp