[엑셀 VBA] MySQL 01. 워크시트 Custom Properties 활용하기

작성자
dra****
작성일
2021-09-14 19:12
조회
42

하나의 커다란 웹 플랫폼을 혼자 개발하고 있는 중입니다. 백수 과로사(?)하게 생겼습니다.
어느 한 파트가 끝나서 이제 엑셀하고 연동하여 CRUD를 하고 있는 중입니다.

보안 문제인데, 가장 좋은 것은 아이디와 비번을 수시로 입력하는 방법이 최선입니다.
그런데 이것이 여간 귀찮은 문제가 아니어서 여러 고민 끝에 Custom Properties에 해당 정보를 숨겨 두는 것입니다.

MySQL 서버에 연결하려면, MySQL을 설치 해야 겠지요.
설치 끝나고 DB하나 만들고, 그 안에 테이블 만들고 필드 설정하고.
Phmyadmin을 쓰면 편리하게 만들 수 있습니다. 사용자 ID와 비번 만들고.

개념1.
내 컴퓨터 안에 있는 서버들의 서버명은 모두 "localhost"입니다.
그리고 이에 접근할 사용자 아이디와 비밀번호 그리고 DB 명이 필요 합니다.

개념2.
내 컴퓨터가 아닌 다른 사람의 컴퓨터 혹은 웹 서버에 접근 하려고 하면 서버이름(웹주소)을 해당 업체에서 제공 받아야 합니다. 이를 일명 원격 서버(remote server)라 합니다.

개념3.
내 컴퓨터에 있는 database의 Table들은 나중에 웹 서버에 손 쉽게 이식할 수 있습니다.
웹 서버에서 작동하는 웹 페이지들은 모두 같은 서버 안에 있기 때문에 서버명이 "localhost"가 됩니다.

이제 기본 정보를 알았으니 데이터베이스에 연결 해야 겠지요. 저는 서버를 임대해서 쓰고 있기 때문에 "localhost"가 아닌 해당 서버의 주소를 씁니다. 그래야 개인 PC가 아닌 어느 곳에서나 엑셀로 접근이 가능하기 때문입니다. 웹으로 로그인하면 엑셀에서 작업한 내용도 모두 조회 및 수정도 가능하게 해 논 상태입니다. 웹에서 수정한 내용도 엑셀에서 조회 및 수정도 가능합니다.

    Dim ServerAddress    As String:  ServerAddress = "localhost"
    Dim myDataBase       As String:  myDataBase = "데이터베이스 이름"
    Dim myUserName       As String:  myUserName = "사용자 이름"
    Dim myPassword       As String:  myPassword = "사용자 비밀번호"
    Dim port             As String:  port = "3306"
 
	Dim strConn As String
	strConn = "Driver={MySQL ODBC 8.0 Unicode Driver};Server=" & ServerAddress & ";" & _
			  "Port= " & port & ";" & _
			  "Database=" & myDataBase & ";" & _
			  "Uid=" & myUserName & ";" & _
			  "Pwd=" & myPassword & ";"
 
	Set Conn = New ADODB.Connection
    Conn.Open strConn

 

이렇게 하면 MySQL 서버에 연결이 되고 사용할 준비가 다 된 것입니다.

문제는 위에 적나라하게 모든 정보가 노출 되어 있다는 점....

그래서 워크시트의 Customer Properties 안에 내용을 숨기는 것이고, 정보를 최대한 노출 시키지 않는 겁니다. 아래는 각각의 값을 세팅하는 것입니다. 세팅후에 배포할 때는 읽어 들이는 코드만 나두고 위의 정보 코드와 아래의 코드를 삭제하고 배포하는 것입니다. 저는 DB 관련 기본 내용을 보관하는 시트 이름을 "SCHEMA"라 정의 해 놓았습니다.

   With ThisWorkbook.Worksheets("SCHEMA").CustomProperties
      .Add Name:="ServerAddress", Value:=ServerAddress
      .Add Name:="myDataBase", Value:=myDataBase
      .Add Name:="myUserName", Value:=myUserName
      .Add Name:="myPassword", Value:=myPassword
      .Add Name:="port", Value:=port
   End With

 

