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  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 gt  CREATE USER  golden      IDENTIFIED BY  password   Query OK  0 rows affected  0 00 sec   mysql gt  GRANT ALL PRIVILEGES ON       TO  golden       Query OK  0 rows affected  0 00 sec   mysql gt  FLUSH PRIVILEGES  Query OK  0 rows affected  0 00 sec   mysql gt  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

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

User · Answer

In my case the same error happen because I was trying to use mysql by just typing  mysql  instead of  mysql -u root -p

User · Answer

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   lt  lt username gt  gt        IDENTIFIED BY   lt  lt password gt  gt    GRANT ALL PRIVILEGES ON       TO   lt  lt username gt  gt         FLUSH PRIVILEGES    Try logging into the MYSQL instance  This worked for me to eliminate this error

User · Answer

connect your server from mysqlworkbench and run this command-  ALTER USER  root   localhost  IDENTIFIED BY  yourpassword

User · Answer

First I created the user using    CREATE user user localhost IDENTIFIED BY  password txt     After Googling and seeing this  I updated user s password using    SET PASSWORD FOR  user   localhost    PASSWORD  password txt      and I could connect afterward

User · Answer

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

User · Answer

According way you create your user  MySQL interprets a different manner  For instance  if you create a user like this    create user user01 identified by  test01     MySQL expects you give some privilege using grant all on  lt your db gt    to user01   Don t forget to flush privileges   But  if you create user like that  by passing an IP address   you have to change it to   create user  user02   localhost  identified by  teste02     so  to give some privileges you have to do that   grant all on  lt your db gt    to user02 localhost  flush privileges

User · Answer

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

User · Answer

Try this  If you have already created your user  you might have created your user with the wrong password  So drop that user and create another user by doing this  To see your current users    SELECT Host User FROM mysql user   To drop the user   DROP User   lt your-username gt    localhost    After this you can create the user again with the correct password  CREATE USER   lt your-username gt    localhost  IDENTIFIED WITH mysql native password BY   lt correct password gt     then FLUSH PRIVILEGES  You might still run into some more errors with getting access to the database  if you have that error run this   GRANT ALL PRIVILEGES ON     to   lt your-username gt    localhost

User · Answer

It looks like you re trying to make a user  golden      but a different user by the name of  golden   localhost  is getting in the way has precedence    Do this command to see the users    SELECT user host FROM mysql user    You should see two entries    1  user  golden  host    2  user  golden  host localhost  Do these Command   DROP User  golden   localhost   DROP User  golden         Restart MySQL Workbench   Then do your original commands again    CREATE USER  golden      IDENTIFIED BY  password   GRANT ALL PRIVILEGES ON       TO  golden         Then when you go to try to sign in to MySQL  type it in like this     Hit  Test Connection  and enter your password  password

User · Answer

For annoying searching getting here after searching for this error message       Access denied for user  someuser somewhere   using password  YES    The issue for me was not enclosing the password in quotes  eg  I needed to use -p password  instead of -ppassword

User · Answer

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

User · Answer

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

User · Answer

Make sure the user has a localhost entry in the users table  That was the problem I was having  EX   CREATE USER  username   localhost  IDENTIFIED BY  password

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

Examples related to mysql

Examples related to mysql-error-1045