Thesql access denied from localhost

auroraMySQL

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"

As requested:

SELECT USER()
admin@xx.xx.xxx.x   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

Best Answer

% deliberately does not include localhost. This decision was possibly made to emphasize the difference between the relatively secure local connection versus coming in from the World Wild Web. Or perhaps because it implies a socket instead of TCP/IP. (There is not much performance difference.)

So, either

  • Use the IP (or hostname) of the machine, or
  • GRANT ... TO admin@localhost ...;