Workbook authentification

[Chris R. Lee]

Hi,

I work in a highly regulated industry, where people still do simple but long
and repetitive analytical chemical calculations with pocket calculators,
because of doubts about the validity of Excel spreadsheets. Excel is great
for rapidly setting up calculations, but it was never designed for security.

Sometimes we acquire data from balances or other instruments using supplied
proprietary software with DDE links, or else WinWedge (TAL) communications
software. In this situation you can't protect the worksheet that receives
data, though you can hide it.

You can take a limited amount of risk with security - for example you can
assume that users won't go to extreme lengths to hack your protections. In
my experience the biggest problem is people copying a secure workbook to a
different file, in order to do something slightly different; they tend to
mess up the formulas and the protection always disappears. In practice,
Excel would be OK if laboratory managers could have some indication that the
correct uncloned workbook has been used.

Would a reasonably secure solution be to place some kind of signature in a
hidden part of each worksheet, with an indication on a visible part that the
signature is correct? I'm thinking either of a checksum or a standard
electronic signature system. For reference, one way of making an electronic
signature is to place 2 big prime numbers in the hidden area and give the
result of their multiplication in a visible area.

I'd be grateful if anyone has working knowledge of this kind of protection,
together with references to reputable literature. There's plenty of stuff
about electronic signatures on the web, but when it comes to practical
solutions, you need some specialised knowledge and a traceable source for
the information. Ideally, there should be no more than a few lines of VBA,
because any sort of written code is very hard to validate.

Regards

 

[Harlan Grove]

...
...

Would a reasonably secure solution be to place some kind of signature in a
hidden part of each worksheet, with an indication on a visible part that the
signature is correct? I'm thinking either of a checksum or a standard
electronic signature system. For reference, one way of making an electronic
signature is to place 2 big prime numbers in the hidden area and give the
result of their multiplication in a visible area.

...

Like a hidden column or row? Easily unhidden, and if the worksheet isn't
protected, not too difficult to overwrite. Also, even easier to leave a digitial
signature as-is in a hidden range but fubar all formulas and data in visible
cells. Digital signatures *ONLY* work in software that provides unavoidable
built-in support for them. High-end e-mail and electronic commerce systems
provide this (if a user other than the original author saves a signed document,
the newly saved document carries the new user's digital signatire rather than
the author's). Excel doesn't, and the sort of event handler coding you'd need to
write to add it *ROBUSTLY* would be decidedly nontrivial.

As for checksums, you'd need to restrict them to cells containing formulas only,
but it's only *probable* (though VERY probable), not certain, that equal
checksums mean no changes.

If you're going to do this, checksums on all cells containing formulas -
including the addresses of the cells containing the formulas - would be the
ideal way to go. Better still would be comparing 'fixed' areas of user workbooks
to corresponding areas of read-only base files stored in locations that users
can't access.

. . . Ideally, there should be no more than a few lines of VBA,
because any sort of written code is very hard to validate.

Just like the source code to Excel itself would be nearly impossible to
validate, leaving you back where you started - how can you trust Excel to give
correct answers?