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

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

하나의 커다란 웹 플랫폼을 혼자 개발하고 있는 중입니다. 백수 과로사(?)하게 생겼습니다.
어느 한 파트가 끝나서 이제 엑셀하고 연동하여 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을 하다가 다시 엑셀로 돌아오니 머리가 백짓장이 되어 버렸습니다. 갑자기 하나도 생각이 안납니다. 그래서 예전에 코딩 해 두었던 코드들 하나씩 들추어 내서 기억력을 회복하는 중 ㅠㅠ

전체 4

  • 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을 확실하게 걸어 주는 프로그램들이 있습니다. 차후 소개하겠습니다.

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


      • 2021-09-17 23:12

        @dra**** 님 그렇군요.. 참 한번에 끝내기가 어렵네요 ㅎㅎ;

        정말 쉬운게 없는 것 같습니다.


  • 2021-11-10 11:32

    좋은 정보 감사합니다.


전체 48
번호 제목 작성자 작성일 추천 조회
23999
New [엑셀 VBA] Snippet - Public IP, Local IP, Mac Address 구하기 (4)
dra**** | 2021.11.25 | 추천 1 | 조회 33
dra**** 2021.11.25 1 33
23794
[엑셀 VBA] Snippet - 변수를 clipboard로 복사하기 (5)
dra**** | 2021.11.21 | 추천 2 | 조회 54
dra**** 2021.11.21 2 54
23337
HTML 라이브러리 사용하기 귀찮을 때, 사용하는 파싱함수 (getElementsByTag, getAttribute) (6)
트로피 오빠두엑셀 | 2021.11.09 | 추천 0 | 조회 70
트로피 오빠두엑셀 2021.11.09 0 70
23142
[잡설] 엑셀을 이용한 웹 자동화: SEO 이야기 (10)
dra**** | 2021.11.04 | 추천 0 | 조회 154
dra**** 2021.11.04 0 154
22802
[잡설] 웹 서버에서 내보내는 엑셀 파일 및 데이터 이야기 (6)
dra**** | 2021.10.25 | 추천 0 | 조회 111
dra**** 2021.10.25 0 111
22325
[엑셀 VBA] 파워쿼리 자동화 4. CRUD (3)
dra**** | 2021.10.12 | 추천 1 | 조회 150
dra**** 2021.10.12 1 150
22161
[엑셀 VBA] 파워쿼리 자동화 3. MySQL Class (8)
dra**** | 2021.10.07 | 추천 0 | 조회 170
dra**** 2021.10.07 0 170
21839
[엑셀 VBA] 파워쿼리 자동화 2. 자동화 실습 (8)
dra**** | 2021.09.28 | 추천 0 | 조회 225
dra**** 2021.09.28 0 225
21764
[엑셀 VBA] 개체 Reference - QueryTable Object 첨부파일 (2)
dra**** | 2021.09.26 | 추천 0 | 조회 96
dra**** 2021.09.26 0 96
21675
[엑셀 VBA] 파워쿼리 자동화 1. 자동화 순서 (8)
dra**** | 2021.09.23 | 추천 0 | 조회 393
dra**** 2021.09.23 0 393
21496
[엑셀 VBA] 개체 Reference - ADODB.Recordset : Database 필수 첨부파일 (4)
dra**** | 2021.09.16 | 추천 1 | 조회 190
dra**** 2021.09.16 1 190
21491
[잡설] IT가 흘러가고 있는 방향에 대한 단상 - 무엇을 공부해야 하는가? (21)
dra**** | 2021.09.16 | 추천 4 | 조회 218
dra**** 2021.09.16 4 218
21482
[엑셀 VBA] MySQL 02. Range -> 배열 -> INSERT (5)
dra**** | 2021.09.16 | 추천 0 | 조회 202
dra**** 2021.09.16 0 202
21412
[엑셀 VBA] MySQL 01. 워크시트 Custom Properties 활용하기 (4)
dra**** | 2021.09.14 | 추천 1 | 조회 113
dra**** 2021.09.14 1 113
20740
[엑셀 VBA] 개체 Reference - ListObject Object - [표]가지고 놀기 첨부파일 (12)
dra**** | 2021.08.29 | 추천 4 | 조회 218
dra**** 2021.08.29 4 218
20211
[엑셀 VBA] Windows API - winuser.h : 윈도우즈 프로그램의 핵심 첨부파일 (8)
dra**** | 2021.08.17 | 추천 3 | 조회 202
dra**** 2021.08.17 3 202
20194
[잡설] 엑셀이 웹으로 가야 하는 이유 (23)
dra**** | 2021.08.16 | 추천 12 | 조회 476
dra**** 2021.08.16 12 476
20153
[엑셀 VBA] 개체 Reference - FileSystem Object 첨부파일 (3)
dra**** | 2021.08.15 | 추천 1 | 조회 141
dra**** 2021.08.15 1 141
19717
[엑셀 VBA] 데이터 자동화 - 웹에서 제공하는 MySQL Sever를 이용한 자동화 개념 (15)
dra**** | 2021.08.05 | 추천 2 | 조회 343
dra**** 2021.08.05 2 343
19715
[엑셀 VBA] 개체 Reference - APPLICATION Object 첨부파일 (2)
dra**** | 2021.08.05 | 추천 0 | 조회 118
dra**** 2021.08.05 0 118