Solved

Excel Security Considerations

Posted on 2011-03-23
17
308 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
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 
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

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

Drop Down List with Unique/Distinct Values (Part II - ComboBox or ListBox and Data Validation List Bonus!) David Miller (dlmille) Intro This article focuses on delivering unique, sorted lists to list objects (e.g., ComboBox, ListBox) and Dat…
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…

707 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

12 Experts available now in Live!

Get 1:1 Help Now