Details
-
Bug
-
Status: Open (View Workflow)
-
Major
-
Resolution: Unresolved
-
10.2(EOL), 10.3(EOL)
-
None
Description
On versions before 10.4 multiple authentication methods are not allowed, so setting a password for a account identified via unix_socket plugin causes a warning saying that the action has no significance.
However, the warning is wrong. The change has a two-stage effect.
First, it immediately modifies mysql.user.plugin value to set it to mysql_native_password instead of unix_socket. But it doesn't yet start affecting authentication, the user can still login via unix_socket and cannot login with the password.
Second, after FLUSH the change applies fully and authentication with the password works, while authentication via unix_socket does not.
See the output below.
MariaDB [test]> install soname 'auth_socket';
|
Query OK, 0 rows affected (0.002 sec)
|
|
|
MariaDB [test]> create user elenst@localhost identified via 'unix_socket';
|
Query OK, 0 rows affected (0.002 sec)
|
|
|
MariaDB [test]> grant all on *.* to elenst@localhost;
|
Query OK, 0 rows affected (0.001 sec)
|
|
|
MariaDB [test]> exit
|
Bye
|
|
|
$ echo $USER
|
elenst
|
|
|
$ bin/mysql --socket=/data/bld/10.2/data/tmp/mysql.sock
|
Welcome to the MariaDB monitor. Commands end with ; or \g.
|
Your MariaDB connection id is 10
|
Server version: 10.2.27-MariaDB-debug Source distribution
|
|
|
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
|
|
|
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
|
|
|
MariaDB [(none)]> show grants;
|
+--------------------------------------------------------------------------------+
|
| Grants for elenst@localhost |
|
+--------------------------------------------------------------------------------+
|
| GRANT ALL PRIVILEGES ON *.* TO 'elenst'@'localhost' IDENTIFIED VIA unix_socket |
|
+--------------------------------------------------------------------------------+
|
1 row in set (0.00 sec)
|
|
|
MariaDB [(none)]> select user, host, plugin, password from mysql.user where user='elenst';
|
+--------+-----------+-------------+----------+
|
| user | host | plugin | password |
|
+--------+-----------+-------------+----------+
|
| elenst | localhost | unix_socket | |
|
+--------+-----------+-------------+----------+
|
1 row in set (0.00 sec)
|
|
|
MariaDB [(none)]> set password = password('foo');
|
Query OK, 0 rows affected, 1 warning (0.00 sec)
|
|
|
MariaDB [(none)]> show warnings;
|
+-------+------+-----------------------------------------------------------------------+
|
| Level | Code | Message |
|
+-------+------+-----------------------------------------------------------------------+
|
| Note | 1699 | SET PASSWORD has no significance for users authenticating via plugins |
|
+-------+------+-----------------------------------------------------------------------+
|
1 row in set (0.00 sec)
|
|
|
MariaDB [(none)]> select user, host, plugin, password from mysql.user where user='elenst';
|
+--------+-----------+-----------------------+-------------------------------------------+
|
| user | host | plugin | password |
|
+--------+-----------+-----------------------+-------------------------------------------+
|
| elenst | localhost | mysql_native_password | *F3A2A51A9B0F2BE2468926B4132313728C250DBF |
|
+--------+-----------+-----------------------+-------------------------------------------+
|
1 row in set (0.00 sec)
|
|
|
MariaDB [(none)]> exit
|
Bye
|
|
|
$ sudo bin/mysql --socket=/data/bld/10.2/data/tmp/mysql.sock -uelenst -pfoo
|
ERROR 1698 (28000): Access denied for user 'elenst'@'localhost'
|
|
|
$ bin/mysql --socket=/data/bld/10.2/data/tmp/mysql.sock
|
Welcome to the MariaDB monitor. Commands end with ; or \g.
|
Your MariaDB connection id is 13
|
Server version: 10.2.27-MariaDB-debug Source distribution
|
|
|
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
|
|
|
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
|
|
|
MariaDB [(none)]> flush privileges;
|
Query OK, 0 rows affected (0.00 sec)
|
|
|
MariaDB [(none)]> exit
|
Bye
|
|
|
$ bin/mysql --socket=/data/bld/10.2/data/tmp/mysql.sock
|
ERROR 1045 (28000): Access denied for user 'elenst'@'localhost' (using password: NO)
|
|
|
$ sudo bin/mysql --socket=/data/bld/10.2/data/tmp/mysql.sock -uelenst -pfoo
|
Welcome to the MariaDB monitor. Commands end with ; or \g.
|
Your MariaDB connection id is 15
|
Server version: 10.2.27-MariaDB-debug Source distribution
|
|
|
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
|
|
|
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
|
|
|
MariaDB [(none)]>
|