Mvc: data scheiden op basis van user authentication

[Garma]

Hallo,

Ik heb een asp .net 4.0 mvc3 c# applicatie die mbv het entity framework werkt. Nu is de uitdaging waar ik voor sta dat ik een implementatie nodig heb die in staat is aan de hand van de ingelogde gebruiker, data volledig te scheiden. Zie het alsof de gebruiker een volledig eigen domein heeft. Denk bijvoorbeeld aan een applicatie waar verschillende klanten gebruik van gaan maken in dezelfde database ivm scaling.

Ik ben vrij nieuw met mvc3, en ik heb me al eea verdiept in user authentication mbv memberships en roles. Dit lijkt vrij simpel, maar ik kan geen manier vinden waarmee ik de data volledig kan scheiden zonder elke query handmatig aan te passen met iets als (where customerid= ) etc.

Iemand een idee hoe ik dit een beetje netjes kan doen? Het is absoluut essentieel dat de data volledig gescheiden is, dus elke query zal gecheckt moeten worden. Ik zoek dus een manier waarmee ik dit min of meer automatisch kan doen

[Grijze Vos]

Om hoeveel users gaat het? Als dat niet al te veel is zou je kunnen overwegen een database per user te gebruiken. Dat lijkt me het makkelijkste te realiseren.

(Waarom zou je dit btw willen hebben?)

[TheNephilim]

Je kunt toch een prefix voor alle tabelnamen zetten in je database? `{$prefix}_table`

Of anders kun je een centrale database maken met de algemene gegevens, en per user een database voor alle specifieke gegevens van die gebruiker.

[HuHu]

Het lijkt me dat je toch het customerid moet opnemen in iedere query.

TheNephilim schreef op maandag 23 april 2012 @ 10:25:
Je kunt toch een prefix voor alle tabelnamen zetten in je database? `{$prefix}_table`

Of anders kun je een centrale database maken met de algemene gegevens, en per user een database voor alle specifieke gegevens van die gebruiker.

Tabelnamen prefixen is niet handig. Dan moet je per (nieuwe) user een nieuwe set tabellen aanmaken en krijg je hele vieze databases. Daarnaast moet je nog steeds per user de query aanpassen.

[glrfndl]

Je kan ook elke klant zijn eigen schema geven. Lees anders dit document van Microsoft even door of Google even op multi tenant/multi tenancy

[Standeman]

TheNephilim schreef op maandag 23 april 2012 @ 10:25:
Je kunt toch een prefix voor alle tabelnamen zetten in je database? `{$prefix}_table`

Of anders kun je een centrale database maken met de algemene gegevens, en per user een database voor alle specifieke gegevens van die gebruiker.

Dat systeem heb ik al meegemaakt en is een drama qua schaalbaarheid. Voor 5 users is het nog te doen, maar voor 5000 zie je echt door de bomen het bos niet meer en wordt troubleshooten erg lastig. Tevens ging in mijn geval (Mysql) de sql client redelijk vaak over zijn nek of extreem traag. Dat was ook niet zo gek want in mijn geval waren er zo'n 20 tabellen per customer dus waren er 100.000 tabellen in een schema.

Dan moet je bij schema wijzigingen ook nog eens al je tabellen gaan updaten wat echt een hel is en kunnen vele ORM mappers zonder kunst en vliegwerk er niet mee omgaan.

Het gebruik van een customer_id in de relevante tabellen is imo vele malen duidelijker en werkbaarder.

[ Voor 17% gewijzigd door Standeman op 23-04-2012 12:22 ]

[Gomez12]

Alternatief voor tig duizenden tabellen is wellicht tig duizenden views.

Gewoon een SP maken die voor een nieuwe klant de views aanmaakt.

Ik vind het niet fraai, ik zie niet wat er mis is met customerid in elke query te proppen. Maar views is imho een betere mogelijkheid dan tabellen...

[Garma]

Standeman schreef op maandag 23 april 2012 @ 12:17:
[...]


Dat systeem heb ik al meegemaakt en is een drama qua schaalbaarheid. Voor 5 users is het nog te doen, maar voor 5000 zie je echt door de bomen het bos niet meer en wordt troubleshooten erg lastig. Tevens ging in mijn geval (Mysql) de sql client redelijk vaak over zijn nek of extreem traag. Dat was ook niet zo gek want in mijn geval waren er zo'n 20 tabellen per customer dus waren er 100.000 tabellen in een schema.

