1

After a fresh installation of MariaDB, the system's user root is able to connect as root without password with : sudo mysql -u root

After reimporting my backup, I can't do this anymore :

user@server:~$ sudo mysql -u root
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)

Table mysql.user contains :

MariaDB [mysql]> select host, user, password, plugin from mysql.user;
+-----------+------------+-------------------------------------------+-----------------------+
| host      | user       | password                                  | plugin                |
+-----------+------------+-------------------------------------------+-----------------------+
| localhost | root       |                                           | mysql_native_password |
| localhost | phpmyadmin | *xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx | mysql_native_password |
| %         | user1      | *xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx | mysql_native_password |
| localhost | seafile    | *xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx | mysql_native_password |
| localhost | gogs       | *xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx | mysql_native_password |
| localhost | user2      | *xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx |                       |
| localhost | freshrss   | *xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx |                       |
+-----------+------------+-------------------------------------------+-----------------------+
7 rows in set (0,001 sec)

How can I get back this behaviour ?

I'm using mariadb 10.5 and Debian 11.

Thanks

fdamien12
  • 43
  • 4
  • I don't know why `root` user have empty `password` column. A password is set for this user, too. – fdamien12 Apr 10 '23 at 15:14
  • help? [question](https://stackoverflow.com/questions/14739645/how-to-create-a-root-user-with-no-password-in-mysql). – francisco Apr 10 '23 at 15:26

1 Answers1

1

First thing, from MariaDB-10.4 onwards mysql.user is a view that doesn't completely contain everything related to the user. mysql.global_priv is the more complete version.

For a complete view of a user, use show create user root@localhost.

To change use alter user:

MariaDB [mysql]> alter user root@localhost IDENTIFIED VIA   unix_socket;
Query OK, 0 rows affected (0.000 sec)

MariaDB [mysql]> show create user root@localhost;
+-----------------------------------------------------------+
| CREATE USER for root@localhost                            |
+-----------------------------------------------------------+
| CREATE USER `root`@`localhost` IDENTIFIED VIA unix_socket |
+-----------------------------------------------------------+
1 row in set (0.000 sec)
danblack
  • 12,130
  • 2
  • 22
  • 41
  • Is it possible to continue to be able to login with password ? https://stackoverflow.com/questions/41846000/enable-password-and-unix-socket-authentication-for-mariadb-root-user says `ALTER USER root@localhost IDENTIFIED VIA unix_socket OR mysql_native_password USING PASSWORD("my-password");` works, but I got an error. – fdamien12 Apr 11 '23 at 06:58
  • Like [MDEV-27733](https://jira.mariadb.org/browse/MDEV-27733)? Please report bugs if that's not it. – danblack Apr 11 '23 at 07:40
  • I get `ERROR 1396 (HY000): Operation ALTER USER failed for 'root'@'localhost'` – fdamien12 Apr 11 '23 at 09:09
  • Can you include the `mysql.global_priv` row containing `root` ,`localhost` in a [bug report](https://jira.mariadb.org) and we'll see what can be done. What MariaDB version was the backup from? – danblack Apr 11 '23 at 23:46