Több felhasználó adatai egy adatbázisban, sémánként szétválasztva–de hogy írjunk ehhez programot?

Azure alkalmazások írásakor természetes igény, hogy egy alkalmazáspéldányból több felhasználót is kiszolgáljunk (azaz több-bérlős alkalmazást készítsünk). Ennek egy fontos vonatkozása, hogy a felhasználók adatait is egymástól szétválasztva kezeljük. Ennek talán legjobb megoldása a SQL séma-alapú szétválasztás, azonban a modern adathozzáférési módok (pl. Entity Framework) ezt nehézkesen kezelik. Ebben a bejegyzésben ezt a problémát hidaljuk át.


Miért jó a séma-alapú szétválasztás?

Felhasználóink adatainak szétválasztását alapvetően 3 módon tudjuk megoldani.

  1. Minden felhasználó egy adatbázisban van. A szétválasztást üzleti logikai szinten oldjuk meg – pl. minden sorban eltároljuk, hogy ez a sor épp melyik céghez tartozik egy CompanyID mezővel. Így mindenkit be tudunk passzírozni egyetlen adatbázisba, viszont egy egyszerű programozói hiba (pl. véletlenül elírt feltételvizsgálat…) is súlyos biztonsági réshez vezethet, amin keresztül az ügyfelek hozzáférnek egymás adataihoz.
  2. Minden felhasználó külön adatbázist kap. Ez Azure-ban nagyon egyszerűen megoldható. Előnye, hogy a szétválasztás tökéletes, mert minden cég SQL felhasználót kaphat, amelynek csak a saját adatbázisához van jogosultsága. Így egy esetleges programozási hiba esetén sem tud az Kutya Bt. felhasználója hozzáférni a Macska Bt. adataihoz. Hátránya természetesen a sok adatbázisból jelentkező pluszköltség.
  3. Minden felhasználó egy adatbázisban van, azonban külön sémákat kap. SQL-ben a séma gyakorlatilag “névtérként” is felfogható. Az egyes sémákon belül lehetnek azonos nevű táblák (pl. kutyabt.Products és macskabt.Products), és a felhasználók jogosultságait is nagyon egyszerűen tudjuk sémánként korlátozni. Így megkapjuk az előző két pont előnyeit (SQL-szintű, biztonságos izoláció és költséghatékonyság) azok hátrányai nélkül. Ez sincs azonban ingyen: a modern adathozzáférési technológiák, mint a Linq2Sql és az Entity Framework, nehézkesen kezelnek egy ilyen adatmodellt. Az adatmodell megalkotásakor mindkét technológia beírja ugyanis a táblák teljes nevét az adatmodellbe (pl. kutyabt.Products), és ennek futásidejű megváltoztatására nem is ad aztán lehetőséget.

Ebben a blogbejegyzésben egy olyan Entity Framework-ot használó Azure alkalmazást készítünk, amely a 3. pontban foglaltaknak megfelelő adatmodellt használ, azaz több ügyfelet kezel, és minden ügyfél neveit külön-külön sémába tárolja el.

Az adatbázis szerkezete

Kezdésként létrehozzuk a fejlesztéshez használt adatbázist. Ez 2 saját sémából (kutyabt és macskabt) áll, mindkettőben 1-1 azonos Products táblával. Mindkét sémához tartozik egy-egy SQL login és SQL felhasználó is (szintén kutyabt és macskabt), akik a saját sémájuknál tulajdonosok (schema owner), viszont a többi sémára nincsenek jogaik. (Emlékezzünk vissza, hogy SQL-ben először szerver szinten fel kell venni a jelszavakat tartalmazó SQL login-okat, melyekhez adatbázisonként társíthatunk a konkrét jogosultságokat tartalmazó SQL felhasználókat.)

