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]
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";
Or:
GRANT ALL PRIVILEGES ON *.* TO 'user_name'@'%' IDENTIFIED BY "Password";
For testing purpose, you can start the server with the --skip-grant-tables option:
$ mysqld --skip-grant-tables
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';
When finished, flush privileges:
FLUSH PRIVILEGES;
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;
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
this does not
$ mysql -u root -p
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';
+------------------+-----------+-------------------------------------------+------------------+----------------+
| 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';
FLUSH PRIVILEGES;
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 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';
Try this one below ->
grant all privileges on hellodb.* to 'hellouser'@'localhost' identified by '123';
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;
The additional information:
*.* is the good way, not this one:
`%`.*
Top comments (0)