Dan moet je bij schema wijzigingen ook nog eens al je tabellen gaan updaten wat echt een hel is en kunnen vele ORM mappers zonder kunst en vliegwerk er niet mee omgaan.

Het gebruik van een customer_id in de relevante tabellen is imo vele malen duidelijker en werkbaarder.

precies mijn probleem. De toepassing is overigens inderdaad een Saas-achtige applicatie, dus potentieel duizenden gebruikers waar alle data in principe volledig gescheiden is en geen enkele data is gedeeld. Facebook zonder sharen zeg maar. Iemand bekend met Jira? Proefaccountje daarvan is ook een voorbeeld.

De reden dat ik zoek naar een andere oplossing dan de customer_id versie is tweeledig:

- ik zou wel een oplossing willen die per definitie geen securitygaten heeft; gescheiden tabellen/database zou dat als voordeel hebben, maar dat is dus niet zo schaalbaar. Met zo'n customer_id moet ik voortdurend opletten of de gebruiker niet iets geks kan doen waarmee er data gedeeld wordt, of nog erger, bewerkt.
- het toevoegen van zo'n customer_id clause klinkt als een hoop foutgevoelig werk. Ik vind het entity framework onder andere fantastisch omdat ik niet één query meer hoef te debuggen. Heeft me al erg veel werk bespaard.

@grlfndl: ziet er interessant uit, thx zal het eens lezen

Iets wat er op leek wat ik tegenkwam zijn custom model binders
http://buildstarted.com/2...-mvc-3-with-imodelbinder/
Daarmee zou je in principe kunnen zorgen dat je automatisch de user hebt, maar nog niet de combinatie met de query.

[ Voor 8% gewijzigd door Garma op 23-04-2012 19:22 ]

[Gomez12]

Garma schreef op maandag 23 april 2012 @ 19:18:
[...]
- ik zou wel een oplossing willen die per definitie geen securitygaten heeft; gescheiden tabellen/database zou dat als voordeel hebben, maar dat is dus niet zo schaalbaar. Met zo'n customer_id moet ik voortdurend opletten of de gebruiker niet iets geks kan doen waarmee er data gedeeld wordt, of nog erger, bewerkt.

Wat is er niet schaalbaar aan meerdere dbases? Dat zou ik eerder omschrijven als wel schaalbaar. Performance problemen op server 1, ok dan gooien we even dbase x/y over naar server 2 etc. Qua performance / caching kost het je nagenoeg niets, aangezien je toch volledige scheiding wilt en er dus niets te cachen valt over meerdere klanten heen.

Ook als je per definitie geen securitygaten wilt dan moet je simpelweg de data gaan scheiden. Het liefst galvanisch gescheiden zodat ook een hack op je server zelf maar max 1 klant treft.

Je wilt a maar je probeert juist richting b te gaan en hoopt dan a erin te kunnen hacken.

[Garma]

hmm misschien heb je gelijk. Het zou wel betekenen dat bij een upgrade al de databases geupgrade moeten worden.. maar dat zou ook met nette cleansteps kunnen.

[Styxxy]

Garma schreef op maandag 23 april 2012 @ 20:16:
hmm misschien heb je gelijk. Het zou wel betekenen dat bij een upgrade al de databases geupgrade moeten worden.. maar dat zou ook met nette cleansteps kunnen.

Dat zou je in principe ook perfect kunnen automatiseren etc; dat is nu ook niet echt een issue.

[Gomez12]

Garma schreef op maandag 23 april 2012 @ 20:16:
hmm misschien heb je gelijk. Het zou wel betekenen dat bij een upgrade al de databases geupgrade moeten worden.. maar dat zou ook met nette cleansteps kunnen.

Hoe wil je anders test -> pre-live -> live conversies gaan doen? Of wil je gewoon maar wat upgrades ongetest in live gaan doen?

Je cleansteps heb je al nodig in een enigszins nette omgeving, dus daar zie ik het probleem niet van in.

[Garma]

