none
Rollen - Berechtigungen auf Datenebene RRS feed

  • Frage

  • Hallo Zusammen,

    ich möchte auf Datenebene Berechtigungen vergeben, d.h. ich habe eine Tabelle wo Rechnungswerte zum Mandanten abliegen. Natürlich soll nicht jeder Zugriff auf alle Mandanten haben, sonder nur wenige Personen.

    Ich möchte somit auf Dateninhalt Berechtigungen erteilen.

    Gibt es hierzu ein gutes Buch oder ein Tutorial, wo dies erklärt ist. Dies hat natürlich auch Auswirkungen auf die Reports, d.h. ich habe beispielsweise eine Report mit einem Filter auf Mandanten und die Combobox soll nur die Mandanten beinhalten, für die der User berechtigt ist.

    Danke.

    Gruss Klaus

    Freitag, 14. Oktober 2011 08:12

Antworten

  • Hallo Klaus,

    ich würde Dir ebenfalls empfehlen die von Christoph vorgeschlagenen Möglichkeiten ins Auge zu fassen.

    Denn zeilenbasierte Berechtigungen sind nur mit größerem Aufwand realisierbar.
    Siehe dazu: Implementing Row- and Cell-Level Security in Classified Databases Using SQL Server 2005
    und daran hat sich bei SQL Server 2008 (R2) nichts geändert.

    Gruß Elmar

    • Als Antwort markiert Alex Pitulice Donnerstag, 20. Oktober 2011 11:37
    Freitag, 14. Oktober 2011 09:26
  • Hallo Klaus,
    hier ist ein einfaches Beispiel, das zeigt, wie man abhängig dem  aktuellen User eigene Mandanten-Daten über eine View abfragen könnte.
    use tempdb
    go
    --
    -- Testanmeldung login1/login1
    --
    create login [login1] with
    	password=N'login1',
    	default_database=[tempdb],
    	check_expiration=off,
    	check_policy=off
    go
    --
    -- Pro Mandant wird eine DB-Rolle angelegt
    --
    create role [mandant1] authorization [dbo]
    go
    create role [mandant2] authorization [dbo]
    go
    --
    -- DB User für die Anmeldung Login1
    --
    create user [user1] for login [login1]
    go
    --
    -- User1 wird zu Mandant1 zugeordnet
    -- Hinweis: Ein Datenbank Benutzer wird nur zu einer Mandant-Db-Rolle zugeordnet
    --
    exec sp_addrolemember N'mandant1', N'user1'
    go
    --
    -- Tabelle, die von allen Mandanten verwendet wird
    --
    create table data
    (
    	id int identity,
    	value int,
    	principal_id int	-- Mandantenkennung(RoleID)
    )
    go
    --
    -- testdata
    --
    insert into data(value,principal_id)values
    (123, user_id('mandant1')),
    (987, user_id('mandant2'))
    go
    
    --
    -- Über die View werden Benutzer nur eigene Mandantendaten abfragen können.
    --
    create view vwdata
    as
    select id, value from data where principal_id in 
    	(
    		select role_principal_id
    		from sys.database_role_members m
    		where member_principal_id = user_id(user)
    		and role_principal_id
    		in (user_id('mandant1'),user_id('mandant2'))
    	)
    go
    
    grant select on [dbo].[vwdata] to [mandant1]
    go
    
    --
    -- Test: melde Dich als login1 um die Berechtigungen zu testen
    -- Die Abfrage muss nur einen Datensatz mit dem Wert 123 in der Spalte Value zurückliefern
    --
    select * from vwdata
    go
    --
    -- drops
    --
    drop view vwdata
    go
    drop table data
    go
    drop user user1
    go
    drop login login1
    go
    drop role mandant1
    go
    
    



    Freitag, 14. Oktober 2011 13:27
  • Hallo Klaus, ganz so kompliziert würde ich es nicht machen. Baue Dir eine View, die eine Einschränkung beinhaltet, entweder über SUSER_NAME() oder eine andere Möglichkeit, den aktuellen User einzuschränken. Das könnte auch eine Mandanten-ID in der Datentabelle sein und eine weitere Tabelle, in der die Zusammenhänge von Mandanten-ID und SUSER_NAME() definiert sind. Dann braucht die View entsprechend einen Join auf die Mandantenzuordnung mit Mandanten-ID und ein Where auf die Mandantenzuordnung. Folgendes Beispiel ist etwas simpler gestrickt, mit SUSER_NAME() direkt in der Datentabelle und einem Trigger zum setzen des SUSER_NAME().

    use tempdb
    go
     create table meineDaten(ID int identity, Wert varchar(100), Anwender sysname
    NULL, constraint xpkmeineDaten primary key (ID));
    go
    insert into meineDaten(Wert) values('Testdatensatz vor Trigger');
    go
    Create View v_Test as
    Select *
    from meineDaten
    where Anwender = SUSER_NAME();
    go
    create trigger I_meinedaten on meineDaten
    for Insert
    as
         update meineDaten
         set Anwender = SUSER_NAME()
         from meineDaten m inner join inserted i on m.ID = i.ID;
    go
    insert into meineDaten(Wert) values('Testdatensatz mit Mandant');
    
    Select *
    from meineDaten;
    Select *
    from v_Test;
    
    go
    drop view v_Test;
    drop table meineDaten;

    Das zweite Beispiel funktioniert wie oben beschrieben und geht davon aus, dass die Anwendung die Mandanten_ID setzt.

    use tempdb
    go
    
    create table Mandanten(Mandanten_ID int, constraint xpkMandanten primary key
    (Mandanten_ID));
    create table Mandanten_Anwender(Mandanten_ID int REFERENCES
    Mandanten(Mandanten_ID), Anwender sysname NOT NULL, constraint
    xpkMandanten_Anwender primary key (Mandanten_ID, Anwender));
    create table meineDaten(ID int identity, Wert varchar(100), Mandanten_ID int
    REFERENCES Mandanten(Mandanten_ID), constraint xpkmeineDaten primary key
    (ID));
    
    go
    insert into Mandanten(Mandanten_ID) values(1); -- Meine Mandant
    insert into Mandanten(Mandanten_ID) values(2); -- Fremder Mandant
    insert into Mandanten_Anwender(Mandanten_ID, Anwender) values(1,
    SUSER_NAME()); -- Meine Anmeldung
    insert into Mandanten_Anwender(Mandanten_ID, Anwender) values(2,
    'Meierbier'); -- Irgendeine andere Anmeldung
    
    insert into meineDaten(Mandanten_ID, Wert) values(1, 'Mein Datensatz');
    insert into meineDaten(Mandanten_ID, Wert) values(2, 'Datensatz des anderen
    Mandaten');
    go
    Create View v_Test as
    Select D.ID, D.Wert
    from meineDaten D inner join Mandanten_Anwender M on D.Mandanten_ID =
    M.Mandanten_ID
    where M.Anwender = SUSER_NAME();
    go
    
    Select *
    from meineDaten;
    Select *
    from v_Test;
    
    go
    drop view v_Test;
    drop table meineDaten;
    drop table Mandanten_Anwender;
    drop table Mandanten;

     Einen schönen Tag noch,
    Christoph
    --
    Microsoft SQL Server MVP
    www.insidesql.org/blogs/cmu

    • Als Antwort markiert Alex Pitulice Donnerstag, 20. Oktober 2011 11:36
    Montag, 17. Oktober 2011 07:09
    Beantworter

Alle Antworten

  • Hallo Klaus,
    ein Tutorial dazu kenne ich nicht, aber folgende Vorgehensweisen sollten üblich sein:
    1.) Mandanten getrennt in verschiedenen Datenbanken. Der User mit Recht auf alle Mandanten sammelt sich diese über Joins in Views zusammen.
    2.) Mandanten in einer Datenbank. Alle Anwender verwenden Views zum Zugriff auf die Daten, wo die Einschränkung implementiert ist. Der User mit Recht auf alle Mandanten hat entsprechend weniger Einschränkungen.

    Ich tendiere zur zweiten Lösung, da sie einfacher zu handhaben ist. Wenn die Mandanten komplett unabhängige Bereiche/Firmen sind, könnte die Lösung 1 besser sein, da hier dann auch die Backups nur Daten einer Firma beinhalten.
     Einen schönen Tag noch,
    Christoph
    --
    Microsoft SQL Server MVP
    www.insidesql.org/blogs/cmu

    Freitag, 14. Oktober 2011 08:32
    Beantworter
  • Hallo Klaus,

    ich würde Dir ebenfalls empfehlen die von Christoph vorgeschlagenen Möglichkeiten ins Auge zu fassen.

    Denn zeilenbasierte Berechtigungen sind nur mit größerem Aufwand realisierbar.
    Siehe dazu: Implementing Row- and Cell-Level Security in Classified Databases Using SQL Server 2005
    und daran hat sich bei SQL Server 2008 (R2) nichts geändert.

    Gruß Elmar

    • Als Antwort markiert Alex Pitulice Donnerstag, 20. Oktober 2011 11:37
    Freitag, 14. Oktober 2011 09:26
  • Danke Christoph,

    d.h. ich kann nur Berechtigungen auf Tabellen oder Views erzeugen? Ich habe derzeit 15 Mandanten. Dies würde bedeuten, ich erzeuge entweder 15 Tabellen oder 15 Views. Bei Cubes würde sich dies auch verfielfältigen. Bei jedem weiteren Mandanten erhöht sich dies. Optimal wäre doch wirklich eine Berechtigungsfunktion, die auf Dateninhalt geht. Ich meine dies gehört zu haben, dass es möglich sei. Vielleicht geht es ja.

    Gruss Klaus

    Freitag, 14. Oktober 2011 09:27
  • Das hört sich ja wirklich sehr komplex an. Ich dachte es geht einfacher.

    Wenn ich die Variante 2 nehme würde es dann so sein, dass ein normaler User keine Rechte auf die Tabelle hat, aber Rechte auf seine Views? Ich muss sicherstellen, dass er nicht auf die Tabelle zugreift und alle Daten sieht.

    Müsste ich dann auch die Reports für alle 15 Mandanten duplizieren, oder kann ich dies auch mit einem Report machen?

    Danke.

    Gruss

    Klaus

    Freitag, 14. Oktober 2011 10:12
  • Hallo Klaus,
    hier ist ein einfaches Beispiel, das zeigt, wie man abhängig dem  aktuellen User eigene Mandanten-Daten über eine View abfragen könnte.
    use tempdb
    go
    --
    -- Testanmeldung login1/login1
    --
    create login [login1] with
    	password=N'login1',
    	default_database=[tempdb],
    	check_expiration=off,
    	check_policy=off
    go
    --
    -- Pro Mandant wird eine DB-Rolle angelegt
    --
    create role [mandant1] authorization [dbo]
    go
    create role [mandant2] authorization [dbo]
    go
    --
    -- DB User für die Anmeldung Login1
    --
    create user [user1] for login [login1]
    go
    --
    -- User1 wird zu Mandant1 zugeordnet
    -- Hinweis: Ein Datenbank Benutzer wird nur zu einer Mandant-Db-Rolle zugeordnet
    --
    exec sp_addrolemember N'mandant1', N'user1'
    go
    --
    -- Tabelle, die von allen Mandanten verwendet wird
    --
    create table data
    (
    	id int identity,
    	value int,
    	principal_id int	-- Mandantenkennung(RoleID)
    )
    go
    --
    -- testdata
    --
    insert into data(value,principal_id)values
    (123, user_id('mandant1')),
    (987, user_id('mandant2'))
    go
    
    --
    -- Über die View werden Benutzer nur eigene Mandantendaten abfragen können.
    --
    create view vwdata
    as
    select id, value from data where principal_id in 
    	(
    		select role_principal_id
    		from sys.database_role_members m
    		where member_principal_id = user_id(user)
    		and role_principal_id
    		in (user_id('mandant1'),user_id('mandant2'))
    	)
    go
    
    grant select on [dbo].[vwdata] to [mandant1]
    go
    
    --
    -- Test: melde Dich als login1 um die Berechtigungen zu testen
    -- Die Abfrage muss nur einen Datensatz mit dem Wert 123 in der Spalte Value zurückliefern
    --
    select * from vwdata
    go
    --
    -- drops
    --
    drop view vwdata
    go
    drop table data
    go
    drop user user1
    go
    drop login login1
    go
    drop role mandant1
    go
    
    



    Freitag, 14. Oktober 2011 13:27
  • Hallo Klaus, ganz so kompliziert würde ich es nicht machen. Baue Dir eine View, die eine Einschränkung beinhaltet, entweder über SUSER_NAME() oder eine andere Möglichkeit, den aktuellen User einzuschränken. Das könnte auch eine Mandanten-ID in der Datentabelle sein und eine weitere Tabelle, in der die Zusammenhänge von Mandanten-ID und SUSER_NAME() definiert sind. Dann braucht die View entsprechend einen Join auf die Mandantenzuordnung mit Mandanten-ID und ein Where auf die Mandantenzuordnung. Folgendes Beispiel ist etwas simpler gestrickt, mit SUSER_NAME() direkt in der Datentabelle und einem Trigger zum setzen des SUSER_NAME().

    use tempdb
    go
     create table meineDaten(ID int identity, Wert varchar(100), Anwender sysname
    NULL, constraint xpkmeineDaten primary key (ID));
    go
    insert into meineDaten(Wert) values('Testdatensatz vor Trigger');
    go
    Create View v_Test as
    Select *
    from meineDaten
    where Anwender = SUSER_NAME();
    go
    create trigger I_meinedaten on meineDaten
    for Insert
    as
         update meineDaten
         set Anwender = SUSER_NAME()
         from meineDaten m inner join inserted i on m.ID = i.ID;
    go
    insert into meineDaten(Wert) values('Testdatensatz mit Mandant');
    
    Select *
    from meineDaten;
    Select *
    from v_Test;
    
    go
    drop view v_Test;
    drop table meineDaten;

    Das zweite Beispiel funktioniert wie oben beschrieben und geht davon aus, dass die Anwendung die Mandanten_ID setzt.

    use tempdb
    go
    
    create table Mandanten(Mandanten_ID int, constraint xpkMandanten primary key
    (Mandanten_ID));
    create table Mandanten_Anwender(Mandanten_ID int REFERENCES
    Mandanten(Mandanten_ID), Anwender sysname NOT NULL, constraint
    xpkMandanten_Anwender primary key (Mandanten_ID, Anwender));
    create table meineDaten(ID int identity, Wert varchar(100), Mandanten_ID int
    REFERENCES Mandanten(Mandanten_ID), constraint xpkmeineDaten primary key
    (ID));
    
    go
    insert into Mandanten(Mandanten_ID) values(1); -- Meine Mandant
    insert into Mandanten(Mandanten_ID) values(2); -- Fremder Mandant
    insert into Mandanten_Anwender(Mandanten_ID, Anwender) values(1,
    SUSER_NAME()); -- Meine Anmeldung
    insert into Mandanten_Anwender(Mandanten_ID, Anwender) values(2,
    'Meierbier'); -- Irgendeine andere Anmeldung
    
    insert into meineDaten(Mandanten_ID, Wert) values(1, 'Mein Datensatz');
    insert into meineDaten(Mandanten_ID, Wert) values(2, 'Datensatz des anderen
    Mandaten');
    go
    Create View v_Test as
    Select D.ID, D.Wert
    from meineDaten D inner join Mandanten_Anwender M on D.Mandanten_ID =
    M.Mandanten_ID
    where M.Anwender = SUSER_NAME();
    go
    
    Select *
    from meineDaten;
    Select *
    from v_Test;
    
    go
    drop view v_Test;
    drop table meineDaten;
    drop table Mandanten_Anwender;
    drop table Mandanten;

     Einen schönen Tag noch,
    Christoph
    --
    Microsoft SQL Server MVP
    www.insidesql.org/blogs/cmu

    • Als Antwort markiert Alex Pitulice Donnerstag, 20. Oktober 2011 11:36
    Montag, 17. Oktober 2011 07:09
    Beantworter
  • Super vielen Dank für die sehr nützlichen und super ausführlichen Berichte. Ich bin nun am testen, welche Weg der bessere für uns ist.
    Mittwoch, 19. Oktober 2011 11:36