Another Happy Customer
Another book for the shelves. Another solution for a reader. I thought this one was interesting enough to make it worth passing on. So, here goes.
Kathy was having trouble with the Excel export that's demonstrated here and discussed here and here. Basically it's a form that contains a URL of a view. When the form is opened it launches Excel, which then loads the data from the view, via the URL.
The problem was that Kathy's database doesn't allow Anonymous access. This is a problem for Excel as it's not authenticated with Domino, whereas the browser which launched it usually is. When Excel tries to retrieve the data it does so as user Anonymous, who has no access. Symptoms then include a login prompt in Excel or simply the "No documents found" message (if there is some public access permission given).
The solution I came up with might be a little controversial to some and not one I'd normally condone, but I think it works perfectly well for this situation. To give Excel access to the view I added the username and password of a special user to the URL. Excel now opens the following URL once launched:
http://excelreader:pass123@www.domain.com/reports.nsf/ExportData?OpenViewA special user called Excel Reader/Company needs adding to the Domino Directory with a short/user name of excelreader and an internet password of "pass123". Their sole purpose is to provide Excel access to the view. All you need to do is add this user to the database's ACL as a basic Reader.
What are the security risks for this? None really. There's obviously the risk of users seeing this URL, although this is unlikely as it will never store in the browser's history, as it's Excel that actually opens it. Even if they did see these credentials, they presumably have greater access than this user to start with. There's also the risk of third parties snooping the password from the URL. Maybe. But they can also sniff the password of actual users, which, although not a part of any URL, are still sent as plain text through the ether.
This was half of the solution I provided. The other half was an interesting one (honest) and I'll share it on Monday. Have nice weekends.
Skip to the 
You can subscribe to an individual
Well, Kathy won't be such a happy customer anymore when she finds out that your solution doesn't work in correctly patched versions of Internet Explorer.
SUMMARY
A security update is available that removes support for handling user names and passwords in HTTP and HTTP with Secure Sockets Layer (SSL) or HTTPS URLs in Microsoft Internet Explorer. The following URL syntax is no longer supported in Internet Explorer or in Windows Explorer after you install the MS04-004 Cumulative Security Update for Internet Explorer (832894): http(s)://username:password@server/resource.ext
The link below points to the Knowledge Base article:
{Link}
This patch dates back to april last year and I would have thought that every web developer knew about this by now because of the great impact it had.
Do I get a book now? Sorry Jake, just kidding...
I would advise against it but you can use this syntax if you are using session based authentication. Even with patched IE versions
http://<hostname>/names.nsf?login&username=<username>&password=<password>redirectto=<targetdb>
Why not call an agent that can be run anonymously. Set the agent to run with the rights of someone trusted, rather than the person invoking the agent. Your agent could then do print statements like
print |<html xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:x="urn:schemas-microsoft-com:office:excel" xmlns="http://www.w3.org/TR/REC-html40">|
print |<table>|
etc, etc. If you simply want to iterate over the docs in a view, it's a fairly simple loop for the TD tags. Also, I think there's a 1000 line limit on iqy files which you avoid with the agent.
If you store the url to the agent in excel, like an iqy file, the spreadsheet will update itself whenever you open it.
Henk. You had me worried for a second there. Thinking about it now I knew about this change in IE. In fact it's on my machine. My IE is about as up-to-date as you can get. Why does it work then? Well, because Excel is reading the URL and it ain't IE. Not even an embedded IE if I'm right, it's just a URL fetch.
So, yes, it does work. Phew. I can cancel the call to Amazon returns dept now ;-)
Steve. That is what Kathy is/was doing after she'd given up on this method. Agents can do it and hers worked perfectly. However, like me, I suspect she'd rather do without an agent where a view will do. Wouldn't we all?
Hi!
I've been searching your website for a contact email...I'm just not that smart. :) Do you have a contact email for Jim? Can you email me with it or have hi email me? Thanks so much!
It's under the Contact link on the "navigator" panel.
Jim? Jim Bobble?
I have had a lot of success with customers using the Jakarta POI libraries. This enables you to create the entire Excel file on the server then stream it to the browser.
{Link}
This also allows you to read Excel files to import the contents into a database.
Hope this helps,
Brendon.
That solution concerns me. What else can the Excel user access from the database? Just the view, or other elements as well? Surely if the database is not open for anonymous access, that means that data contained therein is meant to be private. Snooped or not, this seems like a huge risk to take with the customer data.
Would some form of DHTML/Javascript solution work better? ie. get the data into the browser (embed the view to produce js statements or a data island), and then launch Excel from there, passing the data over. That way, your seperating Excels use of the data from the server.
Dave. It's not normally something I'd suggest, but, the more I think about it, the less I can see wrong with it.
The "Excel Reader" user is just a Reader with no create rights. Presumably the user who opens Excel already has at least the level of access and then some. What's the danger? That the user uses these credentials to login with? Why would they? The user is blissfully unaware that a different user's details has been used anyway. I really can't see the problem with it.
It's not that you've created another login that's the issue, but that you've provided access to secure data through an insecure mechanism. If your worried about password sniffing, what's to stop someone sniffing the password from the URL and then accessing that data when they like. And does that login allow access to other data that you weren't planning on. Or could an admin inadvertantly give that user greater rights than you planned because they don't understand the implications. Anything that circumvents the normal security model seems to me like asking for trouble.
But there's no more danger of the Excel user's password being sniffed than that of the *real* user.