Freigeben über

Erste Schritte mit Berechtigungen für Datenbank-Engine

Gilt für:SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceAzure Synapse AnalyticsAnalytics Platform System (PDW)SQL-Datenbank in Microsoft Fabric

In diesem Artikel werden einige grundlegende Sicherheitskonzepte überprüft und dann eine typische Implementierung von Berechtigungen beschrieben. Berechtigungen im Datenbankmodul werden auf Serverebene über Anmelde- und Serverrollen und auf Datenbankebene über Datenbankbenutzer und Datenbankrollen verwaltet.

SQL-Datenbank und SQL-Datenbank in Microsoft Fabric bieten die gleichen Optionen in jeder Datenbank, aber die Berechtigungen auf Serverebene sind nicht verfügbar.

Note

Microsoft Entra ID war zuvor als Azure Active Directory (Azure AD) bekannt.

Sicherheitsprinzipale

Ein Sicherheitsprinzipal ist die Identität, die SQL Server verwendet, der Berechtigungen zum Ausführen von Aktionen zugewiesen werden können. Sicherheitsprinzipale sind in der Regel Personen oder Personengruppen, können aber andere Entitäten sein, die vorgeben, Personen zu sein. Sicherheitsprinzipale können mithilfe der in diesem Artikel gezeigten Transact-SQL Beispiele oder mithilfe von SQL Server Management Studio erstellt und verwaltet werden.

Logins

Anmeldungen sind einzelne Benutzerkonten für die Anmeldung beim SQL Server-Datenbankmodul. SQL Server und SQL-Datenbank unterstützen Anmeldungen basierend auf der Windows-Authentifizierung und Anmeldungen, die auf der SQL Server-Authentifizierung basieren. Informationen zu den beiden Arten von Anmeldungen finden Sie unter Auswählen eines Authentifizierungsmodus.

Feste Serverrollen

In SQL Server sind feste Serverrollen eine Reihe vorkonfigurierten Rollen, die eine bequeme Gruppe von Berechtigungen auf Serverebene bereitstellen. Anmeldungen können Rollen mit der ALTER SERVER ROLE ... ADD MEMBER-Anweisung hinzugefügt werden. Weitere Informationen finden Sie unter ALTER SERVER ROLE. SQL-Datenbank unterstützt die festen Serverrollen nicht, verfügt jedoch über zwei Rollen in der master Datenbank (dbmanager und loginmanager), die wie Serverrollen fungieren.

Benutzerdefinierte Serverrollen

In SQL Server können Sie eigene Serverrollen erstellen und diesen Berechtigungen auf Serverebene zuweisen. Anmeldungen können Serverrollen mit der ALTER SERVER ROLE ... ADD MEMBER-Anweisung hinzugefügt werden. Weitere Informationen finden Sie unter ALTER SERVER ROLE. SQL-Datenbank unterstützt die benutzerdefinierten Serverrollen nicht.

Datenbankbenutzer

Um zugriff auf eine Anmeldung bei einer Datenbank zu gewähren, erstellen Sie einen Datenbankbenutzer in dieser Datenbank und ordnen den Datenbankbenutzer einer Anmeldung zu. Der Datenbankbenutzername ist in der Regel identisch mit dem Anmeldenamen nach Konvention, muss jedoch nicht identisch sein. Jeder Datenbankbenutzer ist einer einzelnen Anmeldung zugeordnet. Eine Anmeldung kann nur einem Benutzer in einer Datenbank zugeordnet werden, aber sie kann als ein Benutzer in mehreren verschiedenen Datenbanken zugeordnet werden.

Es können auch Datenbankbenutzer erstellt werden, die keine entsprechende Anmeldung haben. Diese Benutzer werden als eigenständige Datenbankbenutzerbezeichnet. Microsoft empfiehlt die Verwendung von enthaltenen Datenbankbenutzern, da es einfacher ist, Ihre Datenbank auf einen anderen Server zu verschieben. Wie eine Anmeldung kann ein eigenständiger Datenbankbenutzer entweder die Windows- oder SQL Server-Authentifizierung verwenden. Weitere Informationen finden Sie unter Machen Sie Ihre Datenbank portabel, indem Sie enthaltene Datenbanken verwenden.

Es gibt 12 Typen von Benutzern mit geringfügigen Unterschieden dahingehend, wie sie sich authentifizieren und wen sie darstellen. Eine Liste der Benutzer finden Sie unter CREATE USER.

Feste Datenbankrollen

