In diesem Artikel m\u00f6chte ich beschreiben, wie man eigene Datenbank-Rollen anlegt und diesen bestimmte Rechte gibt.<\/p>\n\n\n\n
Dies wird zum Beispiel ben\u00f6tigt, wenn man ETLs im SQL Server Agent ausf\u00fchren l\u00e4sst, die nat\u00fcrlich gewisse Rechte auf der Datenbank ben\u00f6tigen. Ich sehe es oft, dass die ausf\u00fchrenden User dann dbo-Rechte bekommen, da man (oder der Integrator) bei der Installation der Jobs nicht wei\u00df, welche Rechte sie genau ben\u00f6tigen. Best practice ist nat\u00fcrlich, dass die ausf\u00fchrenden User m\u00f6glichst wenig Rechte bekommen.<\/p>\n\n\n\n
Ich sehe es als Entwickler-Aufgabe an, die Rollen zu erstellen und mit den n\u00f6tigen Rechten auszustatten.
Es ist dann die Aufgabe des Integrators, die entsprechenden User anzulegen und der Rolle zuzuordnen.<\/p>\n\n\n\n
Den User kennt n\u00e4mlich der Entwickler in der Regel nicht, welche Rechte er aber braucht, wei\u00df der Integrator in der Regel nicht.<\/p>\n\n\n\n
In meinem Beispiel verwende ich eine Datenbank namens „Faktura“ und einen User namens „HOGWARTS\\Tobias“<\/p>\n\n\n\n
Das Anlegen einer Rolle ist ganz einfach – hier hei\u00dft sie „db_ETL_Verarbeitung<\/em>„<\/p>\n\n\n\n Nun m\u00fcssen wir definieren, welche Rechte die Rolle haben soll. Das sind nat\u00fcrlich nur Beispiele, aber wenn man die Syntax kennt, findet man im Internet noch alle m\u00f6glichen Beispiele.<\/p>\n\n\n\n Nun m\u00fcssen wir nur noch den User dieser Rolle zuweisen:<\/p>\n\n\n\n Man kann Rollen auch verschachteln. So k\u00f6nnte man statt obigen GRANT SELECT auf dem Schema dbo <\/em>die Rolle auch zum data reader<\/em> machen. Deswegen entfernen wir erst das SELECT-Recht und f\u00fcgen dann die Rolle hinzu:<\/p>\n\n\n\n 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.<\/p>\n\n\n\n Somit ist die Entwickler- und Administratoren\/Integratoren-T\u00e4tigkeit sauber getrennt.<\/p>\n\n\n\n Eine sch\u00f6ne M\u00f6glichkeit zu testen, ob die Rolle die richtigen Rechte hat, ist „EXECUTE AS LOGIN<\/em>„. <\/p>\n\n\n\n Damit impersoniert man sich als der betreffende User (hier HOGWARTS\\Tobias) und kann die Statements ausf\u00fchren und sehen, ob die Rechte entsprechend vorhanden sind.<\/p>\n\n\n\n Das folgende Beispiel<\/p>\n\n\n\n liefert als Ergebnis, dass die Statements 1, 2 und 4 ausgef\u00fchrt werden, Statement 3 liefert Meldung 229, Ebene 14, Status 5, Zeile 9 <\/p>\n","protected":false},"excerpt":{"rendered":" Motivation In diesem Artikel m\u00f6chte ich beschreiben, wie man eigene Datenbank-Rollen anlegt und diesen bestimmte Rechte gibt. Dies wird zum Beispiel ben\u00f6tigt, wenn man ETLs im SQL Server Agent ausf\u00fchren l\u00e4sst, die nat\u00fcrlich gewisse Rechte auf der Datenbank ben\u00f6tigen. Ich sehe es oft, dass die ausf\u00fchrenden User dann dbo-Rechte bekommen, da man (oder der Integrator) … SQL Server: spezifische Rollen f\u00fcr z.B. ETL-Verarbeitung<\/span> weiterlesen use Faktura\n go\n CREATE ROLE [db_ETL_Verarbeitung]\n GO<\/pre>\n\n\n\n
In unserem Beispiel soll sie<\/p>\n\n\n\n GRANT SELECT ON SCHEMA::dbo TO [db_ETL_Verarbeitung]\n GO\n GRANT UPDATE ON dbo.Spesensaetze TO [db_ETL_Verarbeitung]\n GO\n GRANT EXECUTE ON [dbo].[getDatumDate] TO [db_ETL_Verarbeitung]\n GO <\/pre>\n\n\n\n
ALTER ROLE [db_ETL_Verarbeitung] ADD MEMBER [HOGWARTS\\Tobias]\n GO<\/pre>\n\n\n\n
REVOKE SELECT ON SCHEMA::dbo TO [db_ETL_Verarbeitung]\n GO\n ALTER ROLE [db_datareader] ADD MEMBER [db_ETL_Verarbeitung]\n GO<\/pre>\n\n\n\n
Vorteil<\/h4>\n\n\n\n
Testen<\/h3>\n\n\n\n
execute as login = 'hogwarts\\tobias'\n\n select * from Rechnungen\n\n update Spesensaetze\n set Gruppe_id = Gruppe_id\n where 1=0\n\n update Rechnungen\n set RechnungsJahr = RechnungsJahr\n where 1=0\n\n select [dbo].getDatumDate(20200101)<\/pre>\n\n\n\n
<\/p>\n\n\n\n
\nThe UPDATE permission was denied on the object ‚Rechnungen‘, database ‚Faktura‘, schema ‚dbo‘.<\/p>\n\n\n\n