MySQL: root 유저 비밀번호 설정 후에도 shell 접근이 안되는 경우 # auth_socket # mysql_native_password # mysql -u root -p

Ubuntu 환경에서 MySQL 설치후,

sudo 권한으로 mysql shell에 들어가서 root의 패스워드를 변경하고 나서

다시 ubuntu shell에서 mysql -u root -p 명령어로 shell에 접근하려고 하는데,

 

ERROR 1698 (28000): Access denied for user 'root'@'localhost'

 

위 오류가 발생하며 접근이 안되는 경우가 있다.

이럴 경우 프로젝트에서 DB 설정을 통해 접근해도 같은 식으로 접근이 불가하다.

 

sudo 권한으로 다시 mysql shell에 들어가거나

sudo mysql

위 명령어로 mysql에 접근하자 ( Log in to MySQL using the socket authentication (without a password) )

 

그리고 아래 명령어로 root 유저의 인증 방법을 확인해보자

SELECT user, host, plugin FROM mysql.user WHERE user = 'root';

 

 

위와 같이 plugin 컬럼이 mysql_native_password가 아니라 auth_socket (또는 다른 방식) 으로 되어 있다면

아래 명령어를 통해 변경하고, 변경사항을 반영해준다 (flush privileges;)

ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'your_new_password';
FLUSH PRIVILEGES;

 

아래와 같이 바뀌면 된다.

 

auth_socket 방식은 Unix socket 파일을 통해서 유저가 연결하도록 하므로 보안적으로 더 안전하다고 한다.

같은 ubuntu 환경에서 설치를 해도 버전이나 설정에 따라서 auth_socket 방식으로 되어 있을수도, 아닐 수도 있는데,

password 방식으로만 이용하던 환경에서 위 문제는 상당히 당황스럽게 다가왔다.

 

위 두가지 방식 외에도 다른 인증 방식을 사용할 수 있다고 한다.

 

Why is MySQL's Authentication Method Plugin auth_socket?

In recent versions of MySQL, particularly on Debian-based systems like Ubuntu, the default authentication method for the root user is often set to auth_socket for security reasons. This is intended to improve security by requiring users to connect using the Unix socket file rather than a password. This way, MySQL ensures that the user is authenticated through the operating system's user management.

What are auth_socket and mysql_native_password?

auth_socket

• Mechanism: Uses Unix socket file for authentication.
• Usage: Authenticates the MySQL user based on the Unix system user running the MySQL client. This means the user must have a corresponding Unix user account.
• Pros: More secure because it eliminates the need for a password in certain cases.
• Cons: Less flexible for remote connections or scripts that require password authentication.

mysql_native_password

• Mechanism: Uses a password for authentication.
• Usage: Standard method for authenticating MySQL users, requiring a username and password.
• Pros: More versatile and works well for remote connections and scripts.
• Cons: Passwords can be a security risk if not managed properly.

Other Authentication Methods

MySQL supports several other authentication plugins. Here are a few:

caching_sha2_password

• Mechanism: Uses SHA-256 hashing for passwords.
• Usage: Default authentication plugin from MySQL 8.0.
• Pros: Stronger security compared to mysql_native_password.
• Cons: May require additional configuration for older clients.

sha256_password

• Mechanism: Uses SHA-256 hashing for passwords.
• Usage: Provides strong security with some of the advanced features of caching_sha2_password.
• Pros: Strong encryption.
• Cons: Requires SSL/TLS for connections unless RSA is used for password exchange.

pam

• Mechanism: Pluggable Authentication Modules (PAM).
• Usage: Allows integration with various PAM-supported authentication methods, like LDAP, Kerberos, etc.
• Pros: Flexible and can integrate with existing authentication infrastructures.
• Cons: Complexity in configuration and dependency on the PAM configuration.

dialog

• Mechanism: Provides a mechanism for interactive client/server password authentication.
• Usage: Can be used to implement custom authentication dialogs.
• Pros: Flexibility in implementing custom authentication schemes.
• Cons: More complex to implement and manage.

 

Summary

• auth_socket: Authenticates via Unix socket file, tying MySQL user to the Unix user.
• mysql_native_password: Standard username/password authentication.
• Other methods like caching_sha2_password, sha256_password, pam, and dialog offer various levels of security and flexibility.

Each authentication method serves different security needs and use cases, so choosing the right one depends on your specific environment and requirements.