MariaDB 클라언트-서버 TLS/SSL 클라이언트 사용시 HeidiSQL 12 버전에서 SSL 접속에 대해서 수정을 반영

글 타래:

1. MariaDB 클라언트-서버 TLS/SSL 암호화 연결(1)
2. MariaDB 클라언트-서버 TLS/SSL 암호화 연결(2)-2508업데이트
3. MariaDB 클라언트-서버 TLS/SSL 암호화 연결(3)

서버측에서 MariaDB/MySQL 의 TLS를 활성화한 다음 실제 다양한 클라이언트에서 접속을 시도해 보자. 서버에서 생성한 CA 파일 ca.pem, 클라이언트 인증서 client-ssl.pem, 클라이언트 키 client-key.pem 을 다운로드해서 사용한다.

글 진행 순서

1. 클라이언트 인증서 다운로드
2. DB API: python 에서 SSL 접속
3. HeidiSQL 에서 SSL 접속 (2508업데이트)
4. MySQL Workbench 에서 SSL 접속

1. 클라이언트 인증서 다운로드

앞서 MariaDB 클라언트-서버 TLS/SSL 암호화 연결(1) 만든 ca.pem, client-cert.pem, client-key.pem 을 외부접속할 클라이언트 PC로 다운받는다.

1
2
3
4
5
6

> mkdir .ssl/mysql/
> cd .ssl/mysql
# 다운로드
> scp USERID@HOST_IP:/etc/ssl/mysql/ca.pem .
> scp USERID@HOST_IP:/etc/ssl/mysql/client-cert.pem .
> scp USERID@HOST_IP:/etc/ssl/mysql/client-key.pem .

SSH 에 다른 포트 번호를 사용하면 scp -P PORT 로 사용한다.

다운로드한 파일은 3종류로

1
2
3
4
5
6

> ls .ssl/mysql
Mode                 LastWriteTime         Length Name
----                 -------------         ------ ----
-a---        2023-07-16  오후 4:38           1984 ca.pem
-a---        2023-07-16  오후 4:38           1497 client-cert.pem
-a---        2023-07-16  오후 4:38           1679 client-key.pem

서버와 비슷하게 mysql client 설정에 인증키들을 설정해주면 그냥 접속해도 ssl로 접속된다. 아래 참고2 에서 mysql client 의 클라이언트 측 my.cnf/my.ini 파일에 ssl-ca, ssl-cert, ssl-key 를 설정하면 된다.

2. DB API: python 에서 SSL 접속

pymysql, sqlalchemy 등 mysql client API 에 ssl_ca, ssl_key, ssl_cert 인자에 클라이언트용 인증 파일을 연결한다.

pymysql

pymysql.connect 에 ssl_ca, ssl_key, ssl_cert 인자 클라이언트용 인증 파일의 경로가 SSL_CA, SSL_CERT, SSL_KEY 에 있다고 가정한다.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

conn = pymysql.connect(host='192.168.0.10', 
                       user=DB_USER, 
                       password=DB_PW,
                       db='bookstore', 
                       ssl_ca=SSL_CA,
                       ssl_key=SSL_KEY,
                       ssl_cert=SSL_CERT,
                       charset='utf8')
 
# Connection 으로부터 Cursor 생성
curs = conn.cursor()
sql = "select * from book"
curs.execute(sql)
rows = curs.fetchall()
print(rows)     # 전체 rows
conn.close()

SQLAlchemy : engine

sqlalchemy의 create_engin에 connect_args 인자에 클라이언트 인증 파일의 경로가 SSL_CA, SSL_CERT, SSL_KEY 에 있다고 가정한다.

1
2
3
4
5
6
7
8
9

ssl_args = {'ssl_ca': SSL_CA,
           'ssl_cert': SSL_CERT,
           'ssl_key': SSL_KEY}
engine = sqla.create_engine(
    f'mysql+pymysql://{DB_USER}:{DB_PW}@192.168.0.24/bookstore',
    connect_args=ssl_args)

query = """SELECT * FROM book;"""
df = pd.read_sql(sqla.text(query), con=engine)

C 라이브러리 사용 SSL 접속

