Mariadb – How to modify authentication plugin in mariadb starting with 10.4.1 to get thesql access for root user without password when migrating thesql to Mariadb

mariadbpermissions

At https://kofler.info/root-login-problem-mit-mariadb/ a method is described for modifying the plugin being used for authenticating a user 

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

leading to

ERROR 1348 (HY000): Column 'plugin' is not updatable

https://mariadb.com/kb/en/library/mysqlglobal_priv-table/

explains that there is now a single column global_priv table instead and the original user table is now a view.

select * from mysql.global_priv where User='root';

is possible or

SELECT CONCAT(user, '@', host, ' => ', JSON_DETAILED(priv)) FROM mysql.global_priv where user='root';

for a more readable version.

Unfortunately the documentation says nothing about

  1. How to modify the column under this design decision?
  2. The motivation for the change?

It would be great to get these two questions answered – obviously #1 being the more pressing one.

Best Answer

see: https://mariadb.com/kb/en/library/authentication-plugin-unix-socket/

ALTER USER root@localhost identified via  unix_socket;  
flush privileges;

also 

mysql

now works for the root user without password.

The statement *"The unix_socket authentication plugin is also installed by default ..."

does have no effect if you migrate from Oracle's mysql to mariadb

Related Question