MySQL Access Denied: Why Can't My Non-Root User Connect?

MySQL: Access Denied for Non-Root User

Problem:

When attempting to log in to MySQL as a non-root user, the error "Access denied for user 'test'@'localhost' (using password: YES)" is encountered, despite having created the user and granted privileges.

Steps Taken to Create User and Privileges:

• Installed MySQL on RHEL 5.7 64bit
• Created MySQL database using mysql_install_db
• Started MySQL service
• Performed mysql_upgrade

Issue with Non-Root User Access:

The issue arises when attempting to log in as a non-root user.

Solution:

To resolve the access denied error, avoid granting all privileges over all databases to a non-root user. This practice is not secure and compromises system integrity. Instead, grant only the necessary privileges to the user.

Grant Command:

The following command can be used to grant specific privileges to a user:

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

For example, the following command grants insert, select, delete, and update privileges on the database "test":

GRANT INSERT, SELECT, DELETE, UPDATE ON test.* TO 'user'@'localhost' IDENTIFIED BY 'password';

Additional Information:

For a detailed list of all privileges, refer to the MySQL documentation. To view information related to user hosts, execute the following query (logged in as "root"):

select Host, User from mysql.user;

The above is the detailed content of MySQL Access Denied: Why Can't My Non-Root User Connect?. For more information, please follow other related articles on the PHP Chinese website!