MySQL 8.0 SHA-256 인증(Authentication) - caching_sha2_password - Caching SHA-2 Pluggable Authentication

Last Updated on 7월 19, 2024 by Jade(정현호)

안녕하세요
이번 포스팅에서는 MySQL의 인증(Authentication)에 관련된 내용에 대해서 확인해보도록 하겠습니다.

authentication plugin

MySQL에 접속 시 사용되는 계정 인증 플러그인(방식)은 여러 다양한 방식을 지원하고 있습니다.

그 중에서 이번 글에서는 mysql_native_password 과 caching_sha2_password 에 대해서 이야기해보려고 합니다.

MySQL 5.7 버전까지 계정 인증(비밀번호)에 대한 기본 인증 방식은 mysql_native_password 이었습니다.

mysql_native_password 는 계정의 비밀번호를 SHA-1 알고리즘을 통해 해시된 해시 값을 저장해두고 로그인시 입력한 비밀번호의 해시 값과 동일한지 비교하는 방식입니다. 그래서 동일한 패스워드에는 항상 동일한 해시 값이 생성되게 됩니다.

그래서 만약 두개의 계정이 같은 비밀번호를 사용하고 있다면 저장된 해시 값도 동일한 값으로 확인이 되게 됩니다.

MySQL 8버전에서 새로운 인증 방식이 도입되었고 그것은 caching_sha2_password 이고 8버전 부터 인증 방식의 기본값으로 되었습니다.

caching_sha2_password는 MySQL 5.6에서 추가된 sha256_password 인증 방식에 대한 개선된 인증 방식으로 비밀번호의 해시 값을 생성하기 위해서 SHA-2(256bit) 알고리즘을 사용하고 salt 함수를 이용하고 있습니다.

salt는 기본적으로 계정 비밀번호를 변환하는 암호화 해시 함수로 그 결과가 무작위라서 각 실행 마다 해시 값이 달라지게 되고 그래서 두 계정이 동일한 비밀번호를 사용하더라도 해시 값이 달라져서 저장된 해시 값을 통해서 비밀번호가 같은 지를 확인할 수 없게 됩니다.

이와 같이 sha256 알고리즘 및 salt를 이용한 비밀번호 해시 값 저장은 안전하지만, 해시 값을 계산 방식의 복잡함에 의해서 부하가 높고 성능이 떨어질 수 있게 되고 그에 따라서 이를 개선하고 보완하기 위해서 해시 값을 메모리에 캐시해서 사용하는 방식이 caching_sha2_password 입니다.

다만 caching_sha2_password 인증방식을 이용하기 위해서 중요한점은 SSL/TLS 또는 RSA 키 페어 사용이 필요 하다는 점입니다.

[참고] SSL/TLS 접속에 관련되어서는 핀다 기술 블로그에 작성한 MySQL과 SSL/TLS에 대한 내용을 참조하시면 됩니다.

MySQL과 SSL/TLS통한 접속

SSL/TLS 그리고 handshake에 관련된 내용을 중점으로 다루어 봤으며 MySQL과 연관되어 MySQL에서의 SSL/TLS 접속에 관한 글을 핀다 회사 기술 블로그에 작성하였습니다.

Changes in MySQL 8.0.34 & Motivation

얼마전에 출시된(글 작성 기준으로) MySQL 8.0.34 버전에서의 변화된 내용 중에서는 인증방식과 관련된 내용이 포함되어 있고 그것은 인증 방식 중 mysql_native_password가 Deprecated 되었다는 내용입니다.

보통의 경우 기존의 드라이버와의 호환성이나 SSL/TLS 미사용 환경, authentication_string(해시값) 활용 등 여러 가지 이유에 의해서 MySQL 8 버전의 기본 인증 방식인 caching_sha2_password 대신 기존의 기본 인증 방식인 mysql_native_password 으로 설정해서 많이 사용하였습니다.

MySQL 8.0.34 버전에서는 mysql_native_password이 Deprecated 가 되었습니다.
아직 Removed가 되지는 않았으나 어느 마이너 버전에서 Remove 가 될지는 아직까지 알 수 없습니다.

MySQL 8.0.34 버전에서 default_authentication_plugin 파라미터를 mysql_native_password 로 설정하면 MySQL 기동 시 로그에 다음과 같은 warning 이 발생하는 것을 확인할 수 있습니다.

[Warning] [MY-013360] [Server] Plugin mysql_native_password reported: 
''mysql_native_password' is deprecated and will be removed in a future release. 
Please use caching_sha2_password instead'

* 가로 길이에 따른 개행 되어있습니다.

MySQL 8.0.27 버전에서는 default_authentication_plugin 파라미터가 Deprecated가 되었는데요

개인의견으로 default_authentication_plugin 파라미터의 Remove와 mysql_native_password 인증방식의 Remove가 같은 마이너 버전에서 되지 않을까 하는 예상을 해봅니다.

그래서 이번 글을 작성하게 된 Motivation 은 어느 마이너 버전일지는 알 수 없지만, mysql_native_password 인증 방식이 Removed 가 되면 향후에는 보편적으로 caching_sha2_password 인증방식을 사용하게 될 부분이 예상됨에 따라서 caching_sha2_password 에 대한 내용을 정리하게 되었습니다.

[MySQL 9.0 내용 추가] MySQL 9.0 버전부터 mysql_native_password authentication 플러그인이 제거(removed) 되었습니다.
그에 따라 다음의 3개 서버 옵션(파라미터)도 같이 제거(removed) 되었습니다.

