Details

Type: Task
Status: Closed (View Workflow)
Priority: Major
Resolution: Not a Bug
Fix Version/s: N/A
Component/s: Authentication and Privilege System
Labels:
None

Description

Resolved

Please see comment, my mistake, can be closed. Pardon!

The environment

$ lsb_release -a

No LSB modules are available.

Distributor ID:	Ubuntu

Description:	Ubuntu 18.04.3 LTS

Release:	18.04

Codename:	bionic

$ uname -a

Linux test-vm-01 4.15.0-72-generic #81-Ubuntu SMP Tue Nov 26 12:20:02 UTC 2019 x86_64 x86_64 x86_64 GNU/Linux

$ mysql --version

mysql  Ver 15.1 Distrib 10.4.11-MariaDB, for debian-linux-gnu (x86_64) using readline 5.2

MariaDB was installed from the MariaDB repository

root user is set to use unix_socket auth by running mariadb-secure-installation with params

Enter current password for root (enter for none): - <Enter>

Switch to unix_socket authentication [Y/n] - Y

Change the root password? [Y/n] - n

Remove anonymous users? [Y/n] - Y

Disallow root login remotely? [Y/n] - Y

Remove test database and access to it? [Y/n] - Y

Reload privilege tables now? [Y/n] - Y

Reference

MariaDB documentation for CREATE USER states that One can specify many authentication plugins, they all works as alternatives ways of authenticating a user.

CREATE USER safe@'%' IDENTIFIED VIA ed25519 USING PASSWORD('secret') OR unix_socket;

The issue

Base

sudo mysql -u root

SELECT user,host,plugin,authentication_string FROM mysql.user;

+-------+-----------+-----------------------+-----------------------+

| User  | Host      | plugin                | authentication_string |

+-------+-----------+-----------------------+-----------------------+

| root  | localhost | unix_socket           |                       |

| mysql | localhost | mysql_native_password | invalid               |

+-------+-----------+-----------------------+-----------------------+

CREATE USER 'testuser'@'localhost' IDENTIFIED VIA mysql_native_password USING PASSWORD('testpass') OR unix_socket;

GRANT ALL PRIVILEGES ON *.* TO 'testuser'@'localhost' WITH GRANT OPTION;

FLUSH PRIVILEGES;

SELECT user,host,plugin,authentication_string FROM mysql.user;

+----------+-----------+-----------------------+-------------------------------------------+

| User     | Host      | plugin                | authentication_string                     |

+----------+-----------+-----------------------+-------------------------------------------+

| root     | localhost | unix_socket           |                                           |

| mysql    | localhost | mysql_native_password | invalid                                   |

| testuser | localhost | mysql_native_password | *00E247AC5F9AF26AE0194B41E1E769DEE1429A29 |

+----------+-----------+-----------------------+-------------------------------------------+

QUIT;

All unix_socket'ish login attempts fail

$ sudo mysql -u testuser

ERROR 1698 (28000): Access denied for user 'testuser'@'localhost'

# mysql -u testuser

# ERROR 1698 (28000): Access denied for user 'testuser'@'localhost'

Using password (testpass) works

$ mysql -u testuser -p

Enter password:

Extra observation 1

If testuser at this point is altered to use unix_socket auth explicitly...

sudo mysql -u root

ALTER USER testuser@localhost IDENTIFIED VIA unix_socket;

FLUSH PRIVILEGES;

SELECT user,host,plugin,authentication_string FROM mysql.user;

+----------+-----------+-----------------------+-------------------------------------------+

| User     | Host      | plugin                | authentication_string                     |

+----------+-----------+-----------------------+-------------------------------------------+

| root     | localhost | unix_socket           |                                           |

| mysql    | localhost | mysql_native_password | invalid                                   |

| testuser | localhost | unix_socket           |                                           |

| warpnode | localhost | mysql_native_password | *00E247AC5F9AF26AE0194B41E1E769DEE1429A29 |

+----------+-----------+-----------------------+-------------------------------------------+

QUIT;

...then login no longer works on BOTH methods

# mysql -u testuser

ERROR 1698 (28000): Access denied for user 'testuser'@'localhost'

$ sudo mysql -u testuser

ERROR 1698 (28000): Access denied for user 'testuser'@'localhost'

$ mysql -u testuser -p

Enter password:

ERROR 1698 (28000): Access denied for user 'testuser'@'localhost'

Extra observation 2

If testuser at this point is altered to use mysql_native_password auth explicitly...

ALTER USER 'testuser'@'localhost' IDENTIFIED VIA mysql_native_password USING PASSWORD('testpass');

FLUSH PRIVILEGES;

SELECT user,host,plugin,authentication_string FROM mysql.user;

+----------+-----------+-----------------------+-------------------------------------------+

| User     | Host      | plugin                | authentication_string                     |

+----------+-----------+-----------------------+-------------------------------------------+

| root     | localhost | unix_socket           |                                           |

| mysql    | localhost | mysql_native_password | invalid                                   |

| testuser | localhost | mysql_native_password | *00E247AC5F9AF26AE0194B41E1E769DEE1429A29 |

+----------+-----------+-----------------------+-------------------------------------------+

...then using password (testpass) works (again)

$ mysql -u testuser -p

Enter password:

Expected behaviour

If the documentation is correct, then

1) IDENTIFIED VIA mysql_native_password USING PASSWORD('testpass') OR unix_socket should yield ability to log in via both methods

# mysql -u testuser

$ mysql -u testuser -p

2) Explicit ALTER USER testuser@localhost IDENTIFIED VIA unix_socket should yield ability to log in via unix_socket

# mysql -u testuser

Thanks in advance!

Attachments

Issue Links

is caused by

CONC-441 Default user name for C/C is wrong if login user is different from effective user

Closed

Activity

Ascending order - Click to sort in descending order

Reinis Adovics created issue - 2019-12-25 21:58

Reinis Adovics made changes - 2019-12-25 22:01

