Why Does My MySQL/MariaDB Root User Lack Privileges, Showing 'Access Denied'?

"Access Denied for User 'root'@'localhost' (using password: YES) - No Privileges?"

This error arises when the root user of MySQL lacks the necessary privileges to perform an operation.

Root User's Null Schema Privileges

Usually, the root user has full privileges. However, in this case, it's reported that the root's schema privileges are null, indicating it has no permissions whatsoever.

Impact on Various Platforms

This problem affects the user's ability to operate the MySQL server across different platforms. The user is encountering obstacles even when logged in as "[email protected]".

Troubleshooting Attempts

The user has attempted various solutions, such as "FLUSH HOSTS" and "FLUSH PRIVILEGES", but these have proved unsuccessful.

Solution for MySQL 5.7

If you are experiencing this issue in MySQL 5.7 or later, the solution is to enable password-based authentication:

1. Execute the following SQL query:

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

2. Note that the "authentication_string" for the root user should not be empty. If it is, update it with the current root password:

   ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '<Current-Root-Password>';

3. Flush the privileges:

   FLUSH PRIVILEGES;

Solution for MariaDB

For MariaDB, use the following query to set the root user's password:

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

You can find more information at https://mariadb.com/kb/en/set-password/.

Note:

After disabling authentication plugins or modifying the root user's password, it is recommended to run the "mysql_secure_installation" command to enhance security.

The above is the detailed content of Why Does My MySQL/MariaDB Root User Lack Privileges, Showing 'Access Denied'?. For more information, please follow other related articles on the PHP Chinese website!