2

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-column, 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? :)

marc_s
  • 8,932
  • 6
  • 45
  • 51
MicBehrens
  • 769
  • 1
  • 8
  • 19
  • 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
  • @gbn: It was an error from my side, so the question regarding that has been removed :) – MicBehrens Feb 14 '12 at 14:40
  • @AaronBertrand: True, but on the other hand it makes the creating of new customers and further development on the product A LOT easier? – MicBehrens Feb 14 '12 at 14:41
  • 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
  • One of the big factors on the decision is that; changing/adding 1 coloumn in 1 table at 1 customer results in doing the same thing over and over again for all the customers. And who knows, there could be 1000s of customers? The article refer to 3 approaches, where the third is "Shared Database, Shared Schema", which is the one I am referring to. – MicBehrens Feb 14 '12 at 14:49
  • 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
  • I see. My problem here is though, that I normally design the tables using the design-part of the SSMS. So maybe the thing I really should be doing, is to change personal habits? – MicBehrens Feb 14 '12 at 15:24
  • @AaronBertrand 500 DBs on one instance, wow! How was the performance on that one? It must have been a joy to tune that instance and see the DBs functioning smoothly in harmony. – StanleyJohns Feb 14 '12 at 20:02
  • 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
  • @StanleyJohns yes, it is always fun, that one kept me busy (and in turn made the SAN admin a mortal enemy :-)). – Aaron Bertrand Feb 14 '12 at 20:04
  • @AaronBertrand: I rest my case. I'm gonna use separate databases anyway :) And then I'm gonna use the designer a lot less :) Thanks a lot! :) – MicBehrens Feb 15 '12 at 08:03

1 Answers1

4

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

Community
  • 1
gbn
  • 69,809
  • 8
  • 163
  • 243
  • Ah great, thanks! And the multitenancy-part has been done correct? – MicBehrens 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