DataBase
[MariaDB] 보안점검 - 비밀번호 취약점(SHA1) 개선
빤따스뤽
2024. 9. 26. 16:07
사전 배경
MariaDB 10.4.x 버전의 사용자 비밀번호 해쉬 방식은 기본 sha1로 보안점검에 취약 사항으로 도출 됨
sha2(256) 업그레이드 권고를 받았지만 기본적으로 지원하지 않아서 별도의 플러그인을 설치 해야 함
몇가지 방법이 있지만 ed25519(ECDSA) 알고리즘을 적용하기로 함
(추가 : MariaDB 10.4.? 이후 버전 부터는 좀더 안전한 알고리즘을 지원하는 것으로 확인 했으니 변경하기 전에 자신의 DB버전에서 다른 플러그인을 설치 하지 않고 처리가 가능한지 먼저 확인 할 것을 권장)
ed25519(ECDSA) 알고리즘에 대한 설명
https://ko.wikipedia.org/wiki/%ED%83%80%EC%9B%90%EA%B3%A1%EC%84%A0_DSA
MariaDB ed25519플러그인에 대한 설명
https://runebook.dev/ko/docs/mariadb/authentication-plugin-ed25519/index
주의 사항
해당 플러그인을 설치 하면 jdbc 연결시 MySQL 드라이버를 사용 던 아래 방식을 할 수 없고 MariaDB 전용 드라이버를 써야 함
기존 MySQL 드라이버를 사용 할 수 없음
Class.forName("com.mysql.jdbc.Driver");
... 생략 ...
String url = "jdbc:mysql://localhost:3306/mydatabase?...";
Connection con = DriverManager.getConnection(url, "user", "password");
... 생략 ...수정 (Maven 기준)
<dependency>
<groupId>org.mariadb.jdbc</groupId>
<artifactId>mariadb-java-client</artifactId>
<version>${적합한 버전}</version>
</dependency>Class.forName("org.mariadb.jdbc.Dirver");
... 생략 ...
String url = "jdbc:mariadb://localhost:3306/mydatabase?...";
Connection con = DriverManager.getConnection(url, "user", "password");
... 생략 ...
1. 설치
1-1. 플러그인 디렉토리 확인 및 ed25519 플러그인 설치 여부 확인
MariaDB [mysql]> show global variables like 'plugin_dir%';
+---------------+--------------------------+
| Variable_name | Value |
+---------------+--------------------------+
| plugin_dir | /usr/lib64/mysql/plugin/ |
+---------------+--------------------------+
1 row in set (0.002 sec)
MariaDB [mysql]> show plugins;
+-------------------------------+----------+---------------------+--------------------------+---------+
| Name | Status | Type | Library | License |
+-------------------------------+----------+---------------------+--------------------------+---------+
| binlog | ACTIVE | STORAGE ENGINE | NULL | GPL |
| mysql_native_password | ACTIVE | AUTHENTICATION | NULL | GPL |
| mysql_old_password | ACTIVE | AUTHENTICATION | NULL | GPL |
| wsrep | ACTIVE | REPLICATION | NULL | GPL |
| CSV | ACTIVE | STORAGE ENGINE | NULL | GPL |
| MEMORY | ACTIVE | STORAGE ENGINE | NULL | GPL |
| Aria | ACTIVE | STORAGE ENGINE | NULL | GPL |
| MyISAM | ACTIVE | STORAGE ENGINE | NULL | GPL |
| MRG_MyISAM | ACTIVE | STORAGE ENGINE | NULL | GPL |
| CLIENT_STATISTICS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INDEX_STATISTICS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| TABLE_STATISTICS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| USER_STATISTICS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| SQL_SEQUENCE | ACTIVE | STORAGE ENGINE | NULL | GPL |
| InnoDB | ACTIVE | STORAGE ENGINE | NULL | GPL |
| INNODB_TRX | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_LOCKS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_LOCK_WAITS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_CMP | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_CMP_RESET | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_CMPMEM | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_CMPMEM_RESET | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_CMP_PER_INDEX | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_CMP_PER_INDEX_RESET | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_BUFFER_PAGE | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_BUFFER_PAGE_LRU | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_BUFFER_POOL_STATS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_METRICS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_FT_DEFAULT_STOPWORD | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_FT_DELETED | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_FT_BEING_DELETED | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_FT_CONFIG | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_FT_INDEX_CACHE | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_FT_INDEX_TABLE | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_SYS_TABLES | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_SYS_TABLESTATS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_SYS_INDEXES | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_SYS_COLUMNS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_SYS_FIELDS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_SYS_FOREIGN | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_SYS_FOREIGN_COLS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_SYS_TABLESPACES | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_SYS_DATAFILES | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_SYS_VIRTUAL | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_MUTEXES | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_SYS_SEMAPHORE_WAITS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_TABLESPACES_ENCRYPTION | ACTIVE | INFORMATION SCHEMA | NULL | BSD |
| INNODB_TABLESPACES_SCRUBBING | ACTIVE | INFORMATION SCHEMA | NULL | BSD |
| PERFORMANCE_SCHEMA | ACTIVE | STORAGE ENGINE | NULL | GPL |
| SEQUENCE | ACTIVE | STORAGE ENGINE | NULL | GPL |
| unix_socket | ACTIVE | AUTHENTICATION | NULL | GPL |
| FEEDBACK | DISABLED | INFORMATION SCHEMA | NULL | GPL |
| user_variables | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| partition | ACTIVE | STORAGE ENGINE | NULL | GPL |
| SERVER_AUDIT | ACTIVE | AUDIT | server_audit.so | GPL |
| simple_password_check | ACTIVE | PASSWORD VALIDATION | simple_password_check.so | GPL |
+-------------------------------+----------+---------------------+--------------------------+---------+
56 rows in set (0.002 sec)
MariaDB [mysql]>[root@EbMP_STG ~]# ls -al /usr/lib64/mysql/plugin/
합계 8920
drwxr-xr-x. 3 root root 4096 4월 2 2021 .
drwxr-xr-x. 3 root root 20 4월 2 2021 ..
-rwxr-xr-x 1 root root 72048 2월 19 2021 auth_ed25519.so
-rwxr-xr-x 1 root root 11136 2월 19 2021 auth_gssapi_client.so
-rwxr-xr-x 1 root root 11672 2월 19 2021 auth_pam.so
drwx------ 2 mysql root 27 4월 2 2021 auth_pam_tool_dir
-rwxr-xr-x 1 root root 11672 2월 19 2021 auth_pam_v1.so
-rwxr-xr-x 1 root root 11152 2월 19 2021 caching_sha2_password.so
-rwxr-xr-x 1 root root 75688 2월 19 2021 client_ed25519.so <-- 기본 설치 되어 있음
-rwxr-xr-x 1 root root 11120 2월 19 2021 dialog.so
-rwxr-xr-x 1 root root 11776 2월 19 2021 disks.so
-rwxr-xr-x 1 root root 20208 2월 19 2021 file_key_management.so
-rwxr-xr-x 1 root root 107344 2월 19 2021 ha_archive.so
-rwxr-xr-x 1 root root 74504 2월 19 2021 ha_blackhole.so
-rwxr-xr-x 1 root root 103352 2월 19 2021 ha_federated.so
-rwxr-xr-x 1 root root 144376 2월 19 2021 ha_federatedx.so
-rwxr-xr-x 1 root root 6585928 2월 19 2021 ha_mroonga.so
-rwxr-xr-x 1 root root 197648 2월 19 2021 ha_sphinx.so
-rwxr-xr-x 1 root root 1190840 2월 19 2021 ha_spider.so
-rwxr-xr-x 1 root root 243816 2월 19 2021 handlersocket.so
-rwxr-xr-x 1 root root 11888 2월 19 2021 locales.so
-rwxr-xr-x 1 root root 11792 2월 19 2021 metadata_lock_info.so
-rwxr-xr-x 1 root root 6992 2월 19 2021 mysql_clear_password.so
-rwxr-xr-x 1 root root 12536 2월 19 2021 query_cache_info.so
-rwxr-xr-x 1 root root 16240 2월 19 2021 query_response_time.so
-rwxr-xr-x 1 root root 61544 2월 19 2021 server_audit.so
-rwxr-xr-x 1 root root 11096 2월 19 2021 sha256_password.so
-rwxr-xr-x 1 root root 11800 2월 19 2021 simple_password_check.so
-rwxr-xr-x 1 root root 11936 2월 19 2021 sql_errlog.so
-rwxr-xr-x 1 root root 46184 2월 19 2021 wsrep_info.so
[root@EbMP_STG ~]#플러그인 디렉토리에 해당 플러그인이 존재 하지만 활성화 되어 있지는 않음
2. 플러그인 설치
MariaDB [mysql]> install soname 'auth_ed25519';
Query OK, 0 rows affected (0.431 sec)
MariaDB [mysql]> show plugins;
+-------------------------------+----------+---------------------+--------------------------+---------+
| Name | Status | Type | Library | License |
+-------------------------------+----------+---------------------+--------------------------+---------+
| binlog | ACTIVE | STORAGE ENGINE | NULL | GPL |
| mysql_native_password | ACTIVE | AUTHENTICATION | NULL | GPL |
| mysql_old_password | ACTIVE | AUTHENTICATION | NULL | GPL |
| wsrep | ACTIVE | REPLICATION | NULL | GPL |
| CSV | ACTIVE | STORAGE ENGINE | NULL | GPL |
| MEMORY | ACTIVE | STORAGE ENGINE | NULL | GPL |
| Aria | ACTIVE | STORAGE ENGINE | NULL | GPL |
| MyISAM | ACTIVE | STORAGE ENGINE | NULL | GPL |
| MRG_MyISAM | ACTIVE | STORAGE ENGINE | NULL | GPL |
| CLIENT_STATISTICS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INDEX_STATISTICS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| TABLE_STATISTICS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| USER_STATISTICS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| SQL_SEQUENCE | ACTIVE | STORAGE ENGINE | NULL | GPL |
| InnoDB | ACTIVE | STORAGE ENGINE | NULL | GPL |
| INNODB_TRX | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_LOCKS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_LOCK_WAITS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_CMP | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_CMP_RESET | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_CMPMEM | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_CMPMEM_RESET | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_CMP_PER_INDEX | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_CMP_PER_INDEX_RESET | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_BUFFER_PAGE | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_BUFFER_PAGE_LRU | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_BUFFER_POOL_STATS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_METRICS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_FT_DEFAULT_STOPWORD | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_FT_DELETED | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_FT_BEING_DELETED | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_FT_CONFIG | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_FT_INDEX_CACHE | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_FT_INDEX_TABLE | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_SYS_TABLES | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_SYS_TABLESTATS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_SYS_INDEXES | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_SYS_COLUMNS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_SYS_FIELDS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_SYS_FOREIGN | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_SYS_FOREIGN_COLS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_SYS_TABLESPACES | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_SYS_DATAFILES | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_SYS_VIRTUAL | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_MUTEXES | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_SYS_SEMAPHORE_WAITS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_TABLESPACES_ENCRYPTION | ACTIVE | INFORMATION SCHEMA | NULL | BSD |
| INNODB_TABLESPACES_SCRUBBING | ACTIVE | INFORMATION SCHEMA | NULL | BSD |
| PERFORMANCE_SCHEMA | ACTIVE | STORAGE ENGINE | NULL | GPL |
| SEQUENCE | ACTIVE | STORAGE ENGINE | NULL | GPL |
| unix_socket | ACTIVE | AUTHENTICATION | NULL | GPL |
| FEEDBACK | DISABLED | INFORMATION SCHEMA | NULL | GPL |
| user_variables | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| partition | ACTIVE | STORAGE ENGINE | NULL | GPL |
| SERVER_AUDIT | ACTIVE | AUDIT | server_audit.so | GPL |
| simple_password_check | ACTIVE | PASSWORD VALIDATION | simple_password_check.so | GPL |
| ed25519 | ACTIVE | AUTHENTICATION | auth_ed25519.so | GPL |
+-------------------------------+----------+---------------------+--------------------------+---------+
57 rows in set (0.002 sec)
MariaDB [mysql]>콘솔에서 설치한 것 만으로 DB재시작 시에도 적용이 됨. (나의 경우 my.cnf에 플러그인을 로드 하도록 설정 하면 재시작시 오류 발생함)
3. 사용자계정 적용
사용자 계정 생성시 해당 알고리즘을 사용 하도록 수정해야 함
CREATE USER '사용자ID'@'사용IP' IDENTIFIED VIA ed25519 USING PASSWORD ('{비밀번호}') PASSWORD EXPIRE INTERVAL 90 DAY;비밀번호 만료 기한 90일 적용
Good Luck!