Most of mysql users will face with a login error, when they try to login with another user name into mysql db on localhost. I am one of them. Typically, these commands are run:
> GRANT ALL ON *.* TO 'fatih'@'%' IDENTIFIED BY 'istanbul';
> quit;
Then, we try 'mysql -u fatih -p'. This ends with an error : 'ERROR 1045 (28000): Access denied for user 'fatih'@'localhost' (using password: YES)'
It doesn't seem reasonable at first look and to figure what is going on out, passwords and grants are usually checked as first action. However, both will not work, because granted from '%' behaves unexpectedly if you try to login a mysql db in your computer. Since localhost is resolved to 127.0.0.1 in /etc/hosts file, mysql interprets the login attempt from user 'fatih' as fatih@localhost which doesn't have permission to authenticate.
There are two ways to handle this issue.
Specify another ip address to which any domain name is resolved in /etc/hosts file. Call command 'ifconfig', and pick up eth0 ipv4 address. When you call the command 'mysql -u fatih -p -h 12.12.12.1', you will be able to login. When you run 'select user()' command, you will get 'fatih@12.12.12.1' The point there, there should not be any line which do resolving for this particular ip address in /etc/hosts file.
As second option, you can give grants for a user from specific domain name. In this case, this domain name is localhost.
> GRANT ALL ON *.* TO 'fatih'@'%' IDENTIFIED BY 'istanbul';
> quit;
Then, we try 'mysql -u fatih -p'. This ends with an error : 'ERROR 1045 (28000): Access denied for user 'fatih'@'localhost' (using password: YES)'
It doesn't seem reasonable at first look and to figure what is going on out, passwords and grants are usually checked as first action. However, both will not work, because granted from '%' behaves unexpectedly if you try to login a mysql db in your computer. Since localhost is resolved to 127.0.0.1 in /etc/hosts file, mysql interprets the login attempt from user 'fatih' as fatih@localhost which doesn't have permission to authenticate.
There are two ways to handle this issue.
Specify another ip address to which any domain name is resolved in /etc/hosts file. Call command 'ifconfig', and pick up eth0 ipv4 address. When you call the command 'mysql -u fatih -p -h 12.12.12.1', you will be able to login. When you run 'select user()' command, you will get 'fatih@12.12.12.1' The point there, there should not be any line which do resolving for this particular ip address in /etc/hosts file.
As second option, you can give grants for a user from specific domain name. In this case, this domain name is localhost.
- Login mysql shell with root user <root@localhost privilege is defined as default>.
- Run mysql command : "grant all privileges on *.* to fatih@localhost identified by 'istanbul';"
- Quit mysql console, then login with user fatih : 'mysql -u faith -p'. Once you login, if you type select user();, you will see that you have been logged in as fatih@localhost.
A user from other hosts will be able to login if they are granted with '%'. But this will not work if login attempt and mysql server are in the same host. This article is for explaining this ambiguity.
Easy to do, hard to discover ;)
Easy to do, hard to discover ;)
Comments
Post a Comment