2

I have a mariaDB 10.3 install. The mysql.table states that 'root' login relies on mysql_native_password plugin, with no password set.

If my linux user is root, I am able to connect to mysql using;

mysql -u root -h localhost

If I login as a non privileged Linux user, when I try to connect to mysql, I get the following error:

ERROR 1698 (28000): Access denied for user 'root'@'localhost'

Usually people having this issue have a plugin like unix_sock enabled (ex see this reply), but I don't. And by reading the mysql_native_password doc, I don't think the plugin is the issue. This plugin doesn't seem to rely on the Linux user to authenticate (I'm not native English, I may missed the point).

Any idea ?

Glorfindel
  • 2,205
  • 5
  • 19
  • 26
nonayme
  • 23
  • 4

1 Answers1

3

Correct. Only the root account on most Linux-based systems can connect to MariaDB (and MySQL) as the root account. This is to prevent poor authentication practices as have been witnessed time and again where a WordPress (or any other CMS) installation uses the root database account, gets compromised, and gives the attackers full control over the database and every other website connecting to it.

If you would like to connect to MariaDB as root from a terminal connection, you will need to use sudo:

sudo mysql

In many situations, you will not need to specify the username here as it’s assumed the system admin for the server wants access to the root database account.

If you need an administrator-level account to manage the database from an external tool, such as PhpMyAdmin, TablePlus, or MySQL Workstation, you can create an account and grant appropriate privileges:

CREATE USER 'admin'@'%' IDENTIFIED WITH mysql_native_password BY 'superSecretPassword!123';
GRANT ALL ON *.* TO 'admin'@'%' WITH GRANT OPTION;

While this account can also be misused, like 'root'@'localhost', it is generally better practice to go this way.

matigo
  • 2,078
  • 1
  • 7
  • 15