Field	Original Value	New Value
Description	h1. The environment {noformat} $ lsb_release -a No LSB modules are available. Distributor ID: Ubuntu Description: Ubuntu 18.04.3 LTS Release: 18.04 Codename: bionic $ uname -a Linux test-vm-01 4.15.0-72-generic #81-Ubuntu SMP Tue Nov 26 12:20:02 UTC 2019 x86_64 x86_64 x86_64 GNU/Linux $ mysql --version mysql Ver 15.1 Distrib 10.4.11-MariaDB, for debian-linux-gnu (x86_64) using readline 5.2 {noformat} MariaDB was installed [from the MariaDB repository\|https://downloads.mariadb.org/mariadb/repositories/#distro=Ubuntu&distro_release=bionic--ubuntu_bionic&mirror=exascale&version=10.4] _root_ user is set to use _unix_socket_ auth by running _mariadb-secure-installation_ with params {noformat} Enter current password for root (enter for none): - <Enter> Switch to unix_socket authentication [Y/n] - Y Change the root password? [Y/n] - n Remove anonymous users? [Y/n] - Y Disallow root login remotely? [Y/n] - Y Remove test database and access to it? [Y/n] - Y Reload privilege tables now? [Y/n] - Y {noformat} h3. Reference MariaDB documentation for CREATE USER states that [One can specify many authentication plugins, they all works as alternatives ways of authenticating a user\|https://mariadb.com/kb/en/create-user/#identified-viawith-authentication_plugin]. h1. The issue h3. Base {noformat} sudo mysql -u root SELECT user,host,plugin,authentication_string FROM mysql.user; +-------+-----------+-----------------------+-----------------------+ \| User \| Host \| plugin \| authentication_string \| +-------+-----------+-----------------------+-----------------------+ \| root \| localhost \| unix_socket \| \| \| mysql \| localhost \| mysql_native_password \| invalid \| +-------+-----------+-----------------------+-----------------------+ CREATE USER 'testuser'@'localhost' IDENTIFIED VIA mysql_native_password USING PASSWORD('testpass') OR unix_socket; GRANT ALL PRIVILEGES ON . TO 'testuser'@'localhost' WITH GRANT OPTION; FLUSH PRIVILEGES; SELECT user,host,plugin,authentication_string FROM mysql.user; +----------+-----------+-----------------------+-------------------------------------------+ \| User \| Host \| plugin \| authentication_string \| +----------+-----------+-----------------------+-------------------------------------------+ \| root \| localhost \| unix_socket \| \| \| mysql \| localhost \| mysql_native_password \| invalid \| \| testuser \| localhost \| mysql_native_password \| 00E247AC5F9AF26AE0194B41E1E769DEE1429A29 \| +----------+-----------+-----------------------+-------------------------------------------+ QUIT; {noformat} All _unix_socket_'ish login attempts fail {noformat} $ sudo mysql -u testuser ERROR 1698 (28000): Access denied for user 'testuser'@'localhost' # mysql -u testuser # ERROR 1698 (28000): Access denied for user 'testuser'@'localhost' {noformat} Using password (_testpass_) works {noformat} $ mysql -u testuser -p Enter password: {noformat} h3. Extra observation 1 If _testuser_ is set to use _unix_socket_ auth explicitly... {noformat} sudo mysql -u root ALTER USER testuser@localhost IDENTIFIED VIA unix_socket; FLUSH PRIVILEGES; SELECT user,host,plugin,authentication_string FROM mysql.user; +----------+-----------+-----------------------+-------------------------------------------+ \| User \| Host \| plugin \| authentication_string \| +----------+-----------+-----------------------+-------------------------------------------+ \| root \| localhost \| unix_socket \| \| \| mysql \| localhost \| mysql_native_password \| invalid \| \| testuser \| localhost \| unix_socket \| \| \| warpnode \| localhost \| mysql_native_password \| 00E247AC5F9AF26AE0194B41E1E769DEE1429A29 \| +----------+-----------+-----------------------+-------------------------------------------+ QUIT; {noformat} ...then login no longer works on BOTH methods {noformat} # mysql -u testuser ERROR 1698 (28000): Access denied for user 'testuser'@'localhost' $ sudo mysql -u testuser ERROR 1698 (28000): Access denied for user 'testuser'@'localhost' $ mysql -u testuser -p Enter password: ERROR 1698 (28000): Access denied for user 'testuser'@'localhost' {noformat} h3. Extra observation 2 If _testuser_ is set to use _ mysql_native_password_ auth explicitly... {noformat} ALTER USER 'testuser'@'localhost' IDENTIFIED VIA mysql_native_password USING PASSWORD('testpass'); FLUSH PRIVILEGES; SELECT user,host,plugin,authentication_string FROM mysql.user; +----------+-----------+-----------------------+-------------------------------------------+ \| User \| Host \| plugin \| authentication_string \| +----------+-----------+-----------------------+-------------------------------------------+ \| root \| localhost \| unix_socket \| \| \| mysql \| localhost \| mysql_native_password \| invalid \| \| testuser \| localhost \| mysql_native_password \| *00E247AC5F9AF26AE0194B41E1E769DEE1429A29 \| +----------+-----------+-----------------------+-------------------------------------------+ {noformat} ...then using password (_testpass_) works (again) {noformat} $ mysql -u testuser -p Enter password: {noformat} h1. Expected behaviour If the documentation is correct, then 1)_ IDENTIFIED VIA mysql_native_password USING PASSWORD('testpass') OR unix_socket_ should yield ability to log in via both methods {noformat} # mysql -u testuser $ mysql -u testuser -p {noformat} 2) Explicit _ALTER USER testuser@localhost IDENTIFIED VIA unix_socket_ should yield ability to log in via _unix_socket_ {noformat} # mysql -u testuser {noformat} Thanks in advance!	h1. The environment {noformat} $ lsb_release -a No LSB modules are available. Distributor ID: Ubuntu Description: Ubuntu 18.04.3 LTS Release: 18.04 Codename: bionic $ uname -a Linux test-vm-01 4.15.0-72-generic #81-Ubuntu SMP Tue Nov 26 12:20:02 UTC 2019 x86_64 x86_64 x86_64 GNU/Linux $ mysql --version mysql Ver 15.1 Distrib 10.4.11-MariaDB, for debian-linux-gnu (x86_64) using readline 5.2 {noformat} MariaDB was installed [from the MariaDB repository\|https://downloads.mariadb.org/mariadb/repositories/#distro=Ubuntu&distro_release=bionic--ubuntu_bionic&mirror=exascale&version=10.4] _root_ user is set to use _unix_socket_ auth by running _mariadb-secure-installation_ with params {noformat} Enter current password for root (enter for none): - <Enter> Switch to unix_socket authentication [Y/n] - Y Change the root password? [Y/n] - n Remove anonymous users? [Y/n] - Y Disallow root login remotely? [Y/n] - Y Remove test database and access to it? [Y/n] - Y Reload privilege tables now? [Y/n] - Y {noformat} h1. Reference MariaDB documentation for CREATE USER states that [One can specify many authentication plugins, they all works as alternatives ways of authenticating a user\|https://mariadb.com/kb/en/create-user/#identified-viawith-authentication_plugin]. h1. The issue h3. Base {noformat} sudo mysql -u root SELECT user,host,plugin,authentication_string FROM mysql.user; +-------+-----------+-----------------------+-----------------------+ \| User \| Host \| plugin \| authentication_string \| +-------+-----------+-----------------------+-----------------------+ \| root \| localhost \| unix_socket \| \| \| mysql \| localhost \| mysql_native_password \| invalid \| +-------+-----------+-----------------------+-----------------------+ CREATE USER 'testuser'@'localhost' IDENTIFIED VIA mysql_native_password USING PASSWORD('testpass') OR unix_socket; GRANT ALL PRIVILEGES ON . TO 'testuser'@'localhost' WITH GRANT OPTION; FLUSH PRIVILEGES; SELECT user,host,plugin,authentication_string FROM mysql.user; +----------+-----------+-----------------------+-------------------------------------------+ \| User \| Host \| plugin \| authentication_string \| +----------+-----------+-----------------------+-------------------------------------------+ \| root \| localhost \| unix_socket \| \| \| mysql \| localhost \| mysql_native_password \| invalid \| \| testuser \| localhost \| mysql_native_password \| 00E247AC5F9AF26AE0194B41E1E769DEE1429A29 \| +----------+-----------+-----------------------+-------------------------------------------+ QUIT; {noformat} All _unix_socket_'ish login attempts fail {noformat} $ sudo mysql -u testuser ERROR 1698 (28000): Access denied for user 'testuser'@'localhost' # mysql -u testuser # ERROR 1698 (28000): Access denied for user 'testuser'@'localhost' {noformat} Using password (_testpass_) works {noformat} $ mysql -u testuser -p Enter password: {noformat} h3. Extra observation 1 If _testuser_ is set to use _unix_socket_ auth explicitly... {noformat} sudo mysql -u root ALTER USER testuser@localhost IDENTIFIED VIA unix_socket; FLUSH PRIVILEGES; SELECT user,host,plugin,authentication_string FROM mysql.user; +----------+-----------+-----------------------+-------------------------------------------+ \| User \| Host \| plugin \| authentication_string \| +----------+-----------+-----------------------+-------------------------------------------+ \| root \| localhost \| unix_socket \| \| \| mysql \| localhost \| mysql_native_password \| invalid \| \| testuser \| localhost \| unix_socket \| \| \| warpnode \| localhost \| mysql_native_password \| 00E247AC5F9AF26AE0194B41E1E769DEE1429A29 \| +----------+-----------+-----------------------+-------------------------------------------+ QUIT; {noformat} ...then login no longer works on BOTH methods {noformat} # mysql -u testuser ERROR 1698 (28000): Access denied for user 'testuser'@'localhost' $ sudo mysql -u testuser ERROR 1698 (28000): Access denied for user 'testuser'@'localhost' $ mysql -u testuser -p Enter password: ERROR 1698 (28000): Access denied for user 'testuser'@'localhost' {noformat} h3. Extra observation 2 If _testuser_ is set to use _mysql_native_password_ auth explicitly... {noformat} ALTER USER 'testuser'@'localhost' IDENTIFIED VIA mysql_native_password USING PASSWORD('testpass'); FLUSH PRIVILEGES; SELECT user,host,plugin,authentication_string FROM mysql.user; +----------+-----------+-----------------------+-------------------------------------------+ \| User \| Host \| plugin \| authentication_string \| +----------+-----------+-----------------------+-------------------------------------------+ \| root \| localhost \| unix_socket \| \| \| mysql \| localhost \| mysql_native_password \| invalid \| \| testuser \| localhost \| mysql_native_password \| *00E247AC5F9AF26AE0194B41E1E769DEE1429A29 \| +----------+-----------+-----------------------+-------------------------------------------+ {noformat} ...then using password (_testpass_) works (again) {noformat} $ mysql -u testuser -p Enter password: {noformat} h1. Expected behaviour If the documentation is correct, then 1) _IDENTIFIED VIA mysql_native_password USING PASSWORD('testpass') OR unix_socket_ should yield ability to log in via both methods {noformat} # mysql -u testuser $ mysql -u testuser -p {noformat} 2) Explicit _ALTER USER testuser@localhost IDENTIFIED VIA unix_socket_ should yield ability to log in via _unix_socket_ {noformat} # mysql -u testuser {noformat} Thanks in advance!

