[MDEV-21928] ALTER USER doesn't remove excess authentication plugins from mysql.global_priv Created: 2020-03-12  Updated: 2020-08-02  Resolved: 2020-04-27

Status: Closed
Project: MariaDB Server
Component/s: Authentication and Privilege System
Affects Version/s: 10.4.12
Fix Version/s: 10.4.13

Type: Bug Priority: Critical
Reporter: Geoff Montee (Inactive) Assignee: Sergei Golubchik
Resolution: Fixed Votes: 0
Labels: None

Issue Links:
Problem/Incident
is caused by MDEV-11340 Allow multiple alternative authentica... Closed
is caused by MDEV-12484 Enable unix socket authentication by ... Closed
Relates
relates to MDEV-23374 ALTER USER documentation needs to ref... Closed
relates to MDEV-21929 Enhance ALTER USER for multiple authe... Open

 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)



 Comments   
Comment by Geoff Montee (Inactive) [ 2020-03-12 ]

Here's the workaround:

MariaDB [(none)]> UPDATE mysql.global_priv SET Priv=JSON_REMOVE(Priv, '$.auth_or') WHERE User='root' AND Host='localhost';
 
Query OK, 1 row affected (0.001 sec)
 
Rows matched: 1  Changed: 1  Warnings: 0
 
 
 
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 BY PASSWORD '*2470C0C06DEE42FD1618BB99005ADCA2EC9D1E19' |
 
+---------------------------------------------------------------------------------------------------+
 
1 row in set (0.000 sec)

Comment by Elena Stepanova [ 2020-03-12 ]

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.

Comment by Geoff Montee (Inactive) [ 2020-03-12 ]

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.

Comment by Elena Stepanova [ 2020-03-12 ]

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.

Comment by Geoff Montee (Inactive) [ 2020-03-12 ]

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;

Comment by Geoff Montee (Inactive) [ 2020-03-12 ]

I created MDEV-21929 as a feature request to implement support for the more advanced operations, in case we want to do that.

Generated at Thu Feb 08 09:10:54 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.