maria-discuss team mailing list archive

[Sergei Golubchik <serg@xxxxxxxxxxx>]

Hi, Reindl!

On Sep 05, Reindl Harald wrote:
> 
> > 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?

for a couple of reasons.
every new release was adding more columns to mysql.user, and
mysql_upgrade was getting more and more complex trying to convert all
possible intermediate table structures into the latest. and the
privilege code was doing the same, as it should work without
mysql_upgrade, so it was guessing and adapting to all intermediate
numbers of columns. Not always correctly, the latest bug here is MDEV-23201.

with a json we'll never need to run mysql_upgrade on mysql.user and
mysql.global_priv ever. I hope :)

a second reason - mysql.user can only have one auth plugin per user,
while 10.4 supports multiple alternative authentications.

besides, it doesn't matter whether the structure is clear or json-like
crap, privilege tables are internal matter of the server, users can but
aren't supposed to look inside, there is no guarantee that the structure
will be stable or readable. changing privilege tables directly is
fragile and is not recommended since 2000.

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

Hmm, okay. I see. Unfortunately it means creating three distinct
accounts doing grants three times, etc. And they can get out of sync
too.

But you're right, if the goal is to allow only access from three
different hosts, then I don't see any other solution.

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