Ennek létrehozásához először csatlakozzunk rá SQL Azure (vagy helyi SQL Server adatbázisunkra), és hozzunk létre két SQL login-t kutyabt és macskabt néven, valamilyen egyszerű jelszóval. Ehhez futtassuk le az alábbi SQL szkriptet a master adatbázison.

CREATE LOGIN kutyabt
    WITH PASSWORD = ‘PASSWORD’
GO

CREATE LOGIN macskabt
    WITH PASSWORD = ‘PASSWORD’
GO

Majd készítsünk egy üres adatbázist (mondjuk IesDemo néven; Azure esetén ez a webes felületen tehető meg), és futtassuk le rá az alábbi SQL szkriptet.

CREATE USER kutyabt
    FOR LOGIN kutyabt
    WITH DEFAULT_SCHEMA = dbo
GO

CREATE USER macskabt
    FOR LOGIN macskabt
    WITH DEFAULT_SCHEMA = dbo
GO

IF NOT EXISTS (SELECT * FROM sys.schemas WHERE name = N’kutyabt’)
EXEC sys.sp_executesql N’CREATE SCHEMA [kutyabt] AUTHORIZATION [kutyabt]’

IF NOT EXISTS (SELECT * FROM sys.schemas WHERE name = N’macskabt’)
EXEC sys.sp_executesql N’CREATE SCHEMA [macskabt] AUTHORIZATION [macskabt]’

GO

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[kutyabt].[Products]’) AND type in (N’U’))
BEGIN
CREATE TABLE [kutyabt].[Products](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [Name] [nvarchar](max) COLLATE Hungarian_CI_AS NULL,
CONSTRAINT [PK_Products] PRIMARY KEY CLUSTERED
(
    [Id] ASC
)WITH (STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF)
)
END

GO

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[macskabt].[Products]’) AND type in (N’U’))
BEGIN
CREATE TABLE [macskabt].[Products](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [Name] [nvarchar](max) COLLATE Hungarian_CI_AS NULL,
CONSTRAINT [PK_Products_1] PRIMARY KEY CLUSTERED
(
    [Id] ASC
)WITH (STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF)
)
END

GO

Ezzel létrehoztuk az alkalmazás adatbázisát.

Tesztelési lehetőség: Csatlakozzunk a SQL szerverhez kutyabt és macskabt névvel is. Látni fogjuk, hogy mindkét felhasználó csak a saját sémáját látja, a másik felhasználó sémájához nem tud hozzáférni. Ez adja majd az elkészült alkalmazás biztonságát. (Ne felejtsük el, hogy a felhasználók csak az IesDemo adatbázishoz férnek hozzá, a master-hez nem, így a bejelentkezési űrlapon célzottan az IesDemo adatbázisra kell csatlakozni!)

image

A kutyabt felhasználó ezt látja:

image

A macskabt felhasználó pedig ezt:

image

Mit tud majd az alkalmazás?

Alkalmazásunkban a felhasználó kiválasztja a használni kívánt sémát, az alkalmazás pedig

  1. automatikusan előállítja a megfelelő connection string-et, amely a kellő sémára kapcsol bennünket rá, a sémához tartozó SQL felhasználó nevében (ami a többi sémához garantáltan nem fér hozzá, megvalósítva így a biztonságos szétválasztást),
  2. bekonfigurálja az Entity Framework Data Model-t, hogy az adott sémát használja, így az összes többi programkódunknak nem is kell tudnia az egész több-sémás szétválasztásról, elég, ha egyszerűen az Entity Frameworkön keresztül hozzáfér a szükséges adatokhoz.

Egy egyszerű Azure-alkalmazást készítünk, ami az Azure metaprojekten túl egyetlen webes szerepkört tartalmaz.

image

A webes szerepkörön egy listából lehetőséget adunk a kívánt séma kiválasztására, majd kiválasztás után az alkalmazás legenerálja a sémához tartozó SQL felhasználót tartalmazó connection string-et, csatlakozik az adatbázisra, és kilistázza a megfelelő sémához tartozó tábla adatait.

