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

Question

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.

Answer 1

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

Answer 2

ALTER USER 'root'@'localhost' IDENTIFIED WITH <auth_plugin> USING PASSWORD('RootPass');