Great way to Hide Sensitive Data

[yankee428]

As we all know, Excel is not a secure environment by any means. If you want application security you need to step up to Access at a minimum , if not mySQL. So if you need to share a workbook with others, but don't want to give them rights to see the whole workbook, how do you hide certain data, yet still have access to it yourself.

There are tons of password hacks out there, so forget Excel Protection. And Hiding sheets is too easy to get around. You need to hide and lock down the hide.

1. create a workbook with two worksheets
2. create secret data table on sheet(2)
3. rename sheet(2) to a difficult to guess name (I'll use difficult!)
4. insert a combo box built from control toolbox on sheet(1) and give it the code from below(change parameters where needed)
5. in VBA, make difficult! xlVeryHidden
6. now password protect the VBA project

Users of the file now have read and lookup access to part of your data while you still hide the other part. The same could be done for data input using a textbox. Just make sure that you do not reference the name of the secret worksheet anywhere that is visible to the user.

Disclaimer: There is one work around that could be used to compromise this method, but as far as I can see, this is as good as it gets.

Private Sub ComboBox1_Change()
Dim lkupval As String
Dim tblarray As Variant
Dim rslt As String
On Error GoTo errorhandler
'!!!!!!!!!!!!!!!!!!!!!!SET THE FOLLOWING FOUR PARAMATERS ACCORDING TO YOUR SHEET!!!!!!!!!!!!!!!!!!!!
lkupval = Sheets(1).Range("AA1")
tblarray = Sheets(2).Range("A1:D4")
lkupval2 = "AA1"
rslt = "F4"
If lkupval = "" Then
    Sheets(1).Range(rslt) = ""
    ComboBox1.Value = ""
    Exit Sub
End If
Cells(4, 6) = Application.WorksheetFunction.VLookup(lkupval, tblarray, 3, False)
Exit Sub
errorhandler:
    MsgBox "Please Choose from the drop down list"
    ComboBox1.Value = ""
    Sheets(1).Range(lkupval2).Value = ""
    Sheets(1).Range("F4").Select
End Sub

 

[DRJ]

Unfortunately VBA protection is crap. No matter what protect scheme you try to use, someone can just open the workbook and get into the VBA project and do whatever they want.

 

[Legacy 5624]

To unhide VeryHidden sheets, is it not merely a matter of looping through all the sheets and setting the Visible property of each sheet to true?
(It is not necessary to know the name of the VeryHidden sheet.)

 

[yankee428]

DRJ, ??? When I lock the code to a project, how would someone be able to just get right into it? All the cracks out there are for Excel Passwords, not for VBA.

Pooh, You're right, that is the workaround, but that requires someone who knows at least some code and a pretty good understanding of Excel as opposed to any Joe user who goes to net and pulls down an Excel password *******.

 

[Legacy 5624]

Pooh, You're right, that is the workaround, but that requires someone who knows at least some code and a pretty good understanding of Excel as opposed to any Joe user who goes to net and pulls down an Excel password *******.

I don't think so. It's in VBE Help. Probably also in chapter 1 of most VBA books.

 

[Tom Urtis]

All the cracks out there are for Excel Passwords, not for VBA.

You can't be serious. You are probably well-intentioned but totally off base. Here are just a few of the many links for commercially sold software to get into the VBE:

http://www.lostpassword.com/vba.htm

http://www.tomdownload.com/utilities/security_encryption/advanced_vba_password_recovery.htm

http://www.gold-software.com/download210.html

http://www.softpile.com/Utilities/Password_Recovery/Review_37168_index.html


And it's not like the actual password needs to be recovered; several different combinations of the same password hash can be unkeyed.

The protection platform in Microsoft products is very weak. In its defense, Microsoft never claims to have reliable protection. As I always say, passwords are like the lock on your home's front door; its primary purpose is to keep your friends out. If someone really wants to get in, they will get in.

Here's some background on password protection if you (or anyone reading this) did not know.

When someone "password protects" a sheet in Excel (I know you were referring to the VBE but hold on), they generate a 16-bit 2-byte hash. When unprotecting the sheet, that value is compared to the hash. Excel allows for up to 255 password characters in its worksheet protection scheme. Keep in mind, since it is a case-sensitive scheme, there are over 60 acceptable characters, which translate into the multiple trillions of possibilities. Since the combination of possible passwords is much greater than the combination of possible hashes, many passwords might share the same hash.

In a much-popularized example, you can see this for yourself with the word "test". Protect a sheet with the word "test" (without the quotes). Now unprotect it with "zzyw".

The MD5 hash being used is a standard mixing algorithm, executed as follows:

- take the ASCII values of all characters
- shift left the first character 1 bit
- shift left the second 2 bits
- continue for quantity of characters up to 15 bits, with the higher bits rotated
- XOR those values
- XOR the count of characters
- XOR the constant 0xCE4B

As you probably know, XOR is a logical term associated with a mathematical compound statement, sort of an acronym for "exclusive or". In this case, statement "A" is the password value you type in. Statement "B" is the generated hash. The XOR operation returns TRUE when only one of its combinations is TRUE. Here's why that translates to more than one password value possible, seen in the context of a truth table:

A B Result XOR
T T FALSE
T F TRUE
F T TRUE
F F FALSE


The VBE password scheme is not much further advanced than the sheet protection scheme, and hence is crackable by brute force or "back door" methods which throw millions of multi-hash-matching combinations until a compatible match is made. What all this boils down to is, if you don't want your work stolen or cracked, don't publish it.

 

[yankee428]

Guys, I think you're missing a couple of points here.

1. Because it is so easy to hack Excel Passwords, that is why combining a password with a veryhiddensheet and not referencing the source sheet name is better than just simple Excel Passwords alone.

Yeah, it would be easy for us to get around, but I don't think many users know about veryhiddensheets AND for each worksheet statements AND password hacks.

2. My point was "this is as good as it gets in Excel"

 

[Tom Urtis]

Guys, I think you're missing a couple of points here.
1. Because it is so easy to hack Excel Passwords, that is why combining a password with a veryhiddensheet and not referencing the source sheet name is better than just simple Excel Passwords alone.

The name of the source sheet has absolutely no bearing or hindrance on password protection or password recovery. It is a total non-issue where security is concerned.

I don't think many users know about veryhiddensheets AND for each worksheet statements AND password hacks.

I'm not trying to sound rude here, but maybe this is the case in your orbit. In the wider Excel community, it is not exactly advanced expertise to know about VeryHidden sheets, loops for the Worksheet collection, and the commercially sold software programs, one of which is an advertised banner displayed prominently on this very page from LostPassword.com. You are counting on people being unaware of basic Excel features.

2. My point was "this is as good as it gets in Excel"

Debatable, depending on what you mean by "in Excel". The next step might be addins via COM, VB or C++ apps. Not foolproof either, musically analagous to modern digital quality versus Microsoft's security Victrola. This link collaborated on by me, dk, Ekim, and JPG last year gave some tips for where to start.
http://216.92.17.166/board2/viewtopic.php?t=71236&highlight=security

 

[Nimrod]

Hello yankee428

Your sol'n is well intentioned but is really not going to stop people who really want to see the data. A few weeks ago I posted about a similiar situation. I had provided a xls book with hidden data and a locked VBE to a company. One of the employees was curious about where the data was so he took it home and had his 12 year old son hack it open in one evening. The son didn't know much about excel but he knew how to use the Internet ! :wink:

So I really have to agree with Tom U. in that the sol'n really isn't going to go very far in preventing the curious from figuring it out.

In other words I don't agree that it's "Great way to Hide Sensitive Data" .... however it's a pretty good way to prevent very basic , honest , un-curious users from seeing source data ".... :wink: .... and has always had a place in my quiver of sol'ns

PLEASE NOTE:
You’re just asking to be challenged when you post that you got a “great solution” for anything. This site has got some pretty sharp minds e.g. Tom U.

 

[yankee428]

lol ok