Einsatz der Query Store mit least Privileges anstelle von db_owner, um Separation of Duties zu erreichen
(Der Original-Artikel ist hier auf dem Microsoft Techcommunity Blog zu finden: Using Query Store with least privileges instead of db_owner to achieve Separation of Duties)
Wenn man den Query Store in einer Produktionsumgebung verwendet, müssen Kunden manchmal sehr spezifisch delegieren, wenn es darum geht, wer was in Sachen Leistungsanalyse und/oder Tuning tun darf.
Die Query Store ist eine Komponente, die pro Datenbank aktiviert werden kann, und wird zur Fehlerdiagnose bei der Abfrageleistung verwendet. Wenn Sie verstehen wollen, wie Query Store funktioniert und wie sie Ihnen helfen kann, können Sie mit dieser Seite einsteigen: Monitoring performance by using the Query Store.
In diesem Artikel werde ich zeigen, wie Kunden die typischen Aktivitäten rund um den Query Store isolieren und minimale Berechtigungssets an Nutzergruppen vergeben können, denen bestimmte Auftragen übertragen werden.
Wer und was: Rollen identifizieren
Was Sicherheit betrifft, hilft es im Allgemeinen, zuerst die Rollen zu identifizieren, die mit der betreffenden Funktion arbeiten werden.
– Ich verwende den Begriff Rolle, um Aufgaben zusammenzufassen, die ähnlich sind und die höchstwahrscheinlich von der gleichen Gruppe von Leuten bearbeitet werden. Meistens (so wie hier) wird sie am Ende als eine Datenbankrolle in SQL umgesetzt.
Typischerweise gibt es bei der Arbeit mit der Query Store 3 Aspekte, die in Rollen widerspiegelt werden können:
- Konfiguration – Query Store an- und ausschalten, die Inhalte löschen, ihre Inhalte auf die Festplatte verschieben und ihre Einstellungen verändern.
- Die Berichte ansehen oder die DMVs verwenden, um die Query Store Inhalte (Abfragen, Pläne und Wartestatistiken) zu analysieren, um Einblicke zu erhalten, aber nicht unbedingt die Berechtigung zu haben, etwas zu ändern
- Pläne aktiv durch force oder unforce ändern, basierend auf den Informationen, die unter 2) erhalten wurden
Das mag jeder ein wenig anders aufteilen. Meistens werden die Rollen 2 und 3 durch die gleiche Gruppe von Leuten ausgeführt. In kleineren Umgebungen mit weniger Aufgabenisolierung wird alles in derselben Hand sein.
Erforderliche Berechtigungen
Wissen wir einmal, was wir benötigen, um hinsichtlich Berechtigungen zu trennen, können wir die minimal erforderlichen Berechtigungen identifizieren, um das Principle of Least Privileges einzuhalten.
Kurze Info
Mittlerweile werden Sie erkannt haben, dass es 2 sehr grundsätzliche Sicherheitsprinzipien gibt, die sich durch diesen Artikel ziehen: Separation of Duties (SoD) und Principle of Least Privileges (PoLP).
Rolle 1, nennen wir sie DatabaseAdmin (was darauf hinweist, dass dies höchstwahrscheinlich nicht die einzige Aufgabe der Rolle im echten Leben sein wird), wird das Ausführen solcher Befehle erfordern wie:
ALTER DATABASE QS_SoD SET QUERY_STORE = ON
- Um die Query Store einzuschalten. Hinweis: In der Azure SQL Datenbank ist die Query Store standardmäßig aktiviert.
ALTER DATABASE [QS_SoD] SET QUERY_STORE (MAX_STORAGE_SIZE_MB = 200)
ALTER DATABASE [QS_SoD] SET QUERY_STORE (QUERY_CAPTURE_MODE = ALL)
- Um die Größe oder den Abfrageerfassungsmodus zu verändern.
ALTER DATABASE [QS_SoD] SET QUERY_STORE CLEAR ALL
- Bereinigung
Quintessenz: All diese Konfigurationsoptionen sind Teil des ALTER DATABASE-Befehls.
Da ist noch etwas: Die Inhalte auf die Festplatte schreiben, die noch nicht auf die Disk geschrieben worden sind. Das wird unter Verwendung der gespeicherten Prozedur sp_query_store_flush_db gemacht.
Die geringste Berechtigung, die für alles Obengenannte erforderlich ist, einschließlich der Ausführung der bereitgestellten gespeicherten Prozedur: ALTER auf der Datenbank
Kurze Info
In SQL Server oder Azure SQL Database Managed Instance können Sie ALTER auf einer Datenbank auf zweierlei Weise berechtigen: Neben der Datenbankberechtigung “ALTER” gibt es auch die Serverberechtigung “ALTER ANY DATABASE”. Die Serverberechtigung zu verwenden, würde auch in dem Fall Sinn ergeben, wenn Sie diese Berechtigungen mit einem Befehl auf alle Datenbanken auf einer bestimmten SQL Instanz vergeben möchten. Sie muss an einen Server Principal vergeben werden (idealerweise eine benutzerdefinierte Serverrolle).
Rolle 2, nennen wir sie QueryStoreReader, wird SSMS verwenden wollen, um die eingebauten Berichte zu nutzen, aber auch Zugriff auf die Query Store DMVs für komplexere und Massen-analysen. Da die SSMS-Berichte auf den DMVs basieren, wird dies von derselben Berechtigung abgedeckt.
Die Liste der Query Store DMVs ist:
- sys.database_query_store_options
- sys.query_context_settings
- sys.query_store_plan
- sys.query_store_query
- sys.query_store_query_text
- sys.query_store_wait_stats
- sys.query_store_runtime_stats
- sys.query_store_runtime_stats_interval
Die minimale Berechtigung, die für alles Obengenannte erforderlich ist: VIEW DATABASE STATE
Hinweis zur Azure SQL Database
Wenn Sie Intelligent Performance im Azure Portal verwenden, um die Leistung zu analysieren und Empfehlungen zu sammeln, ist dies unabhängig von der Datenbankberechtigung innerhalb der SQL-Maschine. Aktivitäten im Portal werden mit Azure RBAC Rollen kontrolliert. Das wird Thema eines anderen Artikels sein.
Rolle 3, nennen wir sie TuningTeam, ist diejenige, die aktiv Abfragepläne ändern kann, indem sie Pläne durch den Query Store auf einfache Weise erzwingt und die Erzwingung zurücknimmt.
Kurze Info
Als Best-Practice sollte dies eher die Ausnahme bleiben oder eine schnelle Reparatur sein, bis die Abfrage oder Indexe so getuned wurden, dass der Abfrageoptimierer wieder automatisch einen guten Plan erzeugen kann. Es ist daher erst recht sinnvoll, nicht zu vielen Leuten diese Berechtigung zu erteilen.
Einen Plan zu erzwingen, kann durch die Query Store Berichte erreicht werden oder durch das direkte Aufrufen der jeweiligen gespeicherten Prozedur.
Die folgenden gespeicherten Prozeduren ändern aktiv Pläne oder die gespeicherten Inhalte für einen bestimmten Plan, und es würde höchstwahrscheinlich sinnvoll sein, diese dieser Rolle zuzuteilen:
- sp_query_store_force_plan
- sp_query_store_remove_plan
- sp_query_store_remove_query
- sp_query_store_reset_exec_stats
- sp_query_store_unforce_plan
Die Mindestberechtigung, die für alles Obengenannte erforderlich ist: ALTER on the Database
(Außerdem muss das TuningTeam auch die Information aus dem Query Store sehen können, daher also auch VIEW DATABASE STATE.)
Das Problem: hohe Privilegien erforderlich
Die Berechtigung für die ALTER-Berechtigung auf der Datenbank für das TuningTeam ist das offensichtliche Problem: Mit dem aktuellen Set an Berechtigungen, die für die Query Store-Vorgänge erforderlich sind, ist es nicht von Haus aus möglich, die DatabaseAdmins von einem TuningTeam zu trennen. Hier müssen wir eine bestimmte Technik verwenden, um granulare Berechtigungen anzupassen: Befehle in spezielle gespeicherte Prozeduren einpacken.
Kurze Info
Die ALTER-Berechtigung auf einer Datenbank vergibt momentan 54 Berechtigungen, was nicht nur die Query Store-Konfiguration abdeckt, sondern auch andere Datenbankoptionen bis hin zum Erstellen, Ändern und Löschen aller Datenbankobjekte. Es ist immerhin weniger als die 81 Berechtigungen, die durch CONTROL impliziert werden, aber es impliziert eine Reihe an Methoden, die eine noch weitere Erhöhung von Privilegien ermöglichen würden.
Die Lösung: Code Wrapping in gespeicherten Prozeduren mit EXECUTE AS
Die folgende Lösung hat sich als sehr praktisch bei vielen Szenarien erwiesen, seit sie 2005 im SQL Server vorgestellt wurde: Das Verwenden der EXECUTE AS clause einer benutzerdefinierten gespeicherten Prozedur, die die Befehle enthält, die hohe Privilegien erfordern.
In unserem Szenarium sind die 5 betroffenen „Befehle“ tatsächlich selbst gespeicherte Prozeduren. (sp_query_store_force_plan, sp_query_store_remove_plan, sp_query_store_remove_query, sp_query_store_reset_exec_stats, sp_query_store_unforce_plan).
Alle von ihnen werden mit mindestens einem Parameter ausgeführt, entweder query_id oder plan_id oder beide. Keine Standardeinstellungen. Daher ist es sehr einfach oder gar trivial umzusetzen, und das vorliegende Beispiel kann ohne größere Änderungen an alle anderen vier gespeicherten Prozeduren angepasst werden.
Das Konzept ist folgendes:
1) Wir müssen einen Datenbank-Principal mit den erforderlichen Privilegien haben, um die Befehle/gespeicherten Prozeduren ausführen zu lassen: ALTER DATABASE
– Dieser Principal sollte nicht für interaktive Arbeit verwendet werden und wird daher ohne Login und Passwort erstellt. Im vorliegenden Beispielskript heißt dieser Nutzer internal_principal_ALTER_DB.
Kurze Info
Als eine Best-Practice, die hilft, das Analysieren und Berichten über vergebene Berechtigungen zu vereinfachen, verwende ich immer und ohne Ausnahme Rollen und vergebe Berechtigungen ausschließlich an Rollen. Das ist der Grund, weshalb ich in diesem Beispiel den extra Schritt gehe, der auch nur 2 Befehle mehr erforderte (CREATE und ALTER Role).
Im vorliegenden Beispielskript wird diese Rolle role_internal_principal_ALTER_DB genannt. (Nicht „hübsch“, aber auf den Punkt, hoffe ich)
2) Wir erstellen eine gespeicherte Prozedur mit EXECUTE AS und verwenden den Principal, den wir erstellt haben, um den Kontext zu spezifizieren, in dem das Modul ausgeführt wird. Dies erfordert keine extra Berechtigungen für den, der die gespeicherte Prozedur ausführt, außer der EXECUTE-Berechtigung selbst.
CREATE OR ALTER PROCEDURE db_tuning_tools.up_sp_query_store_force_plan
@query_id bigint
, @plan_id bigint
WITH EXECUTE AS ‚internal_principal_ALTER_DB‘
AS
EXECUTE sp_query_store_force_plan
@query_id = @query_id
, @plan_id = @plan_id
GO
3) Die gespeicherte Prozedur enthält eine der gelisteten gespeicherten Prozeduren und gibt alle Parameter an sie weiter.
– Als ein Vorteil ermöglicht dies Ihnen, zusätzliche operative oder Geschäftslogik hinzuzufügen wie zum Beispiel:
A) Protokollieren, Wer und Wann die Aktion versuchte
B) Weitere Checks einschließen wie “Ausführung während kritischer Geschäftszeiten blocken“ oder eine 2. Genehmigung erfordern
C) Die Ausführung durch Anwendung von Service Broker-Queuing aufschieben, anstelle direkter Ausführung
Um ein paar Beispiel zu nennen.
4) Der TuningTeam-Rolle die Execute-Berechtigung für die Wrapper-Prozeduren erteilen.
Im vorliegenden Beispielskript wird diese Rolle Role_TuningTeam
Diese sollte nicht auf Objekt-Bereich sondern auf Schema-Scope erelt werden. Wir wollen auch sicherstellen, keins der existierenden Schemas zu verwenden, um andere Objekte zu beeinflussen. Daher ist die sauberste Methode, ein separates Schema für diese speziellen Prozeduren zu erstellen, auf die das TuningTeam Zugriff benötigt.
Im vorliegenden Beispielskript heißt das Schema db_tuning_tools.
Neben dem Zugriff auf diese Prozeduren muss das TuningTeam auch die Inhalte des Query Stores sehen können, was die VIEW DATABASE STATE-Berechtigung erfordert.
Nachteil
Auf einen kleinen Nachteil muss ich jedoch hinweisen: Wenn man diese Art der Lösung verwendet, nämlich anstelle der eingebauten gespeicherten Prozeduren oder Befehle benutzerdefinierten Code zu verwenden, kann die graphische UI von Management Studio nicht mehr verwendet werden. Genauer gesagt: Pläne via Query Store Berichte zu forcen oder unforcen. Die Berichte selbst können natürlich noch geöffnet werden. Aber einen Plan via des UI zu forcieren zu versuchen wird einfach zu einem Berechtigungsfehler führen, und in dem Fall muss das TuningTeam instruiert werden, die benutzerdefinierten Prozeduren für diese Aktionen zu verwenden.
Schlussfolgerung
Das Gewähren von Zugang zu nur Teilen der Query Store Funktionen kann mit wenig zusätzlichem Aufwand ermöglicht werden, indem man Wrapper-Prozeduren mit EXECUTE AS verwendet.
In den hier beigefügten Skripten finden Sie die Beispiellösung.
Happy Tuning and Securing
Andreas
