Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

share|improve this question
add comment

1 Answer 1

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'
share|improve this answer
    
thanks for your detailed reply. It makes sense. And yes, your assumption about my mention of dsn-less connection is correct. –  Tom E Oct 11 '12 at 3:49
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.