Why Am I Getting an 'Access Denied' Error When Granting MySQL Privileges?

Access Denied for 'root'@'localhost' While Attempting to Grant Privileges

When trying to grant privileges as the root user, you may encounter an access denied error despite having the necessary privileges. This error can occur if you attempt to grant privileges to the mysql.users table.

Understanding the Issue

The mysql.users table stores user information, including privileges. By default, only the root user can modify this table. Attempting to grant privileges on this table to other users can trigger the access denied error.

Solution

To resolve this issue, grant privileges on all databases and tables except for the mysql.users table. Use the following syntax:

GRANT ALL PRIVILEGES ON `%`.* TO '[user]'@'[hostname]' IDENTIFIED BY '[password]' WITH GRANT OPTION;

In this syntax, replace:

• %.*: Represents all databases and tables except mysql.users
• [user]: The username to which you want to grant privileges
• [hostname]: The hostname or IP address from which the user can connect
• [password]: The password for the user

Additional Information

• Avoid using the root user with all privileges for security reasons.
• Consider creating a separate user with limited privileges for granting access to other users.
• Ensure that the user you grant privileges to has the correct hostname and password.

The above is the detailed content of Why Am I Getting an 'Access Denied' Error When Granting MySQL Privileges?. For more information, please follow other related articles on the PHP Chinese website!