Beschreiben von Datenbank- und Objektberechtigungen

Abgeschlossen

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 Ansichten erteilt, 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 wird und die gleiche Berechtigung verweigert wird, wird die DENY Erteilung immer ersetzt, und der Benutzer wird der Zugriff auf das jeweilige Objekt verweigert.

Im Beispiel wird der Benutzer Demo gewährt SELECT und dann Berechtigungen für den Dbo verweigert SELECT. Unternehmenstabelle . 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). Wenn dies verweigert wird, wird der Benutzer daran gehindert, die Daten innerhalb des Objekts anzuzeigen.
INSERT	Ermöglicht dem Benutzer das Einfügen von Daten in das Objekt. Wenn dies verweigert wird, wird verhindert, dass der Benutzer Daten in das Objekt einfügt.
UPDATE	Ermöglicht dem Benutzer das Aktualisieren von Daten innerhalb des Objekts. Wenn dies verweigert wird, wird verhindert, dass der Benutzer Daten im Objekt aktualisiert.
DELETE	Ermöglicht dem Benutzer das Löschen von Daten innerhalb des Objekts. Wenn dies verweigert wird, wird der Benutzer daran gehindert, Daten aus dem Objekt zu löschen.

Azure SQL-Datenbank und Microsoft SQL Server verfügen über andere Berechtigungen, die bei Bedarf erteilt, 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.

AUSFÜHREN ALS

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. Als nachfolgende Befehle und Anweisungen werden mithilfe des neuen Kontexts mit den Berechtigungen ausgeführt, die diesem Kontext gewährt wurden.

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. Mit dem Befehl "revoke" werden alle GRANT Berechtigungen oder DENY Berechtigungen für das für den angegebenen Benutzer angegebene Recht entfernt.

Besitzketten

Ein Konzept namens Verkettung gilt für Berechtigungen, mit denen Benutzer Berechtigungen von anderen Objekten erben können. 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.

Führen Sie im Beispiel als Datenbankbesitzer oder Serveradministrator aus, wird ein neuer Benutzer erstellt und als Mitglied einer neuen SalesReader-Rolle hinzugefügt, der dann die Berechtigung zum Auswählen aus einem beliebigen Objekt erteilt und eine Prozedur im Vertriebsschema ausführt. 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 Abfrage führt zu einem Fehler, dass der Benutzer DP300User1 nicht SELECT über die Berechtigung verfügt, da die Rolle, zu der der Benutzer gehört, keine Berechtigungen im Produktionsschema 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, gibt es eine nicht unterbrochene Besitzkette, und die Ausführung wird erfolgreich ausgeführt, 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 sehen, indem Sie die gespeicherte Prozedur so ändern, dass sie mit dynamischem SQL wie folgt ausgeführt wird.

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 DP300User1-Benutzer erhält eine Fehlermeldung, dass der Benutzer nicht SELECT über die Berechtigung für die Tabelle "Production.Product " verfügt, genau wie der Benutzer versucht hat, 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.

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

Die Anweisung generiert eine Liste der T-SQL-Anweisungen, um alle Datenbanken auf dem Server zu sichern. In der Regel wird diese generierte T-SQL mit sp_executesql einem anderen auszuführenden Programm ausgeführt oder übergeben.