Reinis Adovics made changes - 2019-12-25 22:03

Description

h1. The environment

{noformat}
$ lsb_release -a
No LSB modules are available.
Distributor ID: Ubuntu
Description: Ubuntu 18.04.3 LTS
Release: 18.04
Codename: bionic

$ uname -a
Linux test-vm-01 4.15.0-72-generic #81-Ubuntu SMP Tue Nov 26 12:20:02 UTC 2019 x86_64 x86_64 x86_64 GNU/Linux

$ mysql --version
mysql Ver 15.1 Distrib 10.4.11-MariaDB, for debian-linux-gnu (x86_64) using readline 5.2
{noformat}

MariaDB was installed [from the MariaDB repository|https://downloads.mariadb.org/mariadb/repositories/#distro=Ubuntu&distro_release=bionic--ubuntu_bionic&mirror=exascale&version=10.4]

_root_ user is set to use _unix_socket_ auth by running _mariadb-secure-installation_ with params

{noformat}
Enter current password for root (enter for none): - <Enter>
Switch to unix_socket authentication [Y/n] - Y
Change the root password? [Y/n] - n
Remove anonymous users? [Y/n] - Y
Disallow root login remotely? [Y/n] - Y
Remove test database and access to it? [Y/n] - Y
Reload privilege tables now? [Y/n] - Y
{noformat}

h1. Reference

MariaDB documentation for CREATE USER states that [One can specify many authentication plugins, they all works as alternatives ways of authenticating a user|https://mariadb.com/kb/en/create-user/#identified-viawith-authentication_plugin].

h1. The issue

h3. Base

{noformat}
sudo mysql -u root

SELECT user,host,plugin,authentication_string FROM mysql.user;

+-------+-----------+-----------------------+-----------------------+
| User | Host | plugin | authentication_string |
+-------+-----------+-----------------------+-----------------------+
| root | localhost | unix_socket | |
| mysql | localhost | mysql_native_password | invalid |
+-------+-----------+-----------------------+-----------------------+

CREATE USER 'testuser'@'localhost' IDENTIFIED VIA mysql_native_password USING PASSWORD('testpass') OR unix_socket;
GRANT ALL PRIVILEGES ON *.* TO 'testuser'@'localhost' WITH GRANT OPTION;
FLUSH PRIVILEGES;

SELECT user,host,plugin,authentication_string FROM mysql.user;

+----------+-----------+-----------------------+-------------------------------------------+
| User | Host | plugin | authentication_string |
+----------+-----------+-----------------------+-------------------------------------------+
| root | localhost | unix_socket | |
| mysql | localhost | mysql_native_password | invalid |
| testuser | localhost | mysql_native_password | *00E247AC5F9AF26AE0194B41E1E769DEE1429A29 |
+----------+-----------+-----------------------+-------------------------------------------+

QUIT;

{noformat}

All _unix_socket_'ish login attempts fail

{noformat}
$ sudo mysql -u testuser
ERROR 1698 (28000): Access denied for user 'testuser'@'localhost'
# mysql -u testuser
# ERROR 1698 (28000): Access denied for user 'testuser'@'localhost'
{noformat}

Using password (_testpass_) works
{noformat}
$ mysql -u testuser -p
Enter password:
{noformat}

h3. Extra observation 1

If _testuser_ is set to use _unix_socket_ auth explicitly...

{noformat}
sudo mysql -u root
ALTER USER testuser@localhost IDENTIFIED VIA unix_socket;
FLUSH PRIVILEGES;

SELECT user,host,plugin,authentication_string FROM mysql.user;

+----------+-----------+-----------------------+-------------------------------------------+
| User | Host | plugin | authentication_string |
+----------+-----------+-----------------------+-------------------------------------------+
| root | localhost | unix_socket | |
| mysql | localhost | mysql_native_password | invalid |
| testuser | localhost | unix_socket | |
| warpnode | localhost | mysql_native_password | *00E247AC5F9AF26AE0194B41E1E769DEE1429A29 |
+----------+-----------+-----------------------+-------------------------------------------+

QUIT;
{noformat}

...then login no longer works on BOTH methods

{noformat}
# mysql -u testuser
ERROR 1698 (28000): Access denied for user 'testuser'@'localhost'
$ sudo mysql -u testuser
ERROR 1698 (28000): Access denied for user 'testuser'@'localhost'
$ mysql -u testuser -p
Enter password:
ERROR 1698 (28000): Access denied for user 'testuser'@'localhost'
{noformat}

h3. Extra observation 2

If _testuser_ is set to use _mysql_native_password_ auth explicitly...

{noformat}
ALTER USER 'testuser'@'localhost' IDENTIFIED VIA mysql_native_password USING PASSWORD('testpass');
FLUSH PRIVILEGES;
SELECT user,host,plugin,authentication_string FROM mysql.user;

+----------+-----------+-----------------------+-------------------------------------------+
| User | Host | plugin | authentication_string |
+----------+-----------+-----------------------+-------------------------------------------+
| root | localhost | unix_socket | |
| mysql | localhost | mysql_native_password | invalid |
| testuser | localhost | mysql_native_password | *00E247AC5F9AF26AE0194B41E1E769DEE1429A29 |
+----------+-----------+-----------------------+-------------------------------------------+

{noformat}

...then using password (_testpass_) works (again)
{noformat}
$ mysql -u testuser -p
Enter password:
{noformat}

h1. Expected behaviour

If the documentation is correct, then

1) _IDENTIFIED VIA mysql_native_password USING PASSWORD('testpass') OR unix_socket_ should yield ability to log in via both methods

{noformat}
# mysql -u testuser
$ mysql -u testuser -p
{noformat}

2) Explicit _ALTER USER testuser@localhost IDENTIFIED VIA unix_socket_ should yield ability to log in via _unix_socket_