• --mysql-native-password
• --mysql-native-password-proxy-users
• default_authentication_plugin

• 포스팅 테스트 환경
- MySQL 8.0.34 - 2 인스턴스(Replication)

[참고] MySQL 8.0.34 버전이 LTS(Long-Term Support) 이 되었으며, 새로운 버전으로 출시된 MySQL 8.1.0은 Innovation release 로 명명되었습니다. 이와 관련된 자세한 내용은 아래 MySQL Blog 페이지를 참조하시면 됩니다.

caching_sha2_password

caching_sha2_password 인증 방식(플러그인)은 MySQL 8.0에서 기본 인증 방식(플러그인)이 되었으며, 앞에서 일부 설명한 내용과 같이 비밀번호의 해시 값을 생성하기 위해서 SHA-2(256bit) 알고리즘을 사용하고 salt 함수를 이용합니다.

비밀번호를 변환하는 암호화 해시 함수로 그 결과가 무작위라서 매번 해시 값이 달라집니다.

SHA-256 알고리즘 및 salt를 이용한 비밀번호 해시 값 계산 과정의 복잡함에 의해서 부하가 높고 성능이 떨어질 수 있게 되고 그래서 해시 값을 메모리에 캐시해서 사용하는 방식이 caching_sha2_password 입니다.

sha256_password 와 caching_sha2_password 차이점으로는 다음과 같습니다.

• sha256_password: MySQL 5.6에 추가되었으며 SHA-256 알고리즘을 통해 인증을 구현합니다.

• caching_sha2_password: SHA-256 인증(예: sha256_password)을 통해 구현하지만 성능 향상을 위해 서버 측에서 캐싱을 사용하고 추가 기능이 있습니다.

caching_sha2_password 플러그인은 기존의 sha256_password에 비해 다음과 같은 장점을 제공합니다.

• 서버 쪽에서 메모리 내 캐시를 사용하면 이전에 연결한 적이 있는 사용자가 다시 연결할 때 더 빠르게 다시 인증할 수 있습니다.
• RSA 기반 암호 교환은 MySQL이 연결된 SSL 라이브러리에 관계없이 사용할 수 있습니다.
• Unix 소켓 파일 및 공유 메모리 프로토콜을 사용하는 클라이언트 연결에 대한 지원이 제공됩니다.

설치

서버 측 플러그인은 서버에 내장되어 있으며 명시적으로 로드 할 필요가 없으며 언로드하여 비활성화 할 수 없습니다.

클라이언트 측 플러그인은 "libmysqlclient" 클라이언트 라이브러리에 내장되어 있으며 또는 libmysqlclient 라이브러리가 연결된 모든 프로그램에서 사용할 수 있습니다

서버 측 플러그인은 sha2_cache_cleaner 감사 플러그인을 도우미로 사용하여 암호 캐시 관리를 수행합니다.

caching_sha2_password와 마찬가지로 sha2_cache_cleaner 내장되어 있으므로 설치할 필요가 없습니다.
            

Using caching_sha2_password plugin

caching_sha2_password 플러그인 방식으로 계정을 사용하려면 크게 2가지 방법이 있습니다.

먼저 계정 생성 또는 alter user 유저로 변경시에 다음과 같이 명시적으로 caching_sha2_password을 지정하는 방법입니다.

mysql> CREATE USER 'sha2user'@'%'
IDENTIFIED WITH caching_sha2_password BY 'password';

MySQL서버는 caching_sha2_password 플러그인을 계정에 할당하고 SHA-256 및 salt를 사용하여 사용자 계정 암호를 암호화 및 해시하고 해당 값을 mysql.user 테이블의 authentication_string 컬럼에 값을 저장합니다.

두번째 방법으로는 기본 인증 플러그인 설정을 caching_sha2_password 하는 것입니다. 이럴 경우 더 간단하게 create user 와 alter user 구문을 사용할 수 있습니다.

MySQL 8.0 버전부터는 default_authentication_plugin 파라미터의 기본값은 caching_sha2_password 입니다.

default_authentication_plugin를 mysql_native_password 이나 다른 플러그인으로 명시적으로 생성한 경우 다음과 같이 명시적으로 설정하거나 default_authentication_plugin 파라미터 라인을 제거해서 기본값으로 사용하도록 설정합니다.

[mysqld]
default_authentication_plugin=caching_sha2_password

caching_sha2_password 이 기본 인증으로 설정되었다면 다음과 같이 계정 생성시 명시적으로 플러그인을 지정하지 않아도 caching_sha2_password 으로 계정은 할당됩니다.

mysql> CREATE USER 'sha2user'@'%' IDENTIFIED BY 'password';

mysql> select user,plugin from mysql.user where user='sha2user';
+----------+-----------------------+
| user     | plugin                |
+----------+-----------------------+
| sha2user | caching_sha2_password |
+----------+-----------------------+

테스트를 위해서 권한을 부여하도록 하겠습니다.(글의 테스트 예제를 위한 것으로 필수는 아님)

mysql> grant select on mysql.user to sha2user@'%';

mysql> flush privileges;

[참고] 계정 생성시 할당된 인증 방식으로 복제됨

예를 들어 소스(Source) 인스턴스는 default_authentication_plugin를 기본값인 caching_sha2_password 로 설정되어 있고, 리플리카(Replica) 인스턴스는 명시적으로 mysql_native_password 로 설정되어 있을 수 있습니다.
또는 반대 설정이 될 수도 있습니다.

