Beschreiben von Datenbank- und Objektberechtigungen

  • 10 Minuten

Alle relationalen Datenbankverwaltungsplattformen haben vier grundlegende Berechtigungen, die DML-Vorgänge (Data Manipulation Language, Datenbearbeitungssprache) kontrollieren. Diese Berechtigungen sind SELECT, INSERT, UPDATE und DELETE und gelten für alle SQL Server-Plattformen. Alle diese Berechtigungen können für Tabellen und Sichten gewährt, widerrufen oder verweigert werden. Wenn eine Berechtigung mit der GRANT-Anweisung erteilt wird, dann wird die Berechtigung dem Benutzer oder der Rolle erteilt, auf den/die in der GRANT-Anweisung verwiesen wird. Benutzern können Berechtigungen auch mit dem Befehl DENY verweigert werden. Wenn einem Benutzer eine Berechtigung gewährt und dieselbe Berechtigung verweigert wird, hat DENY immer Vorrang vor der Gewährung (GRANT), und dem Benutzer wird der Zugriff auf das spezifische Objekt verweigert.

A T-SQL Example of a DENY overriding a GRANT

Im obigen Beispiel werden dem Benutzer „Demo“ SELECT-Berechtigungen für die Tabelle dbo.Company gewährt und anschließend SELECT-Berechtigungen verweigert. Wenn der Benutzer versucht, eine Abfrage auszuführen, die eine Auswahl aus der Tabelle dbo.Company trifft, erhält der Benutzer die Fehlermeldung, dass die SELECT-Berechtigung verweigert wurde.

Berechtigungen für Tabellen und Sichten

Tabellen und Sichten stellen die Objekte dar, für die innerhalb einer Datenbank Berechtigungen erteilt werden können. Innerhalb dieser Tabellen und Sichten können Sie zusätzlich die Spalten einschränken, auf die ein bestimmter Sicherheitsprinzipal (Benutzer oder Anmeldung) zugreifen kann. SQL Server und Azure SQL-Datenbank enthalten auch Sicherheit auf Zeilenebene, die verwendet werden kann, um den Zugriff weiter einzuschränken.

Berechtigung Definition
SELECT Ermöglicht dem Benutzer das Anzeigen der Daten innerhalb des Objekts (Tabelle oder Ansicht). Wird sie verweigert, wird der Benutzer daran gehindert, die Daten innerhalb des Objekts anzuzeigen.
INSERT Ermöglicht dem Benutzer das Einfügen von Daten in das Objekt. Wird sie verweigert, wird der Benutzer daran gehindert, Daten in das Objekt einzufügen.
UPDATE Ermöglicht dem Benutzer das Aktualisieren von Daten innerhalb des Objekts. Wird sie verweigert, wird der Benutzer daran gehindert, Daten im Objekt zu aktualisieren.
DELETE Ermöglicht dem Benutzer das Löschen von Daten innerhalb des Objekts. Wird sie verweigert, wird der Benutzer daran gehindert, Daten aus dem Objekt zu löschen.

Azure SQL-Datenbank und Microsoft SQL Server verfügen über weitere Berechtigungen, die je nach Bedarf gewährt, widerrufen oder verweigert werden können.

Berechtigung Definition
CONTROL Erteilt den Objekten alle Rechte. Es erlaubt dem Benutzer, der diese Berechtigung besitzt, jede beliebige Aktion an dem Objekt durchführen, einschließlich des Löschens des Objekts.
REFERENCES Gewährt dem Benutzer die Fähigkeit, die Fremdschlüssel des Objekts anzuzeigen.
TAKE OWNERSHIP Ermöglicht dem Benutzer, den Besitz des Objekts zu übernehmen.
VIEW CHANGE TRACKING Ermöglicht dem Benutzer, die Einstellung für die Änderungsverfolgung für das Objekt anzuzeigen.
VIEW DEFINITION Ermöglicht dem Benutzer, die Definition des Objekts anzuzeigen.

Berechtigungen für Funktionen und gespeicherte Prozeduren

Wie Tabellen und Ansichten haben auch Funktionen und gespeicherte Prozeduren mehrere Berechtigungen, die gewährt oder verweigert werden können.

