In BI-Projekten geht es häufig um sehr sensible Daten, die nur für einen eingeschränkten Nutzerkreis gedacht sind. Auch innerhalb dieses Nutzerkreises haben selten alle Anwender die gleichen Rechte. In einem solchen Fall muss ein wasserdichtes Berechtigungskonzept implementiert werden. Und Ziel eines guten Berechtigungskonzepts ist, dass nur die minimal notwendigen Rechte vergeben werden.
In diesem Blogbeitrag geht es ausschließlich um die Sicherheit in SQL Server auf relationalen Datenbanken. Besonders zur Anwendung auf der Hybrid-Planung ist das hier beschriebene Vorgehen gedacht, aber auch für relationale Datenerfassung anwendbar und auch allgemein bei Anbindung der relationalen Datenbank für den SQL-Durchgriff empfehlenswert.
Sicherheitskonzepte in SQL Server
Berechtigungsvergabe auf relationalen Datenbanken
Die einfachste Möglichkeit, auf relationalen Datenbanken Rechte zu vergeben, sind die Standard-Rollen – entweder für die gesamte Instanz (sysadmin, securityadmin, …) oder für eine bestimmte Datenbank (db_owner, db_datareader, …). Diese Rollen sollten jedoch nur unter ganz bestimmten Voraussetzungen vergeben werden, da die Rechte sehr weitreichend sind. Wenn beispielsweise alle Anwender die gleichen umfassenden Rechte haben sollen und auch alle Daten sehen dürfen, dann könnte zum Beispiel die Rolle db_datareader vergeben werden.
DeltaMaster-ETL-Rollen
DeltaMaster ETL bietet inzwischen vier Datenbankrollen an: DeltaMaster_User, DeltaMaster_User_Write, DeltaMaster_User_restricted und DeltaMaster_User_Write_restricted. Alle Rollen vergeben dedizierte Rechte und sind gegenüber den Standard-Rollen eindeutig vorzuziehen. Dennoch gibt es auch bei diesen Rollen einiges zu beachten. DeltaMaster_User und DeltaMaster_User_Write existieren bereits seit vielen Jahren in DeltaMaster ETL. Bei der Erstellung des relationalen Datenmodells werden die Objekte überwiegend per Namenskonvention berechtigt.
DeltaMaster_User erhält Leserechte (SELECT) für alle Objekte mit den folgenden Präfixen:
- T_DIM, V_DIM
- T_FACT, V_FACT
- T_Import, V_Import
- T_S, V_S
- T_D, V_D
- T_WriteBack
- T_WriteTable
- V_WriteBackSQL
- V_SEC
DeltaMaster_User_Write erhält zusätzlich Rechte auf den folgenden Objekten:
- P_WriteBackSQL: EXECUTE
- T_WriteBack: INSERT/UPDATE/DELETE
- T_WriteTable: SELECT/INSERT/UPDATE/DELETE
- TMV_WriteBackSQL: SELECT
Also insgesamt auch sehr umfassende Rechte. Ein User kann mit einem einfachen SQL-Client, z. B. SQL Server Management Studio (SSMS), alle Daten in den Tabellen sehen. Auch die Import-Tabellen und die darauf aufbauenden Views sind mit berechtigt. Das kann natürlich genauso gewünscht sein. Wenn jedoch ein restriktiveres Berechtigungskonzept implementiert wird, dann darf auf keinen Fall eine dieser Rollen vergeben werden.
Die Rollen mit dem Postfix „_restricted“ reduzieren die oben genannten Objekte im Wesentlichen auf die zum Datenmodell zugehörigen Objekte T_DIM/V_DIM, T_FACT/V_FACT, V_SEC, T_Writeback. Objekte zur Erzeugung des Datenmodells werden nicht berechtigt (T_Import, T_S_, …). Das ist schon deutlich besser, jedoch trotzdem nicht ausreichend. Warum?
Es werden Rechte auf Tabellen vergeben: Wenn ein User Rechte auf einer Tabelle hat, dann kann dieser auch alle Daten der Tabelle sehen. Es könnten zwar Spalten maskiert werden, aber ein umfassendes Berechtigungskonzept könnte auf diese Weise nicht umgesetzt werden.
In Views und Prozeduren hingegen können Informationen ausgeblendet oder ergänzt werden, und vor allem kann zur Laufzeit der ausführende User abgefragt werden. Auf diese Weise können dynamische Berechtigungen implementiert werden und den Anwendern wird nur das angezeigt, worauf diese auch berechtigt sind. Dieses Prinzip wird beispielsweise schon lange bei V_SEC-Views benutzt. Auf die untergeordneten Objekte (Tabellen oder Views) sind im Regelfall keine weiteren Rechte erforderlich, da keine Berechtigungsprüfung auf diesen Objekten stattfindet. Dieses Prinzip wird „Ownership Chaining” genannt.
Ownership Chaining
„Ownership Chaining” ist ein Standardverhalten in SQL Server, das häufig verwendet wird, ohne dass man sich dessen bewusst ist. Dieses Prinzip reicht in den allermeisten Fällen auch aus, sodass keine aufwändigen Programmierungen vorgenommen werden müssen.
Wie funktioniert „Ownership Chaining“? Allgemein formuliert: Wenn ein Nutzer ein Modul ausführt, welches ein Objekt referenziert, sowie Modul und Objekt denselben Eigentümer besitzen, dann findet auf den referenzierten Objekten keine Berechtigungsprüfung statt. Dabei sind folgende Voraussetzungen zu beachten:
- Die Objekte gehören alle dem gleichen Schema, z. B. dbo.
- Es darf kein dynamisches SQL verwendet werden, denn bei dynamischem SQL-Code wird immer die Berechtigungsprüfung aktiviert.
Hinweis: Bei dynamischem SQL und Verwendung von Parametern, die sich durch Nutzereingaben verändern, sollten immer Vorkehrungen gegen SQL-Injection getroffen werden. - „Ownership Chaining“ funktioniert nur bei DML-Statements (INSERT, UPDATE, DELETE, MERGE, EXECUTE). TRUNCATE kann beispielsweise nicht ausgeführt werden, da hierfür ALTER-Rechte erforderlich sind.
- Die Objekte liegen in der gleichen Datenbank. Datenbankübergreifend wird im Regelfall die Berechtigungsprüfung aktiviert. Diese kann zwar deaktiviert werden, jedoch ist hiervon dringend abzuraten, da andernfalls die Sicherheit grundsätzlich reduziert wird.
- Metadaten können ebenfalls nicht abgefragt werden. Dabei kann es dazu kommen, dass das Ergebnis ein anderes ist als erwartet. Beispiel: Abfragen auf die object_id, welche häufig in Prozeduren verwendet wird, erfordert VIEW-DEFINITION-Rechte, und wenn diese nicht vergeben sind, dann wird das Objekt auch nicht gefunden, und die Prüfung liefert immer NULL zurück.
Ein grundsätzlicher Vorteil von „Ownership Chaining“ ist, dass normalerweise relativ wenig Objekte berechtigt werden müssen und somit vieles vor den Anwendern versteckt werden kann.
Certificate Signing
Wenn nicht alle Voraussetzungen gegeben sind – z. B. wenn dynamisches SQL angewendet werden soll, aber keine Rechte auf Tabellen vergeben werden sollen – dann ist „Certificate Signing“ die präferierte Lösung. „EXECUTE AS“ ist eine alternative Möglichkeit, die einfach anzuwenden ist, jedoch ist „Certificate Signing“ aus mehreren Gründen vorzuziehen.
Mittels „Certificate Signing“ können Rechte sehr gezielt vergeben werden, es ist vollständig automatisierbar und nicht komplex:
- Signiertes Zertifikat erstellen
- Modul signieren
- Virtuellen User für das Zertifikat erstellen
- Berechtigungen für den User erstellen
Vereinfacht ausgedrückt: Ein Anwender erhält zur Laufzeit die notwendigen Rechte, um beispielsweise nur über eine Prozedur Daten in einer Tabelle ändern zu dürfen.
Zertifikat erstellen
CREATE CERTIFICATE my_first_cert -- name ENCRYPTION BY PASSWORD = 'Password123' -- password WITH SUBJECT = 'This is my first certificate' -- description
Modul signieren
ADD SIGNATURE TO p_test BY CERTIFICATE my_first_cert WITH PASSWORD = 'Password123'
Hinweis: Immer, wenn ein Modul geändert wird, muss dieses neu signiert werden. Denn durch eine Signatur wird sichergestellt, dass der Code sich nicht verändert hat.
Virtuellen User für das Zertifikat erstellen
CREATE USER my_first_cert_user FROM CERTIFICATE my_first_cert
Dieser Schritt ist ausschließlich dafür notwendig, dass nur einem User Rechte zugewiesen werden können und nicht einer Signatur. Der User und die Signatur sind unmittelbar verknüpft und daher kann für eine Signatur auch immer nur ein User angelegt werden. Der Usertyp wird auch mit „CERTIFICATE_MAPPED_USER“ bezeichnet.
User berechtigen
Das Entscheidende ist die Rechtevergabe auf den virtuellen User. Theoretisch wäre es möglich, diesem einfach db_owner-Rechte zu geben. Aber dann könnte im Zweifelsfall, wenn etwas nicht beachtet wird, auch eine Sicherheitslücke entstehen. Deutlich besser und absolut zu empfehlen ist eine dedizierte Vergabe der Rechte.
GRANT INSERT ON dbo.T_MyTable TO my_first_cert_user
Der tatsächlich ausführende AD-User muss dann letztendlich nur noch auf das signierte Objekt berechtigt werden. Das kann selbstverständlich auch über Rollen erfolgen.
Einwände
Jetzt könnte angemerkt werden, dass die meisten User gar nicht in der Lage sind, einen anderen Client zu verwenden, die Verbindungen zum Server auszulesen und sich so Zugang zu Daten zu beschaffen, die nicht für denjenigen bestimmt sind. Das ist in den meisten Fällen auch richtig, aber was ist, wenn es doch jemandem gelingt?
Ein anderer Einwand könnte sein, dass der Datenbankserver für die Anwender gar nicht erreichbar ist, da sich die Anwender immer über einen Webclient, also eine Zwischenschicht, verbinden müssen. Das ist auch absolut richtig, wenn eine Firewall zwischen Client und Datenbankserver ist und der Anwender nur über bestimmte Ports und/oder über eine bestimmte Anwendung an die Daten gelangt. In diesem Fall steuert die Anwendung die Berechtigungen der User. Nur was ist, wenn es in der Anwendung selbst Sicherheitslücken gibt oder Anwender doch noch auf einem anderen Weg berechtigt werden, z. B. wenn ein Zugang über den DeltaMaster-Full-Client vom Desktop aus hergestellt und somit das Sicherheitskonzept ausgehebelt wird?
Ein Sicherheitskonzept darf und soll sogar aus mehreren Ebenen bestehen. Wird eine Ebene ausgehebelt, so steht der „Eindringling“ vor der nächsten Hürde. Je höher der Sicherheitsbedarf ist, je sensibler die Daten sind, desto ausgefeilter sollte ein Sicherheitskonzept sein.
Anwendung auf unsere Hybrid-Planung
Für das hier gewählte Beispiel wurde die Hybrid-Planung der Version 6.2.8 verwendet und ein Datenmodell mit nur einer Datenbank. Datenbankübergreifende Zugriffe sind auch möglich, jedoch ist dafür noch ein wenig mehr zu tun – und ich persönlich habe noch keine Praxiserfahrung damit gesammelt. Wer mehr zu Signierung und datenbankübergreifenden Zugriffen wissen möchte, der wird in der ersten, am Ende dieses Blogbeitrags genannten Quelle, welche ich absolut empfehlen kann, fündig.
Um die Hybrid-Planung wasserdicht zu machen, das heißt keine Faktentabellen zu berechtigen, müssen in diesem Fall nur noch die Prozeduren P_WritebackSQL_Cancel und P_WriteBackSQL_Commit signiert werden, da diese dynamisches SQL beinhalten. Sowohl für eine dedizierte Rechtevergabe als auch für eine Automatisierung ist es sinnvoll, für jede Prozedur ein eigenes Zertifikat anzulegen.
Die ersten drei Schritte sind bereits oben erläutert und für beide Prozeduren fast gleich, daher ist nachfolgend nur der Code für die Cancel-Prozedur ohne Erläuterungen aufgeführt.
Zertifikat anlegen
CREATE CERTIFICATE cert_P_WriteBackSQL_Cancel ENCRYPTION BY PASSWORD = 'Was auch immer' WITH SUBJECT = 'Cancel Hybrid-Planung'
Prozedur P_WriteBackSQL_Cancel signieren
ADD SIGNATURE TO dbo.P_WriteBackSQL_Cancel BY CERTIFICATE cert_P_WriteBackSQL_Cancel WITH PASSWORD = ‘Was auch immer'
User für Zertifikat anlegen
CREATE USER cert_user_P_WriteBackSQL_Cancel FROM CERTIFICATE cert_P_WriteBackSQL_Cancel
Rechte für den User vergeben
Für die Prozedur „P_WriteBackSQL_Cancel“ sind DELETE-, INSERT- und SELECT-Rechte auf allen T_WriteBackSQL_FACT-Tabellen erforderlich sowie DELETE- und SELECT-Rechte auf allen Rollback-Tabellen.
GRANT INSERT ON dbo.T_WriteBackSQL_FACT_01_ABC TO cert_user_P_WriteBackSQL_Cancel GRANT SELECT ON dbo.T_WriteBackSQL_FACT_01_ABC TO cert_user_P_WriteBackSQL_Cancel GRANT DELETE ON dbo.T_WriteBackSQL_FACT_01_ABC TO cert_user_P_WriteBackSQL_Cancel GRANT SELECT ON dbo.T_WriteBackSQL_FACT_01_ABC_Rollback TO cert_user_P_WriteBackSQL_Cancel GRANT DELETE ON dbo.T_WriteBackSQL_FACT_01_ABC_Rollback TO cert_user_P_WriteBackSQL_Cancel
Für die Prozedur „P_WriteBackSQL_Commit“ sind SELECT- und UPDATE-Rechte auf allen T_WriteBackSQL_FACT-Tabellen erforderlich sowie DELETE- und SELECT-Rechte auf allen Rollback-Tabellen.
GRANT SELECT ON dbo.T_WriteBackSQL_FACT_01_ABC TO cert_user_P_WriteBackSQL_Commit GRANT UPDATE ON dbo.T_WriteBackSQL_FACT_01_ABC TO cert_user_P_WriteBackSQL_Commit GRANT SELECT ON dbo.T_WriteBackSQL_FACT_01_ABC_Rollback TO cert_user_P_WriteBackSQL_Commit GRANT DELETE ON dbo.T_WriteBackSQL_FACT_01_ABC_Rollback TO cert_user_P_WriteBackSQL_Commit GRANT INSERT ON dbo.T_WriteBackSQL_FACT_01_ABC_Archive TO cert_user_P_WriteBackSQL_Commit
Der Anwender erhält zur Laufzeit bei Ausführung der Prozeduren einen Token, der diese Berechtigungen hat. Auf diese Weise können Datensätze über die Prozeduren in den Tabellen geändert werden, ohne dass der Anwender auf diese Tabellen überhaupt berechtigt ist.
Quellen
- http://www.sommarskog.se/grantperm.html
- https://www.mssqltips.com/sqlservertip/3336/how-to-use-module-signing-for-sql-server-security/
- https://docs.microsoft.com/de-de/dotnet/framework/data/adonet/sql/writing-secure-dynamic-sql-in-sql-server
- https://docs.microsoft.com/de-de/dotnet/framework/data/adonet/sql/signing-stored-procedures-in-sql-server