{noformat}
# mysql -u testuser
{noformat}

Thanks in advance!

h1. The environment

{noformat}
$ lsb_release -a
No LSB modules are available.
Distributor ID: Ubuntu
Description: Ubuntu 18.04.3 LTS
Release: 18.04
Codename: bionic

$ uname -a
Linux test-vm-01 4.15.0-72-generic #81-Ubuntu SMP Tue Nov 26 12:20:02 UTC 2019 x86_64 x86_64 x86_64 GNU/Linux

$ mysql --version
mysql Ver 15.1 Distrib 10.4.11-MariaDB, for debian-linux-gnu (x86_64) using readline 5.2
{noformat}

MariaDB was installed [from the MariaDB repository|https://downloads.mariadb.org/mariadb/repositories/#distro=Ubuntu&distro_release=bionic--ubuntu_bionic&mirror=exascale&version=10.4]

_root_ user is set to use _unix_socket_ auth by running _mariadb-secure-installation_ with params

{noformat}
Enter current password for root (enter for none): - <Enter>
Switch to unix_socket authentication [Y/n] - Y
Change the root password? [Y/n] - n
Remove anonymous users? [Y/n] - Y
Disallow root login remotely? [Y/n] - Y
Remove test database and access to it? [Y/n] - Y
Reload privilege tables now? [Y/n] - Y
{noformat}

h1. Reference

MariaDB documentation for CREATE USER states that [One can specify many authentication plugins, they all works as alternatives ways of authenticating a user|https://mariadb.com/kb/en/create-user/#identified-viawith-authentication_plugin].

{noformat}
CREATE USER safe@'%' IDENTIFIED VIA ed25519 USING PASSWORD('secret') OR unix_socket;
{noformat}

h1. The issue

h3. Base

{noformat}
sudo mysql -u root

SELECT user,host,plugin,authentication_string FROM mysql.user;

+-------+-----------+-----------------------+-----------------------+
| User | Host | plugin | authentication_string |
+-------+-----------+-----------------------+-----------------------+
| root | localhost | unix_socket | |
| mysql | localhost | mysql_native_password | invalid |
+-------+-----------+-----------------------+-----------------------+

CREATE USER 'testuser'@'localhost' IDENTIFIED VIA mysql_native_password USING PASSWORD('testpass') OR unix_socket;
GRANT ALL PRIVILEGES ON *.* TO 'testuser'@'localhost' WITH GRANT OPTION;
FLUSH PRIVILEGES;

SELECT user,host,plugin,authentication_string FROM mysql.user;

+----------+-----------+-----------------------+-------------------------------------------+
| User | Host | plugin | authentication_string |
+----------+-----------+-----------------------+-------------------------------------------+
| root | localhost | unix_socket | |
| mysql | localhost | mysql_native_password | invalid |
| testuser | localhost | mysql_native_password | *00E247AC5F9AF26AE0194B41E1E769DEE1429A29 |
+----------+-----------+-----------------------+-------------------------------------------+

QUIT;

{noformat}

All _unix_socket_'ish login attempts fail

{noformat}
$ sudo mysql -u testuser
ERROR 1698 (28000): Access denied for user 'testuser'@'localhost'
# mysql -u testuser
# ERROR 1698 (28000): Access denied for user 'testuser'@'localhost'
{noformat}

Using password (_testpass_) works
{noformat}
$ mysql -u testuser -p
Enter password:
{noformat}

h3. Extra observation 1

If _testuser_ is set to use _unix_socket_ auth explicitly...

{noformat}
sudo mysql -u root
ALTER USER testuser@localhost IDENTIFIED VIA unix_socket;
FLUSH PRIVILEGES;

SELECT user,host,plugin,authentication_string FROM mysql.user;

+----------+-----------+-----------------------+-------------------------------------------+
| User | Host | plugin | authentication_string |
+----------+-----------+-----------------------+-------------------------------------------+
| root | localhost | unix_socket | |
| mysql | localhost | mysql_native_password | invalid |
| testuser | localhost | unix_socket | |
| warpnode | localhost | mysql_native_password | *00E247AC5F9AF26AE0194B41E1E769DEE1429A29 |
+----------+-----------+-----------------------+-------------------------------------------+

QUIT;
{noformat}

...then login no longer works on BOTH methods

{noformat}
# mysql -u testuser
ERROR 1698 (28000): Access denied for user 'testuser'@'localhost'
$ sudo mysql -u testuser
ERROR 1698 (28000): Access denied for user 'testuser'@'localhost'
$ mysql -u testuser -p
Enter password:
ERROR 1698 (28000): Access denied for user 'testuser'@'localhost'
{noformat}

h3. Extra observation 2

If _testuser_ is set to use _mysql_native_password_ auth explicitly...

{noformat}
ALTER USER 'testuser'@'localhost' IDENTIFIED VIA mysql_native_password USING PASSWORD('testpass');
FLUSH PRIVILEGES;
SELECT user,host,plugin,authentication_string FROM mysql.user;

+----------+-----------+-----------------------+-------------------------------------------+
| User | Host | plugin | authentication_string |
+----------+-----------+-----------------------+-------------------------------------------+
| root | localhost | unix_socket | |
| mysql | localhost | mysql_native_password | invalid |
| testuser | localhost | mysql_native_password | *00E247AC5F9AF26AE0194B41E1E769DEE1429A29 |
+----------+-----------+-----------------------+-------------------------------------------+

{noformat}

...then using password (_testpass_) works (again)
{noformat}
$ mysql -u testuser -p
Enter password:
{noformat}

h1. Expected behaviour

If the documentation is correct, then

1) _IDENTIFIED VIA mysql_native_password USING PASSWORD('testpass') OR unix_socket_ should yield ability to log in via both methods

{noformat}
# mysql -u testuser
$ mysql -u testuser -p
{noformat}

2) Explicit _ALTER USER testuser@localhost IDENTIFIED VIA unix_socket_ should yield ability to log in via _unix_socket_