Ok, waarschijnlijk heb je gelijk. Heb ik alleen nog de vraag hoe ik dit kan implementeren; ik ga dus enerzijds de application users hebben die ik dus graag in de afzonderlijke databases opsla. Aan de andere kant moet ik natuurlijk wel weten welke database ik moet hebben. Denk dat ik er niet aan ontkom om gebruikers dubbel op te slaan (in de user db en een centrale authenticatiedb) of heeft iemand een beter idee?

En is er een manier om dit samen te laten werken met het roles/membership systeem? Binnen de applicatie heb ik nl nog steeds verschillende roles, die dan wel weer voor elke customer hetzelfde zijn overigens.

[Gomez12]

Waarom moet je centraal weten welke database je moet hebben? Waarom zou je niet gewoon een url per applicatie/dbase uitgeven?

Als je het wilt gaan scheiden dan lijkt me een centrale dbase niet handig.

Let op : In principe zou ik gaan voor 1 db met alles erin en in de query's alles regelen. En de scheiding enkel applicatietechnisch regelen, maar jij gaf aan dat niet te willen. Dan is het wmb linksom of rechtsom, maar niet een beetje van dit en een beetje van dat.

[Standeman]

Overigens snap ik niet echt welke securitygaten er zouden moeten zijn tussen de twee verschillende oplossingsrichtingen. Dat je een verkeerd customer_id meegeeft in een query? Het probleem bestaat nog steeds wanneer er een verkeerd schema / view wordt geselecteerd in je app. imo schuif je het securitygat op en los je het niet direct op.

Gomez12 schreef op maandag 23 april 2012 @ 20:05:
[...]

Wat is er niet schaalbaar aan meerdere dbases? Dat zou ik eerder omschrijven als wel schaalbaar. Performance problemen op server 1, ok dan gooien we even dbase x/y over naar server 2 etc. Qua performance / caching kost het je nagenoeg niets, aangezien je toch volledige scheiding wilt en er dus niets te cachen valt over meerdere klanten heen.

Tja,dan moet je weer ergens gaan configureren waar, wie, welke db / schema gebruikt. Mijn ervaring is dat daar regelmatig fuckups vandaan komen. Databases met verschillende versies, app code met verschillende versies en servers die toch niet 100% overeenkomen.

Ik ben zelf altijd voorstander van 1 db, 1 app die dan gerepliceerd worden over verschillende servers. imo de minste kans op (configuratie) problemen.

[ Voor 16% gewijzigd door Standeman op 23-04-2012 21:35 ]

[Gomez12]

Standeman schreef op maandag 23 april 2012 @ 21:31:
Overigens snap ik niet echt welke securitygaten er zouden moeten zijn tussen de twee verschillende oplossingsrichtingen. Dat je een verkeerd customer_id meegeeft in een query? Het probleem bestaat nog steeds wanneer er een verkeerd schema / view wordt geselecteerd in je app. imo schuif je het securitygat op en los je het niet direct op.

Ik vermoed eerder dat hij nu iets heeft staan en bang is om het te rewriten qua query's (en dat hij er dan 1 of 2 vergeet oid)
Als je het niet van de grond af aan goed hebt opgezet kan je veel plezier krijgen met sql-logs nalopen en uitpuzzelen waar die query nou weer vandaan kwam etc.

[...]
Tja,dan moet je weer ergens gaan configureren waar, wie, welke db / schema gebruikt. Mijn ervaring is dat daar regelmatig fuckups vandaan komen. Databases met verschillende versies, app code met verschillende versies en servers die toch niet 100% overeenkomen.

Dat is slechts een kwestie van dichttimmeren (alhoewel ik er ook geen voorstander van ben). Geef niemand directe sql-toegang, alles moet via een tussenlaag lopen, zodat je enkel die tussenlaag gelijk moet houden. Zet er een nagios oid naast die versies checkt etc.

Op zich is het niet zo heel moeilijk, enkel heel kostbaar.

[TheNephilim]

Standeman schreef op maandag 23 april 2012 @ 12:17:
[...]


Dat systeem heb ik al meegemaakt en is een drama qua schaalbaarheid. Voor 5 users is het nog te doen, maar voor 5000 zie je echt door de bomen het bos niet meer en wordt troubleshooten erg lastig. Tevens ging in mijn geval (Mysql) de sql client redelijk vaak over zijn nek of extreem traag. Dat was ook niet zo gek want in mijn geval waren er zo'n 20 tabellen per customer dus waren er 100.000 tabellen in een schema.

