Maria DB Fix - ERROR 1698 (28000): Access denied for user 'root'@'localhost' on a new installation.

Maria DB Fix - ERROR 1698 (28000): Access denied for user 'root'@'localhost' on a new installation.


Today I installed MariaDB on my laptop and faced this weird issue. After the installation, I tried to login to the DB shell using the user 'root' and the blank password. Then it returned me this error message stating:

ERROR 1698 (28000): Access denied for user 'root'@'localhost'
My first guess was that they might have changed the default password and set it to something else. So I did some research and realized it was not the case here. The actual problem was related to the authentication process of MariaDB.

MariaDB uses a plugin called UNIX_SOCKET to authenticate users, which allows them to use the operting system credentials to login. If I wanted to login to MariaDB using the root user, it first requires me to logged into my system as root (which I can temporarily take access using the sudo command.)

But since I'm logged in as the user nimeshka, I cannot request MariaDB to login as another user (in this case, root). So if I enter the command,

mysql -uroot -p
it would tell me that I have no permissions to login. Why? because I'm authenticated to the Ubuntu system as nimeshka, so MariaDB won't trust me when I ask it to login as root :P So it is clear that if I enter the following command, it would allow me to log in.

sudo mysql
You should also note that If I had created a user in mysql with the username nimeshka, I could have logged in to the shell by just entering the mysql command (it will take my current system username and will log me in as the particular mysql user)

Another thing to note: As I was concerned why I didn't face this issue when I installed MariaDB earlier, I looked for a bit more details in their documentation and found a satisfying answer. It says that they use the UNIX_SOCKET plugin by default in new installations of Ubuntu 15 and later (I used to install MariaDB in Ubuntu 14 earlier and today I tried it in Ubuntu 16, which makes sense!).

So here is how to disable the UNIX_SOCKET plugin for root user.

# First we will login with sudo (means root)
$ sudo mysql -u root

# Then switch to the mysql DB.
MariaDB [(none)]> use mysql

# Then we will update the authentication plugin for root user by running the following query.

MariaDB [(none)]> update user set plugin='' where User='root';

# We need to flush privilages and exit the session.
MariaDB [(none)]> flush privileges;
MariaDB [(none)]> quit


That's it guys, hope this will be useful for someone :)

3 comments :

  1. Are you finding an error while receiving the coin in Binance? Looking for the perfect solutions Are you looking for the remedies to erase this error to continue your focus on the work? In order to find the best solutions,you can dial Binance phone number which is functional all the time and users can contact the team at the time of urgency. The team is functional around the clock and is one step away for you.



    ReplyDelete
  2. Can't do that with latest mariadb on osx, I get:

    ERROR 1348 (HY000): Column 'plugin' is not updatable

    ReplyDelete
  3. Thanks for this! I was setting up OpenEMR on mariadb 10.1.47, Ubuntu 18.04 and this fix worked for me. Just wondering if it's safe to leave the Unix socket plugin disabled... Is this error occurring because the setup script is owned by a user other than root? Should I chown the setup script (all of the files?)

    ReplyDelete