{noformat}
# mysql -u testuser
{noformat}

Thanks in advance!

Reinis Adovics made changes - 2019-12-25 22:05

Description

h1. The environment

{noformat}
$ lsb_release -a
No LSB modules are available.
Distributor ID: Ubuntu
Description: Ubuntu 18.04.3 LTS
Release: 18.04
Codename: bionic

$ uname -a
Linux test-vm-01 4.15.0-72-generic #81-Ubuntu SMP Tue Nov 26 12:20:02 UTC 2019 x86_64 x86_64 x86_64 GNU/Linux

$ mysql --version
mysql Ver 15.1 Distrib 10.4.11-MariaDB, for debian-linux-gnu (x86_64) using readline 5.2
{noformat}

MariaDB was installed [from the MariaDB repository|https://downloads.mariadb.org/mariadb/repositories/#distro=Ubuntu&distro_release=bionic--ubuntu_bionic&mirror=exascale&version=10.4]

_root_ user is set to use _unix_socket_ auth by running _mariadb-secure-installation_ with params

{noformat}
Enter current password for root (enter for none): - <Enter>
Switch to unix_socket authentication [Y/n] - Y
Change the root password? [Y/n] - n
Remove anonymous users? [Y/n] - Y
Disallow root login remotely? [Y/n] - Y
Remove test database and access to it? [Y/n] - Y
Reload privilege tables now? [Y/n] - Y
{noformat}

h1. Reference

MariaDB documentation for CREATE USER states that [One can specify many authentication plugins, they all works as alternatives ways of authenticating a user|https://mariadb.com/kb/en/create-user/#identified-viawith-authentication_plugin].

{noformat}
CREATE USER safe@'%' IDENTIFIED VIA ed25519 USING PASSWORD('secret') OR unix_socket;
{noformat}

h1. The issue

h3. Base

{noformat}
sudo mysql -u root

SELECT user,host,plugin,authentication_string FROM mysql.user;

+-------+-----------+-----------------------+-----------------------+
| User | Host | plugin | authentication_string |
+-------+-----------+-----------------------+-----------------------+
| root | localhost | unix_socket | |
| mysql | localhost | mysql_native_password | invalid |
+-------+-----------+-----------------------+-----------------------+

CREATE USER 'testuser'@'localhost' IDENTIFIED VIA mysql_native_password USING PASSWORD('testpass') OR unix_socket;
GRANT ALL PRIVILEGES ON *.* TO 'testuser'@'localhost' WITH GRANT OPTION;
FLUSH PRIVILEGES;

SELECT user,host,plugin,authentication_string FROM mysql.user;

+----------+-----------+-----------------------+-------------------------------------------+
| User | Host | plugin | authentication_string |
+----------+-----------+-----------------------+-------------------------------------------+
| root | localhost | unix_socket | |
| mysql | localhost | mysql_native_password | invalid |
| testuser | localhost | mysql_native_password | *00E247AC5F9AF26AE0194B41E1E769DEE1429A29 |
+----------+-----------+-----------------------+-------------------------------------------+

QUIT;

{noformat}

All _unix_socket_'ish login attempts fail

{noformat}
$ sudo mysql -u testuser
ERROR 1698 (28000): Access denied for user 'testuser'@'localhost'
# mysql -u testuser
# ERROR 1698 (28000): Access denied for user 'testuser'@'localhost'
{noformat}

Using password (_testpass_) works
{noformat}
$ mysql -u testuser -p
Enter password:
{noformat}

h3. Extra observation 1

If _testuser_ is set to use _unix_socket_ auth explicitly...

{noformat}
sudo mysql -u root
ALTER USER testuser@localhost IDENTIFIED VIA unix_socket;
FLUSH PRIVILEGES;

SELECT user,host,plugin,authentication_string FROM mysql.user;

+----------+-----------+-----------------------+-------------------------------------------+
| User | Host | plugin | authentication_string |
+----------+-----------+-----------------------+-------------------------------------------+
| root | localhost | unix_socket | |
| mysql | localhost | mysql_native_password | invalid |
| testuser | localhost | unix_socket | |
| warpnode | localhost | mysql_native_password | *00E247AC5F9AF26AE0194B41E1E769DEE1429A29 |
+----------+-----------+-----------------------+-------------------------------------------+

QUIT;
{noformat}

...then login no longer works on BOTH methods

{noformat}
# mysql -u testuser
ERROR 1698 (28000): Access denied for user 'testuser'@'localhost'
$ sudo mysql -u testuser
ERROR 1698 (28000): Access denied for user 'testuser'@'localhost'
$ mysql -u testuser -p
Enter password:
ERROR 1698 (28000): Access denied for user 'testuser'@'localhost'
{noformat}

h3. Extra observation 2

If _testuser_ is set to use _mysql_native_password_ auth explicitly...

{noformat}
ALTER USER 'testuser'@'localhost' IDENTIFIED VIA mysql_native_password USING PASSWORD('testpass');
FLUSH PRIVILEGES;
SELECT user,host,plugin,authentication_string FROM mysql.user;

+----------+-----------+-----------------------+-------------------------------------------+
| User | Host | plugin | authentication_string |
+----------+-----------+-----------------------+-------------------------------------------+
| root | localhost | unix_socket | |
| mysql | localhost | mysql_native_password | invalid |
| testuser | localhost | mysql_native_password | *00E247AC5F9AF26AE0194B41E1E769DEE1429A29 |
+----------+-----------+-----------------------+-------------------------------------------+

{noformat}

...then using password (_testpass_) works (again)
{noformat}
$ mysql -u testuser -p
Enter password:
{noformat}

h1. Expected behaviour

If the documentation is correct, then

1) _IDENTIFIED VIA mysql_native_password USING PASSWORD('testpass') OR unix_socket_ should yield ability to log in via both methods

{noformat}
# mysql -u testuser
$ mysql -u testuser -p
{noformat}

2) Explicit _ALTER USER testuser@localhost IDENTIFIED VIA unix_socket_ should yield ability to log in via _unix_socket_