Dan moet je bij schema wijzigingen ook nog eens al je tabellen gaan updaten wat echt een hel is en kunnen vele ORM mappers zonder kunst en vliegwerk er niet mee omgaan.

Het gebruik van een customer_id in de relevante tabellen is imo vele malen duidelijker en werkbaarder.

Nou dat is ook beter, maar hij praat over het absoluut scheiden van de data, en dan moet je toch wat. Schaalbaar is een systeem met meerdere databases overig wel lijkt me, alleen het updaten of wijzigen van het schema is inderdaad een groot probleem.

De mooiste oplossing is gewoon 'customer_id' en dan eventueel partitioning op customer_id.

@Garma: Zoals Standeman al aangeeft is het gebruik van customer_id prima. Aparte databases of iets anders blijft gewoon opschuiven van het probleem naar een andere laag. Customer_id kan gewoon een sessie variabele zijn of iets dergelijks, dus die voer je nooit hard in de applicatie in lijkt me.

[Garma]

Standeman schreef op maandag 23 april 2012 @ 21:31:
Overigens snap ik niet echt welke securitygaten er zouden moeten zijn tussen de twee verschillende oplossingsrichtingen. Dat je een verkeerd customer_id meegeeft in een query? Het probleem bestaat nog steeds wanneer er een verkeerd schema / view wordt geselecteerd in je app. imo schuif je het securitygat op en los je het niet direct op.

lijkt me nogal duidelijk toch. schema selecteren gebeurd op 1 plek en is 1 query, nl de connection string. Ik heb tientallen queries met evenzoveel relaties die fout kunnen gaan. Ik bedoel, dit is toch precies de reden dat bedrijven een firewall hebben, nl zodat we niet elke computer afzonderlijk hoeven te beschermen.

@Garma: Zoals Standeman al aangeeft is het gebruik van customer_id prima. Aparte databases of iets anders blijft gewoon opschuiven van het probleem naar een andere laag. Customer_id kan gewoon een sessie variabele zijn of iets dergelijks, dus die voer je nooit hard in de applicatie in lijkt me.

nou dat zie ik niet zo eerlijk gezegd. zoals gezegd is het geen verschuiving van een probleem, het voordeel is dat je maar 1 deurtje hebt dat je hoeft te checken, en met iedereen in één db heb je tientallen deurtjes. En wat hebben sessievariabelen hiermee te maken, die moet je nog steeds inbakken in de query.

ik maak me nog wel zorgen over dat schaal probleem. Maakt het uit of je 100 db's laad voor 100 klanten of dat je 1 db laad met 100 klanten er in qua performance?

[ Voor 10% gewijzigd door Garma op 24-04-2012 19:23 ]

[Guillome]

Hier op mijn werk hebben we erg veel klanten die allen hun eigen database hebben. Hier is het ondoenlijk om het samen te voegen omdat per klant ook andere tabellen kunnen bestaan (dynamisch aangemaakt) en de databases vrij gigantisch kunnen zijn

Wijzigingen worden dmv scripts en tools in alle databases doorgevoerd en sommige wijzigingen worden door klanten zelf gestart na een melding van "er zijn structuurwijzigingen gevonden"

[ Voor 5% gewijzigd door Guillome op 25-04-2012 15:55 ]

[cyberstalker]

Wij werken met een vergelijkbaar systeem. Er is een centrale database waar alle gebruikers met hun rechten zijn opgeslagen, alsmede welke accounts er zijn. Bij die accounts is dan weer opgeslagen welke versie van ons database schema ze gebruiken, zodat we de juiste codeversie kunnen gebruiken om het account te openen.

Database schemas zijn opgeslagen in XML en worden d.m.v. een script gepushed naar een database.

[Verwijderd]

Het klinkt mij meer als een probleem op het gebied van het werken met een database dan een MVC specifiek probleem. Ik ga er even van uit dat je op dit moment ADO.NET entity framework gebruikt in combinatie met MVC. Gebruik je iets anders, dan werken de principes uit deze post nog steeds, maar dan zul je zelf waarschijnlijk wat meer met SQL queries moeten doen.