imageimage

Megjegyzés: Egy éles alkalmazásban a séma kiválasztása persze nem listából történik, hanem mondjuk minden felhasználónk profiljába eltárolhatjuk, hogy ő melyik sémába dolgozhat. Erre az ASP.NET Membership és minden más hitelesítési módszer is egyszerűen ad lehetőséget, így most ezzel külön nem foglalkozunk, mert nem tartozik szigorúan a bejegyzés tárgyához.

Az Entity Framework Data Model létrehozása

Hozzuk létre a fenti adatbázis Entity Framework leképzését. Adjunk hozzá a projekthez egy új ADO.NET Entity Data Modelt, majd válasszuk a Generate from database lehetőséget.

image

image

Csatlakozzunk a szerverre a rendszergazdai fiókunkkal (azaz ne a kutyabt vagy macskabt felhasználókkal). A Visual Studio két táblát is megtalál majd; a kutyabt és a macskabt sémában szereplő táblákat is megjeleníti. A kettőből csak az egyiket (mondjuk a kutyabt –t) válasszuk ki. Az Entity Framework legenerálja a tábla objektummá való leképzését.

image

A connection string előállítása

Ezután a séma, és a sémához rendelt SQL felhasználó ismeretében elő kell állítanunk a connection string-et, és rá kell vennünk az Entity Framework-ot, hogy ennek használatával csatlakozzon a szerverre.

Szerencsére ez egy elég egyszerű feladat, ugyanis a connection string-et egyszerű szövegmanipulációval ki tudjuk generálni, az Entity Framework megfelelő objektuma pedig rendelkezik olyan konstruktorral, amely elfogad általunk generált connection string-et.

Megjegyzés: A példában kódba “égetve” tárolom a connection string-et, ez természetesen kihelyezhető web.config-ba, vagy az Azure Web Role beállításai közé is.

Az alapértelmezett connection string a következő:

metadata=res://*/IesDemoModel.csdl|res://*/IesDemoModel.ssdl|res://*/IesDemoModel.msl;provider=System.Data.SqlClient;provider connection string=’Data Source=XXXXXXXX.database.windows.net;Initial Catalog=IesDemo;Persist Security Info=True;User ID=USER;Password=PASSWORD;MultipleActiveResultSets=False’

Figyelem: Ez nem egy az egyben a web.config-ból kimásolt connection string, mert az tartalmaz " elemeket. Ezeket be kell helyettesíteni sima aposztrófokkal (‘)!

A fenti connection string-ből mindössze az aláhúzott részeket, vagyis a felhasználónevet és a jelszót kell kicserélnünk. Mivel feltettük, hogy ismerjük a használni kívánt SQL felhasználót (és értelemszerűen a hozzátartozó jelszót is), ezt a string.Format() parancs alkalmazásával nagyon könnyen megtehetjük.

string connectionString = string.Format("metadata=res://*/IesDemoModel.csdl|res://*/IesDemoModel.ssdl|res://*/IesDemoModel.msl;provider=System.Data.SqlClient;provider connection string=’Data Source=XXXXXXXX.database.windows.net;Initial Catalog=IesDemo;Persist Security Info=True;User ID={0};Password={1};MultipleActiveResultSets=False’", SchemaDropDownList.Text, "PASSWORD");

Az így előállított connection string-et pedig egyszerűen átpasszoljuk az Entity Framework osztály konstruktorának.

IesDemoEntities dc = new IesDemoEntities(connectionString);

Tesztelési lehetőség: Ha most összerakunk valamilyen nagyon egyszerű kódot, amely az ilyen módon bekonfigurált Entity Framework modellből kiolvassa a tábla adatait, akkor már reménykedhetünk annak helyes működésében. Ha a fenti lépéseket követtük, akkor a kutyabt felhasználóval fog is működni, a macskabt felhasználóval azonban nem: arra panaszkodik majd, hogy nincs joga a SELECT parancs lefuttatásához a kutyabt.Products táblára.

