root 사용자를 제외하고 'test'@ 'localhost'사용자 (암호 사용 : YES)에 대한 액세스가 거부되었습니다.

root 사용자를 제외하고 'test'@ 'localhost'사용자 (암호 사용 : YES)에 대한 액세스가 거부되었습니다.

---

mysql 비 루트 / 관리자 사용자에게 문제가 있습니다. 사용자 및 권한을 생성하기 위해 아래 단계를 따르고 있습니다. 내가 잘못하고있는 경우 수정합니다.

에 설치 중 mysql입니다 RHEL 5.7 64bit. 패키지는 아래에 언급되어 rpm install있습니다.

1. 를 사용하여 mysql db를 mysql_install_db만든 다음
2. mysql 서비스를 시작한 다음
3. mysql_upgrade또한 우리는 서버를 사용하고 있습니다.

이 프로세스 후에는 다음으로 로그인 할 수 root있지만 루트가 아닌 사용자로는 서버에 로그인 할 수 없습니다.

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

이것이 내가 직면 한 문제입니다. 이에 대한 해결책이 있습니까?

---

모든 데이터베이스에 대한 모든 권한을 루트가 아닌 사용자에게 부여하지 마십시오. 안전하지 않습니다 (이미 해당 역할에 "루트"가 있음).

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

---

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 '<<username>>'@'%%' IDENTIFIED BY '<<password>>';
GRANT ALL PRIVILEGES ON * . * TO '<<username>>'@'%%';
FLUSH PRIVILEGES;

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

---

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

---

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

---

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.

---

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.

---

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

---

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 <your_db>.* 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 <your_db>.* to user02@localhost;
flush privileges;

---

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

---

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

---

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

---

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

---

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

참고URL : https://stackoverflow.com/questions/20353402/access-denied-for-user-testlocalhost-using-password-yes-except-root-user