This looks like a duplicate of many questions asked here. I haven't found the answer for my specific problem, so I'm posting it here.
I was trying to create a local database and a user with basic permissions to it:
CREATE DATABASE demodb;
Query OK, 1 row affected (0.00 sec)
USE demodb;
Database changed
CREATE TABLE demotable(id int, name VARCHAR(25));
Query OK, 0 rows affected (0.01 sec)
CREATE USER 'demouser' IDENTIFIED BY 'p';
Query OK, 0 rows affected (0.00 sec)
FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.01 sec)
GRANT SELECT ON demodb.demotable TO 'demouser';
Query OK, 0 rows affected (0.00 sec)
FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)
With this setup, I wasn't able to log in:
mysql -u demouser -p demodb
Enter password: ERROR 1045 (28000): Access denied for user 'demouser'@'localhost' (using password: YES)
mysql -u demouser -p -h 127.0.0.1 demodb
Enter password: ERROR 1045 (28000): Access denied for user 'demouser'@'localhost' (using password: YES)
mysql -u demouser -p -h localhost demodb
Enter password: ERROR 1045 (28000): Access denied for user 'demouser'@'localhost' (using password: YES)
I am using MySQL 5.6 on macOS using the official dmg installer.