Enable password and unix_socket authentication for MariaDB root user?

Question

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)?

Answer 1

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

Answer 2

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.