1

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

1

% 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 ...;
0

I solved it using the external IP from the local machine

mysql -u admin -p -h xx.xx.xx.xx

I don't understand why though...

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.