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

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

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

1. creating mysql db using mysql_install_db, then
2. starting the mysql service then
3. 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, uncomment this line:  # symbolic-links=0    [mysqld_safe]  log-error=/var/log/mysqld.log  pid-file=/var/run/mysqld/mysqld.pid    [root@clustertest3 ~]# ls -ld /var/lib/mysql/mysql.sock  srwxrwxrwx 1 mysql mysql 0 Nov  30 11:09 /var/lib/mysql/mysql.sock    mysql> CREATE USER 'golden'@'%' IDENTIFIED BY 'password';  Query OK, 0 rows affected (0.00 sec)    mysql> GRANT ALL PRIVILEGES ON * . * TO 'golden'@'%';  Query OK, 0 rows affected (0.00 sec)    mysql> FLUSH PRIVILEGES;  Query OK, 0 rows affected (0.00 sec)    mysql> SELECT USER(),CURRENT_USER();  +----------------+----------------+  | USER()         | CURRENT_USER() |  +----------------+----------------+  | root@localhost | root@localhost |  +----------------+----------------+  1 row in set (0.00 sec)    [root@clustertest3 ~]# mysql -ugolden -p  Enter password:  ERROR 1045 (28000): Access denied for user 'golden'@'localhost' (using password: YES)  

This is the problem I am facing, is there any solution to this?

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

Try:

CREATE USER 'golden'@'localhost' IDENTIFIED BY 'password';  GRANT ALL PRIVILEGES ON * . * TO 'golden'@'localhost';  FLUSH PRIVILEGES;  

Or even better use: mysql_setpermission to create the user

Answer by Alberto Mega for Access denied for user 'test'@'localhost' (using password: YES) except root user

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  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

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

For anyone else who did all the advice but the problem still persists.

Check for stored procedure and view DEFINERS. Those definers may no longer exists.

My problem showed up when we changed the wildcard host (%) to IP specific, making the database more secure. Unfortunately there are some views that are still using 'user'@'%' even though 'user'@'172....' is technically correct.

Answer by Makarand Kulkarni for Access denied for user 'test'@'localhost' (using password: YES) except root user

If you are connecting to the MySQL using remote machine(Example workbench) etc., use following steps to eliminate this error on OS where MySQL is installed

mysql -u root -p    CREATE USER '<>'@'%%' IDENTIFIED BY '<>';  GRANT ALL PRIVILEGES ON * . * TO '<>'@'%%';  FLUSH PRIVILEGES;  

Try logging into the MYSQL instance.
This worked for me to eliminate this error.

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

Just add computer name instead of 'localhost' in hostname or MySQL Host address.

Answer by 4af2e9eb6 for Access denied for user 'test'@'localhost' (using password: YES) except root user

I also have the similar problem, and later on I found it is because I changed my hostname (not localhost).

Therefore I get it resolved by specifying the --host=127.0.0.1

mysql -p mydatabase --host=127.0.0.1  

Fatal error: Call to a member function getElementsByTagName() on a non-object in D:\XAMPP INSTALLASTION\xampp\htdocs\endunpratama9i\www-stackoverflow-info-proses.php on line 72