Spreadsheet security

Do you think spreadsheets should be more or less secure?

I know a few people who think that increased spreadsheet security would be a good thing. I’m not so sure.

Personally, I think I would like to see:

  • continue workbook open passwords, and encrypted workbooks
  • remove workbook structure protection
  • remove worksheet protection
  • remove VBA protection
  • increase macros security options
  • enable some limited (‘safe’) macro features even with high security
  • maybe be able to digitally sign worksheet stuff to warn of changes

I think increasing security would give the impression that spreadsheets are appropriate for sensitive production systems. I don’t think they are in most cases. And I think if features were added so they were, then the features that make them great for rapid prototyping would suffer.

It’s easy to get improved security – just use a platform that provides it, eg VB6 and a grid control, or .net (lets not discuss reflector!). And these are great migration paths once you have worked out how everything should work in a nice quick easy open spreadsheet.

In a sentence, my preferred solution would be: optimise spreadsheets on ease of use, make migration to more robust/secure/scalable/etc systems easier. What would be your one sentence? and why?

Whenever I see worksheet protection in Excel

  • a. I remove it
  • b. I think spreadsheet abuse. If it needs protecting then Excel isn’t the right tool.

What do you think?

Where do you sit on the ease of use v security lock down continuum?

Can you see a way to have both with no conflicts?

cheers

Simon

Advertisements

