I currently have a MySQL database which I have added a new user (abbvs) to identified by a password.
The users I have:
mysql> select user,host from mysql.user;
+-------+-----------+
| user | host |
+-------+-----------+
| abbvs | % |
| stnd | % |
| stnd | 127.0.0.1 |
| root | 127.0.0.1 |
| root | ::1 |
| | ATTD |
| root | ATTD |
| | localhost |
| stnd | localhost |
| root | localhost |
+-------+-----------+
10 rows in set (0.00 sec)
I have added SELECT for all databases, and ALL PRIVILEGES to another followed by flush_privileges;
SHOW GRANTS gives me the following:
mysql> show grants for 'abbvs'@'%';
+-------------------------------------------------------------------------------------------------------+
| Grants for abbvs@% |
+-------------------------------------------------------------------------------------------------------+
| GRANT SELECT ON *.* TO 'abbvs'@'%' IDENTIFIED BY PASSWORD '####' |
| GRANT ALL PRIVILEGES ON `vardb`.* TO 'abbvs'@'%' |
+-------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)
However, when I attempt to login via the CLI with the new user I get the following:
[root@ATTD ~]# mysql -u abbvs -p vardb
Enter password:
ERROR 1045 (28000): Access denied for user 'abbvs'@'localhost' (using password: YES)
Is there anything that could be causing this? All other logins work as expected.
Best Answer
This turned out to be the system lacking a localhost user as well as a remote (any host) user.
Once the additional user had been added for localhost everything worked as expected.