MariaDB 인증방식 변경(sha256 가능여부? 그리고 ed25519)

MariaDB 서버는 sha256 인증 플러그인을 보안상의 이유로 채택하지 않음, 즉 지원 안함
관련 링크 : https://mariadb.com/kb/en/authentication-plugin-sha-256/?utm_source=chatgpt.com

그대신 SHA256보다 보안성이 더 높은 ed25519플러그인을 지원함
관련 링크 : https://mariadb.com/kb/en/authentication-plugin-ed25519/?utm_source=chatgpt.com

기본적으로 auth_ed25519.dll이 설치 패키지에 잡혀있으나 혹시나 없을 경우 설치하는 방법

공식 홈페이지에서 설치한 버전에 맞는 패키지를 다운로드 한다. (MSI파일이 아닌 ZIP파일 !!!!) https://mariadb.org/download/?t=mariadb&o=true&p=mariadb&r=10.6.4&os=windows&cpu=x86_64&pkg=msi&mirror=archive lib\plugin 디렉토리에 auth_ed25519.dll파일을 C:\Program Files\MariaDB (버전)\lib\plugin 에 옮기기 cmd>mysql -u root -p  (엔터) > (비밀번호 치고 엔터) MariaDB [(none)]> INSTALL SONAME 'auth_ed25519'; ERROR 2006 (HY000): Server has gone away No connection. Trying to reconnect... Connection id:    4 Current database: *** NONE *** Query OK, 0 rows affected (0.007 sec)

SHOW PLUGINS; 입력 후 맨 아래 ed25519 확인되면 설치 성공

MariaDB [(none)]> SHOW PLUGINS; +-------------------------------+----------+--------------------+------------------+---------+ | Name                          | Status   | Type               | Library          | License | +-------------------------------+----------+--------------------+------------------+---------+ | ed25519                       | ACTIVE   | AUTHENTICATION     | auth_ed25519.dll | GPL     | +-------------------------------+----------+--------------------+------------------+---------+

기존 계정 암호화 방식 변경하기 전에 기존 SHA1 (mysql_native_password)으로 되어있는지 확인

MariaDB [(none)]> select user,host,plugin,authentication_string from mysql.user where user = 'root'; +------+--------------+-----------------------+-------------------------------------------+ | User | Host         | plugin                | authentication_string                     | +------+--------------+-----------------------+-------------------------------------------+ | root | localhost    | mysql_native_password | *8466C287E7375B630D3BDED5CBD263132AA6F5E4 | | root | %            | mysql_native_password | *8466C287E7375B630D3BDED5CBD263132AA6F5E4 | +------+--------------+-----------------------+-------------------------------------------+ +-------------------------------+----------+--------------------+------------------+---------+

SHA1 방식을 ed25519로 변경

MariaDB [(none)]> ALTER USER 'root'@'%' IDENTIFIED VIA ed25519 USING PASSWORD('selvas1234!'); Query OK, 0 rows affected (0.003 sec) MariaDB [(none)]> select user,host,plugin,authentication_string from mysql.user where user = 'root'; +------+--------------+-----------------------+---------------------------------------------+ | User | Host         | plugin                | authentication_string                       | +------+--------------+-----------------------+---------------------------------------------+ | root | localhost    | mysql_native_password | *8466C287E7375B630D3BDED5CBD263132AA6F5E4   | | root | %            | ed25519               | eUP9Wfn81FY1cTLkX9IwNG/cpk2rYtGzg8XEeBo+JFg | +------+--------------+-----------------------+---------------------------------------------+

plugin 값에 ed25519 있으면 성공

MariaDB [mysql]> select * from global_priv; +----------------+------------------+----------------+ | Host           | User             | Priv                | +----------------+------------------+----------------+ | localhost      | mariadb.sys      | {"access":0,"plugin":"mysql_native_password","authentication_string":"","account_locked":true,"password_last_changed":0}                                                      | | %              | root             | {"access":549755813887,"version_id":110100,"plugin":"ed25519","authentication_string":"eUP9Wfn81FY1cTLkX9IwNG/cpk2rYtGzg8XEeBo+JFg","password_last_changed":1748843264}       | +----------------+------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+