Password and unix_socket authentication in MySQL and MariaDB. Error “#1698 - Access denied for user ‘root’@’localhost’” (SOLVED)

How to fix #1698 - Access denied for user ‘root’@’localhost’

In the latest versions of MariaDB (possibly MySQL), unix_socket authentication is used by default. If you are not familiar with it, then you might have encountered the error “#1698 - Access denied for user ‘root’@’localhost’”.

Let's take a look at what unix_socket authentication is in MySQL and MariaDB, and how to fix bug #1698.

MySQL and MariaDB unix_socket authentication

The essence of unix_socket authentication is that if the user has already logged into the system, then he does not need to enter a password when connecting to the DBMS, since his authenticity has already been verified when logging into the OS.

In practice, most people work as a regular user and connect to MySQL as root. As a result, the above error occurs.

You can choose one of the options:

1. Always use sudo when connecting as root.

2. Make changes to the MySQL settings so that ordinary users can connect to the DBMS.

3. Create a MySQL user with the same name as your system username

How to check which authentication method is being used

To view the used authentication method, you can use the following SQL query:

select * from mysql.global_priv where User='root';

Or this, for greater clarity of the output:

SELECT CONCAT(user, '@', host, ' => ', JSON_DETAILED(priv)) FROM mysql.global_priv where user='root';

You can see that mysql_native_password and unix_socket are set as plugin:

{
    "access": 18446744073709551615,
    "plugin": "mysql_native_password",
    "authentication_string": "invalid",
    "auth_or": 
    [
        
        {
        },
        
        {
            "plugin": "unix_socket"
        }
    ]
} 

With this configuration, only unix_socket authentication worked for me.

Enabling and disabling unix_socket authentication

You can switch to password authentication with the following SQL query:

ALTER USER 'root'@'localhost' IDENTIFIED BY 'PASSWORD';

Please note that you need to enter the PASSWORD.

To switch to unix_socket authentication, execute the following SQL query:

ALTER USER 'root'@'localhost' IDENTIFIED VIA unix_socket;

Let's check:

SELECT plugin from mysql.user where User='root';

If mysql_native_password is output, it means that password login is being used.

In fact, unix_socket authentication can be combined with password authentication, but I will not dwell on that.

Replacement for “update user set plugin='' where User='root';”

Previously, a similar effect - changing authentication from unix_socket to password authentication - was achieved using a sequence of commands:

Connecting to MySQL Server:

sudo mysql

At the MySQL prompt, you had to run the commands:

use mysql;
update user set plugin='' where User='root';
flush privileges;
exit

Then the service had to be restarted:

sudo systemctl restart mysql.service

And it was possible to connect without sudo.

mysql -u root -p

In the case shown above, the authentication method was also changed from unix_socket to password, but the new password was not set. If you want the same effect (although it becomes insecure after disabling authentication with unix_socket), then you can run the following requests (i.e. set an empty password):

use mysql;
ALTER USER 'root'@'localhost' IDENTIFIED BY '';
exit

Choosing an authentication method when creating a user

You can create a user with password authentication with an SQL query of the following form:

CREATE USER USERNAME@HOST IDENTIFIED BY 'PASSWORD';

To create a user with unix_socket authentication, execute the following SQL query:

CREATE USER USER@HOST IDENTIFIED VIA unix_socket;