maria-discuss team mailing list archive

[Reindl Harald <h.reindl@xxxxxxxxxxxxx>]

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