Hi, Reindl! On Sep 05, Reindl Harald wrote:
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?
for a couple of reasons. every new release was adding more columns to mysql.user, and mysql_upgrade was getting more and more complex trying to convert all possible intermediate table structures into the latest. and the privilege code was doing the same, as it should work without mysql_upgrade, so it was guessing and adapting to all intermediate numbers of columns. Not always correctly, the latest bug here is MDEV-23201. with a json we'll never need to run mysql_upgrade on mysql.user and mysql.global_priv ever. I hope :) a second reason - mysql.user can only have one auth plugin per user, while 10.4 supports multiple alternative authentications. besides, it doesn't matter whether the structure is clear or json-like crap, privilege tables are internal matter of the server, users can but aren't supposed to look inside, there is no guarantee that the structure will be stable or readable. changing privilege tables directly is fragile and is not recommended since 2000.
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
Hmm, okay. I see. Unfortunately it means creating three distinct accounts doing grants three times, etc. And they can get out of sync too. But you're right, if the goal is to allow only access from three different hosts, then I don't see any other solution. Regards, Sergei VP of MariaDB Server Engineering and security@mariadb.org