[Maria-discuss] Set password for all users, regardless of host value
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.
Thank you.
- Chris
-- Chris Ross Software Engineering Manager – Eventing/Datastore Teams cross2@cisco.com
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@mariadb.org
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
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@mariadb.org
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
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)
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
On 9/5/20, 04:37, "Sergei Golubchik" serg@mariadb.org 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
Hi, Chris!
On Sep 07, Chris Ross (cross2) wrote:
On 9/5/20, 04:37, "Sergei Golubchik" serg@mariadb.org 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@mariadb.org
participants (4)
-
Chris Ross (cross2) -
Hartmut Holzgraefe -
Reindl Harald -
Sergei Golubchik