이제 워크북이 MySQL에 접근 하는 시점에 다시 읽어 들이는 것이지요.

 

   With ThisWorkbook.Worksheets("SCHEMA").CustomProperties
      Dim ServerAddress As String:  ServerAddress = .Item(5).Value
      Dim myDataBase As String:     myDataBase = .Item(1).Value
      Dim myUserName As String:     myUserName = .Item(3).Value
      Dim myPassword As String:     myPassword = .Item(2).Value
      Dim port As String:           port = .Item(4).Value
 
      strConn = "Driver={MySQL ODBC 8.0 Unicode Driver};Server=" & ServerAddress & ";" & _
                "Port= " & port & ";" & _
                "Database=" & myDataBase & ";" & _
                "Uid=" & myUserName & ";" & _
                "Pwd=" & myPassword & ";"
   End With
 
   On Error GoTo Error_OpenMySQL
      Set Conn = New ADODB.Connection
      Conn.Open strConn
	...

 

이 부분들 마져도 클래스 안에 숨겨 놓고 메서드로 호출만 합니다. 이름은 전혀 상관 없는 내용으로 짓고. 그리고 엑셀 실행서 서버에 로그인 하게 하고, 접근 관한 관련된 부분도 이렇게 해 놓았습니다. 눈가리고 아웅 하기지만, 적어도 대부분의 사람들은 Custom Properties를 사용하지 않습니다. 혹시 이 정보가 노출이 되기 쉬울듯 하면, 암호화를 진행 해보려 합니다. 또 한 가지의 방법은 특정 셀에 암호를 붙여 넣기 하고 엑셀 종료시 자동 삭제하도록 하는 방법도 있습니다. DB 서버에 접근하려면 주소와 암호는 필수이기에 어쩔 수 없습니다. 워크북에 암호를 거는 방법도 있고. 할 수 있는 방법은 다 해보는 거지요. 이래도 뚤고 들어 올 수 있다면 국정원이나 CIA 정도는 되야 가능한 이야기 입니다.

참고로 Custom properties를 삭제 할 때는 아래와 같은 방법으로 초기화 할 수 있습니다.

로그인 정보 삭제하는 코드입니다.

Private Sub initCustomProperties()
   If ThisWorkbook.Worksheets("SCHEMA").CustomProperties.Count <= 0 Then Exit Sub
 
   Dim cp As CustomProperty
   For Each cp In ThisWorkbook.Worksheets("SCHEMA").CustomProperties
      If cp.Name = "admin" Then cp.Delete
      If cp.Name = "permit" Then cp.Delete
   Next cp
End Sub

이 코드는 워크북이 처음 실행되면 무조건 실행되게 하여 로그인을 반드시 하도록 만들어 놓았습니다.

다음에는 ListObject(표)의 데이터를 MySQL에 이식하는 방법에 대한 소개입니다.

Range개체는 일종의 배열입니다. 그래서 row를 통채로 읽어서 배열로 전달하면 Recordset에서 알아서 INSERT or UPDATE를 해 줍니다. 따라서 일일히 각각의 값이나 필드명을 세팅할 필요가 없습니다.

P.S. 자바스크립트, PHP, CSS, VUE, GoLang을 하다가 다시 엑셀로 돌아오니 머리가 백짓장이 되어 버렸습니다. 갑자기 하나도 생각이 안납니다. 그래서 예전에 코딩 해 두었던 코드들 하나씩 들추어 내서 기억력을 회복하는 중 ㅠㅠ

회원등급 : 나무1단계
포인트 : 2475 EP
전체 3

  • 2021-09-15 16:51

    CustomProperty 좋은 방법이네요.

    하지만 VBA 자체가 보안이 취약해서 CustomProperty로 숨겨놓더라도 모듈에서 볼 수 있을 듯 한데..

    이건 해결할 수 없는거겠죠?ㅎㅎ;;


    • 2021-09-16 00:40

      Web Query로 로그인하고 여기서 MySQL 세팅 정보를 받아 구현하면 가능한데, JSON을 처리하는 것이 또 일이라. 이미 이 부분은 간단하게 PHP로 구현해 놓은 상태입니다.

      가장 좋은 것은 비번을 수동으로 입력 받도록 하는 것으로 생각 됩니다. 이게 귀찮아서리...

      매번 엑셀을 실행할 때마다 로그인 하도록 하고, 접근 Permit을 Custom Properties에 숨겨 놓으려 합니다.

      일단 로그인에 실패하면 뭐든 안되기에 최소한의 보안은 남겨 두는 셈입니다.

      그리고 VBA Project를 강력하게 잠그는 기능이 있습니다.

      오빠두 님이 하는 것처럼. 그걸로 잠그면 거의 방법 없습니다.

      예전에는 간단하게 Excel 확장자를 zip으로 바꿔 압축 풀어서 내부 파일을 건드려서 하는 방법이 있었는데,

      이 부분들에 대한 Lock을 확실하게 걸어 주는 프로그램들이 있습니다. 차후 소개하겠습니다.

      이제 버튼 하나로 구현하는 파워쿼리 자동화와 자동갱신 등등 많은 것을 보실 수 있을 듯 합니다.


