[MDEV-21928] ALTER USER doesn't remove excess authentication plugins from mysql.global_priv - Jira

Details

Type: Bug
Status: Closed (View Workflow)
Priority: Critical
Resolution: Fixed
Affects Version/s: 10.4.12
Fix Version/s: 10.4.13
Component/s: Authentication and Privilege System
Labels:
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

MDEV-12484 Enable unix socket authentication by default

Closed

relates to

MDEV-23374 ALTER USER documentation needs to reflect multiple authentication methods

Closed

MDEV-21929 Enhance ALTER USER for multiple authentication methods

Open

Activity

Ascending order - Click to sort in descending order

Geoff Montee (Inactive) created issue - 2020-03-12 20:31

Geoff Montee (Inactive) made changes - 2020-03-12 20:36

Field	Original Value	New Value
Description	If you remove {{unix_socket}} authentication from {{root@localhost}}, it seems to come back after the server is restarted. To reproduce: Check that the user has {{unix_socket}} authentication: {code:sql} 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) {code} Remove it: {code:sql} MariaDB [(none)]> ALTER USER 'root'@'localhost' IDENTIFIED VIA mysql_native_password USING PASSWORD('password'); Query OK, 0 rows affected (0.001 sec) {code} Check again: {code:sql} 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) {code} Restart the server: {code:sh} $ sudo systemctl restart mariadb {code} Check again: {code:sql} 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) {code}	If you remove {{unix_socket}} authentication from {{root@localhost}}, it seems to come back after the server is restarted. To reproduce: Check that the user has {{unix_socket}} authentication: {code:sql} 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) {code} Remove it: {code:sql} MariaDB [(none)]> ALTER USER 'root'@'localhost' IDENTIFIED VIA mysql_native_password USING PASSWORD('password'); Query OK, 0 rows affected (0.001 sec) {code} Check again: {code:sql} 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) {code} Restart the server: {code:sh} $ sudo systemctl restart mariadb {code} Check again: {code:sql} 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) {code} More in-depth analysis shows that the {{unix_socket}} entry is not actually removed from {{mysql.global_priv}} after the {{ALTER USER}} statement: {code:sql} 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) {code} And the server restart isn't even necessary. The authentication method also comes back with a {{FLUSH PRIVILEGES}}: {code:sql} 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) {code}

Geoff Montee (Inactive) made changes - 2020-03-12 20:37

Summary

unix_socket authentication comes back for root@localhost after server restart

ALTER USER doesn't remove excess authentication plugins from mysql.global_priv

Geoff Montee (Inactive) made changes - 2020-03-12 20:38

Description

If you remove {{unix_socket}} authentication from {{root@localhost}}, it seems to come back after the server is restarted.

To reproduce:

Check that the user has {{unix_socket}} authentication:

{code:sql}
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)
{code}

Remove it:

{code:sql}
MariaDB [(none)]> ALTER USER 'root'@'localhost' IDENTIFIED VIA mysql_native_password USING PASSWORD('password');
Query OK, 0 rows affected (0.001 sec)
{code}

Check again:

{code:sql}
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)
{code}

Restart the server:

{code:sh}
$ sudo systemctl restart mariadb
{code}

Check again:

{code:sql}
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)
{code}

More in-depth analysis shows that the {{unix_socket}} entry is not actually removed from {{mysql.global_priv}} after the {{ALTER USER}} statement:

{code:sql}
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)
{code}

And the server restart isn't even necessary. The authentication method also comes back with a {{FLUSH PRIVILEGES}}:

{code:sql}
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)
{code}

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:

{code:sql}
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)
{code}

Remove it:

{code:sql}
MariaDB [(none)]> ALTER USER 'root'@'localhost' IDENTIFIED VIA mysql_native_password USING PASSWORD('password');
Query OK, 0 rows affected (0.001 sec)
{code}

Check again:

{code:sql}
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)
{code}

Restart the server:

{code:sh}
$ sudo systemctl restart mariadb
{code}

Check again:

{code:sql}
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)
{code}

More in-depth analysis shows that the {{unix_socket}} entry is not actually removed from {{mysql.global_priv}} after the {{ALTER USER}} statement:

{code:sql}
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)
{code}

And the server restart isn't even necessary. The authentication method also comes back with a {{FLUSH PRIVILEGES}}:

{code:sql}
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)
{code}

Geoff Montee (Inactive) added a comment - 2020-03-12 20:42

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)

Geoff Montee (Inactive) added a comment - 2020-03-12 20:42 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)

Geoff Montee (Inactive) made changes - 2020-03-12 20:47

Link

This issue is caused by ~~MDEV-11340~~ [ ~~MDEV-11340~~ ]

Geoff Montee (Inactive) made changes - 2020-03-12 20:47

Link

This issue is caused by ~~MDEV-12484~~ [ ~~MDEV-12484~~ ]

Elena Stepanova made changes - 2020-03-12 21:32

Status

Open [ 1 ]

Confirmed [ 10101 ]

Elena Stepanova added a comment - 2020-03-12 21:38

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.

Elena Stepanova added a comment - 2020-03-12 21:38 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.

Geoff Montee (Inactive) added a comment - 2020-03-12 21:44

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.

Geoff Montee (Inactive) added a comment - 2020-03-12 21:44 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.

Elena Stepanova added a comment - 2020-03-12 22:03

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.

Elena Stepanova added a comment - 2020-03-12 22:03 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.

Geoff Montee (Inactive) added a comment - 2020-03-12 22:13

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;

Geoff Montee (Inactive) added a comment - 2020-03-12 22:13 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 ;

Geoff Montee (Inactive) made changes - 2020-03-12 23:49

Link

This issue relates to MDEV-21929 [ MDEV-21929 ]

Geoff Montee (Inactive) added a comment - 2020-03-12 23:50

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

Geoff Montee (Inactive) added a comment - 2020-03-12 23:50 I created MDEV-21929 as a feature request to implement support for the more advanced operations, in case we want to do that.

Sergei Golubchik made changes - 2020-03-15 09:19

Priority

Major [ 3 ]

Critical [ 2 ]

Sergei Golubchik made changes - 2020-04-22 15:58

Status

Confirmed [ 10101 ]

In Progress [ 3 ]

Sergei Golubchik made changes - 2020-04-22 15:58

Status

In Progress [ 3 ]

Stalled [ 10000 ]

Sergei Golubchik made changes - 2020-04-27 07:58

Fix Version/s		10.4.13 [ 24223 ]
Fix Version/s	10.4 [ 22408 ]
Resolution		Fixed [ 1 ]
Status	Stalled [ 10000 ]	Closed [ 6 ]

Ian Gilfillan made changes - 2020-08-02 21:48

Link

This issue relates to ~~MDEV-23374~~ [ ~~MDEV-23374~~ ]

Sergei Golubchik made changes - 2021-12-06 21:51

Workflow

MariaDB v3 [ 104822 ]

MariaDB v4 [ 157427 ]

People

Assignee:: Sergei Golubchik

Reporter:: Geoff Montee (Inactive)

Votes:: 0 Vote for this issue

Watchers:: 3 Start watching this issue

Dates

Created:: 2020-03-12 20:31

Updated:: 2020-08-02 21:48

Resolved:: 2020-04-27 07:58

Git Integration

Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.

MariaDB Server

Details

Description

Attachments

Issue Links

Activity

People

Dates

Git Integration