{noformat}
# mysql -u testuser
{noformat}

Thanks in advance!

h1. The environment

{noformat}
$ lsb_release -a
No LSB modules are available.
Distributor ID: Ubuntu
Description: Ubuntu 18.04.3 LTS
Release: 18.04
Codename: bionic

$ uname -a
Linux test-vm-01 4.15.0-72-generic #81-Ubuntu SMP Tue Nov 26 12:20:02 UTC 2019 x86_64 x86_64 x86_64 GNU/Linux

$ mysql --version
mysql Ver 15.1 Distrib 10.4.11-MariaDB, for debian-linux-gnu (x86_64) using readline 5.2
{noformat}

MariaDB was installed [from the MariaDB repository|https://downloads.mariadb.org/mariadb/repositories/#distro=Ubuntu&distro_release=bionic--ubuntu_bionic&mirror=exascale&version=10.4]

_root_ user is set to use _unix_socket_ auth by running _mariadb-secure-installation_ with params

{noformat}
Enter current password for root (enter for none): - <Enter>
Switch to unix_socket authentication [Y/n] - Y
Change the root password? [Y/n] - n
Remove anonymous users? [Y/n] - Y
Disallow root login remotely? [Y/n] - Y
Remove test database and access to it? [Y/n] - Y
Reload privilege tables now? [Y/n] - Y
{noformat}

h1. Reference

MariaDB documentation for CREATE USER states that [One can specify many authentication plugins, they all works as alternatives ways of authenticating a user|https://mariadb.com/kb/en/create-user/#identified-viawith-authentication_plugin].

{noformat}
CREATE USER safe@'%' IDENTIFIED VIA ed25519 USING PASSWORD('secret') OR unix_socket;
{noformat}

h1. The issue

h3. Base

{noformat}
sudo mysql -u root

SELECT user,host,plugin,authentication_string FROM mysql.user;

+-------+-----------+-----------------------+-----------------------+
| User | Host | plugin | authentication_string |
+-------+-----------+-----------------------+-----------------------+
| root | localhost | unix_socket | |
| mysql | localhost | mysql_native_password | invalid |
+-------+-----------+-----------------------+-----------------------+

CREATE USER 'testuser'@'localhost' IDENTIFIED VIA mysql_native_password USING PASSWORD('testpass') OR unix_socket;
GRANT ALL PRIVILEGES ON *.* TO 'testuser'@'localhost' WITH GRANT OPTION;
FLUSH PRIVILEGES;

SELECT user,host,plugin,authentication_string FROM mysql.user;

+----------+-----------+-----------------------+-------------------------------------------+
| User | Host | plugin | authentication_string |
+----------+-----------+-----------------------+-------------------------------------------+
| root | localhost | unix_socket | |
| mysql | localhost | mysql_native_password | invalid |
| testuser | localhost | mysql_native_password | *00E247AC5F9AF26AE0194B41E1E769DEE1429A29 |
+----------+-----------+-----------------------+-------------------------------------------+

QUIT;

{noformat}

All _unix_socket_'ish login attempts fail

{noformat}
$ sudo mysql -u testuser
ERROR 1698 (28000): Access denied for user 'testuser'@'localhost'
# mysql -u testuser
# ERROR 1698 (28000): Access denied for user 'testuser'@'localhost'
{noformat}

Using password (_testpass_) works
{noformat}
$ mysql -u testuser -p
Enter password:
{noformat}

h3. Extra observation 1

If _testuser_ at this point is altered to use _unix_socket_ auth explicitly...

{noformat}
sudo mysql -u root
ALTER USER testuser@localhost IDENTIFIED VIA unix_socket;
FLUSH PRIVILEGES;

SELECT user,host,plugin,authentication_string FROM mysql.user;

+----------+-----------+-----------------------+-------------------------------------------+
| User | Host | plugin | authentication_string |
+----------+-----------+-----------------------+-------------------------------------------+
| root | localhost | unix_socket | |
| mysql | localhost | mysql_native_password | invalid |
| testuser | localhost | unix_socket | |
| warpnode | localhost | mysql_native_password | *00E247AC5F9AF26AE0194B41E1E769DEE1429A29 |
+----------+-----------+-----------------------+-------------------------------------------+

QUIT;
{noformat}

...then login no longer works on BOTH methods

{noformat}
# mysql -u testuser
ERROR 1698 (28000): Access denied for user 'testuser'@'localhost'
$ sudo mysql -u testuser
ERROR 1698 (28000): Access denied for user 'testuser'@'localhost'
$ mysql -u testuser -p
Enter password:
ERROR 1698 (28000): Access denied for user 'testuser'@'localhost'
{noformat}

h3. Extra observation 2

If _testuser_ at this point is altered to use _mysql_native_password_ auth explicitly...

{noformat}
ALTER USER 'testuser'@'localhost' IDENTIFIED VIA mysql_native_password USING PASSWORD('testpass');
FLUSH PRIVILEGES;
SELECT user,host,plugin,authentication_string FROM mysql.user;

+----------+-----------+-----------------------+-------------------------------------------+
| User | Host | plugin | authentication_string |
+----------+-----------+-----------------------+-------------------------------------------+
| root | localhost | unix_socket | |
| mysql | localhost | mysql_native_password | invalid |
| testuser | localhost | mysql_native_password | *00E247AC5F9AF26AE0194B41E1E769DEE1429A29 |
+----------+-----------+-----------------------+-------------------------------------------+

{noformat}

...then using password (_testpass_) works (again)
{noformat}
$ mysql -u testuser -p
Enter password:
{noformat}

h1. Expected behaviour

If the documentation is correct, then

1) _IDENTIFIED VIA mysql_native_password USING PASSWORD('testpass') OR unix_socket_ should yield ability to log in via both methods

{noformat}
# mysql -u testuser
$ mysql -u testuser -p
{noformat}

2) Explicit _ALTER USER testuser@localhost IDENTIFIED VIA unix_socket_ should yield ability to log in via _unix_socket_