전체 38
번호 제목 작성자 작성일 추천 조회
35
[엑셀 VBA] 개체 Reference - ADODB.Recordset : Database 필수 첨부파일
dra**** | 2021.09.16 | 추천 0 | 조회 38
dra**** 2021.09.16 0 38
34
[잡설] IT가 흘러가고 있는 방향에 대한 단상 - 무엇을 공부해야 하는가? (6)
dra**** | 2021.09.16 | 추천 2 | 조회 32
dra**** 2021.09.16 2 32
33
[엑셀 VBA] MySQL 02. Range -> 배열 -> INSERT (2)
dra**** | 2021.09.16 | 추천 0 | 조회 21
dra**** 2021.09.16 0 21
32
[엑셀 VBA] MySQL 01. 워크시트 Custom Properties 활용하기 (3)
dra**** | 2021.09.14 | 추천 1 | 조회 42
dra**** 2021.09.14 1 42
31
[엑셀 VBA] 개체 Reference - ListObject Object - [표]가지고 놀기 첨부파일 (9)
dra**** | 2021.08.29 | 추천 4 | 조회 106
dra**** 2021.08.29 4 106
30
[엑셀 VBA] Windows API - winuser.h : 윈도우즈 프로그램의 핵심 첨부파일 (7)
dra**** | 2021.08.17 | 추천 3 | 조회 103
dra**** 2021.08.17 3 103
29
[잡설] 엑셀이 웹으로 가야 하는 이유 (13)
dra**** | 2021.08.16 | 추천 6 | 조회 201
dra**** 2021.08.16 6 201
28
[엑셀 VBA] 개체 Reference - FileSystem Object 첨부파일 (3)
dra**** | 2021.08.15 | 추천 1 | 조회 77
dra**** 2021.08.15 1 77
27
[엑셀 VBA] 데이터 자동화 - 웹에서 제공하는 MySQL Sever를 이용한 자동화 개념 (13)
dra**** | 2021.08.05 | 추천 1 | 조회 209
dra**** 2021.08.05 1 209
26
[엑셀 VBA] 개체 Reference - APPLICATION Object 첨부파일 (1)
dra**** | 2021.08.05 | 추천 0 | 조회 80
dra**** 2021.08.05 0 80
25
[엑셀 VBA] 개체 Reference - WORKBOOKS, WORKBOOK Object 첨부파일 (4)
dra**** | 2021.07.25 | 추천 2 | 조회 122
dra**** 2021.07.25 2 122
24
[엑셀 VBA] 개체 Reference - WORKSHEETS, WORKSHEET Object 첨부파일 (5)
dra**** | 2021.07.11 | 추천 1 | 조회 154
dra**** 2021.07.11 1 154
23
[엑셀 VBA] 개체 Reference - RANGE Object 첨부파일 (4)
dra**** | 2021.07.06 | 추천 3 | 조회 148
dra**** 2021.07.06 3 148
22
[엑셀 VBA] 자동화 - 엑셀 데이터베이스의 기본 작동 원리 및 구현 방법 (15)
dra**** | 2021.06.28 | 추천 4 | 조회 425
dra**** 2021.06.28 4 425
21
지배하는 자 Vs. 지배당하는 자 (15)
dra**** | 2021.06.27 | 추천 13 | 조회 297
dra**** 2021.06.27 13 297
20
[엑셀 VBA] 모든(?) 색상 값과 VBA로 적용하는 방법 첨부파일 (5)
dra**** | 2021.06.23 | 추천 3 | 조회 255
dra**** 2021.06.23 3 255
19
[엑셀 VBA] Naming Convention - 변수나 개체 이름 붙이는 관행 (5)
dra**** | 2021.06.14 | 추천 3 | 조회 117
dra**** 2021.06.14 3 117
18
[엑셀 VBA] 자동화 - FORMS를 이용한 직원 근무시간과 급여 계산하기
dra**** | 2021.06.13 | 추천 3 | 조회 321
dra**** 2021.06.13 3 321
17
[엑셀 VBA] 자동화 - 데이터 관리의 기본과 고급필터의 유용성
dra**** | 2021.06.10 | 추천 3 | 조회 184
dra**** 2021.06.10 3 184
16
[APP] 심플한 날짜 계산기 첨부파일 (8)
dra**** | 2021.06.09 | 추천 1 | 조회 212
dra**** 2021.06.09 1 212