4월 1 2015
엑셀(EXCEL) : 공유화일을 IP, 사용자 체크하여 열기, 편집 여부 및 로그인, 아웃 기록 보기(관리자)
logon-ip-user-check <—- 첨부 화일
여러분의 성원?에 힘입어 사실 아래 팁의 댓글에 요청 사항이 있어 작성을 해 보았습니다.
아래의 팁은 순수 엑셀 화일을 보안 상태로 만드는 팁이었다면 지금부터 게시하는 내용은
그동안 공유문서를 통해 업무를 진행하시던 분들의 고충 내용을 조금 쉽게 해 주는 팁입니다.
앞에 팁은 하드디스크의 시리얼을 체크하여 유출된 보안 엑셀 파일의 실행을 방지하는 것이나
이것은 사무실에서도 혹은 유출된 화일도 내부 매크로를 알지 못하는 이상 실행을 할 수 없게
만드는 방법입니다. 저는 공유 화일을 쓰지 않고 그냥 내부 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속성에서도 보안에 암호를 걸어야만 정상 동작합나다.
사용자의 편의를 위해서 두 부분의 암호를 임의로 해제한 상태로 첨부해 둡니다.
4월 9 2015
엑셀(EXCEL) – 뚫리지 않는 방패와 뭐든지 뚫을 수 있는 창 만들기
(예시를 위해 WaterMark의 색을 진하게 사용했습니다)
소스 ) 20150409-Watermark
모순! 디지털에서는 영원이라 할지라도 뚫리지 않는 방패를 만들 수 없다고 봅니다.
항상 0과 1로 조합된 데이터를 가공하고 비밀번호나 기타 어떠한 경우에라도 원래의
자료 생산자가 들어갈 수 있는 방법을 마련해 놓아야 하니까요?
엑셀화일을 지정된 컴퓨터에서만 열 수 있게 하는 법
http://www.clien.net/cs2/bbs/board.php?bo_table=lecture&wr_id=265227
공유화일의 IP, 사용자 체크하여 열기, 편집여부 및 로그인, 아웃 기록
http://www.clien.net/cs2/bbs/board.php?bo_table=lecture&wr_id=265837
사용자의 HDD ID를 체크해서 공유 문서의 접근 여부를 확인해서 보안을 1차로 하고
IP, LogOn 정보를 확인해서 2차로 보안을 걸어 문서의 편집, 열람 여부 권한을 설정해서
관리를 하는데 나쁜 경우 카메라로 자료를 사진으로 찍거나 아니면 스크린 캡쳐를 해서
접근한 자료를 가져가는 경우가 있다고 이 문제를 해결할 수 없는지 문의가 있었습니다.
참 어려운 문제입니다. 웹이나 이런 경우는 ActiveX를 덕지 덕지 깔아서 스크린 캡춰를 하지
못하게 하거나 웹의 백그라운드로 이미지를 깔아서 사진 문제로 어떻게든 해결할 수 있는데
엑셀의 경우 참 난감한 문제이더군요.
아이디어를 생각하고 구걸신에게 구걸?해 봤습니다. 역시나 구걸신은 현명하게 답해주더군요.
WaterMark를 까는 것입니다. 보이는 셀마다 로그인한 사용자, 셀정보, 날짜를 넣어서 누가
언제 어디 부분을 사진을 찍더라고 카피를 하더라도 WaterMark가 보이게 하는 것이지요.
물론 아직까지 해결하지 못한 부분이 많습니다. 정식으로 프로그램을 배워서 작성하는 것이
아니라 취미로 하다보니 능력 부족이 그 이유입니다. 우선 WaterMark를 하나 하나 지우고
복사하고 사진을 찍으면 답이 없습니다. (이 부분 누가 아이디어 좀 주세요. 굽신 굽신)
두번째 속도가 조금 느립니다. 셀을 계산해서 Shape를 이용 WaterMark를 뿌리기 때문이고
세번째 마우스의 활용 용도가 떨어집니다. Shape를 이용해서 셀 위에 뿌려놓기 때문에
드래그를 통한 선택이 힘들고 함수 입력시에도 클릭을 통한 위치 입력이 힘들어요.
그래서 이 팁의 최종 사용 목적은 정형화된 Form이 있고 다른 시트에서 계산된 자료를
취합하여 보여주는 보고서 폼에 적합합니다. 물론 속도를 무시하고 전체 Sheet에 적용해도
무방하지만 무리가 있을 것 같습니다.
우선 ThisWorkbook을 더블 클릭해서 아래 코드를 넣어줍니다.
이 Workbook에서는 마우스의 오른쪽 클릭을 제한합니다. 단축키를 사용하는 것은 가능해요.
필요하지 않으면 아래 두 함수를 지우면 됩니다. 사실 마우스를 사용해도 대부분이 키보드를
통해서 사용해야 할 것입니다. 그래서 정형화된 폼에 데이터만 변할 때 사용하라는 것입니다.
Option Explicit
‘ 이 Workbook에서는 마우스의 이용을 제한합니다.
Private Sub Workbook_Activate()
Dim Cbar As CommandBar
For Each Cbar In Application.CommandBars
Cbar.Enabled = False
Next
End Sub
‘ 이 Workbook을 떠나면 마우스가 오른쪽 클릭이 활성화됩니다.
Private Sub Workbook_Deactivate()
Dim Cbar As CommandBar
For Each Cbar In Application.CommandBars
Cbar.Enabled = True
Next
End Sub
‘ 새로운 Sheet를 만들어 복사할 수 없도록 합니다. 그냥 넣어 두었어요.
‘ 다른 Workbook에다 복사하면 할 수 없습니다? 그래도 WaterMark는 복사되고
‘ Macro는 복사되지 않아서 이 부분을 크릭해서 지우려 하면 에러로 지우지 못함.
Private Sub Workbook_NewSheet(ByVal Sh As Object)
With Application
.ScreenUpdating = False
.DisplayAlerts = False
ActiveSheet.Delete
MsgBox “새로운 시트를 생성할 권한이 없습니다! “, 16
.DisplayAlerts = True
.ScreenUpdating = True
End With
End Sub
Module을 하나 만드시고 아래 코드를 넣습니다. WaterMark는 위의 팁을 이용해서
사용자 Logon 정보를 넣고 사용하시면 됩니다. 조금 응용하면 IP도 넣을 수 있겠지요.
오늘은 Full Source로 올려 드리지 못하고 나름대로 응용해서 잘 사용해 주세요.
Option Explicit
Const watermark As String = “hkyoon”
Sub WatermarkShape()
Dim cll As Range
Dim rng As Range
Dim ws As Worksheet
Dim shp As shape
Set ws = ActiveSheet
‘ WaterMark가 들어가 영역입니다.
Set rng = ws.Range(“A1:S28”) ‘Set range to fill with watermark
Application.ScreenUpdating = False
For Each shp In ws.Shapes
shp.Delete
Next shp
For Each cll In rng
Set shp = ws.Shapes.AddShape(msoShapeRectangle, 5, 5, 5, 5)
With shp
.Left = cll.Left
.Top = cll.Top
.Height = cll.Height
.Width = cll.Width
.Name = cll.address
‘ 두가지 순환문을 속도 체크해 보니 if문이 근소하게 빠르게 나타남
‘ Select Case cll.Row Mod 3
‘
‘ Case 0
‘ .TextFrame2.TextRange.Characters.Text = cll.address
‘ Case 1
‘ .TextFrame2.TextRange.Characters.Text = watermark
‘
‘ Case 2
‘ .TextFrame2.TextRange.Characters.Text = Format(Now(), “YYYYMMDD”)
‘
‘ End Select
‘ 여기에 WaterMark를 넣는 루틴입니다.
If cll.Row Mod 3 = 0 Then
.TextFrame2.TextRange.Characters.Text = cll.address ‘ 셀 위치 정보
ElseIf cll.Row Mod 3 = 1 Then
.TextFrame2.TextRange.Characters.Text = watermark ‘Logon 사용자
Else ‘현재 날짜
.TextFrame2.TextRange.Characters.Text = Format(Now(), “YYYYMMDD”)
End If
.TextFrame2.TextRange.Font.Name = “Tahoma”
.TextFrame2.TextRange.Font.Size = 10
.TextFrame2.VerticalAnchor = msoAnchorMiddle
.TextFrame2.TextRange.ParagraphFormat.Alignment = msoAlignCenter
.TextFrame2.WordWrap = msoFalse
.TextFrame.Characters.Font.ColorIndex = 15
.TextFrame2.TextRange.Font.Fill.Transparency = 0.7 ‘ WaterMark색의 진하기?
.Line.Visible = msoFalse
.OnAction = “‘SelectCell “”” & ws.Name & “””,””” & cll.address & “””‘”
With .Fill
.Visible = msoTrue
.ForeColor.ObjectThemeColor = msoThemeColorBackground1
.Transparency = 1
.Solid
End With
End With
Next cll
Application.ScreenUpdating = True
End Sub
Sub SelectCell(ws, address)
Worksheets(ws).Range(address).Select
End Sub
정형화된 Form시트에 이 코드를 삽입합니다. ScrollArea는 사용자가 작업할 공간을 할당해 줍니다.
이 영역 외에는 입력도 스크롤도 할 수 없어 항상 WaterMark가 나타납니다.
Private Sub Worksheet_Activate()
WatermarkShape
ActiveSheet.ScrollArea = “A1:S28”
End Sub
배경으로 이미지를 깔고 통합 문서 보호와 공유를 하면 백그라운드에 WaterMark를 넣으면 되지만
내용을 복사해서 다른 Sheet에 넣으면 되기 때문에 커다란 의미가 없더군요. 지금까지 여러 자료를
찿아가면서 완벽하게 해서 올리려 했지만 아직은 역부족입니다. 그래도 여기까지 올려 두면 더
훌륭한 분들이 추가해서 멋지게 만들어 주시리라 믿고 올립니다.
By vinipapa • 무른모 • 0 • Tags: WateMark, 보안, 엑셀, 워트마크