Fatal Error: Can't Open and Lock Privilege Tables: Table 'host' Is Read Only

At that place is a great take chances that, yous might have encountered this result multiple times in your work. This event occurred to me nearly of times due to the incorrectly entering user proper noun or countersign. Though this is one of the reasons, there are other many chances you might become this outcome. Sometimes, it looks very similar, simply when you dig deeper, you will realize multiple factors contributing to this error. This post will explicate in detail, about of the mutual reasons and work around to fix this issue.

Before get into the solutions, permit us have a general scenario with a username, countersign, host and a database with a table. For instance, let usa consider the following details:

• Username: admin
• Host: 127.0.0.1
• Password: admin@123
• Database: demodb
• Table: demotable

Allow the states create the database and user details in MySQL:

User creation in MySQL

Error message:

Error 1045 (28000): Access denied to user

Case 1: Typo error: username or password

This is the most mutual reason for this error. If y'all entered the username or password wrongly, surely you will get this error. Let u.s.a. reproduce this type error by entering the incorrect username and countersign and allow see the output.

Case 1.1: Typo error in username: demousr

Typo error in user name

Case one.1: Typo error in password: demouser123

Typo fault in countersign

Solution:

Solution for this type of error is very simple. Just enter the correct username and password. This fault volition be resolved. In case if y'all forget the password you lot tin reset the username/password.

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

Case ii: Accessing from incorrect host

MySQL provides host based restriction for user access equally a security features. In our product environment, nosotros 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. Allow us come across the output.

Accessing from incorrect host

In our example, we have divers the host proper noun for the demouser. Let u.s. meet the user details for the demouser from mysql.user tabular array.

User info from mysql.user

Here, my laptop host proper noun is 'RATHISH'. And so access denied to the demouser from the host 'RATHISH'

Solution:

When you face up this blazon of outcome, starting time bank check whether your host is allowed or not by checking the mysql.user tabular array.

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

More often than not, accessing as a root user from remote motorcar is disabled and information technology is not a all-time practice, because of security concerns.

If you lot have requirements to access your server from multiple machines, give access merely to those machines. Information technology is better non to use wildcards (%) and gives universal accesses.

Allow me update the mysql.user table, at present the demouser can access MySQL server from any host.

Updating mysql.user table

Case three: User does not exists:

This type of error occurs when the user, which y'all are trying to admission non exist on the MySQL server. For example, I am trying to login as user 'rathish' and come across, what happens:

User not exist issue

Solutions:

When yous face this blazon of event, but cheque 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.

Scanning mysql.user tabular array

Case four: 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 aforementioned user. Allow me bear witness you this.

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

User creation - MySQL

Now, login as demouser and check the grants for demouser@%:

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

Information technology is not advisable to employ wildcards while defining user host. Try to use the exact hostnam

• It is not advisable to utilise wildcards while defining user host, endeavor to employ 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 commodity. Nosotros volition look into the post-obit related topics in the upcoming articles.

• What to do, if you forgot root password in of MySQL server.
• MySQL Admission privilege issues and user related tables.
• MySQL security features with best practices.

I hope this mail volition 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 y'all encountered this mistake in some other scenario, please add together it on the annotate section and I will edit and update the post, it will be aid for our MySQL readers. Please click on FOLLOW ME button to receive my latest MySQL articles.

estradachai1971.blogspot.com

Source: https://www.rathishkumar.in/2017/04/error-1045-28000-access-denied-for-user-user-host-using-password-YES.html

Share this post