CODEWITHSUNDEEP
vbaexcel
BgreenDSI
BgreenDSI

Reputation: 421

Protecting Excel Worksheet Data From Savvy User

I'm creating a excel application that will be distributed to my associates. Based permissions I need to disable access to certain sheets and features. I've been able to protect and hide sheets but a savvy user is able to iterate over hidden worksheets internally or externally to expose data. I need to stop that.

Here's the process:

When the workbook opens a query to remote database is made to return set of user permissions. Then those permissions are printed to worksheet called "PERMISSIONS".

enter image description here

This code below iterates over worksheet permissions to find a particular setting by passing in column name.

CheckPerm "Timesheet"

Public Function CheckPerm(nPermColTarget As String) As Boolean
Dim Counter As Integer
Dim x As Integer
Dim y As Integer

    Counter = 3
      Do Until Sheet7.Cells(Counter, 1).Value = Associate_Name
        Counter = Counter + 1
      Loop
    y = Counter

    Counter = 2
      Do Until Sheet7.Cells(2, Counter).Value = nPermColTarget
        Counter = Counter + 1
      Loop
    x = Counter

   If Sheet7.Cells(y, x).Value = "ON" Then
      CheckPerm = True
      GrntDenySheetAccess nPermColTarget, y, x, CheckPerm
   Else
   ' "OFF"
      CheckPerm = False
      GrntDenySheetAccess nPermColTarget, y, x, CheckPerm
   End If
End Function


Private Sub GrntDenySheetAccess(nPermColTarget As String, y As Integer, _ 
x As Integer, CheckPerm As Boolean)

Select Case nPermColTarget

   ' Sheet1
    Case "Timesheet"

       If CheckPerm = True Then
          Sheet1.Unprotect "pass"
          Sheet1.Visible = xlSheetVisible
       End If

       If CheckPerm = False Then
          Sheet1.Protect "pass"
          Sheet1.Visible = xlSheetVeryHidden
       End If

 End Select

End Sub

But even though sheet1 is hidden and protected, I can still iterate over hidden sheet1's content to access info. Anyway to stop that?

Thanks

Upvotes: 1

Views: 1146

Answers (1)

Jon Crowell
Jon Crowell

Reputation: 22320

There aren't any ways to safely protect access to anything in an Excel workbook. Passwords are much harder to crack in Excel versions 2007 and onward, but a user can simply save the workbook as a .xls file and then it becomes easy to crack.

The best you can do is to make it a little tougher for somebody to get into. Password protect your file, and set any sheets that you don't want them to get to to xlSheetVeryHidden, which prevents users from unhiding the sheets through the Excel user interface.

The commenters under your question are correct. If you want a secure application, Excel isn't the answer.

enter image description here

Having said that, if you enforce security in the database layer, Excel makes a great UI. Just make sure you don't store or retrieve data that the user shouldn't see.

Upvotes: 3

Related Questions