I'm planning on creating a BCS layer which will be used in a multi tenant scenario. The plan is as follows: multiple clients will all get a SharePoint site. They will sync data from their ERP into a central database, which is then queried by BCS. The database is setup to be multitenant, so all records have a key which includes the tenant Id. I need to setup the BCS so that it only reads data with the correct tenantId, and never allows users to see data from another tenant.
What I have thought of is deploying the BCS model as an assembly and using the code to sniff the context of the call and use that contact to query the correct tenant. But I was wondering if this is fool proof. What would happen when for instance Word connects to the SharePoint environment and query the BCS?
Another approach would be to deploy the BCS sandboxed, so each site gets it's own BCS definition instead. In that case I can probably insert the tenant info into the definition and handle it that way. The only thing to look out for is that a user can never adapt the definition and paste in another id (which would be quite hard when using a guid).
Has anyone ever done this? What approach (maybe there are other ones even better) would you choose and why?
Edit: I've gone with the .net assembly approach to make sure all content is filtered based upon the current SPContext. But it wasn't untill first deployment that I found out that a partitioned BCS app doesn't allow DotNetAssembly type LOBsystems. You can import it fine, and create a new external list; but it won't load any data. Don't know why, don't know how to circumvent this. Either way, I'm kind of screwed now since this means my whole idea isn't feasible at all.
Edit 2 (and I'll create a blog post for this); you can work around the partitioned BCS approach by creating a non partitioned BCS app and including that in your proxy group. Apparantly there are no restrictions on calling a non-partitioned service app from a site subscription bound site (at least not for BCS).