참고2 를 보면 C API를 사용한 `libmariadb.dll`` 라이브러리에서 SSL 사용하는 C++ 콘솔 예제 가 있다.

3. HeidiSQL 에서 SSL 접속 (2508업데이트)

HeidiSQL 에서 SSL 을 이용해 접속할 수 있다. 아래 그림 같이 DB 정보를 입력한다.

그리고 SSL 탭에서 SSL에 서버측의 ssl 파일을 위치하고 CA 검증을 하지 않게 하면 사용이 가능하다.

4. MySQL Workbench 에서 SSL 접속

MySQL Workbench 에서도 SSL 연결로 데이터베이스에 접속할 수 있다. DB 접속 계정 정보를 입력한다.

SSL 탭에서 SSL 관련 옵션을 선택하는데 여기서는 if available 로 지정했다. 서버측이 SSL활성화가 되어 있으면 자동으로 SSL 통신을 진행한다.

Test 로 확인해 보면 SSL 접속이 잘 되고 있는 것을 알 수 있다.

5. 사용자 SSL 권한 부여

새 사용자를 생성할 때 아래 같이 접속시 REQUIRE 인자로 SSL 로만 접속하도록 할 수 있다.

사용자의 추가/권한 부여에 대해서 MySQL CLI/Admin: 권한부여 글을 참고.

1
2
3
4
5
6
7
8
9
10

# SSL/TLS(가장 기본적인 암호화 접속)
mysql> CREATE USER 'admin'@'localhost' IDENTIFIED BY '********' REQUIRE SSL;

# X509
mysql> CREATE USER 'admin'@'localhost' IDENTIFIED BY '********' REQUIRE X509;

# CIPHER 'cipher'
mysql> CREATE USER 'admin'@'localhost' IDENTIFIED BY '********' REQUIRE CIPHER 'EDH-RSA-DES-CBC3-SHA';

mysql> flush privileges;

기존 사용자는 사용자 권한에서 SSL을 권한을 추가한다.

1
2

mysql> GRANT ALL PRIVILEGES ON DB이름.* TO 'USERID'@'%' REQUIRE SSL;
mysql> flush privileges;

주어진 grant에 SSL/X509 등이 주어졌는지 확인한다.

1

mysql> SHOW GRANTS FOR 'USERID'@'HOST';

내부 네트워크 외부 네트워크로 분리해 사용한다면 외부에서는 무조건 SSL 을 사용하도록 할 수 있을 것 같다.

—

1. 참고1 : OpenSSL을 이용한 사설 인증서 생성
2. 참고2 : MariaDB 외부접속시 ssl 사용법, 그리고 ssl 로 replication(동기화) 하기
3. 참고3 : Configuring MySQL to Use Encrypted Connections

WSL을 사용해 외부 디스크를 Linux 에서 마운트 할 수 있다.

• https://learn.microsoft.com/ko-kr/windows/wsl/wsl2-mount-disk

1. 파워셀에서 사용 가능한 디스크를 검색해 DeviceID 를 찾는다.

1

GET-CimInstance -query "SELECT * from Win32_DiskDrive"

다음 같은 결과에서 \\.\PHYSICALDRIVE1 형식의 외부 디스크의 DeviceID 를 확인한다.

2. 디스크를 WSL 에 탑재한다.

PowerShell을 사용하여 위에서 찾은 디스크 경로를 사용하여 디스크를 탑재하고 다음을 실행할 수 있다. DeviceID 가 \\.\PHYSICALDRIVE1 이라고 가정한다.

3. 디스크가 식별되면 다음을 실행합니다.

1

wsl --mount \\.\PHYSICALDRIVE1 --bare

--bare 를 선택하면 해당 배포본의 파일시스템을 자동 선택한다.

다음 같이 파티션 번호과 형식을 지정할 수 있다

1

wsl --mount <DiskPath> --partition <PartitionNumber> --type <Filesystem>

4. WSL 배포본에서 mount

lsblk 로 파티션 확인

1
2
3
4
5
6
7
8
9

$ lsblk
NAME   MAJ:MIN RM   SIZE RO TYPE MOUNTPOINTS
sda      8:0    0 388.4M  1 disk
sdb      8:16   0   186M  1 disk
sdc      8:32   0     4G  0 disk [SWAP]
sdd      8:48   0     1T  0 disk /mnt/wslg/distro
                                 /
sde      8:64   0 476.9G  0 disk
└─sde1   8:65   0 476.9G  0 part

적절한 위치로 마운트 한다.

1

$ mount /dev/sde1 /mnt/disk

WSL 내부에서 fstab 을 수정해 입력한다. sudo blkid 명령으로 연결된 디스크의 파티션 UUID를 확인합니다.

1

UUID=<Your-Partition-UUID> /mnt/disk ext4 defaults 0 2

5. 디스크 분리

분리

1

wsl --unmount <DiskPath>