복제 인스턴스에서도 기준이 되는 것은 생성 시 사용된 인증 방식을 그대로 복제되어 생성됩니다.

소스 인스턴스에서 default_authentication_plugin=caching_sha2_password 으로 설정된 상태에서 특정 인증 플러그인을 지정하지 않고 계정을 생성하고, 리플리카 인스턴스에서는 mysql_native_password 으로 설정되어 있더라도 리플리카에서도 소스에서 생성된 기준인 caching_sha2_password 으로 할당되어 계정이 복제 생성됩니다.

위에서 생성한 sha2user 계정에 대해서 리플리카 인스턴스에서도 caching_sha2_password 인 것을 확인할 수 있습니다.

• 리플리카 인스턴스 조회

mysql> show variables like 'default_authentication_plugin';
+-------------------------------+-----------------------+
| Variable_name                 | Value                 |
+-------------------------------+-----------------------+
| default_authentication_plugin | mysql_native_password |
+-------------------------------+-----------------------+


mysql> select user,plugin from mysql.user where user='sha2user';
+----------+-----------------------+
| user     | plugin                |
+----------+-----------------------+
| sha2user | caching_sha2_password |
+----------+-----------------------+

즉, 소스 인스턴스에서 생성된 기준으로 리플리카 인스턴스에 복제됩니다.
            

authentication_string 확인

mysql_native_password 와 caching_sha2_password 로 생성된 계정의 authentication_string는 다르며, 같은 패스워드라도 caching_sha2_password 로 생성된 해시 값은 다릅니다.

확인을 위해 테스트 유저를 생성하도록 하겠습니다.

mysql> CREATE USER 'native_user'@'%'
IDENTIFIED WITH mysql_native_password BY 'password';

같은 비밀번호인 "password" 에 대해서 다른 authentication_string으로 저장되는 것을 확인할 수 있습니다.

select user,plugin,authentication_string
from mysql.user where user in('sha2user','native_user')\G

*************************** 1. row ***************************
                 user: native_user
               plugin: mysql_native_password
authentication_string: *2470C0C06DEE42FD1618BB99005ADCA2EC9D1E19

*************************** 2. row ***************************
                 user: sha2user
               plugin: caching_sha2_password
