Authentication relying on mysql_native_password seems to also check for Linux user?

All we need is an easy explanation of the problem, so here it is.

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 ?

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

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.

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply