OperationalError: (1698, "Access denied for user 'cotequotey'@'localhost'")

Question

Even after trying the methods in this threadERROR 1698 (28000): Access denied for user 'superuser1'@'localhost', I still have the same error with my username 'cotequotey' which reads:

OperationalError: (1045, "Access denied for user 'cotequotey'@'localhost' (using password: NO)")

And this username 'cotequotey' is the one that I have attributed the auth_socket plugin to, in order for that username to be the default instead of root. So this is what my user table looks like:

mysql> select User,host,plugin, authentication_string from mysql.user;
+------------------+-----------+-----------------------+-------------------------------------------+
| User             | host      | plugin                | authentication_string                     |
+------------------+-----------+-----------------------+-------------------------------------------+
| root             | localhost | mysql_native_password | *B845F78DCA29B8AE945AB9CFFAC24A9D17EB5063 |
| mysql.session    | localhost | mysql_native_password | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| mysql.sys        | localhost | mysql_native_password | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| debian-sys-maint | localhost | mysql_native_password | *0C8DDC30A93F5F8834121C4DF8703A051E215166 |
| cotequotey       | localhost | auth_socket           |                                           |
+------------------+-----------+-----------------------+-------------------------------------------+
5 rows in set (0.00 sec)

Therefore, I expected my connection via 'cotequotey' to work without a password when I did the command:

ubuntu:~/environment/RAD_Final (angela) $ mysql -u cotequotey
ERROR 1045 (28000): Access denied for user 'cotequotey'@'localhost'

However, this command in the console still produces the original error, so I'm wondering if anyone knows of a solution?

I'm wondering if it is a grant permissions issue, since my root grants permissions are the same as those for cotequotey@localhost. Wasn't sure if this made them clash.

+---------------------------------------------------------------------+
| Grants for root@localhost                                           |
+---------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION |
| GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION        |
+---------------------------------------------------------------------+
+---------------------------------------------------------------------------+
| Grants for cotequotey@localhost                                           |
+---------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'cotequotey'@'localhost' WITH GRANT OPTION |
| GRANT PROXY ON ''@'' TO 'cotequotey'@'localhost' WITH GRANT OPTION        |
+---------------------------------------------------------------------------+

Answer 1

According to the docs:

The socket plugin checks whether the socket user name (the operating system user name) matches the MySQL user name specified by the client program to the server. If the names do not match, the plugin checks whether the socket user name matches the name specified in the authentication_string column of the mysql.user system table row. If a match is found, the plugin permits the connection. The authentication_string value can be specified using an IDENTIFIED ...AS clause with CREATE USER or ALTER USER.

It looks like your mysql account name doesn't match your UNIX user name, and you don't have an alternate specified in the authentication_string field.

ADDENDUM: Essentially auth_socket says "if the operating system authenticated you, MySQL will trust you too." The default to accomplish that is to check if the Linux account name and the MySQL account name match (so no one else can log into the machine and gain privileges by claiming to be you). You can override this behavior by specifying an alternate name as explained in the documentation linked above.