« ssh keys on Mac OS X Server: the fuller, longer, better instructions | Main | CrashPlan Pro Server restore metrics, estimates »

Scary Excel "Share Workbook" feature behavior

Nota bene:This article provides a solution for Leopard Server (10.5). Snow Leopard Server (10.6) introduces a kink that makes this solution a better fix for the problem described in this article.

Update: Apple Enterprise Support suggested a modification to the /etc/smb.conf file that seems to have addressed this problem. Citing this entry in the Samba man page the suggestion is to append "nt acl support = no" at the very end of this conf file. Ours now looks like this:

; Site-specific parameters can be added below this comment.   
; END required configuration.

[global]
   use kerberos keytab = yes
   realm = stanford.edu
   veto files = /Thumbs.db/
   veto files = /.DS_Store/
   veto files = /.TemporaryItems/
   log level = 1
   nt acl support = no

You can also find more information by reading the authoritative Samba3-HOWTO.pdf and review sections 16.4, 16.5.3-5 and 43.5 as they pertain to this configuration option.


Original posting:

Here's something I've been chewing on that's alarming. There seems to be an exceptional issue with Microsoft Excel's "Share Workbook" feature while serving a spreadsheet on a Mac OS X Server via both AFP and CIFS.

In short, if a couple of users collaborate simultaneously on a spreadsheet, it will radically (and dangerously) alter your ACLs and POSIX-style permissions, irrespective of the parent folder's settings.

Here's how to reproduce this. Much of this is arbitrary for the sake of illustration, but it should be reproducible with other variables.

Step 1: Set up the shares, groups, perms