authentication_string: $A$005$TFzWRpK(:CaRt?7?DctC5Kye10cgJ3eOetQnWkFaz57JTvWLxhyab34mSR/

mysql_native_password는 항상 같은 해시 값을 생성하는데 반해 caching_sha2_password는 같은 비밀번호라도 매번 다른 authentication_string 을 생성하여 저장합니다.

-- 비밀번호 변경
mysql> alter user sha2user@'%' IDENTIFIED WITH caching_sha2_password BY 'password';

mysql> select user,plugin,authentication_string
from mysql.user where user='sha2user'\G
*************************** 1. row ***************************
                 user: sha2user
               plugin: caching_sha2_password
authentication_string: $A$005$R{D?w4P[%o>i*qD6DL72DBLTJvWUG8e5Vie097cjp424aihgPjb3Cuz0


-- 비밀번호 변경
mysql> alter user sha2user@'%' IDENTIFIED WITH caching_sha2_password BY 'password';

mysql> select user,plugin,authentication_string
from mysql.user where user='sha2user'\G
*************************** 1. row ***************************
                 user: sha2user
               plugin: caching_sha2_password
authentication_string: $A$005$so[W?>si_7Ar653PYJgcPTB.xlNAlMmCP0AgweZwWgSjIHUV9/UJzF7j3


-- 비밀번호 변경
alter user sha2user@'%' IDENTIFIED WITH caching_sha2_password BY 'password';

mysql> select user,plugin,authentication_string
from mysql.user where user='sha2user'\G
*************************** 1. row ***************************
                 user: sha2user
               plugin: caching_sha2_password
authentication_string: $A$005$V071F+xtV\TAP-{+wxYsIxMN4VAVDeiM2Q6TONTFDQ4sy/gSC1xp3LuWBl/

그래서 저장된 해시 값으로 비밀번호를 확인하거나 다른 계정간의 비밀번호 동일한지 등을 확인할 수 없습니다.
           

caching_sha2_password 와 RSA

caching_sha2_password 인증 플러그인으로 접속하려면 크게 두가지 방법을 사용해야 합니다.

1) SSL/TLS 보안 연결로 접속
2) 암호화되지 않은(SSL/TLS 미사용) 연결 시 RSA 사용

즉, SSL/TLS로 접속하거나 RSA를 사용해야 합니다.

caching_sha2_password는 기본적으로 보안 전송(SSL/TLS)을 통한 연결을 지원합니다. 클라이언트에서 SSL/TLS 로 접속하지 않았을 경우 RSA를 사용할 경우 암호화되지 않은 연결을 통해 RSA를 사용하여 암호화된 암호 교환도 지원합니다.

MySQL 서버에서 caching_sha2_password_auto_generate_rsa_keys 파라미터 사용하여 RSA 키 쌍 파일을 자동으로 생성할지 여부를 결정합니다.

caching_sha2_password_auto_generate_rsa_keys 외 유사 파라미터로 sha256_password_auto_generate_rsa_keys 이 있으며 두개 파라미터는 암호화되지 않은 연결을 통해 RSA를 사용하여 보안 암호 교환에 필요한 RSA 키 페어 파일의 자동 생성에 관한 파라미터입니다.

두개 파라미터 기본값은 ON 으로 MySQL 인스턴스 생성시 RSA 키 페어 파일을 자동 생성합니다.

MySQL서버 측에서는 caching_sha2_password_auto_generate_rsa_keys을 별도로 변경하지 않아서 기본값 ON 일 경우 인스턴스 생성시 RSA 키 페어 파일이 생성되며 다음의 두 개의 파라미터를 통해서 RSA 개인 및 공개 키 쌍 파일의 이름을 지정합니다.

• caching_sha2_password_private_key_path
  • 기본값: private_key.pem
• caching_sha2_password_public_key_path
  • 기본값: public_key.pem

테스트 시스템에서 조회해본 내용은 다음과 같습니다.

mysql> show variables like 'caching_sha2_password_private_key_path';
+----------------------------------------+-----------------+
| Variable_name                          | Value           |
+----------------------------------------+-----------------+
| caching_sha2_password_private_key_path | private_key.pem |
+----------------------------------------+-----------------+

mysql> show variables like 'caching_sha2_password_public_key_path';
+---------------------------------------+----------------+
| Variable_name                         | Value          |
+---------------------------------------+----------------+
| caching_sha2_password_public_key_path | public_key.pem |
+---------------------------------------+----------------+

위와 같이 파라미터에서 확인되는 파일은 기본적으로 data dir 아래에 위치해 있습니다.

$ ls -al data | grep "private_key.pem\|public_key.pem"
-rw-------.  1 mysql mysql      1705  x월 xx 13:51 private_key.pem
-rw-r--r--.  1 mysql mysql       452  x월 xx 13:51 public_key.pem

데이터베이스 관리자는 사용할 키 파일의 이름이 파라미터의 기본값과 다른 경우 서버 시작 시 파라미터를 설정해야 합니다.

Caching_sha2_password_rsa_public_key 상태 변수는 caching_sha2_password 인증 플러그인에서 사용하는 RSA 공개 키 값을 표시합니다. RSA 공개 키를 소유한 클라이언트는 나중에 설명하는 것처럼 연결 프로세스 중에 서버와 RSA 키 쌍 기반 암호 교환을 수행할 수 있습니다.

암호화되지 않은(SSL/TLS 미사용) 연결 시 RSA 사용을 할 경우 기본적으로 RSA 공개 키를 클라이언트에 보내지 않습니다. 클라이언트는 클라이언트 측의 복사본 공개 키를 이용하거나 MySQL 서버에 공개 키를 요청할 수 있습니다.

클라이언트에서 공개 키를 신뢰할 수 있는 로컬 복사본을 사용하면 클라이언트/서버 프로토콜의 왕복을 방지할 수 있으며 서버에서 공개 키를 요청하는 것보다 더 안전합니다.

반면에 서버에서 공개 키를 요청하는 것이 더 편리하며(클라이언트 쪽 파일을 관리할 필요가 없음) 보안 네트워크 환경에서는 허용될 수 있습니다.

정리하면 RSA 공개키는 다음과 같은 두가지 방법으로 사용합니다.

• RSA 공개키를 클라이언트 측에 저장하여 사용
• 접속 시 서버에 RSA 공개키를 요청하여 사용

mysql 클라이언트에서 접속하는 경우 공개키 관련해서 다음 두가지 옵션을 사용할 수 있습니다.

• --server-public-key-path 옵션을 사용하여 RSA 퍼블릭 키 파일을 지정합니다.
• --get-server-public-key 옵션을 사용하여 서버에서 퍼블릭 키를 요청합니다.

mysql, mysqlsh, mysqladmin, mysqlbinlog, mysqlcheck, mysqldump, mysqlimport, mysqlpump, mysqlshow, mysqlslap, mysqltest mysql_upgrade 와 같은 클라이언트 프로그램에서는 두 가지 옵션을 지원합니다.

리플리카 인스턴스에서 복제 설정을 하는 경우 CHANGE REPLICATION SOURCE TO 구문 (MySQL 8.0.23 이후) 또는 CHANGE MASTER TO 문(MySQL 8.0.23 이전)에서 SOURCE_PUBLIC_KEY_PATH | MASTER_PUBLIC_KEY_PATH RSA 을 통해서 공개 키를 지정하거나 또는 GET_SOURCE_PUBLIC_KEY | GET_MASTER_PUBLIC_KEY 를 통해서 소스 인스턴스에서 공개 키를 요청하는 옵션을 사용할 수 있습니다.

caching_sha2_password 플러그인을 사용하는 클라이언트의 경우 서버에 연결할 때 암호가 일반 텍스트로 노출되지 않습니다. 비밀번호 전송 방법은 보안 연결 또는 RSA 암호화가 사용되는지 여부에 따라 다릅니다.

• 연결이 안전한 경우(SSL/TSL로 접속 시) RSA 키 페어가 필요하지 않으며 사용되지 않습니다. 이는 TLS를 사용하여 암호화된 TCP 연결과 Unix 소켓 파일 및 공유 메모리 연결에 적용됩니다. 암호는 일반 텍스트로 전송되지만 연결이 안전하기 때문에(SSL/TSL로 접속) 스누핑 할 수 없습니다.
• 연결이 안전하지 않으면(SSL/TSL 미사용) RSA 키 페어가 사용됩니다. 이는 TLS를 사용하여 암호화되지 않은 TCP 연결 및 네임드 파이프(named-pipe) 연결에 적용됩니다.
  RSA는 비밀번호 스누핑을 방지하기 위해 클라이언트와 서버 간의 암호 교환에만 사용됩니다. 서버가 암호화된 암호를 받으면 암호를 해독합니다.

암호 생성 중에 caching_sha2_password에서 사용하는 해시 라운드 수를 변경하려면 다음과 같이 caching_sha2_password_digest_rounds 파라미터를 변경합니다.

[mysqld]
caching_sha2_password_digest_rounds=10000

caching_sha2_password_digest_rounds 기본값은 5000 입니다. 암호 저장을 위해 caching_sha2_password 인증 플러그인에서 사용하는 해시 라운드 수입니다.

기본값보다 해싱 라운드 수를 늘리면 증가량과 관련되어 성능 패널티가 발생합니다.

caching_sha2_password 플러그인을 사용하는 계정 생성은 계정이 생성된 클라이언트 세션에 영향을 미치지 않지만 서버는 작업을 완료하기 위해 해싱 라운드를 수행해야 합니다.

계정을 사용하는 클라이언트 연결의 경우 서버는 해싱 라운드를 수행하고 결과를 캐시에 저장해야 합니다. 그에 따라서 첫 번째 클라이언트 연결에 대한 로그인 시간이 더 길어지게 됩니다.

물론 그 다음 연결에는 그렇지 않습니다. 이 동작은 서버를 다시 시작한 후 또는 캐시가 삭제되는 경우 발생합니다. 캐시 삭제 관련해서는 아래에서 설명합니다.
      

인스턴스 생성 또는 MySQL 서버에 접속 후에 Caching_sha2_password_rsa_public_key 파라미터 값을 확인합니다.

아래 키 정보 내용은 예시임으로 실제 키와는 다릅니다. 다만 내용이 비어 있지 않아야 합니다.

mysql> SHOW STATUS LIKE 'Caching_sha2_password_rsa_public_key'\G

*************************** 1. row ***************************
Variable_name: Caching_sha2_password_rsa_public_key
        Value: -----BEGIN PUBLIC KEY-----
MIGfMA0GCSqGSIb3DQEBAQUAA4GNADCBiQKBgQDO9nRUDd+KvSZgY7cNBZMNpwX6
MvE1PbJFXO7u18nJ9lwc99Du/E7lw6CVXw7VKrXPeHbVQUzGyUNkf45Nz/ckaaJa
aLgJOBCIDmNVnyU54OT/1lcs2xiyfaDMe8fCJ64ZwTnKbY2gkt1IMjUAB5Ogd5kJ
g8aV7EtKwyhHb0c30QIDAQAB
-----END PUBLIC KEY-----

값이 비어 있으면 서버에서 키 파일에 문제가 있는 것입니다. MySQL 오류 로그 등을 통해서 문제를 확인해야 합니다.
     

접속

caching_sha2_password 플러그인으로 인증하는 계정에 접속하기 위해서는 다음과 같이 두가지 방법이 있음을 설명했습니다.

1) SSL/TLS 보안 연결로 접속
2) 암호화되지 않은(SSL/TLS 미사용) 연결 시 RSA 사용

앞에서 언급했듯이 이러한 계정은 보안 연결(SSL/TLS 사용, 이 경우 RSA가 사용되지 않음) 또는 RSA를 사용하여 암호 교환을 수행하는 암호화되지 않은 연결을 사용할 수 있습니다.

• SSL/TLS 방식으로 접속

mysql -u sha2user -p -h 127.0.0.1 --ssl-mode=REQUIRED

또는

mysql -u sha2user -p -h 127.0.0.1


mysql> show session status like 'ssl_version';
+---------------+---------+
| Variable_name | Value   |
+---------------+---------+
| Ssl_version   | TLSv1.3 |
+---------------+---------+

mysql> select current_user;
+--------------+
| current_user |
+--------------+
| sha2user@%   |
+--------------+

select user,plugin from mysql.user where user='sha2user';
+----------+-----------------------+
| user     | plugin                |
+----------+-----------------------+
| sha2user | caching_sha2_password |
+----------+-----------------------+

--ssl-mode 의 기본값은 PREFERRED 으로 서버가 암호화된 연결을 지원하는 경우 암호화된 연결을 설정하고, 암호화된 연결을 설정할 수 없는 경우 암호화되지 않은 연결로 접속합니다.

다음 테스트하기 전에 관리자 계정에서 flush privileges 를 한번 수행 후에 계속 진행합니다.

mysql> select current_user;
+--------------+
| current_user |
+--------------+
| root@%       |
+--------------+

mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)

