[MDEV-21396]  Cannot specify multiple authentication plugins (mysql_native_password and unix_socket) Created: 2019-12-25  Updated: 2020-03-12  Resolved: 2019-12-25

Status: Closed
Project: MariaDB Server
Component/s: Authentication and Privilege System
Fix Version/s: N/A

Type: Task Priority: Major
Reporter: Reinis Adovics Assignee: Unassigned
Resolution: Not a Bug Votes: 0
Labels: None

Issue Links:
Problem/Incident
is caused by CONC-441 Default user name for C/C is wrong if... Closed

 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!

 Comments   
Comment by Reinis Adovics [ 2019-12-25 ]

The issue was that although there is a testuser on the system, login to unix_socket was done using elevated perms which is incorrect as it messes up username-uid relation (caller is not testuser any more, but root). Calling

testuser$ mysql -u testuser

or

sometheruser$ sudo -u testuser mysql -u testuser

works. Pardon for the false alarm, it sometime takes ticket, to finally figure it out.

Comment by Elena Stepanova [ 2019-12-25 ]

Right. And you shouldn't even need to provide -u testuser, it will attempt to login with the unix username (which is the point).

Also, regarding the observation about mysql.user not showing multiple authentication methods – mysql.user is now a view maintained for backward compatibility with existing tools and scripts. It has its limitations, one of which is that it cannot show multiple auth methods. Full information is available in mysql.global_priv table.

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

The issue mentioned in kroko's comment was probably caused by CONC-441. Connector/C (and thus the clients that use it) was using the "login user ID", rather than the "effective user ID". This will be fixed starting in C/C 3.1.8, and the clients that use that version.

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