Re: [Maria-discuss] Set password for all users, regardless of host value
Hi, Chris!
On Sep 07, Chris Ross (cross2) wrote:
>
>
> On 9/5/20, 04:37, "Sergei Golubchik" 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 secur...@mariadb.org
___
Mailing list: https://launchpad.net/~maria-discuss
Post to : maria-discuss@lists.launchpad.net
Unsubscribe : https://launchpad.net/~maria-discuss
More help : https://help.launchpad.net/ListHelp
Re: [Maria-discuss] Set password for all users, regardless of host value
On 9/5/20, 04:37, "Sergei Golubchik" 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
___
Mailing list: https://launchpad.net/~maria-discuss
Post to : maria-discuss@lists.launchpad.net
Unsubscribe : https://launchpad.net/~maria-discuss
More help : https://help.launchpad.net/ListHelp
Re: [Maria-discuss] Set password for all users, regardless of host value
On 05.09.20 12:39, Reindl Harald wrote: and how does json crap magically solve the issue? no more used table schema changes, only table content changes from now on whenever privileges are added from now on. When adding a new privilege no changes on the existing users are even needed at all. When splitting up existing privileges to become more granular the story is a bit more complicated though, see e.g.: https://jira.mariadb.org/browse/MDEV-23610 (That problem is not really json specific though, it would have been the same with the old "one column per privilege" approach. The json change lowered the bar for adding new privileges a bit though) -- Hartmut Holzgraefe, Principal Support Engineer (EMEA) MariaDB Corporation | http://www.mariadb.com/ ___ Mailing list: https://launchpad.net/~maria-discuss Post to : maria-discuss@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-discuss More help : https://help.launchpad.net/ListHelp
Re: [Maria-discuss] Set password for all users, regardless of host value
Am 05.09.20 um 12:32 schrieb Sergei Golubchik: >> 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. which was easy to avoid by simply fire up the same queries followed by "flush privileges" before the json crap everytime when json is the solution i want my problem back ___ Mailing list: https://launchpad.net/~maria-discuss Post to : maria-discuss@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-discuss More help : https://help.launchpad.net/ListHelp
Re: [Maria-discuss] Set password for all users, regardless of host value
Am 05.09.20 um 12:32 schrieb Sergei Golubchik: > On Sep 05, Reindl Harald wrote: >> 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. and how does json crap magically solve the issue? > with a json we'll never need to run mysql_upgrade on mysql.user and > mysql.global_priv ever. I hope :) yeah, throwing away structure to not need to update structure in the future - my god send an asteroid making and end to the human race :-) > 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 it worked - cleaner and quicker than crafting special queries you mostly need only once or twice per year one reason going back to 10.3 as long as possible period ___ Mailing list: https://launchpad.net/~maria-discuss Post to : maria-discuss@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-discuss More help : https://help.launchpad.net/ListHelp
Re: [Maria-discuss] Set password for all users, regardless of host value
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 secur...@mariadb.org ___ Mailing list: https://launchpad.net/~maria-discuss Post to : maria-discuss@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-discuss More help : https://help.launchpad.net/ListHelp
Re: [Maria-discuss] Set password for all users, regardless of host value
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
___
Mailing list: https://launchpad.net/~maria-discuss
Post to : maria-discuss@lists.launchpad.net
Unsubscribe : https://launchpad.net/~maria-discuss
More help : https://help.launchpad.net/ListHelp
Re: [Maria-discuss] Set password for all users, regardless of host value
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 secur...@mariadb.org
___
Mailing list: https://launchpad.net/~maria-discuss
Post to : maria-discuss@lists.launchpad.net
Unsubscribe : https://launchpad.net/~maria-discuss
More help : https://help.launchpad.net/ListHelp
