maria-discuss team mailing list archive

[Sergei Golubchik <serg@xxxxxxxxxxx>]

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

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.

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?

Regards,
Sergei
VP of MariaDB Server Engineering
and security@xxxxxxxxxxx