Facing issues while updating authentication_string in mariadb

Question

I'm new to databases and was doing installation for a software where we have to change following in maria db.

> mariadb-server-10.6  

> sudo mariadb -u root

ALTER USER 'root'@'localhost' IDENTIFIED BY 'mypassword';
    UPDATE mysql.user SET authentication_string = '' WHERE user = 'root';
    UPDATE mysql.user SET plugin = '' WHERE user = 'root';

I'm able to login into mariadb and ALTER password but when I perform Update I'm getting error.

ERROR 1356 (HY000): View 'mysql.user' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them

So was going through the documentation which state that we have to use ALTER user as now user is table instead of view

MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.001 sec)

MariaDB [(none)]> use mysql;
MariaDB [mysql]> ALTER USER root SET authentication_string=''

Tried this and similar some combination but no luck

Answer 1

You should never modify the system tables in the mysql schema. This is internal data and is likely to break things.

To secure your installation, you could use the mariadb-secure-installation script that comes with all MariaDB versions. This will set passwords for the root account as well as do other things to improve the security (e.g. remove anonymous users).

If you just want to prevent access with the root user, you can just drop it. To prevent all access with any of the root user accounts, you can use this:

DROP USER root@localhost;
DROP USER root@'%';