이번에는 암호화되지 않은(SSL/TLS 미사용) 연결로 접속을 시도해보겠습니다.

• 암호화되지 않은(SSL/TLS 미사용) 연결

mysql -u sha2user -p -h 127.0.0.1 --ssl-mode=DISABLED

ERROR 2061 (HY000): 
    Authentication plugin 'caching_sha2_password' 
    reported error: Authentication requires secure connection.

* 가로 길이에 따라서 개행 되었습니다.

caching_sha2_password 인증 플러그인을 사용하는 sha2user 유저로 접속 시 SSL/TLS 미사용으로 설정하였기 때문에(--ssl-mode=DISABLED) 암호화되지 않은 연결을 사용하였으며 그에 따라서 RSA 를 통해서 비밀번호를 전송해야 합니다.

하지만 현재 접속하는 클라이언트는 공개 키가 존재 않고 서버에서도 공개 키를 클라이언트에 보내지 않았기 때문에 비밀번호를 암호화할 수 없으며 연결이 실패합니다.

이럴 경우 서버로 부터 RSA 공개 키(RSA public key)를 요청하도록 설정(옵션 사용) 해야 하며 그때 사용하는 옵션이 --get-server-public-key 입니다.

$ mysql -u sha2user -p -h 127.0.0.1 \
--ssl-mode=DISABLED --get-server-public-key=true