Er zijn verschillende technieken die je kan toepassen om multi-tenancy te realiseren in een applicatie.
Het verschil tussen de technieken zit hem in de volgende aspecten:

1. De schade die je kan aanrichten, mocht het niet helemaal dicht zitten.
2. De (on)mogelijkheid om data te restoren vanuit een backup, mocht dat nodig zijn.
3. Onderhoudbaarheid van je database/applicatie

Hoe dan ook krijg je te maken met meer of minder ellende op deze punten. Er zijn echter altijd wel oplossingen te vinden om het leed te verzachten. Zeker waar het onderhoudbaarheid betreft. Een goed stuk tooling om bijvoorbeeld databases uit te rollen voor een klant kan al heel veel schelen.
Ik denk eerlijk gezegd dat je in eerste instantie vooral moet gaan kijken naar de beveiliging en op het laatst pas naar onderhoud. Het wel of niet hebben van losse databases kan belangrijk zijn, voor bijvoorbeeld het kunnen overdragen van data of het repareren van een probleem (door middel van een restore van de backup of het uitrollen van een patch op de database(s)).

Mogelijkheid 1: Data scheiden, door een tenant kolom op te nemen in je tabellen.
Bij deze techniek, maak je in de database een tabel aan, waarin je de tenants (klanten) neerzet. Elk account dat wordt aangemaakt, wordt vervolgens aan een klant gekoppeld. Als je vervolgens andere data in de database gaat inserten, zul je ervoor moeten zorgen dat telkens het ID van de tenant van die gebruiker, bij in de tabel wordt gezet.

Met entity framework, kun je dat doen door bijvoorbeeld de property TenantID op een object, te vullen. Dit kan bijvoorbeeld door de SaveChanges methode op de DataContext te overriden. Maar je kan het ook met iets meer handwerk, prima in je controllers doen. Dat laatste zorgt wel dat je overal verspreid over de applicatie, dergelijke code hebt staan.

Bij het ophalen van data zul je vervolgens telkens de volgende Where() methode call moeten toevoegen aan de queries die je stelt op de database. Bijvoorbeeld:

C#:

DataContext.Tasks.Where(task => task.TenantId == CurrentTenantId)

Voordelen:

1. Slechts een database, dus bij een nieuwe klant hoe je alleen maar een extra Tenant toe te voegen.

Nadelen:

1. Als het niet dicht zit kunnen bezoekers van je site overal bij. Goed testen dus!
2. Mogelijk meer onderhoud, omdat je oval in de code extra Where statements moet toevoegen.
3. Restore van backup betekend restore van alle data voor alle klanten

Let op! Bij deze techniek kun je wel werken met een aparte data file per data partitie. Je kan op een tabel aangeven dat alle dat die voldoet een bepaalde conditie in een specifieke file terecht moet komen. Op die manier kun je ervoor zorgen dat data van een specifieke tenant altijd bij elkaar in dezelfde file staat. Als er dan iets misgaat voor die tenant, restore je alleen die data file van de database. Er is een catch: dit werkt overigens alleen voor SQL server 2008 en hoger.

Mogelijkheid 2: Gescheiden schema's
Een tweede mogelijkheid is om te werken met schemas in de SQL server database. Hierbij maak je per tenant een nieuw schema aan, waarin je vervolgens alle tabellen, views en stored procedures nogmaals aanmaakt. Om te zorgen dat gebruikers op "hun" schema uitkomen, moet je zorgen dat voor het account waarmee hij naar de database gaat, het default schema is ingesteld op het schema dat voor hem bedoeld is.

Je hebt bij deze techniek dus geen extra code nodig in je applicatie om de data te scheiden. Maar je moet nu wel ervoor zorgen dat gebruikers met hun eigen account de database in gaan. Hiervoor kun je gebruik maken van Impersonation in combinatie met een windows account per user. Er zijn andere mogelijkheden, zoals SQL text login per user en dan dynamisch de connectionstring instellen, maar dat is qua onderhoudbaarheid dan weer niet zo handig. Want als je een Windows account hebt per user, kun je dat account ook rechten geven op bijvoorbeeld zijn eigen uploads map. Daarmee voorkom je dan ook direct dat gebruikers bij elkaars uploads kunnen.

Voordelen:

1. Het is moeilijker/onmogelijk om bij elkaars data te komen, heb je het in de applicatie niet helemaal dicht zitten, dan nog kunnen gebruikers niet bij de spullen van een ander komen.
2. Omdat je gebruik maakt van een Windows account voor je gebruikers, kun je die ook rechten geven op upload folders op je webserver en daarmee dat gedeelte ook afschermen. Hiervoor moet je natuurlijk wel nog een stukje logica bakken, om op de juiste map uit te komen.
3. Nog steeds maar één database op je server.

Nadelen:

1. Restore van backup, betekend restore van alle data voor alle klanten
2. Uitrol voor een nieuwe klant betekent: Nieuw schema in de database, uitvoeren scripts, nieuwe logins maken, koppelen aan het juiste schema. Foutgevoelig als je dit met de hand moet gaan doen.

Mogelijkheid 3: Gescheiden databases
Bij deze techniek maak je per tenant een database aan, met daarin alle tabellen, views en stored procedures voor die klant. Daarnaast heb je dan nog een zogenaamde configuratie database nodig om bij te houden, welke klant nu precies welke database moet gebruiken. Tot slot heb je dan ook nog iets nodig bij de profielen van de gebruikers, om ervoor te zorgen dat je van een gebruiker kan bepalen welke klant hij bij hoort.

Alles staat en valt bij deze techniek, met het goed instellen van de connection string voor de DataContext/DbContext. Verbind je met de goede database, dan gaat alles in een keer goed werken in je website.

Voordelen:

1. Losse databases, restore van backup kan per klant worden uitgevoerd.
2. Klanten kunnen niet bij elkaars data komen, het staat simpelweg niet in de database van de andere klant.
3. Overdracht van gegevens aan de klant is eenvoudig te regelen, je geeft hem de backup van zijn database.

Nadelen:

1. Per klant moet je een nieuwe database uitrollen, configuratie database aanpassen en accounts koppelen.
2. Als je veel klanten hebt, met elk hun eigen database krijg je veel databases die je moet backuppen en patchen. Als je dit wilt, investeer dan veel tijd/geld in het goed automatiseren van dit soort taken.

[TheNephilim]

Erg nette samenvatting van de oplossing, mijn complimenten!

Vergeet bij oplossing 1 niet dat een update/wijziging van het database schema vele malen makkelijker is.

[Garma]

thx.. ik ben vooral nog benieuwd naar de performanceverschillen. Maakt het uit of ik 300 databases draai of 1 databases met 3x zoveel data?

[R4gnax]

Garma schreef op vrijdag 27 april 2012 @ 18:22:
thx.. ik ben vooral nog benieuwd naar de performanceverschillen. Maakt het uit of ik 300 databases draai of 1 databases met 3x zoveel data?

300 losse databases zou sneller moeten zijn. De indices per tabel worden veel kleiner dankzij de fragmentatie over meerdere databases. Ook concurrent access en locks zullen minder snel problemen wanneer veel verschillende klanten tegelijk actief zijn.

(Deze zelfde voordelen heb je ook als je per tenant een schema gebruikt, trouwens.)

[ Voor 8% gewijzigd door R4gnax op 27-04-2012 20:10 ]

[cyberstalker]

Wat ik me nog bedenk, stel dat je ervoor gaat om met een tenant id te werken, zou je ook nog kunnen kijken naar partitioned tables. Dan heb je wel het gemak van een enkele database, maar kun je de grote tabellen wel scheiden in losse bestanden, met elk hun eigen (kleine index).

[Verwijderd]

R4gnax schreef op vrijdag 27 april 2012 @ 20:09:
[...]

300 losse databases zou sneller moeten zijn. De indices per tabel worden veel kleiner dankzij de fragmentatie over meerdere databases. Ook concurrent access en locks zullen minder snel problemen wanneer veel verschillende klanten tegelijk actief zijn.

(Deze zelfde voordelen heb je ook als je per tenant een schema gebruikt, trouwens.)

Heb je dan niet veel meer IO? Ik snap je punt wel hoor, maar ik kan mij voorstellen dat er meer IO acties gedaan moeten worden om hetzelfde te bereiken(bij hoge load). En dat de DBMS bij het gebruik van 1 database slim genoeg is om dingen in het geheugen te laten staan als het veel gebruikt wordt..

Just my 2 cents