매킨토시

4월 1 2015

엑셀(EXCEL) : 공유화일을 IP, 사용자 체크하여 열기, 편집 여부 및 로그인, 아웃 기록 보기(관리자)

여러분의 성원?에 힘입어 사실 아래 팁의 댓글에 요청 사항이 있어 작성을 해 보았습니다.

아래의 팁은 순수 엑셀 화일을 보안 상태로 만드는 팁이었다면 지금부터 게시하는 내용은
그동안 공유문서를 통해 업무를 진행하시던 분들의 고충 내용을 조금 쉽게 해 주는 팁입니다.

앞에 팁은 하드디스크의 시리얼을 체크하여 유출된 보안 엑셀 파일의 실행을 방지하는 것이나
이것은 사무실에서도 혹은 유출된 화일도 내부 매크로를 알지 못하는 이상 실행을 할 수 없게
만드는 방법입니다. 저는 공유 화일을 쓰지 않고 그냥 내부 DB를 이용해서 프로그램을 만들어
자료정리, 프로그램을 배포하고 있었기에 이런 유형이 필요한지를 사실 잘 모르고 있었습니다.

자! 시작합니다.

첨부된 logon_check화일을 실행하면 개인 사용자가 Windows로 logon 사용자명을 보여줍니다.

우선 관리자는 첨부된 엑셀 화일의 VBA코드를 열어 공유하고 있는 사용자명을 등록해 줍니다.
관리자는 전체 화일과 어떤 사용자들이 언제 어디서 로그온했는지의 정보도 볼 수 있게 하고
일반 사용자는 사용자가 작성한 시트만 편집할 수 있고 다른 시트는 보호되어 볼 수만 있습니다.

시트의 이름은 “시트이름-logon명”으로 하셔야 합니다. 시트명 체크 코드가 정형화 되어야만
체크될 수 있을 것 같아 이렇게 정의해서 코드를 만들었습니다. 코드를 이해하시면 더욱 더
유용한 방법을 사용하여 체크하실 수 있을 것 같습니다.

첨부된 logon_check 실행 프로그램이 의심스러우신 분은 아래 내용대로 진행하시면 됩니다.
logon_check는 아래와 같습니다. VBA 편집기에서 참조> Mircosoft Script Runtime을 체크
ThisWorkbook을 더블 클릭해서 아래 코드를 복사, VBA코드에 넣어서 실행하시면 됩니다.
그러면 메시지 박스로 사용자를 보여주므로 이것을 수집해서 Case 문에 추가하시면 됩니다.

<Code>

Option Explicit

Private Declare Function GetUserName Lib “advapi32.dll” Alias “GetUserNameA” _
(ByVal lpBuffer As String, nSize As Long) As Long

Public strUserName, loginip As String

Function FindOutUserName() As String

Dim lpbuff As String * 12
Dim lngret As Long
Dim strUserName As String

On Error GoTo ET

lngret = GetUserName(lpbuff, 12)

strUserName = Left(lpbuff, InStr(lpbuff, Chr(0)) – 1)

FindOutUserName = Trim(strUserName)

Exit Function

ET:

FindOutUserName = “”

End Function

Private Sub Workbook_Open()

Dim username As String

username = FindOutUserName

MsgBox “사용자 이름은 ” & username & ” 입니다”

End Sub

</Code>

엑셀 화일은 VBA초급자라도 알 수 있게 최대한 자세하게 주석을 달아 놓았습니다.
조금 더 응용하셔서 좋은 내용으로 업무나 개인 사용자에 도움이 되었으면 합니다.
버그가 있을 수 있으니 버그가 발생되면 연락 바랍니다. 나름 많이 테스트했는데…

물론 매크로 실행시에 암호를 묻는 대화 상자를 띄어 한번 더 보안을 하면 좋겠지만
매번 등록된 사용자가 더 번거러워 지는 것 같아서 넣지를 않았지만 필요하시면
조금만 수정해서 사용하시면 될 것 같아요. 코드를 조금만이라도 해석할 수 있으면
금방 적용하실 수 있다고 봅니다.

<중요>

앞의 팁 댓글에서의 조언처럼 검토 > 통합문서 보호와 공유에 암호를 걸어야하고
VBA편집기에서 VBA Project속성에서도 보안에 암호를 걸어야만 정상 동작합나다.
사용자의 편의를 위해서 두 부분의 암호를 임의로 해제한 상태로 첨부해 둡니다.

