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 eagerly trying to design the perfect multitenant data architecture for my database. I am getting inspiration from an article posted by Microsoft, based on scenario 3.

I have been given all my tables a tenant-coloumn, and afterwards created a matching VIEW

ALTER TABLE {table_name} add TenantID varbinary(85) DEFAULT SUSER_SID()
CREATE VIEW {view_name} as select * from {table_name} where TenantID = SUSER_SID()

Each of my clients has their own database user account, these users has been given following rights:

db_datareader
db_datawriter
public

And then I changed the following:

DENY SELECT ON {table_name} TO {user_name}
GRANT SELECT ON {view_name} TO {user_name}

Anyway, am I missing something for now? :)

share|improve this question
    
What do you mean "denies connection" please? –  gbn Feb 14 '12 at 14:35
1  
Can I ask why you think sharing tables in a single database is better? The reason I like the single tenant model is that if one customer gets big it's easy to move their database to another instance or filegroup than manually pulling all of their data. It's also much easier to implement security (as you've noticed) and some companies will actually not allow you to store their data in a heap with everyone else's... –  Aaron Bertrand Feb 14 '12 at 14:36
1  
I don't think so. But easier does not necessarily mean better anyway. YMMV and I don't know all of the factors that are going into your decision. I looked at the 6-year old article and couldn't correlate what you meant by "scenario 3." –  Aaron Bertrand Feb 14 '12 at 14:42
1  
In my previous job I managed a single instance with 500+ identical databases - not just one per customer but one per division. If you can script the change that applies to one customer, you can script it 500 times and run it in a loop. It really wasn't all that much different than single-database change management, except as an added bonus we could roll it out to different clients on a different schedule if we needed to (e.g. we had different SLAs and different maintenance windows for different clients). –  Aaron Bertrand Feb 14 '12 at 15:18
1  
@erizias yes, IMHO the designers are horrible, full of bugs, slow you down and contribute to even more bad habits. DDL FTW. I find it much faster to whip out an ALTER TABLE command than it takes for the designer to load. –  Aaron Bertrand Feb 14 '12 at 20:03
show 7 more comments

1 Answer

up vote 3 down vote accepted

I suspect it is how SSMS enumerates tables

However, you don't need DENY. Just don't GRANT.

Best practice would be something like this

  • Remove membership of the db_datareader and db_datawriter role
    (I never use them)
  • Create a specific role (with CREATE ROLE) and GRANT on the view to this.

Then, you don't need to DENY on the table because it won't be checked anyway due to "ownership chaining"

Saying that, I'd also use SCHEMAs so I don't even require GRANT to the view. GRANT once to the SCHEMA. For more, see

share|improve this answer
    
Ah great, thanks! And the multitenancy-part has been done correct? –  Behrens Feb 14 '12 at 14:56
1  
@erizias: Using SID, yes. I'll let Aaron discuss the rest with you :-) I've never had to do it, so never thought about it much –  gbn Feb 14 '12 at 15:00
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.