-- 접속 후 조회
mysql> select current_user;
+--------------+
| current_user |
+--------------+
| sha2user@%   |
+--------------+

mysql> show session status like 'ssl_version';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Ssl_version   |       |
+---------------+-------+

암호화되지 않은(SSL/TLS 미사용) 연결로 접속하였기 때문에 SSL 버전 정보가 없습니다.

다음 테스트하기 전에 관리자 계정에서 flush privileges 를 수행 후에 계속 진행합니다.

mysql> select current_user;
+--------------+
| current_user |
+--------------+
| root@%       |
+--------------+

mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)

사전에 미리 클라이언트 측에 RSA 공개 키가 복사되어 있다면 --server-public-key-path 옵션을 사용해서 접속할 수 있습니다.

옵션에서 지정된 공개 키 파일의 값은 MySQL 서버의 caching_sha2_password_public_key_path 파라미터에서 명명된 서버의 파일의 키 값과 동일해야 합니다.

키 파일에 유효한 공개 키 값이 포함되어 있지만 값이 올바르지 않으면 액세스 거부 오류가 발생하며, 키 파일에 유효한 공개 키가 포함되어 있지 않으면 클라이언트 프로그램에서 사용할 수 없습니다.
                

클라이언트 측에 RSA 공개 키를 저장하는 하는 방법은 크게 두가지 방법이 있을 수 있습니다.

• 데이터베이스 관리자에 의해서 서버의 공개 키 파일의 복사본을 클라이언트 측에 전달
• 서버에 연결할 수 있는 클라이언트 사용자는 SHOW STATUS LIKE 'Caching_sha2_password_rsa_public_key' 구문의 실행결과(반환된 키 값)을 파일에 저장

이처럼 암호화되지 않은(SSL/TLS 미사용) 연결로 접속 시 RSA 공개 키가 필요하며 사전에 저장된 공개 키를 명시적으로 지정하지 않으면 접속하려는 서버로부터 RSA 공개키를 수신해야 합니다.

mysql client에서는 "--get-server-public-key" 이며 접속하는 개발 언어 드라이버 별로 어떤 드라이버는 명시적으로 옵션을 지정 해야지 RSA 공개키를 가져오는 드라이버가 있고, 어떤 드라이버는 기본값이 RSA 암호화를 미지원 하는 경우도 있습니다.

Java에서 접속 property 를 다음과 설정 후 접속을 하게 되면 에러가 발생합니다.(useSSL=false 사용)

jdbc:mysql://127.0.0.1:3306/mysql?useSSL=false&serverTimezone=Asia/Seoul


Exception in thread "main" java.sql.SQLNonTransientConnectionException: Public Key Retrieval is not allowed
    at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:110)
    at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122)
    at com.mysql.cj.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:829)
    at com.mysql.cj.jdbc.ConnectionImpl.<init>(ConnectionImpl.java:449)
    at com.mysql.cj.jdbc.ConnectionImpl.getInstance(ConnectionImpl.java:242)
    at com.mysql.cj.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:198)
    at java.sql/java.sql.DriverManager.getConnection(DriverManager.java:677)
    at java.sql/java.sql.DriverManager.getConnection(DriverManager.java:228)
    at TestConnection_mysqldriver.main(TestConnection_mysqldriver.java:15)
Caused by: com.mysql.cj.exceptions.UnableToConnectException: Public Key Retrieval is not allowed

RSA 공개키가 필요하나 공개 키 검색이 허용되지 않았다는 메시지를 통해 공개 키를 가져오지 못했음을 알 수 있습니다.

이처럼 jdbc driver 에서는 RSA 공개 키를 찾는 속성의 기본값은 false임에 따라서 SSL/TLS를 미사용 하면서 MySQL 계정이 caching_sha2_password 인증 방식을 사용한다면 접속 시 위와 같은 에러가 발생하게 됩니다.

서버로부터 공개 키를 받아오기 위해서는 allowPublicKeyRetrieval 속성을 사용하면 됩니다.

jdbc:mysql://127.0.0.1:3306/mysql?useSSL=false&allowPublicKeyRetrieval=true&serverTimezone=Asia/Seoul

$ java TestConnection_mysqldriver
user :sha2user@%, version: 8.0.34, SSL/TLS Version:

다음 테스트하기 전에 관리자 계정에서 flush privileges 를 수행 후에 계속 진행합니다.

mysql> select current_user;
+--------------+
| current_user |
+--------------+
| root@%       |
+--------------+

mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)

파이썬에서도 접속 설정에서 ssl_disabled= True 으로 속성을 사용할 경우 다음과 같이 접속 시 에러가 발생합니다.

$ python3 py_conn_mysql.py

Traceback (most recent call last):
  File ".../site-packages/mysql/connector/connection_cext.py", line 291, in _open_connection
    self._cmysql.connect(**cnx_kwargs)