Feste Datenbankrollen sind eine Reihe vorkonfigurierten Rollen, die eine bequeme Gruppe von Berechtigungen auf Datenbankebene bereitstellen. Datenbankbenutzer und benutzerdefinierte Datenbankrollen können festen Datenbankrollen mithilfe der ALTER ROLE ... ADD MEMBER-Anweisung hinzugefügt werden. Weitere Informationen finden Sie unter ALTER ROLE.

Benutzerdefinierte Datenbankrollen

Benutzer mit der CREATE ROLE Berechtigung können neue benutzerdefinierte Datenbankrollen erstellen, um Gruppen von Benutzern mit allgemeinen Berechtigungen darzustellen. In der Regel werden Berechtigungen der gesamten Rolle erteilt oder verweigert, was die Verwaltung und Überwachung von Berechtigungen vereinfacht. Mithilfe der ALTER ROLE ... ADD MEMBER-Anweisung können Datenbankbenutzer den Datenbankrollen hinzugefügt werden. Weitere Informationen finden Sie unter ALTER ROLE.

Andere Leitende Personen

Andere hier nicht behandelte Sicherheitsprinzipale umfassen Anwendungsrollen und Anmeldungen und Benutzer basierend auf Zertifikaten oder asymmetrischen Schlüsseln.

Eine Grafik mit den Beziehungen zwischen Windows-Benutzern, Windows-Gruppen, Anmeldeinformationen und Datenbankbenutzern finden Sie unter Erstellen eines Datenbankbenutzers.

Typisches Szenario

Das folgende Beispiel zeigt eine allgemeine und empfohlene Methode zum Konfigurieren von Berechtigungen.

In Microsoft Active Directory oder Microsoft Entra ID.

  1. Erstellen Sie einen Benutzer für jede Person.
  2. Erstellen Sie Windows-Gruppen, die die Arbeitseinheiten und Arbeitsfunktionen darstellen.
  3. Fügen Sie Windows-Benutzer den Windows-Gruppen hinzu.

Wenn der Benutzer eine Verbindung mit vielen Datenbanken herstellt

  1. Erstellen Sie für die Windows-Gruppen eine Anmeldung. (Wenn Sie die SQL Server-Authentifizierung verwenden, überspringen Sie die Active Directory-Schritte, und erstellen Sie hier SQL Server-Authentifizierungsanmeldungen.)

  2. Erstellen Sie in der Benutzerdatenbank einen Datenbankbenutzer für die Anmeldung, die die Windows-Gruppen darstellt.

  3. Erstellen Sie in der Benutzerdatenbank eine oder mehrere benutzerdefinierte Datenbankrollen, die jeweils eine ähnliche Funktion darstellen. Sie könnten beispielsweise eine Rolle als Finanzanalyst und eine Rolle als Vertriebsanalyst haben.

  4. Fügen Sie die Datenbankbenutzer einer oder mehreren benutzerdefinierten Datenbankrollen hinzu.

  5. Erteilen Sie den benutzerdefinierten Datenbankrollen Berechtigungen.

Wenn der Benutzer nur eine Verbindung mit einer Datenbank herstellt

  1. Erstellen Sie in der Benutzerdatenbank für die Windows-Gruppen einen eigenständigen Datenbankbenutzer. (Wenn Sie die SQL Server-Authentifizierung verwenden, überspringen Sie die Active Directory-Schritte, und erstellen Sie hier die SQL Server-Authentifizierung des Datenbankbenutzers.)

  2. Erstellen Sie in der Benutzerdatenbank eine oder mehrere benutzerdefinierte Datenbankrollen, die jeweils eine ähnliche Funktion darstellen. Sie könnten beispielsweise eine Rolle als Finanzanalyst und eine Rolle als Vertriebsanalyst haben.

  3. Fügen Sie die Datenbankbenutzer einer oder mehreren benutzerdefinierten Datenbankrollen hinzu.

  4. Erteilen Sie den benutzerdefinierten Datenbankrollen Berechtigungen.

Das typische Ergebnis an diesem Punkt ist, dass ein Windows-Benutzer Mitglied einer Windows-Gruppe ist. Die Windows-Gruppe verfügt über eine Anmeldung in SQL Server oder SQL-Datenbank. Die Anmeldung ist einer Benutzeridentität in der Benutzerdatenbank zugeordnet. Der Benutzer ist Mitglied einer Datenbankrolle. Nun müssen Sie der Rolle Berechtigungen hinzufügen.

Berechtigungen zuweisen

