Motivation

In diesem Artikel möchte ich beschreiben, wie man eigene Datenbank-Rollen anlegt und diesen bestimmte Rechte gibt.

Dies wird zum Beispiel benötigt, wenn man ETLs im SQL Server Agent ausführen lässt, die natürlich gewisse Rechte auf der Datenbank benötigen. Ich sehe es oft, dass die ausführenden User dann dbo-Rechte bekommen, da man (oder der Integrator) bei der Installation der Jobs nicht weiß, welche Rechte sie genau benötigen. Best practice ist natürlich, dass die ausführenden User möglichst wenig Rechte bekommen.

Ich sehe es als Entwickler-Aufgabe an, die Rollen zu erstellen und mit den nötigen Rechten auszustatten.
Es ist dann die Aufgabe des Integrators, die entsprechenden User anzulegen und der Rolle zuzuordnen.

Den User kennt nämlich der Entwickler in der Regel nicht, welche Rechte er aber braucht, weiß der Integrator in der Regel nicht.

Umsetzung

In meinem Beispiel verwende ich eine Datenbank namens „Faktura“ und einen User namens „HOGWARTS\Tobias“

Das Anlegen einer Rolle ist ganz einfach – hier heißt sie „db_ETL_Verarbeitung„

use Faktura
 go
 CREATE ROLE [db_ETL_Verarbeitung]
 GO

Nun müssen wir definieren, welche Rechte die Rolle haben soll.
In unserem Beispiel soll sie

• aus allen Tabellen des Schemas dbo lesen können (SELECT)
• die Tabelle dbo.Spesensaetze aktualisieren dürfen (UPDATE)
• und die Funktion dbo.getDatumDate ausführen dürfen (EXECUTE)

Das sind natürlich nur Beispiele, aber wenn man die Syntax kennt, findet man im Internet noch alle möglichen Beispiele.

 GRANT SELECT ON SCHEMA::dbo TO [db_ETL_Verarbeitung]
 GO
 GRANT UPDATE ON dbo.Spesensaetze TO [db_ETL_Verarbeitung]
 GO
 GRANT EXECUTE ON [dbo].[getDatumDate] TO [db_ETL_Verarbeitung]
 GO 

Nun müssen wir nur noch den User dieser Rolle zuweisen:

ALTER ROLE [db_ETL_Verarbeitung] ADD MEMBER [HOGWARTS\Tobias]
 GO

Man kann Rollen auch verschachteln. So könnte man statt obigen GRANT SELECT auf dem Schema dbo die Rolle auch zum data reader machen. Deswegen entfernen wir erst das SELECT-Recht und fügen dann die Rolle hinzu:

REVOKE SELECT ON SCHEMA::dbo TO [db_ETL_Verarbeitung]
 GO
 ALTER ROLE [db_datareader] ADD MEMBER [db_ETL_Verarbeitung]
 GO

Vorteil

Dieses Vorgehen hat auch den Vorteil, dass man die Berechtigungen an den einzelnen Objekten (z.B. Execute auf Stored Procedures) nicht auf User-Ebene sondern auf Rollen-Ebene definiert. Das kann zum Beispiel bei Verwendung der Redgate-Tools mit eingecheckt werden. Beim Deployment auf die Produktivserver wird diese Berechtigung dann mit bereitgestellt. Nur die User-Zuordnung zur Rolle muss der Integrator/Administrator dann noch machen.

Somit ist die Entwickler- und Administratoren/Integratoren-Tätigkeit sauber getrennt.

Testen

Eine schöne Möglichkeit zu testen, ob die Rolle die richtigen Rechte hat, ist „EXECUTE AS LOGIN„.

Damit impersoniert man sich als der betreffende User (hier HOGWARTS\Tobias) und kann die Statements ausführen und sehen, ob die Rechte entsprechend vorhanden sind.

Das folgende Beispiel

execute as login = 'hogwarts\tobias'

 select * from Rechnungen

 update Spesensaetze
 set Gruppe_id = Gruppe_id
 where 1=0

 update Rechnungen
 set RechnungsJahr = RechnungsJahr
 where 1=0

 select [dbo].getDatumDate(20200101)

liefert als Ergebnis, dass die Statements 1, 2 und 4 ausgeführt werden, Statement 3 liefert

Meldung 229, Ebene 14, Status 5, Zeile 9
The UPDATE permission was denied on the object ‚Rechnungen‘, database ‚Faktura‘, schema ‚dbo‘.