_mysql_connector.MySQLInterfaceError: 
    Authentication plugin 'caching_sha2_password' 
    reported error: Authentication requires secure connection.

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "py_conn_mysql.py", line 13, in <module>
    ssl_disabled= True,
  File ".../site-packages/mysql/connector/pooling.py", line 293, in connect
    return CMySQLConnection(*args, **kwargs)
  File ".../site-packages/mysql/connector/connection_cext.py", line 120, in __init__
    self.connect(**kwargs)
  File ".../site-packages/mysql/connector/abstracts.py", line 1181, in connect
    self._open_connection()
  File ".../site-packages/mysql/connector/connection_cext.py", line 298, in _open_connection
    ) from err
mysql.connector.errors.InterfaceError: 2061 (HY000): 
    Authentication plugin 'caching_sha2_password' 
    reported error: Authentication requires secure connection.

* 가로 길이에 따른 개행 되어있습니다.

다만 Connector/Python(mysql-connector-python)은 RSA 암호화를 지원하지 않기 때문에 caching_sha2_password 플러그인은 SSL/TLS이 아닌 연결에서는 작동하지 않습니다.(Ref Link)

이와 같이 개발 언어 또는 드라이버별로 RSA 암호화 접속을 미지원을 하는 경우도 있기 때문에 SSL/TLS을 사용할 수 있는 경우 SSL/TLS로 접속을 하는 것이 권장됩니다.

[참고] 드라이버 버전이 낮을 경우 caching_sha2_password 인증 방식 미지원으로 인해서 발생되는 에러 메시지

## Java
Exception in thread "main" java.sql.SQLException: Unable to load authentication plugin 'caching_sha2_password'.
 at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:868)
 at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:864)
 at com.mysql.jdbc.MysqlIO.proceedHandshakeWithPluggableAuthentication(MysqlIO.java:1746)
 at com.mysql.jdbc.MysqlIO.doHandshake(MysqlIO.java:1226)
 at com.mysql.jdbc.ConnectionImpl.coreConnect(ConnectionImpl.java:2191)
 at com.mysql.jdbc.ConnectionImpl.connectOneTryOnly(ConnectionImpl.java:2222)
 at com.mysql.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:2017)
 at com.mysql.jdbc.ConnectionImpl.<init>(ConnectionImpl.java:779)
 at com.mysql.jdbc.JDBC4Connection.<init>(JDBC4Connection.java:47)
 at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
 at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
 at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
 at java.lang.reflect.Constructor.newInstance(Constructor.java:423)
 at com.mysql.jdbc.Util.handleNewInstance(Util.java:425)
 at com.mysql.jdbc.ConnectionImpl.getInstance(ConnectionImpl.java:389)
 at com.mysql.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:330)
 at java.sql.DriverManager.getConnection(DriverManager.java:664)
 at java.sql.DriverManager.getConnection(DriverManager.java:247)
 at TestConnection_mysqldriver.main(TestConnection_mysqldriver.java:28)