Berechtigung Definition
ALTER Gewährt dem Benutzer die Fähigkeit, die Definition des Objekts zu ändern.
CONTROL Gewährt dem Benutzer alle Rechte an dem Objekt.
EXECUTE Gewährt dem Benutzer die Fähigkeit, das Objekt auszuführen.
VIEW CHANGE TRACKING Ermöglicht dem Benutzer, die Einstellung für die Änderungsverfolgung für das Objekt anzuzeigen.
VIEW DEFINITION Ermöglicht dem Benutzer, die Definition des Objekts anzuzeigen.

EXECUTE AS

Mit den Befehlen EXECUTE AS [user name] oder EXECUTE AS [login name] (nur in SQL Server und Azure SQL Managed Instance verfügbar) lässt sich der Benutzerkontext ändern. Nachfolgende Befehle und Anweisungen werden unter Verwendung des neuen Kontexts mit den für diesen Kontext gewährten Berechtigungen ausgeführt.

Wenn ein Benutzer eine Berechtigung besitzt und diese nicht mehr benötigt, können (entweder gewährte oder verweigerte) Berechtigungen mit dem Befehl REVOKE entfernt werden. Der Befehl zum Widerrufen entfernt alle GRANT- oder DENY-Berechtigungen des angegebenen Rechts für den angegebenen Benutzer.

Besitzketten

Für Berechtigungen gilt ein Konzept namens „Verkettung“, das es Benutzern ermöglicht, Berechtigungen von anderen Objekten zu erben. Das häufigste Beispiel für Verkettung ist eine Funktion oder gespeicherte Prozedur, die während ihrer Ausführung auf eine Tabelle zugreift. Wenn die Prozedur denselben Besitzer wie die Tabelle hat, kann die gespeicherte Prozedur ausgeführt werden und auf die Tabelle zugreifen, auch wenn der Benutzer keine Rechte für den direkten Zugriff auf die Tabelle besitzt. Dieser Zugriff ist verfügbar, weil der Benutzer die Rechte zum Zugriff auf die Tabelle von der gespeicherten Prozedur erbt, aber nur während der Ausführung der gespeicherten Prozedur und nur im Kontext der Ausführung der gespeicherten Prozedur.

Im folgenden Beispiel, das als Datenbankbesitzer oder Serveradministrator ausgeführt wird, wird ein neuer Benutzer erstellt und als Mitglied einer neuen Rolle SalesReader hinzugefügt, der dann die Berechtigung erteilt wird, im Sales-Schema jedes Objekt auszuwählen und jede Prozedur auszuführen. Dann wird im Sales-Schema eine gespeicherte Prozedur erstellt, die auf eine Tabelle im Production-Schema (Produktion) zugreift.

Das Beispiel ändert dann den Kontext auf den neuen Benutzer, und es wird versucht, direkt aus der Tabelle im Production-Schema auszuwählen.

USE AdventureWorks2016;
GO

CREATE USER [DP300User1] WITH PASSWORD = 'Pa55.w.rd';
GO

CREATE ROLE [SalesReader];
GO

ALTER ROLE [SalesReader] ADD MEMBER [DP300User1];
GO

GRANT SELECT, EXECUTE ON SCHEMA::Sales TO [SalesReader];
GO

CREATE OR ALTER PROCEDURE Sales.DemoProc
AS
SELECT P.Name, 
    SUM(SOD.LineTotal) AS TotalSales,
    SOH.OrderDate 
FROM Production.Product P
    INNER JOIN Sales.SalesOrderDetail SOD ON (SOD.ProductID = P.ProductID)
    INNER JOIN Sales.SalesOrderHeader SOH ON (SOH.SalesOrderID = SOD.SalesOrderID)
GROUP BY P.Name, 
    SOH.OrderDate
ORDER BY TotalSales DESC;

GO

EXECUTE AS USER = 'DP300User1';

SELECT P.Name, 
    SUM(SOD.LineTotal) AS TotalSales,
    SOH.OrderDate 
FROM Production.Product P
    INNER JOIN Sales.SalesOrderDetail SOD ON (SOD.ProductID = P.ProductID)
    INNER JOIN Sales.SalesOrderHeader SOH ON (SOH.SalesOrderID = SOD.SalesOrderID)
