Sql-server – Multi-tenant database using SQL Server 2008

sql-server-2008sql-server-2008-r2

I am designing a database using a shared schema containing the "security views" to which all logged tenants will be directed.

I read the msdn article on Multi-tenant Data Architecture. I understand the concept to a degree – unclear how to get the tenant view filter to work.

So, I have a tenant table containing a TenantID, and TenantName. I suspect i would need a mapping table to associate the tenantID with roles set up in SQL Server to which user domain logins will be mapped to?

I have the client authenticate using a DSN-less connection.

Please any guidance and examples on this "Mapping" table, and how this relates to the SUSER_SID() described in the article to make this all work will be greatly appreciated.

Would i need to create database / application roles identical to those indicated in the Mapping table??

Thanks

Best Answer

To be clear. When talking about a DSN-less connection what you are really saying is that you are using a connection string that uses the native OLE DB driver to talk to SQL as opposed to ODBC. Just want to clarify.

Usually with a tenant data model the really critical thing to solidify is the process that authenticates users and issues them a connection to the tenant database. Most tenant database models don't use windows authentication as they are hosted or an SaaS product. Your question makes it sound like you are using domain accounts however.

If you were assigning sql logins to each tenant or each windows login was viewed as a unique tenant then the easiest thing to do would be to define the TenantID and the sql login used in the connection to be the same value. Then you wouldn't have to worry about a mapping table. Why? Because SUSER_SID() (the login id) IS the TenantID. You could also use SUSER_SNAME() if you wanted.

CREATE VIEW TenantEmployees AS 
SELECT * FROM Employees WHERE TenantID = SUSER_SID()

If you are not using sql logins or each tennant is associated with more than one login (such as with windows authentication) or business rules preclude you from using the login as the TenantID then yes. The next best thing would be a mapping table. Something like this for example that stores logins and the tenantID they are associated with.

UserKey   |  Tenant       |  TenantID  |  LoginName    |  LoginID
------------------------------------------------------------------------
1         |  Bills Bolts  |  111       |  billbolt     |  0x010050
2         |  Sams Screws  |  111       |  samscrew     |  0x010010
3         |  Nicky Nuts   |  222       |  nickynut     |  0x010000

In this case the view would look like this:

CREATE VIEW TenantEmployees AS 
SELECT Employees.* 
FROM Employees E INNER JOIN TenantMapTable T
    ON E.TenantID = T.TenantID
WHERE T.LoginName = SUSER_SNAME()  /* or T.LoginID = SUSER_SID() if preferred */

Another critical step however is that rights to the actual table are revoked. Only grant access to the table through the view - or this was all for nothing.

Don't forget to remove access to anything but the views from the tenant user login.

Last - keep in mind that you can query a view just like a table. So nothing is stopping you from filtering it further based on the needs of your application via procs or your ORM or where-ever.

select [firstname] from tenantemployees where [dob]='12/25/1980'
Related Question