By vinipapa • 무른모 • 0 • Tags: Excel, 매크로, 보안, 엑셀

3월 27 2015

엑셀화일을 지정 컴퓨터에서만 열 수 있게 하는 법

회사에서 Excel의 암호 잠금 기능을 사용 단가, 비자금? 등의 비밀 문서를
관리하시는 분이 계실 듯합니다. 그런데 요새는 엑셀 암호를 깨는 툴들이
많아 엑셀 파일이 유출되면 암호 잠금도 소용없는 시절입니다.

보통 Excel의 비밀번호를 입력하여 사용자가 관리하는데 너무 열심이고 똑똑한 분이
가끔 암호를 자주 바꾸어 암호를 헷갈려 난감한 경우를 맞이하는 경우를 종종 봅니다.
아무도 모르는 것으로 어렵게 한다고 자기도 모르는 것으로 바꾸고 까먹지요?^^;;

저는 iExceller(http://www.iexceller.com/) 의 팁을 이용, 비밀 문서를 열어볼 수 있는
PC의 HD ID를 체크하여 지정되지 않은 PC에서는 열리지도 않도록 해서 사용합니다.
iExceller님의 팀은 매크로를 보안때문에 실행하지 않으면 다른 Sheet들을 마음대로
볼 수 있기 때문에 사실상 효력이 반감되지만 제 방법은 Welcome 시트 밖에 볼 수
없기 때문에 어떤 다른 Sheet를 볼 수 없습니다.

더 보안을 강화?할려면 기존의 암호 방식을 채택하면 이중 암호의 효과가 있겠지요.
보안이 필요한 엑셀화일을 이런 식으로 만들어서 배포하면 나중에 퇴사한 후에도
사용을 할 수가 없습니다. 배포하기 전에 사용할 사람들의 ID를 전부 넣어 두면
사용하는 사람들은 항상 정상적으로 실행되기 때문에 매크로가 있는지도 몰라요.

원리는 간단합니다. 사용자 PC의 하드 디스크의 시리얼을 체크하여 체크 모듈에
넣어두고 문서를 읽을 때 체크하여 지정 PC가 아니면 퇴출시켜? 버리는 것입니다.
하드 디스크는 포멧을 하거나 새로 변경되면 ID가 바뀌기 때문에 매크로를 사용
언제든지 추가 삭제 할 수 있도록 했습니다.

우선 사용자의 HD ID 체크입니다. 이 첨부된 화일을 실행시키면 HD ID를 보여주고
ID를 클립보드로 복사시켜 줍니다. 이 ID를 첨부된 workbook_open 함수의 case 문에
“xxxxxxxxxxxx”로 되어 있는 부분에 대치하여 검사를 진행시킵니다. 물론 배열이나
기타 등등의 체크 방법이 많이 있겠지만 기밀문서를 여러 사람이 볼 리도 없고?해서
CASE문으로 몇 몇 사람만 등록될 수 있도록 간단히 처리했습니다.

매크로 보안 관련 메시지가 나오는데 매크로 열기를 확인하시면 되고 혹시나 바이러스를
의심하시면 첨부된 코드로 진행하시면 됩니다. ID Check 프로그램도 의심 되시면? ㅋㅋ
아래 매크로를 실행시키면 Welcome Sheet의 A1 셀에 시리얼이 보이도록 해 놓았습니다.

방법은 엑셀의 개발도구 탭의 Visual Basic을 선택하셔서 ThisWorkbook을 더블 클릭하시고
아래의 코드를 복사해 넣으시고 CASE문의 ‘xxxxxxxxxxxx’ 부분을 클립보드에 복사해 놓은
ID를 대치하면 됩니다. 아참! 도구메뉴의 참조에서 Microsoft Scripting Runtime 체크!

비번은 ‘temppwd’입니다. 처음 실행시에 물어 보면 입력하시고 비번 수정 후 다른 이름 저장!
(If password “temppwd” Then 이 부분의 temppwd를 자신의 비번으로 바꾸면 됩니다.)
Visual Basic 도구 메뉴에서 VBA Project 속성에서 보호 탭을 누르시고 “읽기 전용으로 …”를
체크 하시고 암호를 입력하면 이 매크로 자체를 볼 수도 없고 엑셀 시트도 볼 수 없게 되어
누구도 지정되지 않은 PC와 암호를 모르는 사람은 접근 할 수가 없습니다.

순서

1. 첨부된 IDCheck 프로그램 실행하면 아래 화면과 ID를 클립보드로 복사해 줍니다.