Debug School

rakesh kumar
rakesh kumar

Posted on • Updated on

MySQL Access denied Error

How to fix Access denied for user 'user_name'@'host' in MySQL?
Access denied for user root@localhost
access-denied-for-user-root-localhost-mysql

access-denied-for-user-root-localhost
Cannot login to MySQL, access denied
cannot-login-to-mysql-access-denied
mysql access denied for new user
mysql-access-denied-for-new-user
MySQL Access denied on grant all privileges with an admin user
MySQL Access denied on grant all privileges with an admin user
Access denied for user 'root'@'localhost' while attempting to grant privileges. How do I grant privileges?
Access denied for user 'root'@'localhost' while attempting to grant privileges. How do I grant privileges?
AWS RDS - Access denied to admin user when using GRANT ALL PRIVILEGES ON the_db.* TO 'the_user'@'%'
aws-rds-access-denied-to-admin-user-when-using-grant-all-privileges-on-the-db
Access denied for user 'root'@'%'
access-denied-for-user-root
Confused by GRANT ALL PRIVILEGES causing Access Denied for database 'mysql'
[https://dba.stackexchange.com/questions/280776/confused-by-grant-all-privileges-causing-access-denied-for-database-mysql]

(https://dba.stackexchange.com/questions/280776/confused-by-grant-all-privileges-causing-access-denied-for-database-mysql)

How to fix Access denied for user 'user_name'@'host' in MySQL?

access-denied-for-user-root-localhost-mysql

MySQL might return this error when you try to connect to a MySQL Server:

Error 1045 - Access denied for user 'user_name'@'host'...
This is one of the most common errors in MySQL. It typically means you are trying to connect using a user account that doesn’t have enough privileges to connect.

By default, after installation, MySQL only allows localhost connection and blocks any remote connection attempts until the user privileges get re-configure properly. That explains why you can’t connect from a remote client while other server-side programs seem to connect without any issues.

To establish a remote connection, you have to re-configure it and give it proper privileges:

GRANT ALL PRIVILEGES ON *.* TO 'user_name'@'host' IDENTIFIED BY "password";
Enter fullscreen mode Exit fullscreen mode

Or:

GRANT ALL PRIVILEGES ON *.* TO 'user_name'@'%' IDENTIFIED BY "Password";
Enter fullscreen mode Exit fullscreen mode

For testing purpose, you can start the server with the --skip-grant-tables option:

$ mysqld --skip-grant-tables
Enter fullscreen mode Exit fullscreen mode

Then you can access the MySQL grant tables using the SHOW GRANTS statement to check the privileges and change if you want:

SHOW GRANTS FOR 'user'@'host';
Enter fullscreen mode Exit fullscreen mode

When finished, flush privileges:

FLUSH PRIVILEGES;
Enter fullscreen mode Exit fullscreen mode

If the issue arises with the root account only after upgrading MySQL,

it’s likely the authentication plugin has changed to auth_socket by default. So what you can do it to change the authentication method from auth_socket to mysql_native_password:

ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'password';
Then flush privileges:

FLUSH PRIVILEGES;
Enter fullscreen mode Exit fullscreen mode

Cannot login to MySQL, access denied

cannot-login-to-mysql-access-denied

PROBLEMS
After a crash, I re-installed Ubuntu on my dev machine over the previous installation, and after I re-installed all the necessary packages (including mysql-server and mysql-client) I went on restoring the dev database. Unfortunately, I am unable to connect.

This works :

$ sudo su
# mysql -u root
Enter fullscreen mode Exit fullscreen mode

this does not

$ mysql -u root -p
Enter fullscreen mode Exit fullscreen mode

I have tried purging mysql-* and reinstalling everything.
I have tried reconfiguring (i.e. sudo dpkg-reconfigure mysql-server-5.7)
I have tried manually reseting the password using UPDATE queries.
I have tried everything here (ecept that the table user does not have a password column, but an authentication_string one)
Please, can someone help me get this thing working?

mysql> SELECT user, host, authentication_string, password_expired, account_locked FROM user WHERE user = 'root';
Enter fullscreen mode Exit fullscreen mode

+------------------+-----------+-------------------------------------------+------------------+----------------+
| user | host | authentication_string | password_expired | account_locked |
+------------------+-----------+-----------------------+------------------+----------------+
| root | localhost | * | N | N |
| root | % | * | N | N |
+------------------+-----------+-----------------------+------------------+----------------+
5 rows in set (0.00 sec)
Both root users have the same password, set using PASSWORD('password'), and all privileges have been flushed, then the service was restarted.

Contrary to what the documentation says, running sudo dpkg-reconfigure mysql-server-5.7 does not prompt me to specify a new password for root. The command terminates successfully, but I am never asked to specify a password.

SOLUTION

I found my answer, here.

Basically, the plugin value for the user root@'localhost' was not mysql_native_password.

UPDATE user SET plugin = 'mysql_native_password' WHERE user = 'root';
Enter fullscreen mode Exit fullscreen mode 
FLUSH PRIVILEGES;
Enter fullscreen mode Exit fullscreen mode

worked, and I was finally able to log in without super user privileges.

mysql access denied for new user

Problems
Extremely basic question but I'm pulling my hair out trying to figure this out:

mysql> create database hellodb;
Query OK, 1 row affected (0.00 sec)

mysql> grant all privileges on hellodb to hellouser identified by '123';
Query OK, 0 rows affected (0.00 sec)

mat@admin:~$ mysql -u hellouser hellodb -p
Enter fullscreen mode Exit fullscreen mode

Enter password:
ERROR 1045 (28000): Access denied for user 'hellouser'@'localhost' (using password: YES)

Solution
Instead of this statement

grant all privileges on hellodb to hellouser identified by '123';
Enter fullscreen mode Exit fullscreen mode

Try this one below ->

grant all privileges on hellodb.* to 'hellouser'@'localhost' identified by '123';
Enter fullscreen mode Exit fullscreen mode

MySQL Access denied on grant all privileges with an admin user
MySQL Access denied on grant all privileges with an admin user

Problems
When I try to grant privileges to a user with an admin user I get this error message:

GRANT ALL PRIVILEGES ON awesomeproject.* TO 'awesomeproject'@'localhost';
Result:

ERROR 1044 (42000): Access denied for user 'mygoodadmin'@'localhost' to database 'awesomeproject'
If I check the current user's privileges with this command:

SHOW GRANTS FOR 'mygoodadmin'@'localhost';
I get this result:

+------------------------------------------------------------+
| Grants for mygoodadmin@localhost |
+------------------------------------------------------------+
| GRANT USAGE ON . TO 'mygoodadmin'@'localhost' |
| GRANT ALL PRIVILEGES ON %.* TO 'mygoodadmin'@'localhost' |
+------------------------------------------------------------+

Solution

The solution is based on Akina's comment:

GRANT ALL PRIVILEGES ON *.* TO 'dcms_central'@'localhost' WITH GRANT OPTION;
Enter fullscreen mode Exit fullscreen mode

The additional information:

*.* is the good way, not this one:

`%`.*
Enter fullscreen mode Exit fullscreen mode

Top comments (0)