## Python
(2059, "Authentication plugin 'caching_sha2_password' 
    cannot be loaded: The specified module could not be found.")

SHA-2 인증을 위한 캐시 작업

서버 측에서 caching_sha2_password 플러그인은 클라이언트의 더 빠른 인증과 연결을 위해 메모리에 account-name/password-hash pairs 항목을 캐시에 저장하여 사용합니다.

캐시는 다음과 같이 작동합니다.

• 클라이언트가 연결되면 caching_sha2_password 플러그인은 암호가 일부 캐시 항목과 일치하는지 확인합니다. 그렇다면 인증에 성공합니다.
• 일치하는 캐시 항목이 없으면 플러그인은 mysql.user 시스템 테이블의 자격 증명으로 클라이언트를 확인하려고 시도합니다. 이것이 성공하면 caching_sha2_password 플러그인은 클라이언트에 대한 항목을 해시에 추가합니다.
  mysql.user 시스템 테이블에서 확인이 되지 않으면 인증에 실패하고 연결이 거부됩니다.

클라이언트가 처음 연결할 때 mysql.user 시스템 테이블에 대한 인증이 발생합니다. 클라이언트가 이후 연결할 때 캐시 사용으로 인해 더 빠른 인증이 발생합니다.

처음 접속 시 캐시 항목 추가 이외의 암호 캐시에 대한 작업은 sha2_cache_cleaner 감사 플러그인에 의해 처리되며, 이 플러그인은 caching_sha2_password 대신하여 다음 작업을 수행합니다.

• 계정 이름이 바뀌거나 삭제된 계정 또는 자격 증명 또는 인증 플러그인이 변경된 계정에 대한 캐시 항목을 지웁니다.
• FLUSH PRIVILEGES 구문이 실행될 때 캐시를 비웁니다.
• 서버 종료 시 캐시를 비웁니다. (즉, 캐시는 서버를 다시 시작해도 지속되지 않습니다.)

캐시 지우기 작업은 후속 클라이언트 연결에 대한 인증 요구 사항에 영향을 미칩니다. 다음 작업 중 하나를 수행한 후 첫 번째 클라이언트 연결은 보안 연결(TLS 자격 증명, Unix 소켓 파일 또는 공유 메모리를 사용하여 TCP를 사용하여 생성됨) 또는 RSA 키 페어 기반 암호 교환을 사용해야 합니다.

• After account creation.
• After a password change for the account.
• After RENAME USER for the account.
• After FLUSH PRIVILEGES.

FLUSH PRIVILEGES는 전체 캐시를 지우고 caching_sha2_password 플러그인을 사용하는 모든 계정에 영향을 줍니다. 다른 작업은 특정 캐시 항목을 지우고 작업을 수행한 계정에만 영향을 줍니다.

사용자가 성공적으로 인증되면 계정이 캐시에 입력되고 계정에 영향을 주는 다른 캐시 지우기 이벤트가 발생할 때까지 후속 연결에는 보안 연결(SSL/TLS) 또는 RSA 키 쌍이 필요하지 않습니다.

캐시를 사용할 수 있는 경우 서버는 일반 텍스트 암호 전송을 사용하지 않고 보안 연결이 필요하지 않은 challenge-response 메커니즘을 사용합니다.

위에서 접속 테스트를 하기전에 관리자 계정으로 FLUSH PRIVILEGES 을 수행한 부분은 이와 같은 이유였습니다.

위에서 파이썬 코드에서 ssl_disabled= True 으로 속성을 사용할 경우 접속이 불가능한 것을 확인하였습니다. 접속이 가능한 mysql client 나 java 등으로 먼저 접속 후에 다시 파이썬 코드로 접속해보도록 하겠습니다.

### 먼저 파이썬 코드로 접속 시도 -> 에러 발생
$ python3 py_conn_mysql.py

Traceback (most recent call last):
  File ".../site-packages/mysql/connector/connection_cext.py", line 291, in _open_connection
    self._cmysql.connect(**cnx_kwargs)
_mysql_connector.MySQLInterfaceError: 
    Authentication plugin 'caching_sha2_password' 
    reported error: Authentication requires secure connection.

< ...중략...>
mysql.connector.errors.InterfaceError: 2061 (HY000): 
    Authentication plugin 'caching_sha2_password' 
    reported error: Authentication requires secure connection.



### 정상적으로 접속을 수행
$ java TestConnection_mysqldriver
user :sha2user@%, version: 8.0.34, SSL/TLS Version:



#### 다시 파이썬 코드로 접속 -> 정상 접속 가능
$ python3 py_conn_mysql.py
user: sha2user@% ,version: 8.0.34 ,SSL Version:

테스트 내용과 같이 SSL/TLS 방식 또는 RSA 방식을 통해 접속해서 사용자 정보가 캐시에 로드 된 다음에는 SSL/TLS 또는 RSA 키 페어를 사용하지 않아도(사용할 수 없어도) 접속할 수 있었습니다.

다만 캐시 되어있는 정보는 flush privileges 나 유저의 암호를 변경(change password)시에 삭제되기 때문에 언제라도 SSL/TLS 또는 RSA 키 페어를 사용할 수 있는 조건이 되어야 할 것입니다.

접속 가능한 클라이언트 또는 드라이버로 접속 후에 SSL/TLS 또는 RSA 를 사용하지 않고 접속하는 형태는 지속가능한 모습은 아닐 것입니다.

그렇기 때문에 MySQL을 접속시에 가급적 SSL/TLS 사용을 기본 구성 또는 설정으로 하는 것이 가장 좋은 방법이라고 생각 합니다.

이번 글은 여기서 마무리하도록 하겠습니다.

긴 글 읽어 주셔서 감사합니다.
             

Reference

Reference URL
•  mysql.com/relnotes/news-8-0-34.html
• mysql.com/native-pluggable-authentication
• mysql.com/caching-sha2-pluggable-authentication
• mysql.com/sha256-pluggable-authentication
• mysql.com/connection-options
• mysql.com/connector-python-connectargs


연관된 다른 글

MySQL SSL 설정과 접속 - SSL Replication - SSL 환경의 복제 구성

안녕하세요 이번 포스팅에서는 MySQL 에서의 접속 과 복제 시 SSL 기능을 사용하는 내용에 대해서 확인해보도록 하겠습니다.

MySQL 8.0 마이너 버전별 변경된 SSL/TLS 지원 내역

안녕하세요 이번 포스팅에서는 SSL 통신에서 사용되는 프로토콜 관련하여 MySQL 8.0 마이너 버전 별로 지원 현황 및 최근에 변경된 내용에 대해서 확인 해보려고합니다.

MySQL - 계정 생성 - 권한 - ROLE - MySQL 8.0 버전

안녕하세요 이번 포스팅에서는 MySQL 기본적인 계정 생성 과 8.0 에서 추가된 계정 생성시 추가된 옵션, 권한 과 추가된 ROLE 에 대해서 확인해 보도록 하겠습니다. 

MySQL 8.0 변경된 routine 조회 권한 - mysql.proc - SHOW_ROUTINE - information_schema

안녕하세요 이번에는 포스팅에서는 생성된 Routine(Procedure, Function) 의 definer 의 일괄 변경 스크립트 작성 과정에서, 8.0 에서 mysql.proc 이 없어진 것과 관련하여 알게된 변경된 권한과 information_schema 에 대해서 확인 해본 내용을 기술해보려고 합니다.

MySQL - 파라미터 SET PERSIST 과 RESET PERSIST

안녕하세요. 이번 포스팅에서는 MySQL 8 버전에서 사용할 수 있는 파라미터 관리에 대한 SET/RESET PERSIST 에 관한 내용에 대해서 확인해보려고 합니다.

Jade(정현호)

Principal DBA(MySQL, AWS Aurora, Oracle)
핀테크 서비스인 핀다에서 데이터베이스를 운영하고 있어요(at finda.co.kr)
Previous - 당근마켓, 위메프, Oracle Korea ACS / Fedora Kor UserGroup 운영중

Database 외에도 NoSQL , Linux , Python, Cloud, Http/PHP CGI 등에도 관심이 있습니다
purityboy83@gmail.com / admin@hoing.io

hoing.io