Is Excel Workbooks' Protection Really a Myth ??

[monir]

Hi;

1. I've recently reviewed some articles and quite a few of threads dealing
with XL workbooks protection. The general consensus appears to be: " ... XL
protection (using passwords) is a joke, and anyone with some knowledge of VBA
can easily overwrite the protection ...". Is this true ? Some even went
farther by publishing a VBA macro code / procedure that can do just that !

2. That said, can someone please advise on the best available protection
that one can apply ? even if it requires an expert, and only an expert, to
break the protection!

3. Specifically, can I protect my w/b from being accessed by an
anauthorized (non-expert) user ?

4. Will it help if I use a password for the workbook protection, a
different password for the w/s protection, and a 3rd password for the
VBAProject protection ? Will this somehow discourage the unauthorized user,
or simply confuses me ?

Thank you kindly.

 

[Don Guillett]

For most users (like my clients ), password protection is ok but for those
in the know passwords are not too hard to defeat. VBA being a bit more
difficult. So, you need to use a dll or com or another method to really
protect.

Are you CIA?

 

[CLR]

There's an old saying.........."Locks only keep the honest people
out"............one thing you can do tho, is incorporate clandestine means
that will let you know if certain cells have been changed.........even
"they" can be busted by a pro, but most folks don't even think to look for
things like that..........

Vaya con Dios,
Chuck, CABGx3

 

[monir]

Hi Don;

The use of " ... dll or com or another method to really protect..." is above
my level!
Here is a practical scenario which might help explaining the issue and might
trigger a simpler approach.

1. Suppose I've developed a design procedure and programmed it in VBA /
Excel.
2. I decide later to provide you with a copy for your own use.
3. In addition to your promise not to distribute or copy my program, I
still would like to protect my work by ensuring that no one else would have
access (by accident) without my permission.
4. I would use author's password(s) to hide certain worksheets, hide & lock
certain cells & formulas, protect VBAProject, etc., without affecting the
functionality of the program.
5. I would include a "user's password" InputBox in the Workbook_Open event
procedure for your authorized copy of my program.
6. As such, you would have full control of your authorized copy, including
Save, Save As, Copy, etc., as long as you supply the correct "user's
password" to the InputBox function when you open the workbook. (Agree so far
??)
7. To prevent the distribution of the program (accidentally), here is an
idea. How about encoding the "user's password" as the user's e-mail address
??
Can Excel VBA validate the user's entered e-mail address ? How ? The user's
e-mail address must be there in his/her computer somewhere !!

Does the above make sense ? and would it provide a higher level of
protection ?

Oh, by the way, I'm not CIA!

 

[monir]

CLR;

Thank you for your suggestion. My concern is not whether certain cells have
been changed, but rather whether someone has unauthorized access to the
program in the first place!

I've replied in some details to "Don" earlier today. Please review my
extended reply. It might trigger some other approaches! Thanks.

 

[JE McGimpsey]

7. To prevent the distribution of the program (accidentally), here is an
idea. How about encoding the "user's password" as the user's e-mail address
??
Can Excel VBA validate the user's entered e-mail address ? How ? The user's
e-mail address must be there in his/her computer somewhere !!

You're assuming that the user has only one email address to find (I use
at least seven).

Does the above make sense ? and would it provide a higher level of
protection ?

No. It's still trivial to bypass worksheet, workbook and VBA protection.
If your user can open the file, you should assume that they have access
to the *entire* file.

I have worked with clients that have developed rather elaborate schemes,
involving hidden text files and registry values, and using VBA to
constantly check that the appropriate level of protection exists. I was
still able to access the entire workbook, including code, in under 5
minutes, using techniques that are easily discoverable.

 

[monir]

JE McGimpsey;

I agree with most of your arguments. However, I'm not assuming that the
user has only one e-mail address! The user could have as many e-mail
addresses as he/she wishs to have. However, the entered e-mail address (via
InputBox function) to open the file would be validated by the Workbook_Open
event procedure against all the user's encoded valid e-mail addresses and
those available on his/her computer to determine whether he/she is an
authorized user.