Die meisten Berechtigungsanweisungen haben das folgende Format:

<authorization> <permission> ON <securable>::<name> TO <principal>;

  • <authorization> muss GRANT , REVOKE oder DENY sein.

  • Die <permission> legt die von Ihnen zugelassene oder verbotene Aktion fest. Die genaue Anzahl der Berechtigungen unterscheidet sich zwischen SQL Server und Azure SQL-Datenbank. Informationen zu Berechtigungen finden Sie unter Berechtigungen (Datenbankmodul) und sehen Sie weiter unten im Artikel das Diagramm.

  • ON <securable>::<name> ist der Typ des sicherungsfähigen Objektes (Server, Serverobjekt, Datenbank oder Datenbankobjekt) und sein Name. Bei einigen Berechtigungen ist <securable>::<name> nicht erforderlich, weil es nicht eindeutig oder im Kontext unpassend ist. Die CREATE TABLE-Berechtigung erfordert z.B. nicht die <securable>::<name>-Klausel (GRANT CREATE TABLE TO Mary; ermöglicht Mary das Erstellen von Tabellen).

  • <principal> ist der Sicherheitsprinzipal (Anmeldung, Benutzer oder Rolle), der die Berechtigung empfängt oder verliert. Erteilen Sie Berechtigungen nach Möglichkeit stets Rollen.

Die folgende Beispielanweisung gewährt der Rolle mit dem Namen UPDATE die Berechtigung Parts für die im Schema Production enthaltene Tabelle oder Ansicht PartsTeam:

GRANT UPDATE ON OBJECT::Production.Parts TO PartsTeam;

Die folgende Beispielanweisung gewährt die UPDATE Berechtigung für das Production Schema und zusätzlich für jede Tabelle oder Ansicht, die in diesem Schema enthalten ist, der Rolle namens ProductionTeam, was ein effektiverer und skalierbarer Ansatz zum Zuweisen von Berechtigungen als auf der Ebene einzelner Objekte ist.

GRANT UPDATE ON SCHEMA::Production TO ProductionTeam;

Berechtigungen werden Sicherheitsprinzipalen (Anmeldungen, Benutzer und Rollen) mithilfe der GRANT -Anweisung erteilt. Berechtigungen werden mithilfe des DENY -Befehls explizit verweigert. Eine zuvor erteilte oder verweigerte Berechtigung wird mithilfe der REVOKE -Anweisung entfernt. Berechtigungen sind kumulativ, was heißt, dass der Benutzer alle Berechtigungen erhält, die dem Benutzer, der Anmeldung oder den Gruppe erteilt wurden, denen der Benutzer angehört. Durch jedwede Berechtigungsverweigerung werden alle erteilten Berechtigungen außer Kraft gesetzt.

Caution

Ein häufiger Fehler ist der Versuch, GRANT über DENY anstatt über REVOKEzu entfernen. Dies kann zu Problemen führen, wenn ein Benutzer Berechtigungen aus mehreren Quellen erhält, was ein häufiges Szenario sein kann. Im folgenden Beispiel wird das Prinzip veranschaulicht.

Die Gruppe Vertrieb erhält durch die Anweisung SELECT die Berechtigung OrderStatus für die Tabelle GRANT SELECT ON OBJECT::OrderStatus TO Sales;. Der Benutzer Jae ist Mitglied der Sales Rolle. Jae erhält über die Anweisung SELECT außerdem die Berechtigung OrderStatus für die Tabelle GRANT SELECT ON OBJECT::OrderStatus TO Jae; unter ihrem eigenen Benutzernamen. Nehmen wir an, der Administrator möchte die GRANT zur Sales Rolle entfernen.

  • Wenn der Administrator die Anweisung REVOKE SELECT ON OBJECT::OrderStatus TO Sales; korrekt ausführt, erhält Jae über seine eigene Anweisung SELECT weiterhin OrderStatus Zugriff auf die Tabelle GRANT.

  • Wenn der Administrator DENY SELECT ON OBJECT::OrderStatus TO Sales; falsch ausführt, wird Jae als Member der Rolle Sales die Berechtigung SELECT verweigert, da DENY bis Sales ihre individuelle Anweisung GRANT überschreibt.

Note

Berechtigungen können mithilfe von Management Studio konfiguriert werden. Suchen Sie das sicherungsfähige Objekt im Objekt-Explorer, klicken Sie mit der rechten Maustaste darauf, und wählen Sie Eigenschaften aus. Wählen Sie die Seite Berechtigungen aus. Hilfe zum Verwenden der Seite „Berechtigungen“, finden Sie unter Seite 'Berechtigungen' oder 'Sicherungsfähige Elemente'.