In Server Admin.app, create a network sharepoint called Files inside the /Groups directory (turn off the default sharing of the /Groups directory first—you shouldn't share with shares). In Server Admin, enable the directory to be available via AFP and SMB. For SMB, do not enable oplocks or strict locking, and choose "Inherit permissions from parent."

In Workgroup Manager.app, create a group called Bankers and add a few user entities. Let's say, Binky, Sammy and Picky.

Give the Bankers group full control of the Files sharepoint using an access control list (ACL). And for the POSIX-style settings, set the the ownership to be root:admin and set the the permissions set to 700. For example:

drwx------+ 2 root admin 68 Apr 16 21:18 Files

Picture1.png

It should look like this first image.

Inside the Files sharepoint, create a folder called Reports using Server Admin. It should automatically inherit the same ownership, permissions and ACLs as the parent folder.

Step 2: Let's Share This Workbook to users

Let's ask Binky to connect to the Files sharepoint. Since he's in the Bankers group, he mounts the share on his Mac OS X 10.5.6 workstation. He creates an Excel spreadsheet using Excel 2008. We'll call that file Budget.xlsx.

While the spreadsheet is still open, Sammy, a Windows XP user with Excel 2007, also connects to the Files share and attempts to open and modify the file; as expected, he's thwarted. The file is in use. This is a good thing. But Microsoft has a better idea.

If you haven't used the "Share Workbook" feature, it's whole purpose is to allow groups of users to edit the same spreadsheet. Each member can open, modify, save and otherwise collaborate in real time, simultaneously. I never even knew that existed, to tell the truth.

So let's ask Binky to enable the Share Workbook feature from the Tools menu. Sammy can now open and modify the spreadsheet properly. Both Binky and Sammy are able to work together on this spreadsheet.

Now let's get Picky in on the action. From his Windows XP machine, he successfully attempts to open the budget.xlsx file and happily makes changes. The three users, Sammy, Binky and Picky, are now working simultaneously on the same file. The banking department is happy.

Step 3-A: Let's look closer (or, Where things get scary)

Unbeknown to the users, each time any of the users saves the file, the list of entities in the access control list begins to morph. Let's look at the second image, which illustrates what I'm talking about.

Picture2.png

That's definitely not cool. You see how Server Admin shows rows and rows of the Bankers entity? That's not supposed to be there. There are now multiple Bankers entities in the list. Moreover, the POSIX permissions have now been set to 000. This is not a visual anomaly in Server Admin.

Let's have all the users quit Excel and we'll delete the budget.xlsx file.

Step 3-B: More changes!

Now let's change it so that the sharepoint Files has no ACLs at all. The owner and group will be root:bankers while the POSIX permissions are 770. Let's make a daughter folder called something like Stats. It, too, is root:bankers and 770. All of our three users should be able to connect to Files share and work on documents in the Stats folder.

Let's ask Picky, our Windows XP user, to create a new spreadsheet FY09.xlsx and save it in the the Stats folder. Picky will also enable the Share Workbook feature. Let's ask Sammy to open it. Ça marche —that works. And now Binky, the Mac user, can also open and edit the file, too. Again, all three are working happily.

But let's go back and look at the file again. Now we see that there is now an Access Control List with the Bankers group as an entity! And the POSIX settings have been changed to 700 from 770. Remember we started this second example with no ACLs and 770 in the parent folders. See the third image.

Picture6.png

Step 3-C: Another example of random behavior!

Let's ask everyone to close the FY09.xlsx file. This time, Binky, the Mac user, will create a new file FY10.xlsx and store it in the same Stats directory; he'll also enable the Share Workbook feature. Now when Picky, the PC user, attempts to open and edit it, he's alerted that the file is read only! The permissions of the Stats directory remain 770 with root:bankers and no ACLs. The new file has inherited that mode. He should be able to collaborate. Note the permissions, ACLs and ownership hasn't changed. Here, the Share Workbook feature doesn't work.

Picture7.png

Postscript

This, unfortunately, persists even after users disconnect from the server, even if they reboot their workstations. I did not reboot the server in between examples. All the accounts are local to the server. None of the workstations are bound to any directories, either. The /etc/smb.conf file has not been modified. The server is freshly built with no third party software; it's running Mac OS X Server 10.5.6 and the clients are current with their patches and updates. This is about as basic, fresh-from-the-factory as you can get.

TrackBack

TrackBack URL for this entry:
http://www.stanford.edu/group/macosxsig/cgi-bin/mt/mt-tb.cgi/57

Comments (6)

Hello, thanks for detailing the info. We had the same problem our Mac OS 10.5.6. The above edit didn't help us but we discovered that our Mac server wasn't bound (binded?) to the PC Active Directory. We did that and the problem was fixed. -Derryl

Noah Abrahamson Author Profile Page:

This is now an official Apple KB article. See the http://support.apple.com/kb/TS2713 page.

Great stuff- and try using Gemini - it goes beyond the OS means for cross platform, you need to checkout Microsoft site because its name is changed now with Office 2010. And Frankly I don't know it is for Mac OR NOT.
It a plugin (high level) to reteive millions row to analyze.
Microsoft www.sqlservermanagementstudio.net

But anyway- this was a great stuff for mac users with Microsoft Office.

Niels Author Profile Page:

Thank you very much for this useful information!

Niels Author Profile Page:

Hi Noah, we’re upgrading to Office 2011 next month. I am very glad to see the return of the Visual Basic macro language.

I had the same problem with the multiple entities in the list. Do you know if Microsoft has solved this issue with the "Share Workbook" feature in Office 2011?

If you ever need any help with the Excel macro language, let me know. Thanks again.

Thank you for this post. I was having a similar issue with Excel & for a minute I thought my computer was being hacked! I appreciate the information here, it really helped me out. -RSF

Post a comment

(If you haven't left a comment here before, you may need to be approved by the site owner before your comment will appear. Until then, it won't appear on the entry. Thanks for waiting.)


About

This page contains a single entry from the blog posted on April 16, 2009 8:37 PM.

The previous post in this blog was ssh keys on Mac OS X Server: the fuller, longer, better instructions.

The next post in this blog is CrashPlan Pro Server restore metrics, estimates.

Many more can be found on the main index page or by looking through the archives.

Creative Commons License
This weblog is licensed under a Creative Commons License.
Traffic analyzed by Google Analytics. Site powered by Movable Type 4.32-en