title: Multi-tenant db schema: Handling case where tenants need to share data
body: <p>I'm trying to create a schema for a new web application that will be used by multiple tenants. I've done multi-tenant designs in the past that used a schema per tenant, but in this new application the tenants will need to be able to extensively share access to data.</p>
  
  <p>Here's my scenario using a pseudo app:</p>
  
  <ul>
  <li>Company A signs up to 'Project Tracker' to track their software projects</li>
  <li>Company B also signs up to track their software projects </li>
  <li>Company A creates a new project with a bunch of work items in Project Tracker</li>
  <li>Company A then assigns some of these work items to users within Company B that will actually do the work (such as with a contractor scenario)</li>
  </ul>
  
  <p>And I also need to support another wrinkle:</p>
  
  <ul>
  <li>Client of the new software project also needs to be able to sign in and see project status even though they technically aren't a tenant</li>
  </ul>
  
  <p>I realize I could do this with separate schemas and federated users, but I'm trying to keep the experience simpler than that if possible. Since it's a web app, I want to use email addresses as the federation point so that Company A can just assign a work item to joe@companyb.com and when Joe logs in using that email address, he is able to see the work item and can mark it as done when complete.</p>
  
  <p>So my current thinking is to use a row-based multi-tenant approach with object based security. Each object will have a set of roles associated with it (e.g. Admin, Reader, Writer) and the roles will be associated with users. I'll then use these to verify permissions on every object instead of using something like a <code>tenant_id</code>.</p>
  
  <p>This means that anybody can potentially have access to any object in the system if somebody provides them access. Becoming a tenant really just makes you Admin at the root of a permission hierarchy (in the example this is admin of a company entity which allows you to create project entities). Which means that this is no longer really a multi-tenant architecture...</p>
  
  <p>Is there a better way to handle this situation?  </p>