Why Am I Getting a MySQL 'Access Denied' Error for Non-Root Users?

MySQL Access Denied for Non-Root Users: A Comprehensive Guide

Introduction

MySQL, a widely used relational database management system, provides user account management to control access to databases. However, non-root users sometimes encounter an access denied error when attempting to log in. This article aims to address this issue, exploring the steps involved in creating non-root users and troubleshooting the access denied error.

Creating Non-Root Users in MySQL

To create a non-root user, follow these steps:

1. Connect to MySQL as root:

mysql -u root -p

2. Create the user with the desired username and password:

CREATE USER 'golden'@'%' IDENTIFIED BY 'password';

3. Grant specific privileges to the user:

GRANT <privileges> ON database.* TO 'golden'@'localhost' IDENTIFIED BY 'password';

4. Flush privileges to enforce changes:

FLUSH PRIVILEGES;

Troubleshooting Access Denied Errors

If a non-root user receives an access denied error, check the following:

1. Verify Password: Ensure that the password entered is correct.
2. Check User Privileges: Use the SHOW GRANTS command to verify that the user has the necessary privileges.
3. Review User Host: The error message mentions 'golden'@'localhost'. Check that the user is granted access from the correct host ('localhost' in this case).
4. Check Socket Permissions: Ensure that the MySQL socket (/var/lib/mysql/mysql.sock) has appropriate permissions (read and write access for the MySQL user).
5. Disable Old Password Format: Edit /etc/my.cnf, set old_passwords=0, and restart MySQL.

Best Practices

1. Grant Only Necessary Privileges: Avoid granting all privileges to non-root users. Specify the specific permissions required for their tasks.
2. Use Host-Specific Grants: Limit user access to specific hosts (e.g., 'localhost') to enhance security.
3. Monitor User Activity: Use tools like MySQL audit logs to track user activity and detect any suspicious behavior.

By following these steps, you can effectively create and troubleshoot non-root users in MySQL, ensuring secure access to databases for various user profiles.

The above is the detailed content of Why Am I Getting a MySQL 'Access Denied' Error for Non-Root Users?. For more information, please follow other related articles on the PHP Chinese website!