MySQL: Access denied for user 'test'@'localhost' (using password: YES) except root user

Question

I am facing problem with mysql non root/admin user, I am following the below steps for creating user and its privileges, correct me if i am doing wrong, i am installing `mysql` on `RHEL 5.7 64bit`, packages are mentioned below, once i done the `rpm install` we are - creating mysql db using `mysql_install_db`, then - starting the mysql service then - using `mysql_upgrade` also we are doing to the server. After this process i can login as `root` but with a non-root user I am not able to log into the server: ``` [root@clustertest3 ~]# rpm -qa | grep MySQL MySQL-client-advanced-5.5.21-1.rhel5 MySQL-server-advanced-5.5.21-1.rhel5 [root@clustertest3 ~]# cat /etc/my.cnf [mysqld] datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock user=mysql # Default to using old password format for compatibility with mysql 3.x # clients (those using the mysqlclient10 compatibility package). old_passwords=1 # Disabling symbolic-links is recommended to prevent assorted security risks; # to do so, uncommen…

Accepted Answer

Do not grant all privileges over all databases to a non-root user, it is not safe (and you already have "root" with that role)

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

This statement creates a new user and grants selected privileges to it. I.E.:

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

Take a look at the docs to see all privileges detailed

EDIT: you can look for more info with this query (log in as "root"):

```
select Host, User from mysql.user;
```

To see what happened

MySQL: Access denied for user 'test'@'localhost' (using password: YES) except root user

Solution

Problem