Berechtigungshierarchie

Berechtigungen einer Hierarchie aus über- und untergeordneten Elementen. Wenn Sie also die Berechtigung SELECT für eine Datenbank erteilen, enthält diese Berechtigung die Berechtigung SELECT für alle (untergeordneten) Schemas in der Datenbank. Wenn Sie die Berechtigung SELECT für ein Schema erteilen, enthält sie die Berechtigung SELECT für alle (untergeordneten) Tabellen und Sichten im Schema. Die Berechtigungen sind transitiv: Wenn Sie eine Berechtigung für eine Datenbank erteilen SELECT , enthält SELECT sie Berechtigungen für alle (untergeordneten) Schemas und alle (Enkel)-Tabellen und -ansichten.

Berechtigungen können auch abdeckende Berechtigungen aufweisen. Die CONTROL Berechtigung für ein Objekt gibt Ihnen normalerweise alle anderen Berechtigungen für das Objekt.

Da sowohl die Hierarchie über- und untergeordneter Berechtigungen als auch die abdeckende Berechtigungshierarchie für dieselbe Berechtigung gelten können, kann das Berechtigungssystem kompliziert sein. Nehmen wir zum Beispiel eine Tabelle (Region), in einem Schema (Customers), in einer Datenbank (SalesDB).

  • CONTROLDie Berechtigung für die Tabelle Region umfasst alle anderen Berechtigungen für die Tabelle Region, einschließlich ALTER, SELECT, INSERT, UPDATE, DELETE sowie einige andere Berechtigungen.

  • SELECT auf dem Schema Customers, das die Tabelle Region besitzt, beinhaltet die Berechtigung SELECT auf der Tabelle Region.

Die Berechtigung SELECT für die Tabelle Region kann also durch jede dieser sechs Anweisungen erreicht werden:

GRANT SELECT ON OBJECT::Region TO Jae;

GRANT CONTROL ON OBJECT::Region TO Jae;

GRANT SELECT ON SCHEMA::Customers TO Jae;

GRANT CONTROL ON SCHEMA::Customers TO Jae;

GRANT SELECT ON DATABASE::SalesDB TO Jae;

GRANT CONTROL ON DATABASE::SalesDB TO Jae;

Erteilen der geringstmöglichen Berechtigung

Die erste zuvor aufgeführte Berechtigung (GRANT SELECT ON OBJECT::Region TO Jae;) ist die differenziertste. Diese Anweisung ist die kleinstmögliche Berechtigung, die die SELECT gewährt. Zu ihr gehören keine Berechtigungen für untergeordnete Objekte. Es ist ein gutes Prinzip, immer die geringste Berechtigung zu erteilen, aber Sie sollten die Gewährung auf höheren Ebenen in Betracht ziehen, um das Gewährungssystem zu vereinfachen.

Wenn Jae also die Berechtigung für das gesamte Schema benötigt, erteilen Sie SELECT einmal auf Schemaebene, statt SELECT mehrmals auf Tabellen- oder Ansichtsebene zu gewähren. Der Entwurf der Datenbank kann erheblich beeinflussen, wie erfolgreich diese Strategie sein kann. Diese Strategie funktioniert am besten, wenn Ihre Datenbank so konzipiert ist, dass Objekte, die identische Berechtigungen benötigen, in einem einzigen Schema enthalten sind.

Tip

Wenn Sie eine Datenbank und deren Objekte entwerfen, planen Sie von Anfang an, wie Anwendungen und Benutzer auf diese Objekte zugreifen. Verwenden Sie diese Informationen, um den Zugriff auf Tabellen, Ansichten, Funktionen und gespeicherte Prozeduren mithilfe von Schemas zu steuern. Mit Schemas können Sie Zugriffstypen einfacher gruppieren.

Diagramm der Berechtigungen

Das folgende Bild zeigt die Berechtigungen und ihre Beziehungen zueinander. Einige der Berechtigungen auf höherer Ebene (z.B. CONTROL SERVER) sind mehrmals aufgeführt. In diesem Artikel ist nicht ausreichend Platz, um das Poster entsprechend darzustellen. Sie können das Poster zu den Datenbank-Engine-Berechtigungen im PDF-Format in voller Größe herunterladen.

Screenshot aus der PDF-Datei

