Details
-
Bug
-
Status: Closed (View Workflow)
-
Critical
-
Resolution: Fixed
-
10.4.12
-
None
Description
If you use ALTER USER to remove unix_socket authentication from root@localhost, it seems to come back after either the server is restarted or FLUSH PRIVILEGES is executed, because the authentication plugin is not removed from the user account's row in mysql.global_priv.
To reproduce:
Check that the user has unix_socket authentication:
MariaDB [(none)]> SHOW CREATE USER 'root'@'localhost'; |
+----------------------------------------------------------------------------------------------------+ |
| CREATE USER for root@localhost | |
+----------------------------------------------------------------------------------------------------+ |
| CREATE USER 'root'@'localhost' IDENTIFIED VIA mysql_native_password USING 'invalid' OR unix_socket | |
+----------------------------------------------------------------------------------------------------+ |
1 row in set (0.000 sec) |
Remove it:
MariaDB [(none)]> ALTER USER 'root'@'localhost' IDENTIFIED VIA mysql_native_password USING PASSWORD('password'); |
Query OK, 0 rows affected (0.001 sec) |
Check again:
MariaDB [(none)]> SHOW CREATE USER 'root'@'localhost'; |
+---------------------------------------------------------------------------------------------------+ |
| CREATE USER for root@localhost | |
+---------------------------------------------------------------------------------------------------+ |
| CREATE USER 'root'@'localhost' IDENTIFIED BY PASSWORD '*2470C0C06DEE42FD1618BB99005ADCA2EC9D1E19' | |
+---------------------------------------------------------------------------------------------------+ |
1 row in set (0.000 sec) |
Restart the server:
$ sudo systemctl restart mariadb
|
Check again:
MariaDB [(none)]> SHOW CREATE USER 'root'@'localhost'; |
+--------------------------------------------------------------------------------------------------------------------------------------+ |
| CREATE USER for root@localhost | |
+--------------------------------------------------------------------------------------------------------------------------------------+ |
| CREATE USER 'root'@'localhost' IDENTIFIED VIA mysql_native_password USING '*2470C0C06DEE42FD1618BB99005ADCA2EC9D1E19' OR unix_socket | |
+--------------------------------------------------------------------------------------------------------------------------------------+ |
1 row in set (0.000 sec) |
More in-depth analysis shows that the unix_socket entry is not actually removed from mysql.global_priv after the ALTER USER statement:
MariaDB [(none)]> ALTER USER 'root'@'localhost' IDENTIFIED VIA mysql_native_password USING PASSWORD('password'); |
Query OK, 0 rows affected (0.001 sec) |
|
|
MariaDB [(none)]> SELECT JSON_DETAILED(Priv) FROM mysql.global_priv WHERE User='root' AND Host='localhost'\G |
*************************** 1. row ***************************
|
JSON_DETAILED(Priv): {
|
"access": 1073741823, |
"plugin": "mysql_native_password", |
"authentication_string": "*2470C0C06DEE42FD1618BB99005ADCA2EC9D1E19", |
"auth_or": |
[
|
|
|
{
|
},
|
|
|
{
|
"plugin": "unix_socket" |
}
|
],
|
"password_last_changed": 1584045156 |
}
|
1 row in set (0.000 sec) |
And the server restart isn't even necessary. The authentication method also comes back with a FLUSH PRIVILEGES:
MariaDB [(none)]> SHOW CREATE USER 'root'@'localhost'; |
+---------------------------------------------------------------------------------------------------+ |
| CREATE USER for root@localhost | |
+---------------------------------------------------------------------------------------------------+ |
| CREATE USER 'root'@'localhost' IDENTIFIED BY PASSWORD '*2470C0C06DEE42FD1618BB99005ADCA2EC9D1E19' | |
+---------------------------------------------------------------------------------------------------+ |
1 row in set (0.000 sec) |
|
|
MariaDB [(none)]> FLUSH PRIVILEGES; |
Query OK, 0 rows affected (0.001 sec) |
|
|
MariaDB [(none)]> SHOW CREATE USER 'root'@'localhost'; |
+--------------------------------------------------------------------------------------------------------------------------------------+ |
| CREATE USER for root@localhost | |
+--------------------------------------------------------------------------------------------------------------------------------------+ |
| CREATE USER 'root'@'localhost' IDENTIFIED VIA mysql_native_password USING '*2470C0C06DEE42FD1618BB99005ADCA2EC9D1E19' OR unix_socket | |
+--------------------------------------------------------------------------------------------------------------------------------------+ |
1 row in set (0.000 sec) |
Attachments
Issue Links
- is caused by
-
MDEV-11340 Allow multiple alternative authentication methods for the same user
-
- Closed
-
-
-
- Closed
-
- relates to
-
-
- Closed
-
-
MDEV-21929 Enhance ALTER USER for multiple authentication methods
-
- Open
-
Activity
It's not just unix_socket, it seems that ALTER USER doesn't remove alternative authentication methods which haven't been explicitly listed. I don't really know if it should, on one hand, it seems rather dangerous; on the other hand, the only other way to remove alternative authentication is through global_priv update, which is cumbersome. The KB article doesn't seem to have been updated to reflect multiple authentication methods, so it's unclear from there, either.
But if it is not supposed to remove extra authentication methods, of course SHOW GRANTS should still list them, so the bug is valid either way. And documentation update is due.
In my opinion, it would make the most sense to remove alternative authentication plugins that aren't listed in the ALTER USER statement.
Faisal originally noticed this bug, so maybe he has an opinion on this too.
The scary part for me is that it would be very prone to user (admin) errors, and those errors will be difficult to fix. I suppose the most common reason for ALTER USER ... IDENTIFIED is adding a new authentication method. It's not an action which happens often, so admins won't remember all the details of how the command works; and it may seem most natural to many people to just provide the new method, especially if it's done for many users. What will happen then is that password authentication along with the passwords will be removed, and admin won't be able to restore it correctly, thus locking users out.
Ideally, in the long term, it would probably make the most sense to provide syntax that support multiple types of operations, such as:
- An ALTER USER statement to set the authentication methods to exactly what the statement says, and discard any existing ones. I think the syntax for that would be the one mentioned above:
-- change authentication method, discard existing ones
|
ALTER USER 'root'@'localhost' IDENTIFIED VIA mysql_native_password USING PASSWORD('password'); |
- An ALTER USER statement to modify an existing authentication method, and leave other authentication methods as-is. This syntax might make sense:
-- change existing authentication method, keep other existing ones too
|
ALTER USER 'root'@'localhost' MODIFY IDENTIFIED VIA mysql_native_password USING PASSWORD('password'); |
- An ALTER USER statement to add a new authentication method to the beginning of the list, and leave other authentication methods as-is. This syntax might make sense:
-- add new authentication method to beginning, keep existing ones too
|
ALTER USER 'root'@'localhost' ADD IDENTIFIED VIA pam USING 'mariadb' FIRST; |
- An ALTER USER statement to add a new authentication method to the end of the list, and leave other authentication methods as-is. This syntax might make sense:
-- add new authentication method to end, keep existing ones too
|
ALTER USER 'root'@'localhost' ADD IDENTIFIED VIA pam USING 'mariadb' LAST; |
I created MDEV-21929 as a feature request to implement support for the more advanced operations, in case we want to do that.
Here's the workaround:
Query OK, 1 row affected (0.001 sec)