GROUP BY P.Name, 
    SOH.OrderDate
ORDER BY TotalSales DESC;

Die obige Abfrage führt zu dem Fehler, dass der Benutzer DP300User1 keine SELECT-Berechtigung besitzt, weil die Rolle, zu der der Benutzer gehört, keine Berechtigungen im Production-Schema besitzt. Jetzt können wir versuchen, die gespeicherte Prozedur auszuführen:

EXECUTE AS USER = 'DP300User1';

EXECUTE Sales.DemoProc;

Der DP300User1 besitzt die EXECUTE-Berechtigung für die gespeicherte Prozedur im Sales-Schema, da die Rolle des Benutzers die EXECUTE-Berechtigung für das Sales-Schema besitzt. Da die Tabelle denselben Besitzer wie die Prozedur hat, liegt wir eine ununterbrochene Besitzkette vor, und die Ausführung wird erfolgreich sein, und die Ergebnisse werden zurückgegeben.

Berechtigungsänderungen gelten nicht, wenn dynamisches SQL innerhalb gespeicherter Prozeduren verwendet wird. Dynamisches SQL unterbricht die Berechtigungskette, weil dynamisches SQL außerhalb des Kontexts der aufrufenden gespeicherten Prozedur ausgeführt wird. Sie können dieses Verhalten beobachten, indem Sie die obige gespeicherte Prozedur so ändern, dass sie unter Verwendung von dynamischem SQL ausgeführt wird, wie unten gezeigt.

CREATE OR ALTER PROCEDURE Sales.DemoProc
AS
DECLARE @sqlstring NVARCHAR(MAX)

SET @sqlstring = '
SELECT P.Name, 
    SUM(SOD.LineTotal) AS TotalSales, 
    SOH.OrderDate 
FROM Production.Product P
    INNER JOIN Sales.SalesOrderDetail SOD ON (SOD.ProductID = P.ProductID)
    INNER JOIN Sales.SalesOrderHeader SOH ON (SOH.SalesOrderID = SOD.SalesOrderID)
GROUP BY P.Name, SOH.OrderDate'

EXECUTE sp_executesql @sqlstring
GO

--

EXECUTE AS USER = 'DP300User1'

EXECUTE Sales.DemoProc

Der Benutzer DP300User1 erhält eine Fehlermeldung, dass der Benutzer keine SELECT-Berechtigung für die Tabelle Production.Product besitzt, genauso als ob der Benutzer versucht hätte, die Abfrage direkt auszuführen. Berechtigungsketten gelten nicht, und das Benutzerkonto, das das dynamische SQL ausführt, muss Rechte für die Tabellen und Ansichten besitzen, die vom Code innerhalb des dynamischen SQL verwendet werden.

Prinzip der geringsten Rechte

Das Prinzip der geringsten Rechte ist recht einfach. Der Grundgedanke hinter dem Konzept ist, dass Benutzer und Anwendungen nur die Berechtigungen erhalten sollten, die sie für die Erfüllung der jeweiligen Aufgabe benötigen. Anwendungen sollten nur die Berechtigungen haben, die sie benötigen, um die anstehende Aufgabe zu erledigen.

Wenn eine Anwendung beispielsweise über gespeicherte Prozeduren auf alle Daten zugreift, dann sollte die Anwendung nur die Berechtigung zum Ausführen der gespeicherten Prozeduren haben, ohne Zugriff auf die Tabellen.

Dynamische SQL-Anweisungen

Dynamisches SQL ist ein Konzept, bei dem eine Abfrage programmgesteuert erstellt wird. Mit dynamischem SQL können T-SQL-Anweisungen innerhalb einer gespeicherten Prozedur oder einer Abfrage selbst erzeugt werden. Ein einfaches Beispiel sehen Sie unten.

SELECT 'BACKUP DATABASE ' + name + ' TO DISK =''\\backup\sql1\' + name + '.bak'''
FROM sys.databases

Die obige Anweisung generiert eine Liste von T-SQL-Anweisungen, um die gesamte Datenbank auf dem Server zu sichern. Normalerweise wird dieser generierte T-SQL-Code mithilfe von sp_executesql ausgeführt oder an ein anderes Programm zur Ausführung übergeben.