image

Az Entity Framework Data Model bekonfigurálása

Ha belepillantunk a generált EDMX fájlba (jobb gomb/Open With… parancs segítségével kiválaszthatjuk az XML szerkesztőt), meglátjuk, hogy mi a probléma. Az Entity Framework a markupban rögzíti az adatbázis-sémát, így a második esetben hiába jelentkeztünk be macskabt felhasználóval, továbbra is a kutyabt sémában lévő Products táblát próbálta megnyitni (amihez természetesen nincs joga, hiszen ez a teljes műveletsor lényege).

image

Az egyszerű megoldás az lenne, hogy ezt a Schema tag-et egyszerűen kitöröljük, és bízunk benne, hogy az Entity Framework az aktuális felhasználó alapértelmezett sémáját fogja majd használni. Sajnos nem ez történik, az Entity Framework semmilyen sémát nem helyettesít be, és hibára fut.

image

Valahogy máshogy kell megkerülnünk a problémát. Szerencsére már mások is voltak hasonló helyzetben. Egy Brandon Haynes nevű úriember elkészítette az Entity Framework Model Adapter nevű könyvtárat, amely pontosan ezt a kérdést oldja meg: a generált Entity Framework modell minimális fordítási idejű módosításával utána futásidőben dinamikusan tudjuk változtatni a modellben lévő táblák sémáját, nevét stb.

A projekt működési elve: fogja az Entity Framework által generált EDMX modellt, átalakítja a nyers XML-t (átnevezi a táblákat, kicseréli a sémaneveket stb.), majd az elkészült új XML-t betölti Entity Framework Object Context-ként. Szerencsére mindezt elrejti előlünk, mi alig pár sor kód módosításával ki tudjuk használni a képességeit.

A dolog apró szépséghibája, hogy a projekt 2009-ben készült, azóta valószínűleg történt némi változás az EDMX fájl sémájában, ami miatt nem működött jól. A projektben lévő hibákat kijavítottam (a javításokat dokumentáltam itt), a javított verziót pedig (forráskód és lefordított formában is) feltöltöttem ide.

A könyvtár használatához töltsük le azt a fenti linkről, majd a ZIP fájlból emeljük ki a már lefordított DLL fájlt (BrandonHaynes.ModelAdapter.EntityFramework.dll), és vegyünk fel rá referenciát projektünkből.

Utána látogassunk el az IesDemoModel.Designer.cs fájlba, amelyet az Entity Framework generált. A fájl legtetején találjuk meg az Entity Framework container osztály konstruktorait. Ezekbe kell belepiszkálnunk a könyvtár használatához.

image

Először, az objektum az ObjectContext ősosztályból származik le. Cseréljük le ezt AdaptingObjectContext –re.

Másodszor, a fájlban kezdésképp 3 konstruktor is lesz. 1 kivételével az összeset töröljük ki, a megmaradt konstruktor első sorát pedig cseréljük le erre:

public IesDemoEntities(string connectionString, string prefix)
            : base(connectionString, "IesDemoEntities", new ConnectionAdapter(new TableSchemaModelAdapter(prefix), System.Reflection.Assembly.GetExecutingAssembly()))

Ez a sor az Entity Framework Model Adapter belépési pontja. Ennek a konstruktornak át tudjuk adni az általunk kívánt connection string-et és sémanevet; a Model Adapter pedig úgy inicializálja az Entity Framework-ot, hogy a megadott paramétereknek megfelelő Object Context-et kapjuk.

A következőképp tudjuk használni a módosított ObjectContext osztályt:

IesDemoEntities dc = new IesDemoEntities(connectionString, SchemaDropDownList.Text);

