How do I keep Hidden Columns / Rows from being Unhidden / Referenc

[tc69]

1) I have Hidden Columns containing sensitive info in a Worksheet Protected
with a Password which Excel doesn't Print or Scroll into as one would expect
but can be unhidden without Unprotecting much less a Password if I copy to
another Worksheet or Workbook!?!. They even unhide automatically when I copy
to a Workbook in another Excel session. I know Excel isn't considered a
"secure" app, but come on, what's the point in offering a Password option
when anyone who can do a Copy and Paste can defeat it. I tried this in Excel
2000 and 2003. I've disabled Copy and Customize options in my Macros but
since Macros can be disabled on opening a Workbook, those are just added
speed bumps just like any security feature in Excel seems to be.

2) I noticed that values in Hidden Columns / Rows can be referenced from
other Worksheets or Workbooks. I've disabled other Workbooks from being
opened or being already opened but again that's just another speed bump since
they can open another session of Excel or disable Macros which leads to me my
next related question.

3) Is there a way to require Macros to be enabled for a Workbook to be
opened? I don't think this would have to sacrifice security since it could
be setup so that they would still be able to decline to open it if they don't
trust it.

Note that these Hidden Columns contain info that are restricted even to
authorized users of the Workbook so I can't just rely on the Workbook Open
Password which I heard is another speed bump as well since you can get a
utility to defeat it, but I can live with that.

Are these bugs that've been fixed? Are there workarounds? Thanks!

 

[Dave Peterson]

If you're putting sensitive data in an excel workbook and sharing it with
others, then you should expect that others will see it.

There is no protection that you can provide that will allow people to open the
file and not see that data.

Excel's security measures are not meant to provide this kind of
security--they're meant to keep the typical user from overwriting formulas.

 

[tc69]

Wow. That's uh .. pretty definitive. Were you a designer of Excel's
(in)security features or just one of their sleeper newgroup spokepersons?
Just kidding ... sort of.

Is your claim of what I "should" expect just based on the reality of Excel's
apparent lack of security features or actually on Excel's supposed original
design intentions?

I know that I personally and I would bet almost everyone who weren't told
explicitly otherwise "would" expect others will not be able to see or change
"anything" (not just Excel's data) that's accessed by a "password" unless of
course they have access to someone like an NSA cryptologist or world-class
hacker or their tools. Excel's own help says that "you can hide sheets that
contain 'sensitive' data" and "To 'prevent' others from displaying hidden
sheets, rows, or columns, you can protect the workbook or sheet with a
password." Nowhere in Excel's help, could I find any mention that data
hidden with a password could be accessed without one or that we should still
expect others will see it.

Well, in spite of what I "should" expect, I believe I've found a way to
prevent read and write access to parts of a Workbook that would require the
ability to override the Workbook Open or VBA Project passwords to foil which
I heard can be done with 3rd party utilities but my expectations have be
pounded so much at this point that I'd be more than happy if that's the only
exception.

1. Workbook WkbkA is optionally protected by an Open Workbook Password PswdA
which is provided to all authorized users.

2. Workbook WkbkA is empty except for a Workbook.Open Macro that: a) sets
Application.EnableCancelKey = xlDisabled and Application.Interactive =
False, b) calls Application.SendKeys "e" (to force Enable Macros), c) opens
WkbkB with a Password PswdB which is available only to the publisher and
maybe administrative users.

3. WkbkA's VBA Project is protected with a Password PswdC which is
available only to the publisher and maybe administrative users. PswdC can be
the same as PswdB to keep things simple.

4. WkbkB contains the actual sensitive data hidden in Columns / Rows /
Worksheets Protected by a Workbook Password PswdD and Worksheet Password(s)
PswdE1...(n) all of which can be the same as PswdB to keep things simple.

4. WkbkB's Macros are protected by a VBA Password PswdF which can be the
same as PswdB to keep things simple.

5. WkbkB's Macros use the Application .Open Events to only allow one other
Workbook to be already open in the same Excel Session (which would have to be
WkbkA) unless of course the user has access to PswdB (publisher / admin user)
and opened WkbkB manually or set up another Workbook to do so.

6. WkbkB's Macros use the Application .New and .Open Events to disallow
additonal Workbooks to be open in the same Excel Session.

7. WkbkB's Macros disable all occurences of the Copy Controls as well as the
Ctrl-C Key.

That's all there is to it! Easy, huh? Not! Heh, I dare anyone to find
another exception using just Excel's published features.

 

[Susan]

dave is very serious about his definitive statement.

try searching the newsgroup for "security" or "protect workbook" &
you'll find lots of discussions about what has been tried, and
apparently how easy it is for a determined hacker to break in.

granted, if you're dealing with the "average" user who wouldn't even
know where to BEGIN to look for code, you're probably safe. but dave
is warning you about relying on excel's "security" to protect
proprietary or confidential information.

you said "Hidden Columns contain info that are restricted even to
authorized users of the Workbook." if those authorized users WANT to
get in, they will be able to find out how.

& on a side note, if you're going to use the newsgroup to ask
questions & learn about stuff (which is why most of us are here),
isn't it kind of self-defeating to verbally dismiss & bash the person
who answered you? the "guru's" who answer most of the questions are
spending their personal time & efforts to help.
not kidding.
susan

 

[Dave Peterson]

I'm just a user who hangs around in the newsgroups.

The password to open a workbook is more difficult to break--but there is
software available that will do this for you--and it's not too expensive even
for the average person.

http://www.lostpassword.com

There are techniques that have been posted that will break your project's (VBA)
in a matter of seconds--they just remove the password--not try to guess it.

If you rely on a macro for protection, then macros can be disabled and your
protection is gone. I just change my security level to high (or medium and
answer a prompt)--or hold down the shift key to disable
auto_open/workbook_open. But allow the other code to run.

And after the project is unprotected, I can open (with macros disabled), change
your code and reopen so I can get by that protection.

As for workbook and worksheet (tools|Protection options), visit JE McGimpsey's
site:
http://mcgimpsey.com/excel/removepwords.html

====
And I like this one...

Change a setting first:
Tools|Options|Transitions Tab|Check Transition navigation keys

Start a new worksheet
unlock A1 (although this cell could be in a different workbook, too)
Put some "sensitive" data in columns B and hide those columns
Protect the worksheet
Type the = sign in A1 (to start a formula)
move the cursor to the right or down
Look at what you see!

You could try stopping the selection in that worksheet, but that unlocked cell
could be in a brand new workbook (opened before I open your workbook that stops
File|new)--or any old unlocked cell in your workbook.

======
So if you want to put sensitive data in an excel workbook, don't be surprised
if, er, when(!) it gets out.