{noformat}
# mysql -u testuser
{noformat}

Thanks in advance!

Reinis Adovics made changes - 2019-12-25 22:27

Description

h1. The environment

{noformat}
$ lsb_release -a
No LSB modules are available.
Distributor ID: Ubuntu
Description: Ubuntu 18.04.3 LTS
Release: 18.04
Codename: bionic

$ uname -a
Linux test-vm-01 4.15.0-72-generic #81-Ubuntu SMP Tue Nov 26 12:20:02 UTC 2019 x86_64 x86_64 x86_64 GNU/Linux

$ mysql --version
mysql Ver 15.1 Distrib 10.4.11-MariaDB, for debian-linux-gnu (x86_64) using readline 5.2
{noformat}

MariaDB was installed [from the MariaDB repository|https://downloads.mariadb.org/mariadb/repositories/#distro=Ubuntu&distro_release=bionic--ubuntu_bionic&mirror=exascale&version=10.4]

_root_ user is set to use _unix_socket_ auth by running _mariadb-secure-installation_ with params

{noformat}
Enter current password for root (enter for none): - <Enter>
Switch to unix_socket authentication [Y/n] - Y
Change the root password? [Y/n] - n
Remove anonymous users? [Y/n] - Y
Disallow root login remotely? [Y/n] - Y
Remove test database and access to it? [Y/n] - Y
Reload privilege tables now? [Y/n] - Y
{noformat}

h1. Reference

MariaDB documentation for CREATE USER states that [One can specify many authentication plugins, they all works as alternatives ways of authenticating a user|https://mariadb.com/kb/en/create-user/#identified-viawith-authentication_plugin].

{noformat}
CREATE USER safe@'%' IDENTIFIED VIA ed25519 USING PASSWORD('secret') OR unix_socket;
{noformat}

h1. The issue

h3. Base

{noformat}
sudo mysql -u root

SELECT user,host,plugin,authentication_string FROM mysql.user;

+-------+-----------+-----------------------+-----------------------+
| User | Host | plugin | authentication_string |
+-------+-----------+-----------------------+-----------------------+
| root | localhost | unix_socket | |
| mysql | localhost | mysql_native_password | invalid |
+-------+-----------+-----------------------+-----------------------+

CREATE USER 'testuser'@'localhost' IDENTIFIED VIA mysql_native_password USING PASSWORD('testpass') OR unix_socket;
GRANT ALL PRIVILEGES ON *.* TO 'testuser'@'localhost' WITH GRANT OPTION;
FLUSH PRIVILEGES;

SELECT user,host,plugin,authentication_string FROM mysql.user;

+----------+-----------+-----------------------+-------------------------------------------+
| User | Host | plugin | authentication_string |
+----------+-----------+-----------------------+-------------------------------------------+
| root | localhost | unix_socket | |
| mysql | localhost | mysql_native_password | invalid |
| testuser | localhost | mysql_native_password | *00E247AC5F9AF26AE0194B41E1E769DEE1429A29 |
+----------+-----------+-----------------------+-------------------------------------------+

QUIT;

{noformat}

All _unix_socket_'ish login attempts fail

{noformat}
$ sudo mysql -u testuser
ERROR 1698 (28000): Access denied for user 'testuser'@'localhost'
# mysql -u testuser
# ERROR 1698 (28000): Access denied for user 'testuser'@'localhost'
{noformat}

Using password (_testpass_) works
{noformat}
$ mysql -u testuser -p
Enter password:
{noformat}

h3. Extra observation 1

If _testuser_ at this point is altered to use _unix_socket_ auth explicitly...

{noformat}
sudo mysql -u root
ALTER USER testuser@localhost IDENTIFIED VIA unix_socket;
FLUSH PRIVILEGES;

SELECT user,host,plugin,authentication_string FROM mysql.user;

+----------+-----------+-----------------------+-------------------------------------------+
| User | Host | plugin | authentication_string |
+----------+-----------+-----------------------+-------------------------------------------+
| root | localhost | unix_socket | |
| mysql | localhost | mysql_native_password | invalid |
| testuser | localhost | unix_socket | |
| warpnode | localhost | mysql_native_password | *00E247AC5F9AF26AE0194B41E1E769DEE1429A29 |
+----------+-----------+-----------------------+-------------------------------------------+

QUIT;
{noformat}

...then login no longer works on BOTH methods

{noformat}
# mysql -u testuser
ERROR 1698 (28000): Access denied for user 'testuser'@'localhost'
$ sudo mysql -u testuser
ERROR 1698 (28000): Access denied for user 'testuser'@'localhost'
$ mysql -u testuser -p
Enter password:
ERROR 1698 (28000): Access denied for user 'testuser'@'localhost'
{noformat}

h3. Extra observation 2

If _testuser_ at this point is altered to use _mysql_native_password_ auth explicitly...

{noformat}
ALTER USER 'testuser'@'localhost' IDENTIFIED VIA mysql_native_password USING PASSWORD('testpass');
FLUSH PRIVILEGES;
SELECT user,host,plugin,authentication_string FROM mysql.user;

+----------+-----------+-----------------------+-------------------------------------------+
| User | Host | plugin | authentication_string |
+----------+-----------+-----------------------+-------------------------------------------+
| root | localhost | unix_socket | |
| mysql | localhost | mysql_native_password | invalid |
| testuser | localhost | mysql_native_password | *00E247AC5F9AF26AE0194B41E1E769DEE1429A29 |
+----------+-----------+-----------------------+-------------------------------------------+

{noformat}

...then using password (_testpass_) works (again)
{noformat}
$ mysql -u testuser -p
Enter password:
{noformat}

h1. Expected behaviour

If the documentation is correct, then

1) _IDENTIFIED VIA mysql_native_password USING PASSWORD('testpass') OR unix_socket_ should yield ability to log in via both methods

{noformat}
# mysql -u testuser
$ mysql -u testuser -p
{noformat}

2) Explicit _ALTER USER testuser@localhost IDENTIFIED VIA unix_socket_ should yield ability to log in via _unix_socket_

{noformat}
# mysql -u testuser
{noformat}

Thanks in advance!

h1. Resolved

Please see comment, my mistake, can be closed. Pardon!

h1. The environment

{noformat}
$ lsb_release -a
No LSB modules are available.
Distributor ID: Ubuntu
Description: Ubuntu 18.04.3 LTS
Release: 18.04
Codename: bionic

$ uname -a
Linux test-vm-01 4.15.0-72-generic #81-Ubuntu SMP Tue Nov 26 12:20:02 UTC 2019 x86_64 x86_64 x86_64 GNU/Linux