If you're an authorized user, and you're able to bypass the w/s, w/b, and
VBA protection, that is fine and would be the least of my concerns! However,
this would not be that easy for amateurs!

So here is my idea. If I'm sending a copy of my program to someone who has
3 e-mail addresses: john1.com, john2.com and john3.com, I would encode these
3 e-mail addresses in the password protected w/b open event procedure. The
user's e-mail address entry via InputBox would be validated:
..... first against these encoded addresses, and
..... second against the user's valid personal addresses (stored somewhere!)
in the host computer.
The return value from both validations must be True to open the file.
Otherwise, Exit Sub.
(In fact, come to think about it, one needs only the second validation, and
there is no need for the first or the InputBox function!)

Any suggestions, guidance and code help would be greatly appreciated. Thank
you.

 

[Gary Smith]

I think this concept is naive. First, it's practiaclly impossible for you
to know where email addresses might be stored on my machine, espcecially
given the many different email programs available and the different places
and ways they may store account information. Some may not be stored at
all. I don't think my Yahoo address is stored on this machine, at least
not in any way that would be practical to find.

More significantly, if I acquire a copy of a spreadsheet that's licensed
to, say, (e-mail address removed), all I have to do is to set up an account in
Outlook Express for that address, and your software is fooled. There's no
way it can tell that the entry doesn't actually work.

 

[Harlan Grove]

monir wrote...
....

1. Suppose I've developed a design procedure and programmed it in
BA/Excel.
2. I decide later to provide you with a copy for your own use.
3. In addition to your promise not to distribute or copy my program, I
still would like to protect my work by ensuring that no one else would
have access (by accident) without my permission.

Critical qualification, 'by accident'. If accidental access is all
you're concerned about, Excel's internal password protection is
adequate.

4. I would use author's password(s) to hide certain worksheets, hide &
lock certain cells & formulas, protect VBAProject, etc., without affecting
the functionality of the program.
5. I would include a "user's password" InputBox in the Workbook_Open event
procedure for your authorized copy of my program.

And what if users disable macros then open your workbook?

6. As such, you would have full control of your authorized copy,
including Save, Save As, Copy, etc., as long as you supply the correct
"user's password" to the InputBox function when you open the workbook.

No, only necessary to open with macros disabled to gain full access.

7. To prevent the distribution of the program (accidentally), here is an
idea. How about encoding the "user's password" as the user's e-mail
address??

Won't work. Even if Excel could validate users' e-mail addresses, you'd
need macros enabled to run this. Any users who know how to disable
macros could give copies to any other Excel user who know how to
disable macros.

Can Excel VBA validate the user's entered e-mail address ? How ? The
user's e-mail address must be there in his/her computer somewhere !!

Back to the real world, no way to do this for *ALL* e-mail software,
and no way to detect spoofed e-mail addresses.

This message has been given before, and it seems it needs to be given
again: if access control is important to you to protect your IP,
*DON'T* *DISTRIBUTE* *YOUR* *IP* *IN* *SPREADSHEETS*. If you want to
protect your IP, and if you have IP worth protecting, time to learn how
to write compiled applications rather than relying on spreadsheets.
THERE IS NO ALTERNATIVE!

 

[monir]

Gary;

It is abundently clear now (at least to me) from your response and other's
that my concept of a user's validated e-mail address not only impractical but
also can easily be bypassed!!

So one is left with the Excel internal password protection and hopes for the
best! It does after all provide some level of protection! Don't you agree ?

No one so far has commented on "Excel 2003 File:ermission" feature, which
MS claims: " ... it prevents (XL files) from being forwarded, edited or
copied by unauthorized people ... ". I've never used it, since it requires
the downloading of MS IRM latest files, and I'm a bit reluctant to do so
unless "Permission" is of some added protection value.

Perhaps "Permission" is the solution!! either by itself or in combination
with the password(s) protection for Open file, w/s, w/b, VBAProject ?
Would appreciate your insight.

