Access denied for User 'root'@'localhost' - Insufficient permissions?
P粉826283529
P粉826283529 2023-08-07 14:35:51
0
2
536
<p>I keep getting this error. <br /><br />I'm using MySQL Workbench and from what I've found the root user's permissions appear to be empty. No permissions at all. <br /><br />I am having issues with root on multiple platforms and this has been going on for a long time. <br /><br />root@127.0.0.1 seems to have a lot of permissions, but when I log in as root it just assigns the permissions to the local computer - the local computer has no permissions. <br /><br />I have tried FLUSH HOSTS, FLUSH PRIVILEGES and other methods, but none of them succeeded. <br /><br />I need root access. I get so frustrated because when I'm around people who seem to expect me to have access, I actually don't have access, so I can't go into the command line or do anything, and can't grant myself any permissions. <br /><br />When running SHOW GRANTS FOR root, I get the following return: </p><p><code></code></p> <blockquote> <p>Error Code: 1141. There is no such grant defined for user 'root' on host '%'</p> </blockquote><p><br /></p>
P粉826283529
P粉826283529

reply all(2)
P粉864594965

If you have this problem in MySQL 5.7.:

Access denied for user 'root'@'localhost'

Because MySQL 5.7 allows connections via sockets by default, this means you only need to connect to MySQL with administrator privileges.

SELECT user,authentication_string,plugin,host FROM mysql.user;

tThen I saw

+------------------+-------------------------------------------+-----------------------+-----------+
| user             | authentication_string                     | plugin                | host      |
+------------------+-------------------------------------------+-----------------------+-----------+
| root             |                                           | auth_socket           | localhost |
| mysql.session    | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | mysql_native_password | localhost |
| mysql.sys        | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | mysql_native_password | localhost |
| debian-sys-maint | *497C3D7B50479A812B89CD12EC3EDA6C0CB686F0 | mysql_native_password | localhost |
+------------------+-------------------------------------------+-----------------------+-----------+
4 rows in set (0.00 sec)

Allow to connect with administrator privileges and enter a password, then update the values ​​in the table with the command:

ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'Current-Root-Password';
FLUSH PRIVILEGES;

Then run the select command again and you will see that it has changed:

+------------------+-------------------------------------------+-----------------------+-----------+
| user             | authentication_string                     | plugin                | host      |
+------------------+-------------------------------------------+-----------------------+-----------+
| root             | *2F2377C1BC54BE827DC8A4EE051CBD57490FB8C6 | mysql_native_password | localhost |
| mysql.session    | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | mysql_native_password | localhost |
| mysql.sys        | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | mysql_native_password | localhost |
| debian-sys-maint | *497C3D7B50479A812B89CD12EC3EDA6C0CB686F0 | mysql_native_password | localhost |
+------------------+-------------------------------------------+-----------------------+-----------+
4 rows in set (0.00 sec)

Then this is it. After completing the sudo mysql_secure_installation command, you can run the following procedure.

For mariadb, use:


SET PASSWORD FOR 'root'@'localhost' = PASSWORD('manager');

Then set the password. For more information, see https://mariadb.com/kb/en/set-password/.

P粉647504283

Instructions for using resetting password - but instead of resetting the password, force insert a record into the mysql.user table


INSERT INTO mysql.user (Host, User, Password) VALUES ('%', 'root', password('YOURPASSWORD'));
GRANT ALL ON *.* TO 'root'@'%' WITH GRANT OPTION;
Latest Downloads
More>
Web Effects
Website Source Code
Website Materials
Front End Template