I've a very weird problem on mysql/Aurora 1.14 I have installed on a Linux EC2 instance on AWS.
I have an user able to connect remotely to the instance but not locally.
select user,host from mysql.user; user | host admin| %
I'm able to connect with that user from outside
mysql -u admin -pxxxx -h ip
but when I'm inside the machine
mysql -u admin -pxxx ERROR 1045 (28000): Access denied for user 'admin'@'localhost' (using password: YES)
I've tried several other things (with or without sudo)
mysql -u admin -pxxx -h localhost mysql -u admin -pxxx -h 127.0.0.1 mysql -u admin -pxxx --proto=TCP
I've also tried to create another user
create user 'localtest'@'localhost' identified by 'pwd18'; grant all on mydb.* to 'localtest'@'localhost'; flush privileges; mysql -u localtest -ppwd18 -h localhost ERROR 1045 (28000): Access denied for user 'localtest'@'localhost' (using password: YES)
Other weird things is I don't have an user "root" in mysql.user.
And if I try from remote, with user admin to give grant all on all dbs to localtest I get:
GRANT ALL PRIVILEGES ON *.* TO 'localtest'@'localhost' Error Code: 1045. Access denied for user 'admin'@'%' (using password: YES)
No more ideas. Logs is not very useful, just says "access denied"
SELECT USER() email@example.com admin@% SHOW GRANTS FOR 'admin'@'%' GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, PROCESS, REFERENCES, INDEX, ALTER, SHOW DATABASES, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER, LOAD FROM S3, SELECT INTO S3 ON *.* TO 'admin'@'%' IDENTIFIED BY PASSWORD '*xxxxxx' WITH GRANT OPTION
I also gave explicitly all above grants to user 'localtest'@'localhost', but nothing changed