Azaz egyszerűen át tudjuk adni a kívánt connection string-et és sémanevet, eredményül pedig egy teljesen működőképes Entity Framework Object Context-et kapunk vissza. Innentől kezdve nincs más dolgunk, mint a megszokott módon tovább dolgozni az Entity Framework-kel, semmilyen további változtatásra nincs szükség.

Tesztelési lehetőség: Vegyünk fel mindkét Products táblába 1-1 sort, és írjunk valamilyen egyszerű kódot, amely kilistázza a tartalmukat mondjuk egy GridView-be. Ha mindent jól csináltunk, akkor könnyedén tudunk váltogatni a felhasználók között. Ezzel kényelmes módon megvalósítottuk az adatok séma alapján való szeparálását: ha a fentieket egyszer elvégezzük, utána a többi kódunknak nem is kell tudnia a séma esetleges változásairól, és mindenközben folyamatosan védettek vagyunk az esetleges programozási hibákból bekövetkező adatszivárgás ellen.

A kész alkalmazás

A cikkhez készült mintaalkalmazás letölthető innen. Az alkalmazás futtatásához a fentiek alapján létre kell hozni az adatbázist, majd módosítani kell a connection string-et (a Default.aspx.cs és web.config fájlokban).

Mi a helyzet a Linq2Sql-lel?

Linq2Sql esetén egyszerűbb a helyzet, ott ugyanis működik az, hogy a generált markupból kitöröljük a séma nevét. Nyissuk meg a generált DBML fájlt egy XML szerkesztővel, és a

<Table Name="dbo.Products" Member="Products">

jellegű sorokból töröljük ki a sémát:

<Table Name="Products" Member="Products">

A SQL ezután a bejelentkezett felhasználó alapértelmezett sémáját használja majd, így csak arra kell ügyelnünk, hogy a létrehozott felhasználók (példánkban kutyabt, macskabt) alapértelmezett séma tulajdonsága helyesen legyen beállítva.

Megjegyzés: Visszajelzések alapján ez táblák és nézetek esetén remekül működik, az egyedi funkciók (User-Defined Functions) esetén viszont problémák jelentkeznek. Ennek megoldása egy későbbi blogbejegyzés tárgya lesz majd.

Sok sikert a leírtak alkalmazásához, a visszajelzéseket, kérdéseket várom a kommentekbe, illetve az fb@matroit.com címre!

2 thoughts on “Több felhasználó adatai egy adatbázisban, sémánként szétválasztva–de hogy írjunk ehhez programot?

  1. DomainService class for WCF RIA service használata esetén szükség van némi módosításra, hogy a generált DomainService osztályunkkal összhangban müködjön:

    Az Entity Framework Data Model bekonfigurálása

    static string connectionString;
    static string prefix;

    public static void setEFDMInfo(string connStrP, string prefixP)
    {
    connectionString = connStrP;
    prefix = prefixP;
    }

    public IesDemoEntities()
    : base(connectionString, “IesDemoEntities”, new ConnectionAdapter(new TableSchemaModelAdapter(prefix),
    System.Reflection.Assembly.GetExecutingAssembly()))

    DomainService generált osztály módosítása:

    protected override IesDemoEntities CreateObjectContext()
    {
    string connStr = “Saját connection string”;
    string schema = “Saját schema”;
    IesDemoEntities.setEFDMInfo(connStr, schema);
    return base.CreateObjectContext();
    }

Vélemény, hozzászólás?

Adatok megadása vagy bejelentkezés valamelyik ikonnal:

WordPress.com Logo

Hozzászólhat a WordPress.com felhasználói fiók használatával. Kilépés / Módosítás )

Twitter kép

Hozzászólhat a Twitter felhasználói fiók használatával. Kilépés / Módosítás )

Facebook kép

Hozzászólhat a Facebook felhasználói fiók használatával. Kilépés / Módosítás )

Google+ kép

Hozzászólhat a Google+ felhasználói fiók használatával. Kilépés / Módosítás )

Kapcsolódás: %s