28 Responses to “Spreadsheet security”

  1. Biggus Dickus Says:

    “”Whenever I see worksheet protection in Excel

    a. I remove it
    b. I think spreadsheet abuse. If it needs protecting then Excel isn’t the right tool. ”

    ? Are you sure about that Simon?

    Obviously you use spreadsheets with a different class of user than I do.

    To me Protection is the most important functionality in Excel. Without the option to password protect the fomulas and the user’s right to insert and delete rows and columns etc. I would not use spreadsheets at all – just the opposite.

    Go figure ;-)

    Dick

  2. Marcus Says:

    Slightly, OT but I wanted to jump back to a discussion about production environments we had previously and how PC’s were locked down.

    I wanted to get an idea of how many Excel spreadsheets and Access databases were in a particular environment. To do this I use a DOS batch file which writes a directory & file listing to a text file using this DOS command:

    DIR “C:\*.xl?” /S > “C:\Spreadsheets.txt”

    You can change the file extension to search for to whatever you need. I also find this much faster than using the Win API.

    I then import the text file into a database for analysis. I also get a profile of the age and size of the spreadsheets.

    Here’s the interesting thing I found (I’m getting to the point now) – Using a DOS command, I retrieved details about files which I don’t even have access to via Windows Explorer. Could I delete files using the same approach? I wouldn’t want to try. Note that I don’t even have access to the command prompt.

  3. Harlan Grove Says:

    There’s a major distinction between protection in the sense of idiotproofing and security in the sense of hiding IP. Worksheet protection WITHOUT passwords is VERY USEFUL. I don’t know about the rest of you, but I’ve saved man-years of support calls by judicious use of worksheet protection.

    Tangent: Lotus 123 file locking protects all worksheets and prevents worksheet insertion/deletion in a single operation. MUCH MORE EFFICIENT!

    As for macros/VBA, with all the talk about VBA going away, why hasn’t XLM gone away? Word ditched WordBasic many versions ago. This is actually one place where I believe MSFT needs to divide things up. Information-only XLM functions should become standard worksheet functions, and control flow and potential nasties like CALL should be deprecated.

    Next, parametrized defined names would be nice. This would be pure syntactic sugar – unnecessary in the strict sense, but very handy. For example, COSD(x) defined as =COS(RADIANS(x)). There should be no security restrictions with these since their definitions could only be things that could appear in worksheet cell formulas. This would be similar to BASICA’s DEF FN.

    As for VBA, much greater granularity in the security model just ain’t gonna happen in VBA. Maybe in VSTA. Useful things would include udf-like environment lockdown, disabling OLE automation, disabling DLL access. This may be quite awkward since the Kill and Declare statements are part of the language proper rather than add-on objects, but maybe that’s not the case in the VSTA languages.

    As for signing workbooks or worksheets, how would that work? Ignore all cells containing constants? Ignore all unlocked cells? Sign selected ranges?

  4. Marcus Says:

    Okay, back on topic again…

    There’s two main areas I use Excel security.

    1) To prevent users from inadvertently ‘breaking’ something in the spreadsheet – thereby reducing the ‘oops’ factor.

    2) To prevent users from diddling (that’s the technical term) with the VBA code. Again this is mainly to prevent the spreadsheet from breaking but sometimes the client has a need that requires implied security (such as for segregation of duties) and doesn’t want a user being able to ‘manipulate’ the system.

    Biggus: “protect the formulas”

    Everyone has a different approach. I protect the formulas by removing them from the spreadsheet altogether (where practicable). Most calculations are done in a back-end database and only the results are presented in a spreadsheet (there are exceptions to this).

    I’ve developed models in the past where everything was locked down to prevent those darn users from breaking my model. I think I’ve mellowed a little and have allowed users to interact with the spreadsheet more freely.

    I’ve counter this freedom by:
    1) ensuring the spreadsheet can be returned to a ‘start state’ easily

    2) removing the delicate bits from the spreadsheet completely (out of sight, out of mind)

    Cheers – Marcus

  5. Harlan Grove Says:

    So Marcus can run batch files but has no access to command prompt consoles? Try running a batch file containing

    %COMSPEC% /k

    If that doesn’t work, how ’bout

    @echo off
    :loop
    set /p $$=%CD%^>
    %$$%
    goto loop

    Several of you have mentioned USB drives being disabled, but how about CD or DVD drives?

  6. Marcus Says:

    Hi Harlan,
    Thanks for the examples. Here are the results:

    For the first example (COMSPEC) I get the “The command prompt has been disabled by your administrator.” message.

    For the second example I get a command prompt. I’m in.

    Most of the machines here have a DVD unit but lack software to burn.

  7. Biggus Dickus Says:

    Marcus -“I’ve developed models in the past where everything was locked down to prevent those darn users from breaking my model. I think I’ve mellowed a little and have allowed users to interact with the spreadsheet more freely. ”

    It depends on the app and the user…. I do ALL types of spreadsheets and I have done it all the ways described here. I use the tool that fits the requirements and protection of sheets, workbooks and code are just natural to me in any app where the spreadsheet is going to be used by non-technical people (which is nearly all the time in my world ;-) )

    Dick

  8. Harlan Grove Says:

    Marcus, thanks for confirmation.

    With regard to CD/DVD drives, I didn’t mean burning – possibly taking files from your client, I meant bringing utility software with you, though I suppose one such utility could be a console mode CD/DVD burner.

  9. XL-Dennis Says:

    I try to apply the following rules:

    # If I need to protect any IP I use COM/.NET add-ins.

    # Unless the customer explicit require it I never protect any VBA solutions.

    # For the end user UI I usually password protect them (without any password) just to avoid the “oops” situations.

    In general I also got an written agreement with customers which stipulate the costs involved if any solution needs to be restored due to any missue on clients side.

    Kind regards,
    Dennis

  10. Will Riley Says:

    Harlan,

    This tells you something about our IT people…..

    In theory, we have no access to CD/DVD external drives. True, we cannot see them in Windows Explorer.

    But…. Start>Run D:\ or E:\ will open the drives in Windows Explorer. I guess I may tell the IT dept before I leave!

  11. Ken Puls Says:

    Hi guys,

    I make judidicous use of the “Protect Worksheet” ability with no password to prevent the “oops” factor as well.

    As we’re all keenly aware, however, any measure of “security” in Excel is pretty much just a joke. In a recent seminar, I had an entire class of accountants floored when I ripped away their worksheet protection.

    I, for one, would like to see Excel’s lack of security change. A strong Workbook Open password (not one that can be cracked with a $29.95 software program), and a strong VBE project password would be nice. Anything else I don’t think is strictly necessary.

    Like it or not, much financial work is done inside spreadsheets in the real world, and will continue to be so. The fact is that users are applying these features now as “security”, not knowing their weaknesses. To tell them that they’re foolish for thinking that it is secruity isn’t the answer. Giving them real security to actually lock down their files is. It won’t suffer any more abuse than it already does as far as the users, and could actually cut down some of the risks that the users are taking unknowingly.

    Actually, I am curious…. Excel 2007 has an encryption feature. Has it been broken yet, or is it still considered secrure? Anyone know?

  12. Harlan Grove Says:

    Will,

    These are all fine examples of ‘security’ a la Microsoft. MSFT tells sysadmins to protect their systems by making it difficult for users to run CMD.EXE or EXPLORER.EXE. But the nasty little fact of Windows is that it just can’t run if users don’t have access to and run permission for those two programs.

    Will MSFT itself address this by separating EXPLORER the desktop shell and EXPLORER the file manager into separate programs? How about removing the CMD.EXE dependency and letting Windows start without needing the program pointed to by COMSPEC being executable by all users?

    Not likely. MSFT prefers telling customers which fingers to stick into which holes in the security dike rather than pouring concrete for new, much sounder dikes.

    FWIW, my company’s Terminal Server tries to hide it’s C: drive so poor, stupid users don’t save files on them or muck other things up. And I can’t run Explorer directly by launching it from a Shell command in Excel, but I can display Excel’s File > Open dialog, select a drive or directory, right click and choose Explore. Voila, an Explorer window. While it doesn’t show the C: drive, I click into the address bar, enter C:, and Voila, I’m exploring the C: drive.

    Simon mentioned Linux Live CDs in another post. What makes those work is Linux being able to work from READ-ONLY media while creating a RAM disk for /var, /tmp and /home (last for user files). Microsoft needs to figure out how to rewrite Windows to run from READ-ONLY media with system status information (/var), temporary files (/tmp) and user home directories (/home = C:\Documents and Settings) on SEPARATE DRIVES (partitions). Only when they have will Windows BEGIN to approach the level of security that other OSs already provide. Ain’t gonna happen soon because it’d require a different approach to handling the Registry.

    Anyway, if MSFT can’t figure out how to write a secure OS, why would anyone expect their application software to be any better at security?

  13. Stephane Rodriguez Says:

    “Excel 2007 has an encryption feature. Has it been broken yet, or is it still considered secrure? Anyone know?”

    Same algorithm than previous versions, slightly different file container. The irony is that protected spreadsheets are OLE documents (so much for the new ZIP story…). Those $29.95 password-cracking sharewares should support it any day now.

  14. Simon Says:

    Looks like I’m in the minority then.
    I’m not saying I don’t use the odd bit of protection, but I do take it as an indicator of bigger issues.
    cheers
    Simon

  15. sgbhide Says:

    I would just like to add that no software is secure… it can only be protected.
    Same like the lock on your car…. how secure is that…. Well depends…on the thief…

    Having said that I think its a real crime that MS has such a weak encryption for Worksheet, Workbook and VBA Passwords.

    The file open password is as seucre as the length….. and I belive from Excel 97 onwards the only way to break it is through brute force….Which is fair.

    What I cant understand is why didtn MS use the same encryption for Sheet, Workbook and VBA passwords

    Sam

  16. Stephane Rodriguez Says:

    Sam,

    You are absolutely right.

    And if you want to laugh your ass off, just unzip a XLSX spreadsheet in which one of the worksheet tabs is password-protected. You’ll find a “sheetProtection” XML element and a hash of the password. Just empty the XML “password” attribute, or even remove that XML attribute. Then rezip the file. Voilà, no more protection…

    At least in the binary file format, you had to use specialized software to work around that…

    Either Microsoft are dumb, or they are indirectly encouraging the use of server-centric spreadsheets where you can filter out stuff based on the user profile. Server licenses are big money (lock-in), and hugely strategic for Microsoft in the long run (i.e. BI).

  17. Stephane Rodriguez Says:

    The same logic applies to the “very hidden” worksheets farce.

  18. Ralph Says:

    The way I look at it is that a proper security environment is a combination of a lock (protection) and a burglar alarm (intruder detection). There is no point in just building bigger locks – someone will always find the key – or be given it. So you need to know when someone has used the key to change the content – they may be authorised to do so or they may not but you still need to know. Of course we are a vendor who takes this approach so I guess my view is partisan!

  19. Biggus Dickus Says:

    I must be an old codger but I am not quite as freaked out by the potential for someone who really wants to to hack my models.

    I do however believe in the theory of “good-enough” security. “Good enough” to prevent users from stomping on critical formuale and structure in my model and “good enough” to keep the standard internal “Power user” guy from getting in and screwing things up to prove their manhood.

    If someone really did hack the spreadsheet and broke it, I would very easily be able to determine this with a little research – and I would then wash my hands of the application and let the client know what happened so they could take appropriate action.

    Simon: “”I’m not saying I don’t use the odd bit of protection, but I do take it as an indicator of bigger issues.” Like?

  20. Patrick O'Beirne Says:

    Re: “What I cant understand is why didtn MS use the same encryption for Sheet, Workbook and VBA passwords”

    Because the code has to be unscrambled to be executed. If the key is stored in the file, it’s insecure, if it’s not, it would have to be asked for every time the file is opened … or from a cookie or authentication server.

    AFAIK the only way to protect code is to compile it as Xl-Dennis says.

    I raised that question on the Eusprig yahoogroup but not as much response there as Simon got here. But then I didn’t take the contrarian stance he did!

    Some users are like the ones who need C: protection and to be reminded not to open email attachments. Others are smarter but only ever took the time to learn Excel as well as their very expensive day job.

    P.

  21. Biggus Dickus Says:

    Stephane:

    “The same logic applies to the “very hidden” worksheets farce.”

    In what way is this a farce? The ability to make it so that certain worksheets don’t show up if the user goes to unhide them through the UI has value to me.

    Can you explain that a bit..?

    Thanx
    Dick

  22. Stephane Rodriguez Says:

    It’s a farce in the sense anyone can google this thing and find how to work around it without being a developer and without having to buy a third-party software.

  23. Harlan Grove Says:

    Interesting division of opinion: security is worthless unless it’s absolute vs my dumb users only need protection against themselves. I suppose the dividing issue may whether you’re trying to protect your own IP or your employer’s (including the sense that whatever IP we in-h

  24. Biggus Dickus Says:

    Stephane:

    “It’s a farce in the sense anyone can google this thing and find how to work around it without being a developer and without having to buy a third-party software.””

    I see – well that’s pretty much standard for anything these days ;-).

    I’d love to see a tighter worksheet protection but what they have is better than a kick in the butt with a frozen boot (as we Canadians put it). Not using Excel Protection because it isn’t airtight is like saying “I’m not going to go outside because I might get hit by a meteorite!”

    ….. You gotta accept some risks in life methinks ;-) Some protection is better than not using the technology at all.

  25. Stephane Rodriguez Says:

    In fact, this problem is solved with credential-based views. Those credentials are set by the IT department through group policy. The essentially disconnected Excel does not do that, but BI software does. Excel is heading towards this however, as I was explaining in a comment above (you can create partial views when a spreadsheet is published in the repository, in Excel 2007 + Sharepoint).

  26. Harlan Grove Says:

    Can Excel 2007 & Sharepoint really create secured views? Is only part of the workbook opened in Excel 2007? Does Excel prevent saving the view as a local file? Or do you mean views from Excel Services into browsers?

    There’s a difference between securing IP that end-users would normally receive as read-only information (which could be copied/pasted as the basis for derivative work) and protecting naive users from screwing up interactive models.

    From my perspective, very few spreadsheet models are anything like BI. Most of the ones I’ve seen require substantial user entry. For that sort of model current worksheet protection is adequate and useful (though it’s high time MSFT provided a menu command to protect either all worksheets or all selected worksheets in a *SINGLE* operation).

    Anyway, if most of the data on which a given model would be performing calculations came from user entry, Sharepoint would be irrelevant. OTOH, if most of the data came from the company’s central data stores, BI would probably make much more sense than spreadsheets.

  27. Simon Says:

    Dick
    The bigger issues I had in mind were:
    poor design often leaves critical formulas mixed up with areas users need to mess with. I prefer to put all important stuff out of the way on a separate sheet.

    Even bigger issue, I see s/s as more of a prototyping tool, trying to implement a sensitive production system in them is a significant risk (IMO). Of course they are an excellent interface into a system, but not robust enough for a whole (sensitive) system.

    cheers
    Smion

  28. Biggus Dickus Says:

    Simon:

    “poor design often leaves critical formulas mixed up with areas users need to mess with. I prefer to put all important stuff out of the way on a separate sheet.”

    Yes – I agree for the most part – I do that as well. It’s just that sometimes the user needs the feedback from what they type in “real-time” on-screen. I think it’s too limiting to have a hard and fast rule for design because it depends on the business requirement.

    “Even bigger issue, I see s/s as more of a prototyping tool, trying to implement a sensitive production system in them is a significant risk (IMO). Of course they are an excellent interface into a system, but not robust enough for a whole (sensitive) system.””

    I know that’s how IT looks at it, but I find Excel is a useful, cost-effective,flexible and reliable tool for Department apps that simply don’t have the IT Bandwidth or the project funds to justify a big solution.

    To me this is the sweet-spot for Excel and I hope we don’t write off Excel because it isn’t considered a legit Enterprise tool …… There’s a lot of business sitting on the table in Departments and medium and even small businesses that Excel is the perfect tool.

    Just my thoughts.

    Dick

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s


%d bloggers like this: