Protected Excel workbook -> really protected? 1

[flaviooooo]

Hi,

one of our salespeople wants to send an excelsheet (Excel 97) containing production data to one of her customers. She asked me to protect the file. One of the sheets needs to be hidden. On the other sheets I need to hide the formulas...

Now I've hidden the sheet, i've formatted each cell to be hidden (so that the formulas don't show) and i've protected each sheet separately. Then to top things off I've protected the entire workbook so that the customer can't simply unhide the hidden sheet...

Now my question: Is this really protected and secure?? I can imagine that there are hundreds of free and easy to use tools that can hack and crack this security in a minute. But I need your opinion on this one. Is it safe to send this out?

 

[xlbo]

Nope - you must've made the file password protected for read/write access I imagine....to get past this, all one has to do is save the file under a different name and tocrack worksheet level protection is real easy - I've never seen one take > 2 minutes to crack. Essentially, excel is not particularly secure

To make it harder, run this for the sheet that needs to be hidden....

sheets("Sheet Name&quot.visible = xlveryhidden

This makes it so that the sheet cannot be unhidden without code and to be honest, if they know how to unhide a "very hidden" sheet, they'll know enough to get round the rest of the protection

Rgds, Geoff
[blue]Si hoc signum legere potes, operis boni in rebus Latinus alacribus et fructuosis potiri potes![/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244

 

[flaviooooo]

Hmm, well since this contains some of our 'company secrets' it's probably not a good idea to send it this way then

I've thought about creating a access MDE to do this, but my collegue says access can't work like a spreadsheet correctly, like excel can...

What's your opinion on this?

 

[EntilzaSte]

Obvious question, why not produce it as a snapshot file?

If they need to access the cells, then its like xlbo said, it wont matter how much protection you put on it, if they want to get in they will...

Steve

 

[xlbo]

It all depends on how they need to use the spreadsheet really. If they just need to view the info, you could save it as HTML - then they'd just get the viewing ability. If they need to be able to enter data and see how different variables affect the data, you're gonna have to keep it in excel - and that gives security issues. Basically, I've never seen an excel file I couldn't hack

Rgds, Geoff
[blue]Si hoc signum legere potes, operis boni in rebus Latinus alacribus et fructuosis potiri potes![/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244

 

[rdodge]

From what I understand, Access is tougher to break, but it's still not really all that tough to hack. If you want to send a report of some form, but one thathas only values, what may be your best suggestion is to use VBA in Excel to take the results from your own file and have it recorded into a completely separate file that would be sent to the customer.

Example:

I have a series of workbooks for the different machine centers to store their archived statistical data and other information, so as it's readily available for the supervisors to see. From these various workbooks, I have a centralized VBA code that processes the data and records the results into a file called "Production Charts.xls". This "Production Charts.xls" file is then available for other people to see. At one point of time, I use to use formulae, but given the various issues that I have ran into with using External Link Formulae and it has proven to me that these external link formulae weren't too stable for various reasons, some of which were documented by M$ themselves, I no longer use external link formulae.

Your collegue are probably refering to the fact that Access isn't as easy to work with, and it will take some technical knowledge to work with Access.

Ronald R. Dodge, Jr.
Production Statistician
Master MOUS 2000
When the going gets tough, the tough gets going.

 

[SQLBI]

Geoff,

Love the xlVeryHidden!! i normally use .Visible = True/False
but won't anymore.

Have a well deserved star.


Leigh Moore
Solutions 4 MS Office Ltd

http://www.solutions4msoffice.co.uk

 

[SQLBI]

Whilst we're on the subject, is a protected VBA project as vulnerable as a protected worksheet?


Leigh Moore
Solutions 4 MS Office Ltd

http://www.solutions4msoffice.co.uk

 

[xlbo]

Leigh - not AS vulnerable
Worksheets can be unprotected just using code within excel. For workBOOK and project passwords, a utility is generally needed but as you can purchase these for ~£25, it's not really gonna keep a determined hacker out. For general users tho, it should be ok.

With xlveryhidden, just remember that you can't MANUALLY set it to visible - has to be done via code

Rgds, Geoff
[blue]Si hoc signum legere potes, operis boni in rebus Latinus alacribus et fructuosis potiri potes![/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244

 

[rdodge]

There is one exception to this having to be set in code. The following steps could be done to make the see visible:

Go into VB Editor (Alt-F11)

Click on the Project Browser button, if the window is not open.

Click on the Worksheet Object under the VBA Projects (workbook object), and under Excel Objects.

If Properties window not open, click on the Properties button

Now, scroll down the list of properties, and the Visible property can be changed in there with the click of the mouse. I have used this property in one of my files.

Only way to protect this property from being changed this way, protect the VBA project, but as you may know, a determined hacker will buy one of the utility programs which will crack even the toughest password cause the security on VB Projects is very weak. Such utility programs are out there for like $60.00.

Ronald R. Dodge, Jr.
Production Statistician
Master MOUS 2000
When the going gets tough, the tough gets going.

 

[vaneagle]

Hi flaviooooo,

why don't you save the file separately, copy paste special values over the data, once your file is ready to be sent, delete the sheets/columns/rows of data that the receiving person should not have access or vision of.

This way they can hack the file all they want because the data is not there anyway!!

 

[DrBowes]

A few additional comments.

Workbook protection under tools, protection, protect workbook is also very easy, but locking for opening is not so easy. This is because the workbook and worksheet protections within the workbook use hash tables so can be unlocked with strings of As and Bs.

Next Question - Why do you want to send a spreadsheet containing the confidential info anyway? If it is because the bits you want them to see are all formulas simply copy the sheet, then use Edit-paste Special-Values to put the values into a new sheet to send them that.

 

[flaviooooo]

Hi,

the file I need to send needs to be dynamic... The customers can enter some values and based on the 'hidden' info, calculations need to be made.

For example: the customer enters that they need 2.000.000 pieces. Then the file will calculate the production price with our precalculation-values (which can't be seen), and based on that it can calculate a price for the pieces. That's the price our customer will see.

 

[EntilzaSte]

Flavioooo,

Any chance you could come up with a "non secret" number that would produce the same result?

OR what about putting it all on the company Internet site, then allow customers to enter their info, produce their results and they can print it off or save the document??

Otherwise, you do run the risk someone seeing data they shouldnt.

Steve

 

[KenWright]

If you put data into an Excel file, the second you send that file out, for all intents and purposes, that data instantly becomes PUBLIC data. Excel's security options are mainly for preventing unintended changes, and casual access to data. There are various levels of protection that will prevent most users being able to get at the data, but a lot of people that use these groups or the MS groups will be able to crack almost anything you do.

File passwords take a bit longer, as does VBA Project protection, but Workbook and Worksheet protection can often be cracked inside about 12 seconds by using the right piece of code.

If it's Company Confidential or Conmmercially sensitive, then don't put it in.

Regards
Ken................

----------------------------------------------------------------------------
Attitude - A little thing that makes a BIG difference
----------------------------------------------------------------------------

 

[flaviooooo]

OK, Excel is out of the picture to send this file now

But how do you guys feel about a Access MDE - file?
Is there any way to hack or crack this? View design, code etc?

Maybe that could be the way to go

 

[xlbo]

Access would be harder to crack but it's still possible - the ease of cracking will probably be dependant on how much you know about access. I don't know that much but if you can set up proper security, using group level access, it'll be pretty tricky to crack (but still not impossible)

Rgds, Geoff
[blue]Si hoc signum legere potes, operis boni in rebus Latinus alacribus et fructuosis potiri potes![/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244

 

[flaviooooo]

But an MDE file? I wouldn't add passwords etc...

I'd just let all calculations run in a module behind form.
The table fields would be renamed so no one could understand what all the numbers are about.

Is there any way they could get to the code behind the form?

 

[xlbo]

All you need to do is hold down shift whilst opening the file and all on_open code is disabled....

Rgds, Geoff
[blue]Si hoc signum legere potes, operis boni in rebus Latinus alacribus et fructuosis potiri potes![/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244

 

[DrBowes]

I thought all code was always hidden in an MDE.

Also you can run:

currentdb.allowbypasskey = false to disallow bypassing opening settings. Not sure if the syntax is exactly this, haven't done this for a while.