Hi, Chris! On Sep 07, Chris Ross (cross2) wrote:
On 9/5/20, 04:37, "Sergei Golubchik" <serg@mariadb.org> wrote:
Hi, Chris!
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
Hmm. That is a little ugly. This is of course, in our perl/python/shell scripts, being passed into "mysql -e \"command\"". Making something like that, which is not even syntax I recognize, harder.
If you do it from a script and you know the hostname can only be either localhost or %, you can use a much simpler statement alter user if exists '$username'@localhost identified by '$rawpassword', '$username'@'%' identified by '$rawpassword';
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.
This is at least closer to what the old MySQL 5.6 "update mysql.user set password=PASSWORD() where user='val'" looked like. By "fragile and not recommended", for MariaDB 10.5, does that mean it may well stop working in 10.6 or 10.7 ?
No, sorry. I mean updating privilege tables directly is fragile and not recommended in general. Since MySQL 3.23, for the last 20 years. It's not more fragile or less recommended than before, quite the contrary. For example, MySQL 5.7 has removed the 'password' column and your update would not work there (and they were free to do it, precisely because there was no promise to keep privilege tables compatible for direct updates). We've changed to json with an intention to never change privilege table structure again, so there are definitely no plans to make any changes that would break the update statement as above.
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?
The issue is not as complicated as that. Some users have '%', others have 'localhost'. So most users only have one value for hostspec, but different users have different values, so looping through usernames in a script and running a SQL command for each, puts me here.
Regards, Sergei VP of MariaDB Server Engineering and security@mariadb.org