Connect to mariaDB with sudo without password.

huangapple go评论65阅读模式
英文:

Connect to mariaDB with sudo without password

问题

After a fresh installation of MariaDB, the system's user root is able to connect as root without a 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 behavior?

I'm using MariaDB 10.5 and Debian 11.

Thanks.

英文:

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

答案1

得分: 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:

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)
英文:

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)

huangapple
  • 本文由 发表于 2023年4月10日 22:53:08
  • 转载请务必保留本文链接:https://go.coder-hub.com/75978151.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定