Spreadsheet Security

[Lee Meadowcroft]

Hi,

I have developed a rather complex engineering spreadsheet over the past
few months which is to be released within my company very soon. I am
concerned over the security issues of an excel spreadsheet and would
like some advice. A similar peice of software is available on the
internet to buy but is now outdated and therefore I know that there
would be a lot of interest in the spreadsheet beyond my company. It
would be relatively easy for the spreadsheet to get out of the company
either by someone taking a copy with them when they leave or by issuing
the excel file to others instead of pdfs.

My question is how can I best secure my spreadsheet to prevent other
organisations from using it as their own? There are legal issues which
could affect my company should others make mistakes as a result of
using my spreadsheet; regardless of whether or not they used it with my
consent.

The spreadsheet currently has locked and hidden cells, password
protected sheets and password protected VBA code. I am aware that excel
passwords are relatively easy to crack, regardless of length or
complexity. Ideally I would like to convert the software to a
standalone application but am not prepared to pay for software to do
this as I dont intend on selling the spreadsheet and therefore cannot
recover the cost of any software.

Are there any more advanced security tools availble within excel that
can better protect my spreadsheet?

I thought about using the VBA to switch off all mens and buttons except
for open, save and save as such that it is not easy to unprotect the
sheets or enter the VBA processor but am not sure if this is any better
than doing nothing.

Any advice would be much appreciated.

Regards,

Lee

 

[tony h]

There are a lot of options but it does depend on how the spreadsheet is
to be used. The various options do vary in how secure they are. A bit
more detail would be good

regards

 

[Adrian Gallero]

Lee Meadowcroft wrote:
Hi,

My question is how can I best secure my spreadsheet to prevent other
organisations from using it as their own? There are legal issues which
could affect my company should others make mistakes as a result of
using my spreadsheet; regardless of whether or not they used it with
my consent.

I am not a lawyer, but i find this not very likely. If they are using
your spreadsheet without your consent you should have not any
responsability. I would ask for some legal advise on this one.

About Excel, there is really little security you can add to an xls
file. Most of the "security" things are just a record inside the xls
file that says "do not open me". By editing the file and removing those
records, you can access almost everything on seconds.

The spreadsheet currently has locked and hidden cells,

No security here.

password protected sheets and password protected VBA code.

No security here either, at all.

I am aware that excel passwords are relatively easy to crack,
regardless of length or
complexity.

It is not that they are easy to crack, is that you can bypass them
completely with no effort. And you do not need too much technical
knowledge either. Just try to open your file with openoffice, and take
a look at the macros, protected cells, etc.

Ideally I would like to convert the software to a
standalone application but am not prepared to pay for software to do
this as I dont intend on selling the spreadsheet and therefore cannot
recover the cost of any software.

This might be an option, but it is never 100% safe anyway. If an user
can use it on your company, he can use it outside your company.

Are there any more advanced security tools availble within excel that
can better protect my spreadsheet?

The only thing that really adds some security to an xls file is the
Password to open (Tools->Options->Security) If you are using XP/2003,
you can also select the encryption mode, and use something better than
the standar office 97 encryption (that can be easily cracked too)

You will see that if you have a password to open, OpenOffice will ask
for the password for the document. This is so because you can not
really open it without it, since the file is encrypted.

Of course, the drawback of the "password to open" is that you need to
tell your users which password it is, or they will not be able to open
the file. So if someone is using your file, he has to know the password
to open too.

Also, there are tools that can make a brute force attack on the
password to open. If the password is too simple they might guess it,
even if it is not encrypted.

I thought about using the VBA to switch off all mens and buttons
except for open, save and save as such that it is not easy to
unprotect the sheets or enter the VBA processor but am not sure if
this is any better than doing nothing.

I think it is not better than doing nothing. You can just disable the
macros when you open the file. Hiding tools /buttons might be good to
help some inexperienced user (so he will not get lost) but it is not a
security measure, IMHO.

Any advice would be much appreciated.

Well, this is what I can think, sorry if my opinion is not very
positive. But this is the experience I have.

Best regards,
Adrian.

 

[Greg Wilson]

Lee,

A *modest* means of protection that I use is to have my VBA code look for a
certain file on the company network or on the harddrive. If it is not there
then the project refuses to work. So simply making a copy and taking it away
is useless unless they can manage to break the VBA project protection. If
you're like me, the majority of the project's smarts are in the VBA code and
copying worksheets is of minimal value. If you have valuable worksheet
formulas then you might consider converting them to UDFs.

Breaking VBA project protection is difficult compared to worksheet and
workbook protection which is trivial. But it isn't that difficult. However,
IMO it's just a question of the odds. The candidates for stealing my projects
have names and faces and I know their capabilities and the extent to which
they value my projects. With the above safeguard I'm not too worried.

As far as hiding controls, this is a complete waist of time. Breaking
worksheet and workbook (not file open) protection is done through VBA and
bypasses these controls. It can be done via another wb so disabling access to
the VBE through a certain wb isn't worth the bother.

Regards,
Greg

 

[Lee Meadowcroft]

Thankyou for your very comprehensive answers. As I expected, there
isn't an awful lot that I can do about it.

One final idea; on another of my spreadsheets I have used VBA to ask
for a password on opening (rather than the excel password), this is so
that I can enter as an administrator or as a user (the password is
required to enter as an administrator and unlocks certain things within
the spreadsheet). Is this of any use? The password could be set to
change after a certain date which means that even someone who knows how
to get into the spreadsheet can only use it for a limited time.

Thanks,

Lee

 

[Greg Wilson]

Hi Lee,

I'm currently using xl2000. I know that new worksheet/workbook protection
options have been added to later versions and I'm not up to date with these.
However, I would assume that anything worksheet related such as hidden ranges
and formulas would be made accessible upon breaking the worksheet protection.
xlVeryHidden worksheets would be different. Only access to the VBA project
will allow access or even knowledge of their existence.

As far as the password option you mention: If the correct passwords are
recorded in a code module, presumably as a list of constants (e.g. Const Pwd
As String = "monkey2") or taken from a range of cells on an xlVeryHidden
worksheet, then comparing the user's entry to these would be as safe as the
VBA project I would think.

However, if you intend the password to expire after a certain date, then
this is easily bypassed by changing the system date. You program has no way
of knowing the date except through the sytem date/time which most users know
how to change. I know of an unsoffisticated person who got wise to this and
kept using a trial version of something indefinately. I suppose an exception
would be to have it log onto the web and check the date on some site.

I havn't given this issue a lot of thought and could be missing something,
but personally, I think I would have it log the number of uses instead. And
after a given number, the password would be obsolete and would resort to a
different one. The log would have to be kept outside of the wb (e.g. text
file) and the user would have to be kept unaware of its existence. If kept
inside the wb, they need only keep making duplicates of a version that hadn't
expired yet. You could create the log file programmatically if it doesn't
exist.

Alternatively, you might log the date/time, and if it ever goes backward in
time then you would know that they are up to no good. Only allow access on
date/times that are later than the last logged up until expiry.
Theoretically, they could change the date/time so that it was only a little
bit later than the previous access and stretch it out, but this would be a
lot of hassle and they would have to know what you were doing.

Hope this was of some help. I also work for an engineering firm and make
specialized spreadsheets but do so to a limited degree.

Best regards,
Greg