none
Rechtevergabe per Skript RRS feed

  • Frage

  • Hallo,

    ich möchte explizit Rechte auf einzelne Tabellen verweigern. Dabei habe ich mir gedacht (da es über 500! sind) mach ich das doch mal per Skript.

    Also

    SELECT * FROM TESTDB.dbo.sysobjects WHERE xtype='U' and name   like '%test%'

    Das klappt auch! Ergebnis - zu viele!

    Ich würde gern den kompletten Zugriff auf die Tabellen verweigern!

    Deswegen kam ich auf diese Idee:

    use TESTDB
    GO
    DENY UPDATE ON [dbo].[Testtabelle_eins] TO [datenaustausch_BL]
    GO
    use TESTDB
    GO
    DENY ALTER ON [dbo].[Testtabelle_eins] TO [datenaustausch_BL]
    GO
    use TESTDB
    GO
    DENY VIEW CHANGE TRACKING ON [dbo].[Testtabelle_eins] TO [datenaustausch_BL]
    GO
    use TESTDB
    GO
    DENY SELECT ON [dbo].[Testtabelle_eins] TO [datenaustausch_BL]
    GO
    use TESTDB
    GO
    DENY TAKE OWNERSHIP ON [dbo].[Testtabelle_eins] TO [datenaustausch_BL]
    GO
    use TESTDB
    GO
    DENY VIEW DEFINITION ON [dbo].[Testtabelle_eins] TO [datenaustausch_BL]
    GO
    use TESTDB
    GO
    DENY INSERT ON [dbo].[Testtabelle_eins] TO [datenaustausch_BL]
    GO
    use TESTDB
    GO
    DENY DELETE ON [dbo].[Testtabelle_eins] TO [datenaustausch_BL]
    GO
    use TESTDB
    GO
    DENY CONTROL ON [dbo].[Testtabelle_eins] TO [datenaustausch_BL]
    GO
    use TESTDB
    GO
    DENY REFERENCES ON [dbo].[Testtabelle_eins] TO [datenaustausch_BL]
    GO
    

    Gehe ich jetzt unter Sicherungsfähige Elemente der Eigenschaften des einzuschränkenden Benutzers erscheint mir Aktualisieren, Ändern usw. doppelt.

    Ich habe leider keine Referenz ob dies so richtig ist, deswegen stimmt das so?

    Und wie könnte ich ein schlankes Skript bauen damit ich eben nicht alle 500 Tabellen im oben genannt Skript einzeln aufführen muss?

    Bin um jeden Tipp sehr dankbar!

    Montag, 19. März 2018 15:15

Antworten

  • Hallo,

    du hast die Möglichkeit diese Tabellen in ein eigenes Schema zu verlagern und dann die Berechtigungen zentral auf dem Schema zu setzten. Wenn die Rechte immer gleich sind. Dafür müssen deine Tabellen dann für Nutzer in anderen Schemas aber immer über den vollen Namen inkl. Schema angesprochen werden.

    Du kannst auch mehrere Schemas verwenden um die Rechte noch besser zur verteilen. Das ist sicher übersichtlicher als jede Tabelle separat zu berechtigen.

    https://docs.microsoft.com/en-us/sql/t-sql/statements/create-schema-transact-sql


    Benjamin Hoch
    MCSE: Data Platform & Data Management and Analytics
    MCSA: SQL Server 2012/2014 & 2016 DB Administration
    MCSA: Windows Server 2012

    Montag, 19. März 2018 15:27

Alle Antworten

  • Hallo,

    du hast die Möglichkeit diese Tabellen in ein eigenes Schema zu verlagern und dann die Berechtigungen zentral auf dem Schema zu setzten. Wenn die Rechte immer gleich sind. Dafür müssen deine Tabellen dann für Nutzer in anderen Schemas aber immer über den vollen Namen inkl. Schema angesprochen werden.

    Du kannst auch mehrere Schemas verwenden um die Rechte noch besser zur verteilen. Das ist sicher übersichtlicher als jede Tabelle separat zu berechtigen.

    https://docs.microsoft.com/en-us/sql/t-sql/statements/create-schema-transact-sql


    Benjamin Hoch
    MCSE: Data Platform & Data Management and Analytics
    MCSA: SQL Server 2012/2014 & 2016 DB Administration
    MCSA: Windows Server 2012

    Montag, 19. März 2018 15:27
  • Hallo,

    danke für deine Antwort. Ich hätte aber zu Schema noch mal eine Frage. Wenn ich ein weiteres Schema erstellen wie sage ich denn dann diesem Schema das es auf das Standardschema zugreift. Sprich ich möchte das Schema Test erstellen und möchte das stets der gleiche Inhalt in dbo.Tabelletest drinsteht wie in test.Tabelletest. Ist dies dann eine Kopie von dbo.Tabelletest oder ist dies ein "View"?

    Dienstag, 20. März 2018 15:11
  • Hallo Toot,

    bei einer Kopie musst du dich aber darum kümmern dass die Inhalte gleich sind. Mit einer View wäre der Inhalt zumindest per Definition gleich. Ob dann alles funktioniert wie du möchtest, müsste man erstmal prüfen. Ich halte aber die Views in einem eigenem Schema für die sinnvollste Variante.

    Gruß


    Benjamin Hoch
    MCSE: Data Platform & Data Management and Analytics
    MCSA: SQL Server 2012/2014 & 2016 DB Administration
    MCSA: Windows Server 2012

    Dienstag, 20. März 2018 16:15
  • Hallo,

    danke für deine Antwort. Ich hätte aber zu Schema noch mal eine Frage. Wenn ich ein weiteres Schema erstellen wie sage ich denn dann diesem Schema das es auf das Standardschema zugreift. Sprich ich möchte das Schema Test erstellen und möchte das stets der gleiche Inhalt in dbo.Tabelletest drinsteht wie in test.Tabelletest. Ist dies dann eine Kopie von dbo.Tabelletest oder ist dies ein "View"?

    Hallo

    WAS es ist, liegt ja an dem, was Du machst ;-)

    Aber so bis hierher würde ich sagen: baue einfach eine View in Schema2. Wie Benjamim schreibt.

    Sofern beide Schemas den selben Besitzer (ev. dbo?) haben, langt es, wenn die Person Lese-Rechte auf die View erhält. Dann kann sie über diese mittels der Besitzerkette die Tabelle lesen, und sonst nichts.

    Keine Deny-s nötig. Die braucht man nur in Ausnahmefällen.

    Hier noch ein Artikel zu Schemas, der zum Verständnis helfen sollte: Schema-Design für SQL Server: Empfehlungen für Schema-Design mit Sicherheit im Blick


    Andreas Wolter (Blog | Twitter)
    MCSM: Microsoft Certified Solutions Master Data Platform/SQL Server 2012
    MCM SQL Server 2008
    MVP Data Platform MCSE Data Platform
    MCSM Charter Member, MCITP Charter Member etc.
    www.SarpedonQualityLab.com
    (Founder)

    Dienstag, 20. März 2018 17:49