Eine Grafik mit den Beziehungen zwischen den Datenbank-Engine-Prinzipalen- und Serverdatenbank-Objekten finden Sie unter Berechtigungshierarchie (Datenbank-Engine).

Berechtigungen im Vergleich mit festen Server- und Datenbankrollen

Die Berechtigungen der festen Serverrollen und festen Datenbankrollen sind ähnlich, aber nicht genau mit granularen Berechtigungen identisch. Beispielsweise verfügen Mitglieder der sysadmin fixed-Serverrolle über alle Berechtigungen für die Instanz von SQL Server, ebenso wie Anmeldungen mit der CONTROL SERVER Berechtigung.

Durch die Erteilung der CONTROL SERVER-Berechtigung wird ein Login jedoch nicht Mitglied der festen Serverrolle sysadmin, und das Hinzufügen eines Logins zur festen Serverrolle sysadmin gewährt dem Login nicht explizit die CONTROL SERVER-Berechtigung. Manchmal überprüft eine gespeicherte Prozedur Berechtigungen, indem die feste Rolle überprüft und nicht die granulare Berechtigung überprüft wird.

Das Trennen einer Datenbank erfordert beispielsweise die Mitgliedschaft in der db_owner festen Datenbankrolle. Die entsprechende Berechtigung CONTROL DATABASE reicht nicht aus. Diese beiden Systeme arbeiten parallel, interagieren allerdings nur selten. Microsoft empfiehlt die Verwendung des neueren, granularen Berechtigungssystems anstelle von festen Rollen, wenn möglich.

Überwachen von Berechtigungen

Die folgenden Sichten geben Sicherheitsinformationen zurück. Alle sicherheitsbezogenen Ansichten finden Sie unter "Sicherheitskatalogansichten (Transact-SQL)".

View Description
sys.server_principals 1 Anmeldungen und benutzerdefinierte Serverrollen auf einem Server
sys.database_principals Benutzer und benutzerdefinierte Rollen in einer Datenbank
sys.server_permissions 1 Berechtigungen für Anmeldungen und benutzerdefinierte feste Serverrollen
sys.database_permissions Berechtigungen, die Benutzern und benutzerdefinierten festen Datenbankrollen gewährt werden
sys.database_role_members Mitgliedschaft in der Datenbankrolle
sys.server_role_members 1 Mitgliedschaft in der Serverrolle

1 Diese Ansicht ist in der SQL-Datenbank nicht verfügbar.

Examples

Die folgenden Anweisungen geben nützliche Informationen zu Berechtigungen zurück.

A. Liste der Datenbankberechtigungen für jeden Benutzer

Um die expliziten Berechtigungen zurückzugeben, die in einer Datenbank (SQL Server und SQL-Datenbank) erteilt oder verweigert wurden, führen Sie die folgende Transact-SQL-Anweisung in der Datenbank aus.

SELECT perms.state_desc AS State,
       permission_name AS [Permission],
       obj.name AS [on Object],
       dp.name AS [to User Name]
FROM sys.database_permissions AS perms
     INNER JOIN sys.database_principals AS dp
         ON perms.grantee_principal_id = dp.principal_id
     INNER JOIN sys.objects AS obj
         ON perms.major_id = obj.object_id;

B. Auflisten von Mitgliedern der Serverrolle

Um die Mitglieder der Serverrollen zurückzugeben (nur SQL Server), führen Sie den folgenden Befehl aus.

SELECT roles.principal_id AS RolePrincipalID,
       roles.name AS RolePrincipalName,
       server_role_members.member_principal_id AS MemberPrincipalID,
       members.name AS MemberPrincipalName
FROM sys.server_role_members AS server_role_members
     INNER JOIN sys.server_principals AS roles
         ON server_role_members.role_principal_id = roles.principal_id
     LEFT OUTER JOIN sys.server_principals AS members
         ON server_role_members.member_principal_id = members.principal_id;

C. Auflisten aller Datenbankprinzipale, die Mitglied einer Rolle auf Datenbankebene sind

Um die Mitglieder der Datenbankrollen (SQL Server und SQL-Datenbank) aufzulisten, führen Sie die folgende Anweisung in der Datenbank aus.

SELECT dRole.name AS [Database Role Name],
       dp.name AS [Members]
FROM sys.database_role_members AS dRo
     INNER JOIN sys.database_principals AS dp
         ON dRo.member_principal_id = dp.principal_id
     INNER JOIN sys.database_principals AS dRole
         ON dRo.role_principal_id = dRole.principal_id;

Verwandte Inhalte

  • Last updated on