Sunday, 9 April 2017

[Solved] ERROR 1045 (28000): Access denied for user 'user'@'host' (using password: YES)

Error message

Error 1045 (28000): Access denied for user ‘user’@’host’ (using password: YES)

Debugging Summary

  • Check for typo error: username or password. 
  • Check the host name and compare it with mysql.user table host name. 
  • Check user exists or not. 
  • Check whether host contains IP address or host name.
There is a great chance that, you might have encountered this issue multiple times in your work. This issue occurred to me most of times due to the incorrectly entering user name or password. Though this is one of the reasons, there are other many chances you might get this issue. Sometimes, it looks very similar, but when you dig deeper, you will realize multiple factors contributing to this error. This post will explain in detail, most of the common reasons and work around to fix this issue. 

Possible reasons:

  • Case 1: Typo error: username or password.
  • Case 2: Accessing from wrong host.
  • Case 3: User does not exists on the server.
  • Case 4: Mix of numeric and name based hosts.
Before get into the solutions, let us take a general scenario with a username, password, host and a database with a table. For example, let us consider the following details:
  • Username: admin
  • Host: 127.0.0.1
  • Password: admin@123
  • Database: demodb
  • Table: demotable
Let us create the database and user details in MySQL:

Access denied to user: MySQL User creation
User creation in MySQL


Error message:

Error 1045 (28000) Access denied to user MySQL Error message
Error 1045 (28000): Access denied to user






Case 1: Typo error: username or password

This is the most common reason for this error. If you entered the username or password wrongly, surely you will get this error. Let us reproduce this type error by entering the incorrect username and password and let see the output.

Case 1.1: Typo error in username: demousr

Error 1045 (28000): Access denied to user in MySQL (using password: Yes) - typo error in username.
Typo error in user name
Case 1.1: Typo error in password: demouser123

Error 1045 (28000): Access denied to user in MySQL (using password: Yes) - typo error in password
Typo error in password

Solution: 

Solution for this type of error is very simple. Just enter the correct username and password. This error will be resolved. In case if you forget the password you can reset the username/password.

If you forget the password for admin / root account, there are many ways to reset / recapture the root password. I will publish another post on how to reset the root password in-case if you forget root password.

Case 2: Accessing from wrong host

MySQL provides host based restriction for user access as a security features. In our production environment, we used to restrict the access request only to the Application servers. This feature is really helpful in many production scenarios.  

I am trying to access MySQL server from my laptop, my laptop host name is different from the defined host. Let us see the output.
Error 1045 (28000): Access denied to user in MySQL (using password: Yes) - accessing from wrong host
Accessing from wrong host
In our example, we have defined the host name for the demouser. Let us see the user details for the demouser from mysql.user table.

Error 1045 (28000): Access denied to user in MySQL (using password: Yes) - accessing from wrong host
User info from mysql.user

Here, my laptop host name is 'RATHISH'. So access denied to the demouser from the host 'RATHISH'

Solution:

When you face this type of issue, first check whether your host is allowed or not by checking the mysql.user table.

If it is not defined, you can update or insert new record to mysql.user table.

Generally, accessing as a root user from remote machine is disabled and it is not a best practice, because of security concerns.

If you have requirements to access your server from multiple machines, give access only to those machines. It is better not to use wildcards (%) and gives universal accesses.

Let me update the mysql.user table, now the demouser can access MySQL server from any host.

Error 1045 (28000): Access denied to user in MySQL (using password: Yes) - updating mysql.user table
Updating mysql.user table






Case 3: User does not exists:

This type of error occurs when the user, which you are trying to access not exist on the MySQL server. For example, I am trying to login as user ‘rathish’ and see, what happens:

Error 1045 (28000): Access denied to user in MySQL (using password: Yes) - user does not exist
User not exist issue


Solutions:

When you face this type of issue, just check whether the user is exists in mysql.user table or not. If the record not exists, user cannot access. If there is a requirement for that user to access, create a new user with that username.

Error 1045 (28000): Access denied to user in MySQL (using password: Yes) - checking mysql.user table
Scanning mysql.user table


Case 4: Mix of numeric and name based hosts:

Always remember that, MySQL treat the following users, demouser@localhost, demouser@127.0.0.1, demouser@%, demouser@’rathishlaptop’ as different users, not same user. Let me show you this.

Remember we have given SELECT privilege on demodb.demotable to demouser@127.0.0.1.:

Error 1045 (28000): Access denied to user in MySQL (using password: Yes) - user creation.
User creation - MySQL
Now, login as demouser and check the grants for demouser@%:

Error 1045 (28000): Access denied to user in MySQL (using password: Yes) - checking grants.
Grants of demouser@%
We have updated only the host details, but entire access given to demouser@127.0.0.1 is not available to demouser@%.






Important points

It is not advisable to use wildcards while defining user host. Try to use the exact hostnam
  • It is not advisable to use wildcards while defining user host, try to use the exact host name.
  • Disable root login from remote machine.
  • Use proxy user concept.
There are few other concepts related with this topic and getting into details of those topics is very different scope of this article. We will look into the following related topics in the upcoming articles.
  • What to do, if you forgot root password in of MySQL server.
  • MySQL Access privilege issues and user related tables.
  • MySQL security features with best practices.
I hope this post will help for you to fix the MySQL Error Code 1045 Access denied for user in MySQL. If I missed any other scenarios where this error could occur or if you encountered this error in some other scenario, please add it on the comment section and I will edit and update the post, it will be help for our MySQL readers. Please click on FOLLOW ME button to receive my latest MySQL articles.

Post a Comment