Incidentally, and just out of curiousity, you pros have unequivocally stated
that it is easy ( at your level) to bypass password protection:
...... Does it take the same level of expertise to bypass "Open" file
protection versus "VBA Project" protection? or if someone knows-how, it
wouldn't really matter what XL protection to bypass or at what level. Please
don't provide details, for obvious reasons !!
...... Can the pros actually identify the password(s) in the process ?

Thanks again.

 

[monir]

Critical qualification, 'by accident'. If accidental access is all
you're concerned about, Excel's internal password protection is
adequate.

OK. But, how about the pros' statement: " ... XL password protection is a
joke and can easily be bypassed ... ". Am I missing something ?

And what if users disable macros then open your workbook?

No, only necessary to open with macros disabled to gain full access.

Are you saying that if you open a VBAProject protected file with macros
disabled you can still access the entire file ? Doesn't "disable macro"
implicitly imply that the macros for this file would not be available and
thus the program wouldn't run ??

Won't work. Even if Excel could validate users' e-mail addresses, you'd
need macros enabled to run this. Any users who know how to disable
macros could give copies to any other Excel user who know how to
disable macros.

But if you "disable macros", then how would you be able to run the program?
Further, one could give copies, but the recepients must "enable macros" to
run the program. Correct? I most likely got this wrong!

Back to the real world, no way to do this for *ALL* e-mail software,
and no way to detect spoofed e-mail addresses.

This message has been given before, and it seems it needs to be given
again: if access control is important to you to protect your IP,
*DON'T* *DISTRIBUTE* *YOUR* *IP* *IN* *SPREADSHEETS*. If you want to
protect your IP, and if you have IP worth protecting, time to learn how
to write compiled applications rather than relying on spreadsheets.
THERE IS NO ALTERNATIVE!

I agree. This seems to be the general consensus among the respondents!

Incidentally, do you think "Excel File:ermission" provides a better
protection than the password protection? Please review my reply to "Gary" on
that regard. Your insight would be greatly appreciated. Thanks.

 

[Gary Smith]

We gone beyond my area of expertise, but it's generally held to be the
case that anyone with a copy of the file and sufficient determination will
be able to read that file. Obviously some forms of encryption make that
difficult, but it's still not impossible. I recently read an article
which claimed that the most commomly used technique for breaking passwords
is asking someone who knows them.

Gary;

It is abundently clear now (at least to me) from your response and other's
that my concept of a user's validated e-mail address not only impractical but
also can easily be bypassed!!

So one is left with the Excel internal password protection and hopes for the
best! It does after all provide some level of protection! Don't you agree ?

No one so far has commented on "Excel 2003 File:ermission" feature, which
MS claims: " ... it prevents (XL files) from being forwarded, edited or
copied by unauthorized people ... ". I've never used it, since it requires
the downloading of MS IRM latest files, and I'm a bit reluctant to do so
unless "Permission" is of some added protection value.

Perhaps "Permission" is the solution!! either by itself or in combination
with the password(s) protection for Open file, w/s, w/b, VBAProject ?
Would appreciate your insight.

Incidentally, and just out of curiousity, you pros have unequivocally stated
that it is easy ( at your level) to bypass password protection:
..... Does it take the same level of expertise to bypass "Open" file
protection versus "VBA Project" protection? or if someone knows-how, it
wouldn't really matter what XL protection to bypass or at what level. Please
don't provide details, for obvious reasons !!
..... Can the pros actually identify the password(s) in the process ?

 

[JE McGimpsey]

Incidentally, and just out of curiousity, you pros have unequivocally stated
that it is easy ( at your level) to bypass password protection:
..... Does it take the same level of expertise to bypass "Open" file
protection versus "VBA Project" protection? or if someone knows-how, it
wouldn't really matter what XL protection to bypass or at what level. Please
don't provide details, for obvious reasons !!
..... Can the pros actually identify the password(s) in the process ?

File protection is a little more robust than Worksheet, Workbook or VBA
protection. The three latter "protections" take less than a minute each
to bypass with the right knowledge. I've published a macro for the first
two at

