maria-discuss team mailing list archive

[Sergei Golubchik <serg@xxxxxxxxxxx>]

Hi, Chris!

On Sep 07, Chris Ross (cross2) wrote:
> 
> 
> 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.

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@xxxxxxxxxxx