Details
-
Task
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Not a Bug
-
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
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.
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.
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 testuseror
sometheruser$ sudo -u testuser mysql -u testuserworks. Pardon for the false alarm, it sometime takes ticket, to finally figure it out.