maria-discuss team mailing list archive

["Chris Ross (cross2)" <cross2@xxxxxxxxx>]

On 9/5/20, 04:37, "Sergei Golubchik" <serg@xxxxxxxxxxx> 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.

    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 ?

    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.

Thank you.

             - Chris