12

I've a root User on the MariaDB on Ubuntu 16.04.

As default the root user is authenticated by the unix_socket authentication plugin.

I can switch the authentication method to password method by setting

update mysql.user set plugin='' where user='root';

This works fine. But ...

Is there a possibility to authenticate the root user by unix_socket (by root shell) or by password (when it is connected by localhost:3306)?

Jesse Nickles
  • 1,435
  • 1
  • 17
  • 25
powerpete
  • 2,663
  • 2
  • 23
  • 49

5 Answers5

14

A reliable and straightforward way would be to create another super-user and use it when you want to connect by password. 

CREATE USER admin@localhost IDENTIFIED BY 'password';
GRANT ALL ON *.* TO admin@localhost WITH GRANT OPTION;
-- etc
elenst
  • 3,839
  • 1
  • 15
  • 22
13

MariaDb/MySQL considers 'localhost' (unix socket) to be different than '127.0.0.1' (tcp socket) so you could set a password for TCP and none for Unix sockets like so:

MariaDb:

GRANT ALL PRIVILEGES ON *.* TO 'admin'@'127.0.0.1' IDENTIFIED BY 'xxx' WITH GRANT OPTION;
INSTALL SONAME 'auth_socket';
GRANT ALL PRIVILEGES ON *.* TO 'admin'@'localhost' IDENTIFIED VIA unix_socket WITH GRANT OPTION;

MySQL/Percona:

CREATE USER 'admin'@'127.0.0.1' IDENTIFIED BY 'xxx';
GRANT ALL PRIVILEGES ON *.* TO 'admin'@'127.0.0.1' WITH GRANT OPTION;
INSTALL PLUGIN auth_socket SONAME 'auth_socket.so';
CREATE USER 'admin'@'localhost' IDENTIFIED WITH auth_socket;
GRANT ALL PRIVILEGES ON *.* TO 'admin'@'localhost' WITH GRANT OPTION;

You can then login on the local host with mysql -u admin without a password over the unix socket or via tcp socket with a password using mysql -h 127.0.0.1 -u admin -p.

ColinM
  • 13,367
  • 3
  • 42
  • 49
  • 1
    you did not tell how one is suposed to log in. Using mysql --user=root --host=127.0.0.1 -p does not work since the root@localhost soket based masks the other. – user1708042 Aug 12 '20 at 22:15
  • @user1708042 `sudo mysql -u root`. You're forcing a host and password, so its going to look for that match. – ekydfejj Sep 08 '20 at 22:46
  • Since MySQL 8.0, you can't combine `GRANT ALL PRIVILEGES` with `IDENTIFIED BY` in the same command. You must use two commands as in the accepted answer. – Tom Nguyen Feb 07 '22 at 04:42
  • Thanks for the comments @TomNguyen the answer has been updated accordingly. – ColinM Feb 10 '22 at 01:03
7

Yes — starting with MariaDB 10.4 (released in June 2019*), you can use the OR keyword with IDENTIFIED VIA to allow any-of several authentication methods:

ALTER USER `minecraft` 
    IDENTIFIED VIA unix_socket 
    OR mysql_native_password USING PASSWORD("Type me over TCP, or from other accts");

https://mariadb.com/kb/en/pluggable-authentication-overview/#extended-sql-syntax

*Even though OS vendors may be slow-on-the-draw, or even decline to provide updates altogether, MariaDB provides an official repository serving at least this version to distributions as old as Ubuntu 14.04, CentOS & RHEL 6, Debian 7, and Fedora 28 (as well as up to current releases of each), though for some of these (e.g. Fedora < 32) you may have to manually modify the version in the URL.

JamesTheAwesomeDude
  • 658
  • 7
  • 19
  • 2
    Our MariaDB 10.6 didn't like `CREATE OR ALTER ...`. But changing the statement to `ALTER USER `minecraft` IDENTIFIED VIA unix_socket OR mysql_native_password USING PASSWORD("s3cr3tpasswvrd");` worked. – Abdull Aug 03 '21 at 16:50
  • 1
    @Abdull Thanks for your comment; it looks like they never actuallyimplemented that syntax after all. I updated the answer to reflect this. – JamesTheAwesomeDude Sep 15 '22 at 21:52
2

Is there a possibility to authenticate the root user by unix_socket (by root shell) or by password (when it is connected by localhost:3306)?

The purpose of auth_socket in MySQL or unix_socket in MariaDB is to disable password authentication in favor of Unix sockets, which only allow verified local users to connect to the server.

In other words, you can't have multiple authentication plugins for the same user/host. However like others have pointed out, a common workaround is creating additional users (with the same username) that are associated with different host addresses (passwords can even be the same).

As @ColinM mentioned, localhost usually defaults to using sockets, whereas 127.0.0.1 is inherently non-socket because it is an IP address, e.g. TCP protocol.

Here's an example of this for MySQL 8.0.x:

mysql -e "CREATE USER IF NOT EXISTS 'root'@'localhost' IDENTIFIED WITH auth_socket;"
mysql -e "CREATE USER 'root'@'127.0.0.1' IDENTIFIED WITH mysql_native_password BY '@DB_PASSWORD_ROOT';"
mysql -e "CREATE USER 'root'@'::1' IDENTIFIED WITH mysql_native_password BY '@DB_PASSWORD_ROOT';"

To ensure auth_socket is persistent add lines to your my.cnf:

plugin-load-add = auth_socket.so
auth_socket = FORCE_PLUS_PERMANENT

Keep in mind that it is now recommended to create a separate non-root user to be used with database management tools such as phpMyAdmin or Adminer, e.g. admin@127.0.0.1 and also realize that the new password hash plugin caching_sha2_password is less compatible with many applications, esp. if they are not authenticating using either TLS/SSL, Unix sockets, or shared memory.

Jesse Nickles
  • 1,435
  • 1
  • 17
  • 25
  • Additional official documentation: https://dev.mysql.com/doc/mysql-secure-deployment-guide/8.0/en/secure-deployment-configure-authentication.html – Abdull Feb 23 '22 at 12:10
1

As of 10.4 you can simply run: GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED WITH unix_socket WITH GRANT OPTION; As unix_socket is on by default. Then protect that command with sudo.

Edit: As noted by @colinM, if you use this method, which i do, I also do as he noted, protect the socket file permissions if you're going to use this method.

ekydfejj
  • 339
  • 2
  • 14
  • 1
    Protecting the `mysql` command with sudo does not prevent the user from using their own `mysql` client executable. The permissions on the unix socket file would need to be protected as well. – ColinM Feb 10 '22 at 01:06