Solved

Excel Security Considerations

Posted on 2011-03-23
17
309 Views
Last Modified: 2012-08-13
I'm working to finalize a Excel Workbook that I'd like to distribute. However, I'm not certain how to design the security for it. Specifically I'd like to know how to;

1.) Secure the entire workbook from any changes while still allowing all of the worksheets to trade information and calculations.
2.) How to initiate a "global password"
3.) If I lock a spreadsheet, does that mean it cannot update calculations that occur based on links to other worksheets within the same workbook?
4.) What considerations should be used when securing an individual worksheet?
5.) When securing the VB Code and Macros, is protecting the "project" enough?

I know security is not absolute in Excel; however, I'd like to make it rather difficult for someone to hack into the macros or change the calculations that are established. EE expertise on how to approach Excel security would be appreciated.

B.
0
Comment
Question by:Bright01
  • 6
  • 5
  • 3
  • +1
17 Comments
 
LVL 85

Assisted Solution

by:Rory Archibald
Rory Archibald earned 150 total points
ID: 35197020
Depends on your definition of "rather difficult" to hack into. The only way to secure the code is a project password, but that is not at all secure if someone really wants to get at it. You can additionally ofuscate the code by making all the variable names into gibberish and so on, but that will make your maintenance a nightmare, IMO. Worksheet/workbook passwords (other than the one to actually open the workbook) are trivial to crack. Your formulas should still work, but if you have code that manipulates the sheets, you will need to alter that to unprotect and reprotect the sheets/workbook when it runs (you can use the Userinterfaceonly:=True argument when protecting but that does not work for all things)
0
 

Author Comment

by:Bright01
ID: 35197534
rorya,

Can you elaborate on what is and how to use a "Global Password"?

TY,

B.
0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 35197544
'Global password' has no intrinsic meaning to me, other than using the same password for everything. In what context are you using it?
0
 

Author Comment

by:Bright01
ID: 35203090
rorya,

Public comboNo As Long
Public OldGlobalPassword As String, NewGlobalPassword As String

Sub lockme()
    OldGlobalPassword = "PASSWORD"
    NewGlobalPassword = "LOCKME"
    If Now > Sheets("DONOTDELETE").Range("B1").Value Then
        On Error Resume Next
            For i = 1 To ThisWorkbook.Sheets.Count
                If Sheets(i).Name <> "DONOTDELETE" Then
                    Sheets(i).Unprotect OldGlobalPassword
                    Sheets(i).Protect NewGlobalPassword
                    MsgBox "The worksheet is this workbook has been locked. Please contact B on XXX-XXX-XXX-XXX for a new workbook"
                End If
            Next i
        On Error GoTo 0
    End If
End Sub

0
 
LVL 6

Expert Comment

by:FernandoFernandes
ID: 35205752
One suggestion to make it harder (not impossible) for anyone to see the VBA, you could
protect the code with password (layer one of VBA protection)
share the workbook (layer two), will make project become unviewable.
anyone who knows this, will be able to undo it. The good thing is, if they're using password cracking softwares, even if these softwares remove the layer one, the project still remains unviewable bcuz of layer two.

Of course a quick google search may help the 'cracker'...

For all the rest (protecting sheets and workbook), try to use complex passwords and pray, they're so easy to break...
For securing the file, use complex password and  wdifferent types of encryption, bcuz most softwares dont break all encryption types offered !

:)
0
 

Author Comment

by:Bright01
ID: 35205838
I'm not worried about the audience for this Workbook; they barely have Excel 2010.  However, I'd like to protect the worksheets.  If I go into properties and hide a worksheet (or extremely hide a worksheet) and secure it, it will still operate within the math and required calcuations but be hard to detect from a simple "unhide" that may be used on a "tab".  Is that correct?  Also, I still don't know what a "Global Password" is (see code above)....any idea(s)?
0
 
LVL 6

Assisted Solution

by:FernandoFernandes
FernandoFernandes earned 150 total points
ID: 35205883
yes it's correct. All values will still be referenceble, and its formulas will be calculated regardless of visible status !

now, global password is as Sid said, this concept doesnt exist in Excel ... Unless you protect everything with the same password, then you consider as 'global password' or even 'universal password' or 'super password'...
that code is determining a variable called OldGlobal and another called NewGlobal, but it could also be called HiHelloWorld... what the code is doing is, it's making sure that all the sheets are protected with the same password, thats it.
0
 
LVL 6

Expert Comment

by:FernandoFernandes
ID: 35205894
And i dont get this:
'I'm not worried about the audience for this Workbook;'

i think you should be ! but it's just me :)
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 6

Expert Comment

by:FernandoFernandes
ID: 35205906
Ops, sorry, I meant Rorya !
0
 

Author Comment

by:Bright01
ID: 35206091
Fernando,

What I meant to say was that the users of my workbook are not very sophisticated; nor do they probably want to deal with the complexity of the code.  However, once replicated; this workbook could be cracked and I understand that security with Excel is limited.  With that said, I believe based on the comments that you and Rorya have made, that there are ways of making it more difficult to hack. Here are the specfic questions I need answers to;

1.) If I am using a password to access a locked spreadsheet and a different password to protect the VB "Project"; what do I put into the code that Sid built for me in the "OLDGLOBALPASSWORD" section?  I do not have a universal password but rather multiple passwords for different levels.  May I use "*" or "" or just leave it blank?
2.) If I put in a NEWGLOBALPASSWORD based on a timer (again, that Sid built for me) will that replace all passwords?  What I'm attempting to do is lock down the Workbook at a given time and the code I showed above is what has me perplexed on how to adapt.
0
 
LVL 4

Expert Comment

by:yuppydu
ID: 35213158
There are a number of software in he internet to protect excel files. Some are quite good. Most of them allow you to convert your file into an exe file and that makes it almost impossible to crack for the regular user.
0
 

Author Comment

by:Bright01
ID: 35213503
yuppydu,

Really?  Two questions; are they free (can you direct me to the one you like the most) and does that mean that a "user" may not need Excel 2010 to run a Excel 2010 Workbook / App.?

B.
0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 35213703
I've never seen a free one. ;)
0
 
LVL 4

Accepted Solution

by:
yuppydu earned 200 total points
ID: 35213961
They are not free. I'll not comment on the pricing, it depends how valuable is your workbook. I sell an Excel application for financial asset management and I protect it with one of those softwares. Very flexible and very secure even for an advance user.
Don't want to disclose it here, if you're interested you can pm me.
0
 

Author Closing Comment

by:Bright01
ID: 35218475
What's PM?
0
 
LVL 6

Expert Comment

by:FernandoFernandes
ID: 35218568
0
 
LVL 4

Expert Comment

by:yuppydu
ID: 35227560
PM stands for private message. You can write me at: giovanni@5sada.it
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
Convert between Excel file formats (.XLS, .XLSX, .XLSM) with/without macro option David Miller (dlmille) Intro Over this past Fall, I've had the opportunity to see several similar requests and have developed a couple related solutions associate…
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

911 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now