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
-