$ mysql --version
mysql Ver 15.1 Distrib 10.4.11-MariaDB, for debian-linux-gnu (x86_64) using readline 5.2
{noformat}

MariaDB was installed [from the MariaDB repository|https://downloads.mariadb.org/mariadb/repositories/#distro=Ubuntu&distro_release=bionic--ubuntu_bionic&mirror=exascale&version=10.4]

_root_ user is set to use _unix_socket_ auth by running _mariadb-secure-installation_ with params

{noformat}
Enter current password for root (enter for none): - <Enter>
Switch to unix_socket authentication [Y/n] - Y
Change the root password? [Y/n] - n
Remove anonymous users? [Y/n] - Y
Disallow root login remotely? [Y/n] - Y
Remove test database and access to it? [Y/n] - Y
Reload privilege tables now? [Y/n] - Y
{noformat}

h1. Reference

MariaDB documentation for CREATE USER states that [One can specify many authentication plugins, they all works as alternatives ways of authenticating a user|https://mariadb.com/kb/en/create-user/#identified-viawith-authentication_plugin].

{noformat}
CREATE USER safe@'%' IDENTIFIED VIA ed25519 USING PASSWORD('secret') OR unix_socket;
{noformat}

h1. The issue

h3. Base

{noformat}
sudo mysql -u root

SELECT user,host,plugin,authentication_string FROM mysql.user;

+-------+-----------+-----------------------+-----------------------+
| User | Host | plugin | authentication_string |
+-------+-----------+-----------------------+-----------------------+
| root | localhost | unix_socket | |
| mysql | localhost | mysql_native_password | invalid |
+-------+-----------+-----------------------+-----------------------+

CREATE USER 'testuser'@'localhost' IDENTIFIED VIA mysql_native_password USING PASSWORD('testpass') OR unix_socket;
GRANT ALL PRIVILEGES ON *.* TO 'testuser'@'localhost' WITH GRANT OPTION;
FLUSH PRIVILEGES;

SELECT user,host,plugin,authentication_string FROM mysql.user;

+----------+-----------+-----------------------+-------------------------------------------+
| User | Host | plugin | authentication_string |
+----------+-----------+-----------------------+-------------------------------------------+
| root | localhost | unix_socket | |
| mysql | localhost | mysql_native_password | invalid |
| testuser | localhost | mysql_native_password | *00E247AC5F9AF26AE0194B41E1E769DEE1429A29 |
+----------+-----------+-----------------------+-------------------------------------------+

QUIT;

{noformat}

All _unix_socket_'ish login attempts fail

{noformat}
$ sudo mysql -u testuser
ERROR 1698 (28000): Access denied for user 'testuser'@'localhost'
# mysql -u testuser
# ERROR 1698 (28000): Access denied for user 'testuser'@'localhost'
{noformat}

Using password (_testpass_) works
{noformat}
$ mysql -u testuser -p
Enter password:
{noformat}

h3. Extra observation 1

If _testuser_ at this point is altered to use _unix_socket_ auth explicitly...

{noformat}
sudo mysql -u root
ALTER USER testuser@localhost IDENTIFIED VIA unix_socket;
FLUSH PRIVILEGES;

SELECT user,host,plugin,authentication_string FROM mysql.user;

+----------+-----------+-----------------------+-------------------------------------------+
| User | Host | plugin | authentication_string |
+----------+-----------+-----------------------+-------------------------------------------+
| root | localhost | unix_socket | |
| mysql | localhost | mysql_native_password | invalid |
| testuser | localhost | unix_socket | |
| warpnode | localhost | mysql_native_password | *00E247AC5F9AF26AE0194B41E1E769DEE1429A29 |
+----------+-----------+-----------------------+-------------------------------------------+

QUIT;
{noformat}

...then login no longer works on BOTH methods

{noformat}
# mysql -u testuser
ERROR 1698 (28000): Access denied for user 'testuser'@'localhost'
$ sudo mysql -u testuser
ERROR 1698 (28000): Access denied for user 'testuser'@'localhost'
$ mysql -u testuser -p
Enter password:
ERROR 1698 (28000): Access denied for user 'testuser'@'localhost'
{noformat}

h3. Extra observation 2

If _testuser_ at this point is altered to use _mysql_native_password_ auth explicitly...

{noformat}
ALTER USER 'testuser'@'localhost' IDENTIFIED VIA mysql_native_password USING PASSWORD('testpass');
FLUSH PRIVILEGES;
SELECT user,host,plugin,authentication_string FROM mysql.user;

+----------+-----------+-----------------------+-------------------------------------------+
| User | Host | plugin | authentication_string |
+----------+-----------+-----------------------+-------------------------------------------+
| root | localhost | unix_socket | |
| mysql | localhost | mysql_native_password | invalid |
| testuser | localhost | mysql_native_password | *00E247AC5F9AF26AE0194B41E1E769DEE1429A29 |
+----------+-----------+-----------------------+-------------------------------------------+

{noformat}

...then using password (_testpass_) works (again)
{noformat}
$ mysql -u testuser -p
Enter password:
{noformat}

h1. Expected behaviour

If the documentation is correct, then

1) _IDENTIFIED VIA mysql_native_password USING PASSWORD('testpass') OR unix_socket_ should yield ability to log in via both methods

{noformat}
# mysql -u testuser
$ mysql -u testuser -p
{noformat}

2) Explicit _ALTER USER testuser@localhost IDENTIFIED VIA unix_socket_ should yield ability to log in via _unix_socket_

{noformat}
# mysql -u testuser
{noformat}

Thanks in advance!

Elena Stepanova made changes - 2019-12-25 22:29

Component/s		Authentication and Privilege System [ 13101 ]
Component/s	Server [ 13907 ]
Fix Version/s		N/A [ 14700 ]
Resolution		Not a Bug [ 6 ]
Status	Open [ 1 ]	Closed [ 6 ]

Julien Fritsch made changes - 2020-01-23 13:58

Fixing Priority

250

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

Link

This issue is caused by ~~CONC-441~~ [ ~~CONC-441~~ ]

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

Workflow

MariaDB v3 [ 102672 ]

MariaDB v4 [ 134163 ]

People

Assignee:: Unassigned

Reporter:: Reinis Adovics

Votes:: 0 Vote for this issue

Watchers:: 4 Start watching this issue

Dates

Created:: 2019-12-25 21:58

Updated:: 2020-03-12 20:52

Resolved:: 2019-12-25 22:28

Git Integration

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

MariaDB Server

Cannot specify multiple authentication plugins (mysql_native_password and unix_socket)

Details

Description

Resolved

The environment

Reference

The issue

Base

Extra observation 1

Extra observation 2

Expected behaviour

Attachments

Issue Links

Activity

People

Dates

Git Integration