http://www.mcgimpsey.com/excel/removepwords.html

just to reduce the number of variations that were being posted here.

File protection, though, can be bypassed in less than three minutes with
an inexpensive commercial service. See

http://mcgimpsey.com/excel/fileandvbapwords.html

 

[JE McGimpsey]

In-line...

OK. But, how about the pros' statement: " ... XL password protection is a
joke and can easily be bypassed ... ". Am I missing something ?

That's what Harlan was saying. XL's internal protection is adequate to
keep users from accidentally accessing your protected
regions/sheets/projects. Anyone with enough gumption to find these
newsgroups can intentionally bypass those "protections".

Are you saying that if you open a VBAProject protected file with macros
disabled you can still access the entire file ? Doesn't "disable macro"
implicitly imply that the macros for this file would not be available and
thus the program wouldn't run ??

Macros disabled simply prevents the user from running them. They're
still available to be seen in the file.

But if you "disable macros", then how would you be able to run the program?
Further, one could give copies, but the recepients must "enable macros" to
run the program. Correct? I most likely got this wrong!

Easy - hack the VBA protection, open with macros disabled, find the
email address the code looks for, then install that address in the
target email application. Starting up with macros enabled will then work
fine.

 

[Harlan Grove]

JE McGimpsey wrote...
....

File protection, though, can be bypassed in less than three minutes with
an inexpensive commercial service. See

....

It wouldn't even need to be bypassed. If authorized users could only
use the workbook if they know the file-open password, then they could
give copies of the file *AND* the password to unauthorized users.

 

[monir]

....
...
....
...
....

Bypassing password protection in one minute ! That is absolutely shocking!

"monir" wrote...

No one so far has commented on "Excel 2003 File:ermission" feature, which
MS claims: " ... it prevents (XL files) from being forwarded, edited or
copied by unauthorized people ... ". I've never used it, since it requires
the downloading of MS IRM latest files, and I'm a bit reluctant to do so
unless "Permission" is of some added protection value.

Perhaps "Permission" is the solution!! either by itself or in combination
with the password(s) protection for Open file, w/s, w/b, VBAProject ?
It may require, however, the authorized users of the file to have Office
2003 or later!
Would appreciate your insight.

Thank you.

 

[Harlan Grove]

monir wrote...
....

Bypassing password protection in one minute ! That is absolutely

shocking!

Not if it's only intended to prevent INADVERTENT/ACCIDENTAL changes.
Only if you believe it provides IP protection would it be shocking.

No one so far has commented on "Excel 2003 File:ermission" feature, which
MS claims: " ... it prevents (XL files) from being forwarded, edited or
copied by unauthorized people ... ". I've never used it, since it requires
the downloading of MS IRM latest files, and I'm a bit reluctant to do so
unless "Permission" is of some added protection value.

Perhaps "Permission" is the solution!! either by itself or in combination
with the password(s) protection for Open file, w/s, w/b, VBAProject ?
It may require, however, the authorized users of the file to have Office
2003 or later!
Would appreciate your insight.

To the extent this REQUIRES Excel 2003 and Windows 2003 Server to work
at all, it could severely reduce the potential market for your workbook
model. If you want your workbook to be usable by people still running
Excel 97 or 2000 under Windows NT4 or 98 (or 95?!), you can't use IRM.

There's very little other than recapped marketing material on Excel IRM
in the Excel newsgroups, and not much on the web in general. I'd figure
that until Windows 2003 Server is used on more than 2/3 of Windows file
servers IRM won't help you.

 

[JE McGimpsey]

It may require, however, the authorized users of the file to have Office
2003 or later!

It does.

 

[monir]

......

Thank you all for your contributions and help.

 

[nvzyn]

Anyone,

I've read this thread and I'm looking for basically the same "protection"
solution. I have a suite of statistical templates/workbooks that I've written
and want to market. I want to protect the IP (the hidden formulas mainly) and
I'm well aware that MS Password Protection is very easy to "recover."

There has to be a simple & robust way to block access to hidden formulas and
VBA modules. Can anyone define a solution?